I see this a lot with people starting off with financial modeling.They need to build a model for their fund,or their new business idea, or… even their personal finances!
And after a few hours… and hours spent trying in the nights and weekends, they cobble together something which is horrifying enough to be in a Freddy Krueger film.
Well, maybe not quite, but you get the picture.
Here are the 5 main ways they go wrong (obviously just do the opposite and… problem solved!)
Not using a template
There’s so much goodness contained in an Excel template, it’s impossible to list everything. OK, well since you asked, I’ll try… how about that the timeline is setup, so whether it’s a monthly, quarterly, or annual periodicity model, you can change that quickly and it saves you time. And it’s extendible.
Or – you might have cell styles already contained in the template, so you can format it easily.
You may have the call-up counters already calculated… so you don’t need to calculate which year you are in, or which forecast year, or how many days in the period… all things that you absolutely need for a model..
This doesn’t even mention the technical inputs (inputs that don’t change) that have named ranges, or cover page making it look professional, or an input page with the more basic inputs. Enough said? That’s why this is number 1.
Creating different timelines
The numbers you have for Operations are annual, but the debt repayment is quarterly… and drawdown for that expansion project is monthly… what do you do!?
Certainly don’t create different timelines – that’s a huge headache.
Likely what you want to do is weigh the need for more granularity versus the cost of more granularity.
This is really something that comes with experience, but the general principle is to create your timeline to match with the frequency of debt repayments. Why debt repayment? Well because it’s usually semi-annual or quarterly… and because it’s a large portion of the overall cost.
So the trick is to fold the other timeframes into this timeframe – so divide the revenue into quarterly or semi-annual amounts.
I would suggest to go annual periodicity if you don’t need to be too granular in your assessment, if you’re doing a Corporate Model, or perhaps a start up model. And you’re not trying to fine tune cashflow analysis.
Putting hardcodes in formulas
A best practice financial model is transparent, and flexible. Nothing stomps all over these principles like putting hardcodes in your formula.
Need to divide by four quarters in the year? Make a named range out of it, so that the formula is more transparent what you’re trying to do.
The price is fixed at $50/unit for the model duration? Make a separate input out of it, so the model is flexible and you can change the $50 to examine the effect of increasing or lowering prices… called sensitivity analysis.
Modeling too much or too little detail
I was once looking at a model of a multi-billion dollar business. Looking through the cost structure there the usual suspects, staff costs, leasing costs… good… good… and them forklifts and spares…
I squinted at this item. Why on this good green earth would we include forklifts and spares as the 3rd cost item?! It’s overall cost was <0.01% of the total cost to this business.
Modeling this sheds zero light on the business, and has close to zero impact on the business. It’s not just “redundant” and “neutral” to include, it’s a very negative thing because it communicates the modeler has not understood the value drivers of this business, and it muddies the picture to the user of the model.
The value driver is a key consideration, and that will determine how much detail to model to.
This ties in nicely with the 80/20 rule or Pareto’s principle – as a simplification it says that 80% of the benefits come from 20% of the effort. If we apply this to a businesses cost structure, what this boils down to, is somewhere between 3 – 7 items will likely account for 80-90% of the costs. And that’s probably enough detail to capture.
Let’s remember that a model isn’t a harbinger of the future. It’s an approximation, based on the known knowns. Will the future conform to that? Absolutely not. The goal is to make the approximation as close as you can. But keep in mind that humans can only hold a limited amount of concepts in their head. Yes a financial model can contain many relationships… but keep it simple.
And by conforming to the 80/20 rule, and figuring out which 3-7 items will best describe the future of revenues, or costs, or any behaviour you’re trying to predict – will give you a damn close approximation.
Not getting someone to review a model
A good test of keeping it simple, is having a fresh pair of eyes looking at something. There is so much that someone else can pick up. And especially useful if that person has modeling experience. A good financial modeler will not only be able to model something quickly and simply, they’ll be able to cut the wheat from the chaff, to quickly winnow down your model to the bare bones principles – (and yes – same as value drivers above).
They’ll also be able to test relationships in the model, and test the overall model. And cover your blindspots – perhaps they’ve got a different view of the world and their “known knowns” and “known unknowns” overlap yours in a different way.
In summary
The next time someone you know is build a model for the first time – make sure they know these things – it might just save them weeks!
