background
Wall Street Prep

OFFSET MATCH and Data Validation, Part 1

Learn Online Now

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:

Submitting ...

Comments
guest
6 Comments
most voted
newest oldest
Inline Feedbacks
View all comments
Cezary
Cezary
October 1, 2018 5:31 am

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

Haseeb Chowdhry
Haseeb Chowdhry
October 8, 2018 5:58 pm
Reply to  Cezary

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

Allan
Allan
February 3, 2023 1:52 pm

At 10 minutes 10 seconds, what shortcut did you use to fill the remaining cells in row 4?

Brad Barlow
Brad Barlow
February 5, 2023 8:30 pm
Reply to  Allan

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

Frederic Labrosse
Frederic Labrosse
May 10, 2020 12:02 am

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 »

Jeff Schmidt
Jeff Schmidt
May 10, 2020 10:39 am

Frederic:

Yes, this should be possible. Give it a shot using our guidance and see!

Best,
Jeff

Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.