background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for May 2024 for May 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for May 2024 is Open
Wall Street Prep

OFFSET MATCH and Data Validation, Part 1

Last Updated September 17, 2022

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
6 Comments
most voted
newest oldest
Inline Feedbacks
View all comments
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
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
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
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
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
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

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.