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?
Hi, Jeremy,
Here is the link for the template, also in the lesson: https://s3.amazonaws.com/wsp_sample_file/indexmatch_data_validation.xlsx
BB
Hiii Brad,
Thanks for your response. But I am looking for the toolkit that was mentioned in the video lesson.
Thanks, Jeremy. Can you tell me the exact time in the video (and which video) where this is mentioned, so I can see what he’s referring to? Thanks!
BB
Hi Brad,
3:50 – 4:10 in the first video
Hi, Jeremy, Thanks, got it! He is referring to the Boost add-in, which has unfortunately been discontinued. But we would recommend the Macabacus add-in which is similar, however, it is provided by a separate vendor, so we cannot provide it for free. Check out Macabacus’ website to see if they… Read more »
Thank you, Brad
You’re welcome, Francis!
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.
Hi, Wayne,
Where is it indicated that there are 3 sections precisely?
BB
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.
Hi, Inigo,
I think you mean using data tables? In that case, we recommend the data tables lesson in our Excel Crash Course or Financial Statement Modeling course.
BB
Where??
Hi, Inigo,
Those are two of our online classes, please check out our course catalogue to order one of them: https://www.wallstreetprep.com/self-study-programs/premium-package/
BB
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!
Hi, Victoria,
The native Excel shortcut we use to fill formulas to the right is Ctrl R, but it requires you to highlight the cells first using shift and the arrow keys.
BB
Thanks…
You’re welcome!
I think is practical to know written steps on the match/offset scenario.
Hi, William,
Take a close look at the formulas in the videos, then duplicate them yourself or write them down off to the side. The important thing is to grasp them conceptually, so start using them in simple scenarios first and then add complexity.
BB
Can I use the scenario manager to complete this task? Or is it preferred to use the offset/match method
Hi, Noah,
We generally prefer to use a formula like OFFSET so that our model is kept as simple and transparent as possible, and easy to follow.
BB
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 »
Sabrina:
Yes, that is correct. I would recommend just using the OFFSET/MATCH combo we taught and copy it forward to the remaining projections.
Best,
Jeff
Thanks! It worked out!
Sabrina:
Great!
Best,
Jeff
How about using the ‘index’ function instead of the offset-match function.
Julius:
Yes, that can work as well!
Best,
Jeff
Thank you for introducing this useful trick!
Cindy:
You’re very welcome!
Best,
Jeff
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!
Dong,
Our apologies – please reach out to [email protected] for this inquiry – thanks.
– Haseeb
Is there any benefit to using either INDEX MATCH or OFFSET MATCH? Thanks for the videos, very helpful tutorials.
Jacob, I prefer INDEX / MATCH over LOOKUP / MATCH combo functions when looking up information off of large tables – this technique also helps mitigate the file size from increasing as VLOOKUP/HLOOKUP functions are known to increase file size. If I am building forecasting scenarios I prefer using OFFSET… Read more »
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
Ali,
The MATCH function needs a lookup value, so probably not – maybe you can clarify your question – thanks!
Links wont download
Ali – please reach out to [email protected] – thanks!
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?
Hi Marcellus, Here we take the perspective of an investment banker and use one of the standard conventions of using an equity research report to develop our base case. The analyst that develops this report takes into account many different factors to develop his or her assumptions. But, at the… Read more »
How does financial modeling tie in with real time predictive analytics and stock market hedging techniques such as “stop-loss order” etc.?
Hi Marcellus,
These courses focus on modeling company fundamentals and do not incorporate market technicals.
Shannan
Great Tutorial! Thanks!
Glad you’ve found it helpful, Gus!