The purpose of the article is to highlight some principles and rules that in our view help to create robust and maintainable financial models in Excel. It is based on our experience of building and reviewing spreadsheet models, ranging from simple calculations to highly complex business models. We hope it will be useful to many people who work with spreadsheets.
In the business and financial community spreadsheets are widely used. Virtually all important business decisions nowadays are made with the help of some numerical analysis that involves making assumptions and performing calculations. It is relatively easy to do calculations in Excel and financial models are often created by people who have not received any specialist training. Such models are often difficult to understand, maintain and update by other users or reviewers.
There are, however, some guidelines and principles of quality modelling that in our view are relatively easy to follow. In this article we would like to highlight these principles, explain their importance, and also point out some practical tips of modelling.
What is a financial model?
We will talk here about a financial model as a spreadsheet that calculates some numerical outputs from a set of input assumptions. This is called a deterministic model. Quite often the input assumptions are some parameters of a business or a project and the outputs are financial projections calculated for a certain time horizon.
The objective may be to calculate the net present value of an underlying project, future cash requirements or the investment payback. Most often the model will have time periods as columns, e.g. years, quarters or months, and calculations performed across them. A model is usually termed "monthly", "quarterly" or "annual", according to the shortest time interval modelled.
The main components of any given model are: input assumptions, calculations and outputs. There are no specific guidelines in relation to outputs: they should be just clear and fit for purpose. However, there are principles about how to organise inputs and build calculations. We will talk about these in turn below.
Principles around input assumptions
(1) Clearly separate inputs from calculations and outputs
This is probably the most important principle that should be followed in all models, even those that may seem too simple at the start. This is because being able to identify inputs of a spreadsheet is crucial for understanding what it represents, i.e. what its outputs are based on. It is also important to be able to change inputs easily to do "what if" analysis on outputs.
There are several ways to separate inputs:
* Use a different colour – for example, a blue font for inputs and a black one for calculations and outputs. This is usually suitable for very simple models.
* Use different areas of a single worksheet – for example, an area labelled "Inputs" at the top of the worksheet. This is usually suitable for relatively simple models.
* Use different worksheets for inputs – this is the preferred method for any model of a medium to high complexity.
It is also a good idea to use both colour and location to make inputs clear. You could also create a special style in Excel and apply it to all input cells. In that way you could change the parameters of the style at any time, and it would be automatically applied to all relevant cells.
(2) Reduce implicit assumptions to a minimum
Sometimes assumptions get built into the structure of the model. For example, an assumption that a debt repayment will start from year 3 could be implicit in having the repayment calculations start from the third time period in the model. But a better way would be to model this assumption with an explicit input so that the user can vary it if needed. This would make the formulae more complex, but the model more flexible.
Not all assumptions can be made explicit and it is important to find a balance between the model's flexibility and complexity. The main principle is that the modeller should always make a conscious decision about whether assumptions should be implicitly or explicitly modelled.
(3) Avoid using constants inside formulae
This follows from the principle of separating inputs from calculations and keeping implicit assumptions to a minimum. You should use constants inside formulae only for obvious things that never change, for example twelve months in a year. Any less straightforward constants usually warrant being separated out just to make clear the fact that they exist, for example an assumption that there are 7.3 barrels of crude oil in one metric tonne.
(4) Organise input assumptions according to their own logic, not according to the logic of calculations
You should by guided by how the user would think about particular input assumptions, not by how it is easier for modelling. For example, if you are developing a monthly model but are given some annual forecasts for certain elements, record them in the annual format on the input sheet and take care of their monthly impact via calculations.
If there are many different types of assumptions, put them on the assumptions sheet in separate small tables with their own headings. Group such tables in a logical way, using clear labels for sections and subsections. A good approach is to use indentation to make the logical hierarchy clear.
(5) Have only one input for each assumption
You should not duplicate input assumptions. Requiring the user to change a given input in many places would dramatically increase the risk of error.
(6) Specify measurement units for all input assumptions
The model user should be able to understand the measurement unit for every input assumption. Units are usually very obvious to the modeller, but he or she should not assume that everyone who sees the model over its life time will be equally informed. It is a good idea to designate a separate column for measurement units, especially if there are many of them involved. It also helps to avoid errors with the conversion of units.
Principles around calculations
(7) The logical flow of calculations should be top-down and left-to-right
If most formulae take their parameters from rows above and columns to the left, it makes the model logical and easy to follow. In other words, a person can "read" it as a book, without the need to skip pages and return back.
If in some areas of the model the top-down flow cannot be maintained, for example due to a logical circularity, repeat the relevant rows twice: the ones at the top will refer further down and have the word "circular" in the description, and the rows at the bottom will be calculated in the usual way from data above, and also have the word "circular". This will make the circularity clear to any user of the model. But always consider if it is possible to use a different approach or to change input assumptions to avoid circularities, as they usually make models more difficult to use and update. Sometimes, it is preferable to create a macro that would copy and paste values to break the circularity rather than to allow circular references in Excel.
(8) Use consistent formulae across rows
This is a very important principle that, when broken, very often leads to problems and errors. For every calculation row you should input a single formula and copy it across all the columns. You should not use a different formula somewhere in the middle of a row. Doing so increases dramatically the risk of error at the development stage and also during any subsequent updates. If inconsistent formulae cannot be avoided, the relevant spreadsheet areas should be clearly marked with colour and commented. But most likely, such situation indicates a weakness in the model design.
(9) Do not mix time periods of different length
Quite often the modeller is required to calculate short-term and long-term projections with different granularity. For example, the requirement can be to produce monthly forecasts for the first 2 years and annual projections for a number of years after that. The temptation is often to create monthly columns, followed by the annual columns on the same sheet. Such a design decision should be avoided at all costs. You would either need to remember to change formulae in the middle of every row, or would need to make the formulae very complex to take care of the different period lengths correctly. Both options are very likely to lead to problems over the model life cycle.
The recommended approach is to build the whole model using the smallest required granularity, i.e. monthly in the above case. You can always aggregate monthly columns into annual ones on a separate summary sheet. Where it is not possible to build the whole model on the smallest required granularity due to column number restrictions in Excel 2003 or earlier, use separate sheets to model time intervals of different lengths. Fortunately, there are no longer limitations on the number of columns in Excel 2007.
(10) Break up long formulae into simple pieces
You should not seek to reduce the number of cells used by trying to condense too many calculations within a single formula. Writing long and complex formulae that nobody can understand is generally bad modelling practice. You should try to split any complex calculation into smaller pieces and utilise as many rows as needed. Usually the more separate calculations you use, the easier it will be to follow and understand the model, as more logical elements get labelled and the resulting formulae are simpler.
If your formula gets lengthy because you have to repeat some part of it several times, consider creating a defined name for that part. Remember that defined names in Excel can refer not only to cells and ranges, but also can contain formulae. If such formula includes relative references, they would change accordingly, depending on where the defined name is used.
(11) Include automatic error checks
A good quality financial model would always have various error traps and checks built into its logic, which would help to ensure internal consistency of calculations and outputs. Most often such checks represent simple formulae that return zero in case of success and a non-zero value in case of error. For example, to check that a balance sheet balances across all time periods, you should add a row which would subtract total assets from total liabilities. It is a good idea to use a different formatting for that row, e.g. italic or red font. You can also apply conditional formatting, so that any non-zero values there are automatically highlighted.
A good practice is to provide for consolidation of various error checks on individual worksheets onto a summary sheet or a dash board. In this way the user can quickly check for any inconsistencies in the model from a single place.
Useful tips
We have discussed above what we think are general principles of quality modelling. Here we would like to share some tips that are not essential but which you may find useful.
1. Reserve several first columns on every sheet. It is often useful to reserve several columns on every sheet for labels and small supporting calculations so you may want to start the first data column from G or H. Starting in the same column on every sheet would also make it easier to check that references between sheets are correct.
2. Hide gridlines. We would suggest not using gridlines in any complex spreadsheets: they are usually not helpful and rather distracting. To turn off gridlines in Excel 2003 go to Tools > Options > View > Gridlines. In Excel 2007 it is under Office Button > Excel Options > Advanced > Display options for this worksheet > Show gridlines. So there is no need to colour the entire sheet white.
3. Do not use white font. You should avoid using white font along with white cell fill to hide elements. If you want to hide something, always use grouping of rows or columns. In this way, the user clearly sees that there is something hidden and can expand the group to view it as required.
4. Do not use defined names excessively. Some people believe than naming every calculation row in the model is a good practice. In reality, not only this is time consuming during development, but also makes the model extremely difficult to review. The user of such a model would need to trace precedents of every formula in order to understand where it refers to.
5. Consider putting summary rows above calculations. You may prefer putting summary rows above your main calculation rows: in this way one label could serve both as a heading and a summary. If you do that, you should change the grouping settings accordingly, i.e. untick the "Summary rows below details" option on Data > Group and Outline > Setting. In Excel 2007 this option can be found by clicking on a little arrow pointing down and right on the Outline group under the Data tab of the ribbon.
6. Consider using special tools like Arixcel Explorer. They can be extremely helpful for navigating through formulae in the model and making sure that their logic is right.
Conclusion
The power of spreadsheets is in their flexibility, but arguably this is also their main weakness. The lack of imposed structure makes it too easy to create overly complex and disorganised spreadsheets that are difficult to use and prone to error. This is especially true for financial models, where the underlying logic is complex on its own. We hope that the principles discussed in this article can serve as a guide to logical and robust modelling.
No comments:
Post a Comment