First, let us look at units, by which we mean the units of measurement used in a model, such as pounds, pounds thousands and tonnes. I would like to begin with an example from an industry very familiar to me, energy. When modelling an energy generating asset, you will likely find that you need to calculate both the energy production (i.e. a non-financial element) and the revenue it produces. Indeed, many revenue calculations for any industry will almost certainly involve raw input drivers that are non-financial and there will be a series of steps to convert this into the pounds (for example) that you need for your financial statements output. In the screenshot below is an example of an energy revenue calculation, whereby the revenue is calculated as energy generated multiplied by the energy price.

Whoops, How did we get the answer so wrong?

What you cannot tell from this calculation is that we actually have the energy generation in megawatt hours, price in pence per kilowatt hour and we were trying to get the revenue total in pounds. We’ve all been there, it is easy when you have your head in the detail of a model build to not realise you have forgotten to divide or multiply by 100 or 1000 and the eventual output is in completely the wrong ballpark! A great way of preventing this is to create a “units” column in your inputs and calculation sheets.

At the very least, the act of needing to fill this out will require you to pause and think about the units you are working with. I would also recommend splitting out the stages of the calculation so that you convert everything into the units you want, before putting it all together in a calculation. Here is a better example of that energy calculation

Other areas with high potential for units confusion are inflation calculations, FX calculations and percentages.

When inflation comes into play in your model it is crucial that this is labelled clearly. You need the user to be clear on what terms their inputs are when entering them (e.g. is it 2019 prices, 2020 prices?) so that there aren’t any mistakes when multiplying by the inflation factor.

It is also helpful to label the stages of the calculation with real or nominal in the description where appropriate.

Similarly, with multiple currencies in a model, it is vital to label these so that they are appropriately converted to the main currency in the calculations (using a separate and flexible FX input, of course).

And of course, time-based percentages need to be clear on whether or not they are per annum or per month etc. This is a very common mistake I have seen over the years, whereby the annual rate has been used in the quarterly timeframe of the model without being pro-rated first. Another thing to watch out for is accidentally applying a part-period factor twice in a calculation (e.g. at the source input and then again on the calculation sheet).

Getting confused about your positives and negatives is also a recipe for modelling disaster.

This might seem like an obvious thing to spot, but when in the depths of the calculation sheets if you are inconsistent about your approach here, things can spiral out of control, with signs flipping around all over the place. A classic example of this is when building up a tax calculation and including “add backs” of disallowable items, especially those which are already negatives.

In general, it is best to use a logical and intuitive approach – revenues and assets (the good stuff) in positive, and costs and liabilities (the bad stuff) in negative. This is generally how everyone is used to seeing their financial statements outside of a model, so it makes sense to do the same in a model. You can tell what’s what just by looking at the statement, without needing to dig into any formulas.

The code warns against sticking to pure accounting convention with your signage (e.g. positive for debits and negative for credits), and for good reason. As much as you would relish the chance to dust off your DEAD CLIC knowledge and completely follow accounting convention throughout, you would find a conundrum when trying to display some items. For example, a debtors account where you have both the profit and cash elements.

If you followed a rule of positive debits and negative credits on your financial statements, you would show revenue accrued as a negative figure on your profit and loss statement – try explaining that one to the board! In my view, it’s ok to let go of the “rules” when it comes to these details.

My preference is to show all assets and liabilities in their relevant sign in the outputs, but to use whatever seems most sensible in the inputs and calculations as long as you keep your approach consistent within each category of items in your model. To keep things simple, you could display all control accounts (or “corkscrews” as they are sometimes known) to state a positive closing balance and then worry about the correct sign presentation on the eventual output sheet. This is what the code means by a “normally positive” rule, which means that all your inputs are entered as positives (including the costs and liabilities), making it straightforward for the user to enter the inputs.

However, as the code recommends, it is always a good idea to label your rows with the convention you are using to avoid confusion. When you have done all the hard work to build a tricky modelling calculation, you don’t want to slip up on a silly thing like signs or units. But if you follow the modelling code’s advice on approach to these then your chances of embarrassment will be minimized!