Join 750k+ learners today. Learn online from Wall Street Prep — the training firm that prepares new hires at the world's top financial institutions.
Welcome back to the Wall Street Prep Quick Lesson Series!
In the videos below, 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.
Ready to dive in? Before starting the videos below, be sure to download this lesson’s Excel model template: OFFSET/MATCH Excel template.
OFFSET/MATCH, Video 1
OFFSET/MATCH, Video 2
Comments
What is the website to download the toolkit?
hey Brad, can you provide list of financial models from starting to ending,
Thank you.
There are supposed to be 3 sections to this course however only two are here, they are quite good lessons but speaks a little too fast so even on replay at times hard to catch the drift of what is being said.
Could you explain the analysis of scenarios with tables type {TABLE(;cell)} step by step, being “cell” a drop-down cell? You have done this in some other video but unfortunately it does not work for us as you propose. Thank you.
This is a basic Excel question but how do you copy the formula across the table like you do in the first video in minute 9 to populate the table with the formulas? Having a list of these little shortcuts would be super useful!
Thanks…
I think is practical to know written steps on the match/offset scenario.
Can I use the scenario manager to complete this task? Or is it preferred to use the offset/match method
Wow! This is excellent. Very easy to understand based on your explanation. Thanks so much.
Great introduction! Thank you! I was wondering how the column reference, which is currently 0 in the offset function can be made dynamic as there could be instances when the projections are not identical for the entire projection period. Currently there is 0, which allows to copy all values from… Read more »
How about using the ‘index’ function instead of the offset-match function.
Thank you for introducing this useful trick!
How come can not open either template or video in China on my phone? Such a pity, really like the materials, maybe can send me through other methods? Many thanks!
Is there any benefit to using either INDEX MATCH or OFFSET MATCH? Thanks for the videos, very helpful tutorials.
Great video. If you wanted different “best”/base scenarios (say 10% in year one, 11 in year two and 12 in Year 3) can this formula work without referencing the first sell in the match formula
Links wont download
Since analysis and forecasting seem to be the foundation on which the model rests, how many different streams of analyses (financial, economic, etc.) are incorporated into the forecast?
How does financial modeling tie in with real time predictive analytics and stock market hedging techniques such as “stop-loss order” etc.?
Great Tutorial! Thanks!