Wall Street Prep

Quick Lesson: OFFSET MATCH and Data Validation, Part 1

View Courses

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
4 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

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 »

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.