UAW Strike Impact Analysis
The Problem
In late 2023, as the UAW strikes were approaching, we needed a way to quickly understand how plant closures would affect our revenue. This information was critical for planning how to deal with the situation and minimize the financial impact. The goal was to get a clear picture of the revenue loss as soon as possible so we could make informed decisions and adjust our plans for the future.
My Solution
I created an Excel tool that calculated the revenue impact for Stoneridge based on how long a plant was striking. A user would input the number of strike days, and the tool would automatically update the total revenue impact and the specific impact for each plant. I designed the tool to be easily updatable when new IHS vehicle forecasts were released, using Power Query to automatically recognize the new data and refresh the calculations. It calculated the revenue loss by multiplying the strike days by the plant's daily revenue, which was based on the number of vehicles produced per day and the revenue per vehicle. The tool also accounted for normal plant schedules using Excel's net working days function. It was used throughout the strike, and I was awarded the Stoneridge Excellence Award for my successful analysis.
Propulsion System Sensitivity Analysis
The Problem
The goal was to create a file that shows the revenue impact of different market scenarios as the mix of ICE, Hybrid, and EV vehicles changes. We needed something that could be updated with new IHS vehicle volume forecasts and clearly show how shifts in the market would affect our products and opportunities.
My Solution
I created an Excel tool that lets users build different market scenarios for a few OEMs over the next five years, adjusting the percentage split between ICE, EV, and hybrid vehicles. The total number of vehicles stays the same each year to keep the forecast accurate. I used Power Query to make sure the data can be easily updated as new forecasts come in. This tool improved long-range planning and gave Stoneridge a better understanding of how resilient its products would be in the face of drastic market changes. With this insight, Stoneridge can adjust their product mix to remain stable and continue their efforts to be 'drivetrain agnostic' as the market evolves.
Evolving CPV Forecast Analysis
The Problem
Stoneridge uses a "content per vehicle" (CPV) metric to see how much revenue is made per vehicle based on forecasts. This is calculated using revenue data from Campfire and vehicle forecasts from IHS. In the past, this was done manually whenever new forecasts came out, but there was a need for something that could calculate CPV automatically and could compare current and past calculations.
My Solution
I created a file using Power Query to combine multiple IHS forecast files and automatically calculate the CPV metric in the background. The file features two pivot tables to compare different forecast calculations and uses a formula between them to ensure the data aligns. It also includes parameters to adjust the baseline year, baseline forecast, and time dimension columns, making it easy to update and refresh quickly. This setup provides a fast refresh time, future-proofing, and a clear method for comparing forecast calculations.