Rant: "Modelling" Junkies
FFS
To any analyst and associate who needs to hear this:
A model is NOT supposed to be a software. Unless you are working on a large, complex, multi-billion $ transaction with various moving parts, standardised xls files constantly feeding through, huge portfolio of projects/assets (and often even then); you VIRTUALLY NEVER should have 20 lines of formula in a cell (literally seeing this right now) with all sorts of logics that you think makes it bullet-proof (yet entire model breaks if I add a row at the bottom of a tab or turn an input to zero).
These models you make hoping they'll become an amazing template for the next person are often not as flexible as you think, and the next person will have to spend more time undoing your BS than you think.
- Just hardcode the fucking inputs that will not change every day.
- Just link straight to the correct sheets instead of using =INDIRECTs (oh please do NOT use Indirects unless absolutely necessary)
- Just break down big formulas into multiple rows/columns
- Use =SUMIFs and sumproducts more often than index match and avoid Lookups unless you have to (too long and pain to trace)
- Use (x=y) instead of nested IFs unless you have to (seriously who tf enjoys combing through huge nested if formulas to figure out which fckn case is running??)
- Stop creating reference chains to the same thing. I should not have to 'trace precedents' 6 times to find the source. Link as close to input source as possible. I could have written a book with all the time saved from wasting time on that shit while auditing models.
- Stop using shit like EDATES where you can be using EOMONTH (you're not an accountant). I'm sorry I've never taken one of those 1000 excel modelling bootcamps but wtf are they teaching you?
- Generally just stop complicating your life.
For associates/seniors who review models:
- Don't just stress test the model, encourage and teach your juniors to simplify things / dumb the model down.
- Handing your new analysts a stupid page of excel shortcuts is not excel training. Teach them best practices like some of the crap above
Modelling isn't tough, don't make it so and stop glorifying unnecessary complexity. All the scenarios in your head you're trying to build flexibly for, won't happen. If they do, a clean model should make it easy to change/copy through.
Stop trying to be scientists, its a fucking cash flow - just set it up.
If you are absolutely new and building a model from scratch, my advice is: create the output first. Hardcode dummy values. Look at what levers you need to assume to move things around, and go from there. Build what you need to drive the output.
Finally, keep your models CLEAN. don't have inputs all over the place, side calcs feeding into outputs (rush hours are fine but fix it afterwards). These are the things that define a good, reliable excel monkey - not the number of tabs for your candy shop LBO. We're here to close deals, not win stupid prizes.
Kthx
Super endorse this. Personally speaking, I tended to over complicate my models too and a big part of the learning process was realizing I can do the same with less
hahaha!
Completely agree with all of that - specially trace precedents. Have lost count of the number of times I’ve had to trace something back 8-10 times only to lose track of where I started from or what I was actually trying to look at
Pin this
can you explain the "use (x=y)" point please?
instead of using a bunch of nested ifs, you can use (x=y) for a variable which will return a 1 for true, or a 0 for false that you can multiply against the base formula to either turn it on or off, much cleaner to audit
It's just the use of Boolean logic i.e. Trues and False (1s and 0s) to determine what values pop up when. For example, say your 3rd row is monthly dates starting from Apr-26 and in the 5th row you want a value (say £1m stored in cell A1 to show up on a specified date (let's say 31-Dec-2026, stored in cell B1), then you just build the formula in A5 as:
=(A$3=$B$1)*$A$1
and then just copy it across the 5th row. Hence when the date (in row 3) equals the specified date (B1), it will return the specified value (A1), so only cell I5 will show £1m. The difference between this and an =IF might seem minor but it helps structure several situations better, has less text, and also easier to audit. At least in my opinion.
thank you for the helpful explanation
Agreed on most, but am a vehement defender of index(match. But agree w/ the moral of the story...Using overly-complex formulas doesn't add inches to your dick. That said, index(matches, indirects, and offsets can make it much quicker to put something together and no issues using it for those purposes.
Veniam labore enim nihil odit laborum. Tempora consequuntur a beatae ad quas quis. Inventore ut cumque aut porro reiciendis.
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...