Why every Excel model needs a Sensitivity Analysis
The uncertain nature of assumptions combined with flawed calculation logic often results in financial models being very sensitive towards particular inputs.
I am reviewing a financial model for a friend and noticed there’s no stress testing applied to any of the assumptions. Naturally, I wanted to see if the model logic is flawed somewhere.
What I mean is that not all assumptions are created equal. And what I mean by this is that we often get so deep into the rabbit hole when coming up with creative model logic that we often implement formulas that are heavily reliant on specific assumptions.
For example, we can have an assumption that gross margin will be 40% and that admin salaries (the ones that don’t go into production cost) will be 40% of total salaries. As you can probably imagine, changing one of those by 5% would change the overall picture way more than the other. And if you can’t imagine the math, I’ll show you the same in Excel in a bit.
The main point here is that calculations can end up being overly biased towards specific assumptions in our models. And the best way to ensure this is not the case and our model logic is solid is to perform various stress tests, especially with extreme scenarios. What happens if our gross margin ends up at 30%? How about 20%? What happens if our admin salaries end up at 50% of the total? How about 80%? Answering those questions is an extremely important part of finishing our model.
One very common element of a well-built model is a sensitivity analysis, or a sensitivity table. We can use Excel’s built-in Data Table functionality to show the combined effects of changes in any 2 of our assumptions. Unfortunately, spreadsheets are still two-dimensional, so showing 3 or more variables at the same time is not easy without going into complex statistics. Such a table allows us to also test the relationship between two assumptions and how they influence our model together.
I think the best way to illustrate this is to take a look at a very simple model in Excel.
What we have above may seem complex at first, but it’s fairly simple once you take a closer look. We have some assumptions for our Project X financial model. We then use those to calculate a very crude P&L for the project. At the very end, we use the WACC to discount the profits and calculate the Net Present Value (including the initial investment of $4m).
The two assumptions I want to focus on are the Gross Margin at 40% and the Admin Employees Portion at 40%. We use the Gross Margin assumption to back-solve the Cost of Sales. We then use the Admin Employees % to calculate what part of salaries (Number of employees * Average Annual Salary) hasn't been included in Cost of Sales and must go below the Gross Profit, alongside our Other Expenses.
This all looks well and reasonable, but what happens if we drop the Gross Margin to 30%? We are already at a negative NPV. As it turns out, a 10% drop in Gross Margin can turn the project into a total failure. Therefore, this is an assumption we need to scrutinize a lot more. We must be certain 40% is absolutely achievable.
On the other hand, what happens if % of admin employees jumps to 50%? We will then have more operating costs and the profits will shrink. However, even at 50%, the NPV is still almost $2m, making the project a success. While we should still make sure that 40% is a proper assumption, we won’t need so much scrutiny placed on it, as the impact of even large swings like 10% is not so devastating for the project.
And, because we asked the question above, let’s see what happens if our gross margin ends up at 20%. The project becomes a total disaster.
I hope this oversimplified example managed to show the importance of stress testing our models and pay more attention to assumptions our models are overly sensitive towards.
Changing the percentages is one way to do it. Another is to use Excel’s built-in Data Table functionality.
First, we must prepare the table structure. Below is the format that we need. One of the assumptions on the row at the top and one on the left column. We also need the value we are evaluating, in this case the NPV, which goes on the top left corner of the table.
Then we can go to the Data tab > What-If Analysis > Data Table.
Here we will select the Row and Column inputs. These are the respective variables from our Assumptions section, the two 40% figures. Remember, Row input cell is the variable we have on the top row, while Column input cell is the variable we have on the left column.
Excel will then go and essentially recalculate the entire model with the different values for both assumptions that we listed in our table. Add some conditional formatting and you get the following table.
The best way to know the table is correct is to see if the middle value (the one where both assumptions match what we have in our model) is the same as the target value (the NPV). They are both $3.026m, so our table is setup correctly.
We can now see at the intersection of Gross Margin of 30% and Admin Employees % of 40%, and we can see the same negative $803k we saw earlier.
This table allows us to quickly glance at different combinations between the two variables without having to go in and adjust our model assumptions. For example, if our Gross Margin falls at 35%, we can still be profitable if Admin salaries do not increase to more than 45% (NPV of $506k). However, if Gross Margin falls to 30%, there is pretty much no winning scenario (unless the Admin Employees are only 30% of the total).
These two assumptions are also very interconnected, as having less admin salaries means we have more production salaries, but at the same gross margin, this would mean material costs are a smaller part of cost of sales. So, the table is also showing us the relationship between the two assumptions, not only their impact on the model.
Anyway, I hope I managed to convince you to always take a few extra minutes to stress test your models, even if it’s just with a simple sensitivity table. Oh, and if you prefer a more visual tutorial, here’s a video I did a while back explaining the concept in a bit more detail. It was way back, so don’t judge the horrible color grading job I did!
Thanks for reading and for sharing the newsletter with your friends and colleagues. It means the world to me and it’s the main way I can grow and reach more people who may find value in what I have to share.
See you next week!
Best,
Dobri 🍃