How do y'all go about different case analysis when in Excel? Is there any easier way than either doing the whole model over again with bull and bear assumptions, or on the flip side just writing down the bull/bear with no justification in the valuation tab after running the numbers through the model once?
| +35 | Do Quantamental roles really exist? | 16 | 3h |
| +18 | Worth Joining a Small Hedge Fund? | 12 | 13h |
| +15 | Average single manager slope | 5 | 4d |
| +15 | L/S Equity Career London | 10 | 2h |
| +10 | Hedge Fund (Fundamental Long Short Equity) Compensation in Singapore Hong Kong | 2 | 1d |
| +9 | Bad PM | 5 | 2d |
| +8 | Yes, this question again… | 3 | 16h |
| +6 | P72 final round resubmitting case study | 2 | 7m |
| +5 | X | 6 | 3d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
Wait you've been doing the entire model again...lol?
Dude just google scenario analysis on excel what have you been doing lol. Xlookup or index/offset match.
Also seen people just do +-x% on their final EPS number too
Omg… use =choose()
Use the choose function bro. It'll allows you to put the differents sensitivities and see how it impact your EPS.
As someone that has reviewed modeling tests on the PE side, it’s actually pretty surprising how many candidates (mostly IB analysts) don’t know how to build a dynamic operating model with the ability to properly toggle between cases. Don’t ever let this be the reason you don’t get a job in finance please. Others have already said, but there’s multiple ways you can go about this. Pull up any financial model online and walkthrough how to implement. But seriously, look up the choose function…
bunch of rookies on here smh. Offset >>>>> choose function for scenarios toggles.
Finally someone said it
I think the answer is a bit more nuanced than you make it sound. Offset is commonly used and is dynamic, yes, but it also has its downsides imo. The added computational/performance headache when using offset in a robust model shouldn’t be overlooked (recalcs, memory, etc.). Sometimes simpler is better.
Im OP--And yes Im definitely a noob. So I see the choose() function, and I also know how to do sensitivity analysis using data tables, but I am referring to a large amount of assumptions which pertain to the bear,bull, and base cases (i.e. a bunch of operating model drivers would change depending on the case). So should I just use an if() function for all the operating line items that I am forecasting out? Willing to get flamed to know answer
I've seen multiple different versions of assumption mechanics, but I like to work with the below (simplified, but does the trick). The light grey row uses an OFFSET formula to take the relevant number based on the running case (which is a toggle). This grey row you'll then feed into your operating model.
Thanks so much!!! This helps a ton
I would do the same but using choose. Is there any reason/advantage using offset?
Pariatur quos consequuntur ex facilis. Porro omnis tenetur laboriosam omnis. Nisi sint ipsum voluptas natus. Nesciunt vel earum optio ratione vero at. Veniam ab qui laboriosam natus et.
Dolores praesentium voluptatem fuga dolor. Et quis excepturi est quas eaque est ut. Impedit impedit aut aliquid. Odit commodi aliquam error delectus consequuntur facilis.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...