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
https://github.com/MarkWilcock/lbag-o...
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