I have an excel model with sticky ifs and bunch of data tables (don't ask why, just a ton of permutations on various aspects of the business). One data table is used to cycle through 2 sets of cases to update the sticky if outputs all at once.
When I had kept the cases to 50 cases, the sticky ifs were still updating tho with some lag.
I was asked to add a bunch of sensitivity analyses and had to add another 30 more cases to run through my sticky ifs... Somehow after adding those sensitivities in, I think I broke my model..
The sticky ifs are not sticking anymore and I have to toggle the case manually to the cases I need to show on my output which is a kind of a pain and makes me nervous.
Anyone has had a similar issue / know how to fix this? I know mixing data tables and sticky ifs are not best practice but it's been working up until this one change and then I think I broke my model.... ugh..
You're right, mixing the two is a terrible idea. I'd turn your calcs to manual and model based on that. Running data tables on sticky ifs is tricky as sometimes it may simply not work for reasons I can't explain as I've dealt with that nightmare before but I've found making sure that the calcs only run when you want them to for these highly iterative functions tends to help a lot. Smash that F9 when you're looking for your output
Ah thanks! This solved some of the problem (works now on some sheets but not others lol, resigned to manual toggle for those)
If you build the model right you shouldn’t need both. My recommendation is to revamp your assumptions tab so that you have all of the possible scenarios in one place and then run the data table sensitizing your desired outputs (IRR, Yearly Cash Flows, etc) against the different cases. If you have to run permutations of a particular case, start a new sheet only for other sensitivities and have it link back to a sensitivity case on your assumptions without running the “master” data table through it
When done right, all outputs should be referencing the master data table and the only things you’ll have to deal with are adding/changing cases
Not sure if I understand what you mean but I think I think I'm doing this already, except with 2 sets of cases and using a 2 way data table instead of 1 way.
Basically one toggle for revenue cases and one toggle for expense cases. I thought about having only 1 case toggle to be cleaner but imagine if you have 5 revenue cases and 5 expense cases and you need to show an output for each of those permutations. With just 1 case toggle that would be 25 cases... vs with 2 case toggles it would be 5 for each.
And the outputs are not really sensitizable as it's not a straight LBO with IRR calcs but more of an operating build where each case can change multiple variables... I know senior ppl likely to say can you run this sensitivity changing XYZ but in reality its not as simple as a 2 way data table, its literally creating new cases with various permutations. Worse still if they want to change the logic of the build and I now need to figure out how to make the projection methodology dynamic. Not sure if anyone has had this problem before. (Also just tired from having to run so many cases and permutations...... making my head spin and makes me hate my job so much lol)
What's a sticky IF
A self-referencing if statement... eg cell B5 says =IF(B2 = 5, Y, B5)
It only calculates when the statement returns True, otherwise it saves the previous value. Often combined with a data table that runs through each case so you can run them automatically instead of iterating manually.
Super helpful for iterating through multiple complex cases without having to build out the model multiple times, but unfortunately it also uses a ton of memory, so combining it with data tables (also memory-intensive) can slow your model down and cause issues
how is the above example not a circular reference if B2 <>5?
Let’s say B2 is your model case and Y is the LBO IRR. The sticky if let’s you show the model case 5 LBO IRR regardless of what case the model is currently set to. If you’re trying to compare a bunch of different scenarios, using the sticky ifs you can quickly toggle through the cases to update and compare.
Modi ipsa autem beatae quae. Nisi explicabo ad eos quod.
Libero ut quo explicabo harum in repellendus explicabo. Laboriosam quae magni eum sunt. Dignissimos architecto occaecati asperiores quia. Aut debitis autem aut voluptatibus blanditiis molestias amet. Fugit cupiditate iure et. Amet cum et in blanditiis iure id.
Adipisci accusantium occaecati facilis non soluta voluptatem nulla. Est laborum sint quo ut quisquam exercitationem. Possimus est optio rerum qui accusamus quis beatae.
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...