Good finance and modelling practice requires a lot of discipline, patience and planning. Unfortunately, these are not qualities you’ll often find in young modellers just starting their careers. It’s usually not that hard to spot the mistakes that are the tell-tale signs of an inexperienced modeller, once you know what you’re looking for.
The warning signs are most obvious when you look at the entire workbook, and the outputs it’s trying to deliver: The financial model is often rushed and not fully thought through, superfluous calculations abound, and errors born out of a lack of internal controls and testing creep in.
Learning about how these errors are made, and how to solve them, is an important part of developing your commercial understanding and building your career. But there’s no reason why a junior modeller can’t take early action to ensure their models avoid the most obvious mistakes that could delay or even jeopardise a project – not to mention reduce the hours they would have to spend debugging a model!
Start with the end in mind
Quickly after receiving a brief, junior modellers will often make the mistake of diving straight into the work of building the calculations. It’s tempting to start solving problems as soon as you think of them. But until you’ve decided the outputs the project needs, you could be putting a lot of work in that is wasted effort, or worse, creating errors you’ll have to quash later. If you don’t know where you are heading, you are bound to make mistakes.
When coming out of a brief or at the outset of the project, the first ideas put down on to a spreadsheet should be the project’s outputs, what they look like and what questions they need to answer. Through this process, a junior modeller starts with the ultimate reader of the analysis in mind – and avoids creating calculations that don’t serve that end. It may not be as fun as diving into the detail, but it pays dividends over the long term.
Include self-checking mechanisms – and use them!
It’s a common thing to teach a lot of junior modellers to make sure the models they create contain self-testing mechanisms. Audit testing is very common and a given model could have a wide range of ‘safety nets’ designed to catch trouble early on.
The mistake people often fall into is one of flow. When the first errors appear, some confident young modellers feel certain they know the cause – and the solution required. Rather than stopping, they’ll carry on with the idea of checking back later. The obvious problem is, by the time the model is completed, one problem has become five, greatly multiplying the time it will take to work it back out.
I learnt from experience that as soon as one of your tests identifies a problem, you need to be disciplined, stop and figure out what’s going on. I recommend breaking your work down into a series of small chunks and making sure all your tests pass before moving to the next section.
It’s hard to design self-checking measures until you have put the output schedules together, so this is another reason why planning ahead is so crucial.
Separate your inputs from your functions and outputs – and don’t repeat yourself
Experienced modellers know that unanticipated results, errors and bugs are common (probably inevitable) in the modelling process. Not only will modellers make mistakes, clients will often change their mind, which is why ensuring the inevitable process of going back to make alterations or correct problems is as easy as possible.
The first step is keeping a workbook tidy. Anyone that has done any modelling has generally been taught to split their inputs from their workings and outputs rather than having them all grouped together. However, it’s still a classic error for a junior modeller to try take a shortcut and pack multiple functions into one cell, making untangling errors a painstaking task.
While it can be a time consuming process, junior modellers need to be comfortable breaking functions out over multiple lines – and ensuring functions which are used multiple times in a workbook aren’t unnecessarily repeated. Having to alter a formula in one place is far easier than having to find and replace it in four.
Excel has tens of thousands of rows, so there’s no reason to have long formulas, or to repeat a formula everywhere you need it; instead split it out over different rows and then it’s very easy to make any adjustments or to do things later.
Keep it simple, students
Starting with the end in mind, making sure you run tests and keep a tidy workbook are all lessons most modellers learn – like I did – through years of modelling experience. But even getting it right can have pitfalls.
When we tell our trainee modellers to start with the end in mind, this doesn’t mean needing to foresee every possible outcome. Sometimes we see junior modellers who have tried to present every possible ratio and return under the sun – making their jobs much harder for themselves. Being concise is a much more preferable skill to have – it keeps clients from being fixated on the wrong metrics.
Avoiding the pitfalls that beset junior modellers isn’t hard – it just takes a healthy dose of patience, clarity and thought about the end reader.
