Conducting a Scenario Analysis in Your Financial Model

After you’ve finished all the calculations in your financial model, do lots and lots of sensitivity and scenario analyses. Stress-testing with sensitivity analysis 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 effect 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 reflect this.
• Foreign exchange: If fluctuation in currency exchange rates will affect pricing or costings, change the inputs affected 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. Problems 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 provide your financial model the rigor and robustness to cope with the variety of fluctuations in assumptions that are possible in the real world.