Best Practices When Building a Financial Model
The minimum objective of financial models is to be free of logical errors and calculation mistakes.
However, a few additional details separate good and ok models. In this article, we will point at several practices respected by all proficient modelers. Please take a careful look, as these principles can serve as a guideline in your future work.
First, a financial model must be flexible. It should be easy to change one of a model’s inputs, without having to worry whether all the model’s sheets will be changed accordingly. That’s why calculations must be carried out only once, and all cells in the model should be linked. Only input cells may be hard coded numbers. So, rule number one is: build a flexible model that uses cell links, instead of hard inputs.
Another important aspect is easiness to work with and to read. A financial model should be easy to work with. One way to do that is to respect a uniform structure across all worksheets. Try to build blocks that maintain a consistent column structure and in which the same year is shown in the same column. This makes things much easier. It is easier to link, copy, paste, and audit spreadsheets constructed in the same way.
We said the minimum objective of models is to be error-free, right? Therefore, ensure that no calculation mistakes have been made while building the model. The professional way to do that is to insert checks when possible. So, for example, we know Assets are equal to Liabilities and Equity, right? Therefore, we can check if this condition has been satisfied, once we’ve completed a model. If it isn’t, then we’ve made a mistake. Another example can be Income Statement calculation. If historical figures have been transferred correctly, the Net Income figure shown in the model should be the same as the one observed in the source sheet. This is how a modeler can be certain he started from the correct historical figures. Another check can be debt schedule calculation. If a loan is expected to be extinguished in a given year, modelers can build a debt schedule to see if that is the case. The important thing is you should be confident about the numbers in the model.
It is always a good idea to take a final look at the model the next day after you’ve created it. You’ll be able to see the figures much better! And that’s how you can notice there are some strange trends, figures, or projections. A sanity check does not involve inserting mechanical checks only, but also a good hard look at the numbers we’ve produced.
Our next advice is: build calculation blocks, so they can be replicated. This is an idea that co-exists well with the idea to have a uniform structure and always use the same column for a given year in your model. A calculation block is an array of cells that can be easily copied and pasted to calculate a different array of cells. Let’s provide an example. The sheet you see here is structured in a way that would easily allow me to copy and paste one of the formulas multiple times.
These items are modeled as a percentage of revenue, and therefore, by fixing cell references properly, we can copy the same formula multiple items, given we have uniform calculation blocks.
Another best practice we would like to share is to include units in the header of your tables. Third users should be able to understand whether you’ve worked in dollars, euro, or pounds. And importantly, they should be able to tell if the numbers are in thousands, millions, or billions.
And finally, try to create printable documents. Use multiple sheets and organize your tables imagining that someone will try to print them. Therefore, try to avoid huge tables, and remember, your sheets should be organized professionally.
These are some of the best practices that proficient modelers apply in their day-to-day work. Try to use them as much as possible, as these are valuable techniques that will increase the quality of your output.