Continuing under the “clarity” chapter of the financial modelling code, we arrive at the guidance on units and sign convention. When building a model, if we aren’t clear about these, we are setting ourselves up for a world of confusion, and potential modelling catastrophe!
Units
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.
Sign conventions
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.
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!
- Intro to Financial Modelling - Part 19: Wrap-up
- Intro to Financial Modelling - Part 18: Sensitivities and Scenarios
- Intro to Financial Modelling - Part 17: Calculation Techniques - Loan Calculations
- Intro to Financial Modelling - Part 16: Calculation Techniques - Flags and Masks
- Intro to Financial Modelling - Part 15: Error reduction - Common pitfalls
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.