“Life is really simple, but we insist on making it complicated”
Confucius
The most effective financial models follow five common rules and as a result, are practical and useable. Their builders are seen as having the right blend of commercial and technical knowledge and inevitably become a core part of the management team. So let’s have a look at these five rules:
1. Logic is everything
The biggest mistake people make when they try to build even the simplest of financial models is that they don’t fully understand how the business they’re trying to model actually works. If you can’t explain it on a blank sheet of paper, then you’re not ready to open a spreadsheet to start modeling. Starting before you’re ready inevitably results in a model that has severe structural problems and will likely need to be rebuilt.
So before you start, take a piece of paper and map out the main drivers that affect the performance of the business you’re looking at. Include these 5 things:
If you’ve got a good grasp of the business’ operations you have the foundation to start modeling but please don’t ignore the second rule.
2. Keep it simple
Building a financial model is not your opportunity to show off your Excel formula skills. The key to building a successful model is to keep everything as simple as possible. As you build several small layers of complexity, you’ll quickly have a complex model. If any of your steps are too complex, the model will be unusable by anyone else and even you will struggle to retrace your steps next time you open it.
Make each step as simple as possible. If you can easily break a step into two parts, do it. Set your workings out in a logical manner so that one calculation leads to the next. Remember math class at school? There were no prizes for doing the sums in your head!
You need to assume that the next person to use your model won’t be you and won’t know what was going on in your head when you built it. If it’s not logical with simple steps at each stage of your calculation process, the user will quickly give up and rather than getting recognized as a modeling guru, you’ll be branded as an un-commercial technician.
3. The format isn’t just for looks
Some people might think that using a consistent color scheme, font, and cell format is a waste of time. They’re wrong. Making sure your model looks tidy with neat formatting is crucial for most people to be able to understand it. It doesn’t look neat and consistent, other users will quickly switch off and assign it to the corporate archives. Here are some tips:
If your model looks good, other users will be much more inclined to spend the time to understand it. They’ll also find it much easier to follow what you’ve done. You don’t need to be a graphic designer, just don’t let all your hard work get wasted because it looks unprofessional. This is especially important for the output section of your model and you should invest some time to think through how the output dashboard will look.
4. Structure – keeping your house in order
A financial model needs structure, just like a house needs different rooms. Your model should have three completely separate components:
Keeping these components separate is one of the golden rules of modeling. You need to resist the temptation to mix them when you are in the build phase as this will cause you major headaches later. Just as you wouldn’t mix the bathroom and kitchen in a house, don’t mix your model.
5. Deadly modeling sins
I’ve seen all manner of modeling sins in my time, but there are two that stand out for their ability to confuse users and camouflage themselves:
So here’s a thought – don’t do it. Regardless of how insignificant the formula driver is, put it on your main assumptions sheet.
It’s a poor practical joke and an even worse modeling practice. The only conceivable exception is if you’re constructing a data table. Otherwise, don’t do it.
So there you have it. Modeling isn’t that hard after all. If you understand the business you’re looking at then you don’t need a PhD in Microsoft Excel. Just keep it simple, logical and looking good. Be disciplined with your structure and don’t commit one of the deadly modeling sins. Like anything, the more you do it, the better you’ll get.
If you would like to learn more about financial modeling using Microsoft Excel, please click here to download our Financial modeling manual (developed in partnership with Avior Consulting).