Scenario Analysis of a Financial Model in Excel

Scenario Analysis answers what-if questions – as well as the expected case, what is the possible upside and how bad could things get? In this tutorial, we start with a simple financial model, the income statement of a fictitious company. The net profit depends on three variable factors: the inflation, interest and tax rates. We look how the net profit varies over the best and worst cases as well as the base case. The tutorial demos two approaches, a simple switch between scenarios and a data table to show a results grid. Along the way, the demo shows a few useful Excel techniques and provides hints and tips about how to build a robust financial model in Excel.
If you want to follow along, download the start and final spreadsheets from

Timestamps
00:00 Introduction to the case study and model
02:06 Assumptions of the financial model.
03:21 Structure and layout of the sheet.
03:50 Build the income statement
05:55 Switch between the best, central and worst cases
07:35 Add a data table to show results grid of two factors
09:50 Calculate the probability weighted expected value of net profit
11:15 Sneak preview of next videos: Sensitivity and Monte Carlo Analysis in Excel and Power BI
12:12 Conclusion

[aungsawtun]

Thank you indeed for sharing valuable information.Thanks again.

[frankliu]

Hello, the tutorial is full of valuable info. Do you share the excel template?

[funmilayoadabanija]

Thanks for the insightful tutorial, i learnt alot from it. I can't seem to find the next tutorial which is going to focus more on sensitivity analysis, monte carlo analysis, and Power BI visualization. If this has been posted, i would appreciate it if you respond with the link.

[barttitulaerexcelbart]

Very good explained, lots of small techniques. I like that you used the default cell formatting for input, calculations and check (although I don't like orange even if I am Dutch...😉). Small tip: if you have a visual distance in large models, please use hide rows, or even better group by, to see the input and output in one screen. Looking forward to your Monte Carlo, especially in Power BI...

[azizquazi]

Hi, was there any follow-up video of the sensitivity analysis? Looks like, I cannot find this video. Thanks.