5 Secrets Of Great Financial Modelling

“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:

• What drives the business’ revenue and direct costs
• Are overheads fixed or do they step up based on the scale of operations
• What type of capital equipment does the business use and how is this funded
• What type of funding does the business typically use, i.e. debt v equity
• Does the business use more than one legal entity for the business operations?

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:

1. Think about how you want people to read each sheet. You should design each page to lead the user’s eye to where you want them to start and then lead them down the page.  Try to avoid leading the user to scroll right as this breaks their train of thought
2. Headings should be clear with a soft colouring and consistent formatting. Be careful with freezing frames, only do this to freeze information that’s relevant to the whole page.
3. Think about naming each sheet. It should be logical and describe what the sheet is for.

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:

1. Input – this is where all the assumptions that drive the model are kept in a logical order
2. Calculation – this is the engine room of the model where all the workings are performed
3. Output – hey presto, the results of all your work are displayed on as many output sheets as necessary.

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:

• Using hard codes in your model’s formula might seem tempting at the time because it’s fast but it’s also a sure-fire way to make sure you’re model doesn’t work as expected. To make matters worse, finding the problem when the model is finished is like the proverbial needle in a haystack.

So here’s a thought – don’t do it.  Regardless of how insignificant the formula driver is, put it on your main assumptions sheet.

• If you ever want to play a practical joke on someone who needs to understand your model, try hiding some of the model’s text by colouring it white. Your subject’s frustration will be hilarious although when they realize what you’ve done, they’ll appropriately tell you that you’re an idiot and need to get a life.  They’d be right.

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).