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:
You’re logged in! Form submission not required. Access your file below.
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
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 calculation function to give the number of days, for example, X-(X*P%) when I pick either array: Best, Base, and Weak. Is this possible to be done. If so can you explain it to me.
X=180
P= percentage varies depending on the assumption, 10%,19%,12%,10%,50% etc in cells.
Operating Days ( 30 Days X 6 months) 180
Bad weather days
Best case 10.0% 19.0% 12.0% 10.0% 50.0%
Base case 25.0% 25.0% 25.0% 25.0% 25.0%
Weak case 50.0% 50.0% 50.0% 50.0% 50.0%
Frederic:
Yes, this should be possible. Give it a shot using our guidance and see!
Best,
Jeff
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