How can you use financial modeling to simulate scenarios?

Financial modelling combines the core disciplines of accounting, business, mathematics, technology (mostly Excel) and the most important and often overlooked element is lateral thinking problem solving. It’s part art (lateral thinking problem solving, communicating complexity simply and dashboard creation) and science (finance, maths, tech). A financial modeler is more than just a spreadsheeter. Their models contain scenarios, sensitivities and simulations. The build models with structure and design and errors checks and alerts with insightful dashboards.

A well-designed financial model should be flexible, user-friendly, and transparent, allowing for scenario analysis and stress testing. It should also incorporate relevant industry and market data, as well as assumptions that accurately reflect the business environment. By combining historical data with realistic projections, financial models can provide valuable insights into a company's future performance, enabling informed decision-making and strategic planning. Moreover, regular model updates and maintenance are essential to ensure the continued reliability and relevance of the output.

George E Box “All models are wrong, some are useful”. If a model doesn’t contain scenarios at the very least they are less valuable. Nobody can predict the future and all models have risk inherent in the decision they support. This risk is greatly increased without scenarios. Don’t simulate scenarios in your model and you have been warned! Beware are your forecast could bite you in the ass!🤣

The best way to simulate scenarios in a model is to create a scenario manager. This scenario manager should contain the book-ends (best and worst cases) assumptions in it. For example what’s the worst interest rate possible and what’s the best. A third scenario is also then preferable which is what is your “base” case or most likely expected assumption. All great models out there have at least 3 scenarios, base/best/worst cases. Sadly many Excel users don’t know how to create this or their model is full of hardcoding so it is rendered inflexible and impossible to build scenarios. Bad move 😜

Simulating scenarios is essential for any financial model because the dynamics and input factors keep changing. A financial modeller's job is to account for such possibilities (scenarios) within the model to make forecasts and help the stakeholders make better decisions by laying out all the possible outcomes. If a financial model is trying to predict the return of a stock over the next 3 years and we know that the stock's performance depends upon the market's performance, economy, fed funds rate, etc then as per industry standards the model must take a base case scenario, a worst case scenario and the best case scenario.

Don’t forget that financial modelling is changing quickly and Excel has improved significantly over the last 5 years. If you are still using Excel for basic maths and not embraced automation through Power Query, dynamic formulas through Dynamic Arrays and the latest enhancements through LAMBDAs you are falling behind fast! Connect with me and keep up to date 👍😁