Stress-Testing with Sensitivity Analysis
After you’ve finished the model, you can test to see if the calculations are working correctly by changing an input and seeing what impact that change has on the numbers. This technique of changing one single input in isolation is called
sensitivity analysis (as opposed to scenario analysis, which involves changing several variables at once; see the next section).
For example, after you’ve finished the cafe model in Chapter 12, you can test that the model is working correctly by changing one of the input assumptions and see- ing what effect that change have on the output of the model. Follow these steps:
-
Set your model to the base case on the IS Cash Flow page.
Note that Other Costs is $29,192 and Net Income is $7,791.
-
Go to the Assumptions sheet and change the consumables per cup in cell
B17 from $0.45 to $1.
- Stop for a moment to think about what eff you would expect this change to have on your Income Statement.
-
Go back to the IS Cash Flow page.
You’ll notice that Other Costs has jumped to $45,804 and Net Income has dropped to a loss of $3,838!
You can see that the model is working correctly, because the costs are increasing, based on the consumables cost increase, and the profit has reduced, which is what you would expect. It also shows that the model is very sensitive to changes in input costs, which is an interesting insight.
Let’s try another one, a little more drastic this time:
- Set your consumable costs back to $0.45 and make sure the model is set to the base case on the IS Cash Flow page.
- Change the pricing for large and small cups of coff from $4 and $3.50, respectively, to $0.
-
Think about what you would expect to happen in the model.
Revenue should be zero, right?
- Go back to the IS Cash Flow page and check that that is indeed what has happened.
Although you don’t actually expect the consumable costs to increase to a dollar, and you would never charge nothing for your coffee, stress-testing using sensitiv- ity analysis checks both that the formulas are working correctly and that the logic is sound.
See Chapter 8 for more on sensitivity analysis in financial modeling.
Conducting a Scenario Analysis
After you’ve finished all the calculations in your model, do lots and lots of sensi- tivity and scenario analyses. Stress-testing with sensitivity analysis (see the pre- ceding section) will check that the inner workings of the formulas and logic of the model are correct, but how realistic are the assumptions? If the absolute worst happens, what happens to your bottom line? How sensitive is your model to changes in key assumptions? This information will help to test the accuracy and robustness of your model, as well as the soundness of the business, product, or project the model is representing.
At a minimum, a financial model should include at least the following three scenarios, or at least some version of them:
» Best case: Set all assumptions to the highest possible value you can conceive as being achievable (even in your wildest dreams).
» Base case: Set all assumptions to what you actually think is going to happen. Be realistic! This is not the place to be conservative in your estimates — that’s for the worst-case scenario.
» Worst case: Set all assumptions to the lowest imaginable value that you think might happen. If everything that could possibly go wrong does go wrong, what does our model look like?
Additionally, financial models often include other scenarios to take into account possible fluctuations in inputs due to events, such as the following:
» Legislation: If changes in government legislation will have an eff on the price you can charge for your product, material supply, or additional costs such as labor, then change the inputs in your model to refl this.
» Foreign exchange: If fl in currency exchange rates will aff pricing or costings, change the inputs aff by foreign exchange in this scenario.
» Competitors: If the introduction of a new competitor to your market would cause margin squeeze (meaning that you’re no longer able to charge the same
amount for your product), you could include a scenario that shows a decrease in price.
These are just a few generic examples of model scenarios you might use. Scenarios can often flush out anomalies in the model. Look carefully at the results of your scenario analysis. Is it what you would expect to see? Compare the output results side by side. If you increase the inflation amount from 2 percent to 3 percent, do costs increase by the same margin as if you increase it from 3 percent to 4 percent?
Compared to formula mistakes, logic errors can be more difficult to spot. Prob- lems with logic may involve incorrect timing, inserting the wrong inputs and source data assumptions, or using pretax instead of post-tax inputs, for example. Sometimes the mistakes can be a combination of both formula and logic errors, and scenario analysis is a good way of identifying if these sorts of mistakes exist and flushing them out.
Thorough stress-testing, along with scenario and sensitivity analyses, will pro- vide your financial model the rigor and robustness to cope with the variety of fluctuations in assumptions that are possible in the real world.
See Chapter 8 for more on scenario analysis in financial modeling.
