In this video, I’ll show you how to integrate scenarios into financial models. We’ll do this by building a drop down menu in Excel using data validation and connecting the drop down menu to the scenario analysis using the OFFSET / MATCH function.
Click here to go to Part 2 of this Quick Lesson.
Before we begin: Get the Excel template file
Use the form below to get the Excel template file used in this lesson:
Hi,
Great stuff,
My question:
Is there any opportunity to make let’s say a kind of Compound Scenario that would comprise of e.g. subscenarios:
Revenue growth Base case;
Operating expense margin: Best case
Interest expense as % of revenue: Weak case
Tax rate: Base case
Cezary,
That’s a good idea, but I haven’t seen that done typically in financial models. We like to keep things aligned on our scenarios as a best practice – thanks!
Best,
Haseeb
At 10 minutes 10 seconds, what shortcut did you use to fill the remaining cells in row 4?
Hi, Allan,
He is using an add-in shortcut called ‘power fill right’ or ‘smart fill right’, usually Ctrl Shift R, which fills to the right quickly without having to first move the cursor. This comes with add-ins like Macabacus or CapIQ.
BB
Hello, I really liked this model as it is helping me with building my 5-year start-up model. However in one of my in of my scenarios i have a percent value of operating days based on bad weather. I would like the output of the offset to also perform a… Read more »
Frederic:
Yes, this should be possible. Give it a shot using our guidance and see!
Best,
Jeff