We have reviewed many Excel based business or financial projection models over the years. The models were developed for projecting start up operations, cash flow projections to accompany loan applications and for business planning or budgeting. In reviewing these models and building our own models, we have developed a list of common problems or weaknesses with spreadsheet projection models.
The list includes:
1. Incomplete models – the models often only have income statements or income statements and balance sheets. Projection models should include all three financial statements: income statement, balance sheet and cash flow statement. The cash flow statement is the statement that ties the other two together and ensures that you do not have interlinking statement errors.
2. Failure to interlink the financial statements – the models are often prepared independently and as a result often have errors and can not be reconciled. This is the result of not being able to see the balancing effect of the balance sheet.
3. Hard coded assumptions – the models have the assumptions hard coded and do not use relational formulas and input areas in the model. As a result, it is impossible to quickly play what if scenarios with volume and pricing differentials.
4. Long formulas – the models utilize very long formulas which are difficult to interpret. The key is to use calculation areas which break the formulas into segments and allow a reviewer or other user to quickly see how the developer arrived at the final number used in the projection. Even more importantly, it simplifies the debugging process (locating errors).
The only exception to the long formula rule would be for if then statements and look up formulas.
5. Lack of an input area – the models bury key assumptions in the formulas throughout the model. Use of an input area will make it easier for you, the lender or the investor to see the assumptions together in a summary section. Failure to use input areas with all the related assumptions together minimizes the chance of failing to change all the related assumptions in the models when making changes to an assumption or playing “what if” scenarios.
6. Layout is hard to Navigate – the model is not organized properly and is built on one page or worksheet (like the original spreadsheets required before models had multiple pages). Using multiple worksheets allows the use of identifying worksheet labels and aids in the development of a separate table of contents worksheet.
7. Print formats are not set up – the model does not have the print areas and page breaks set up for easy printing. Without the proper printing setups the user will/may have a difficult time following the model on the various continuing pages with inappropriate print breaks in the middle of sections.
This list relates to the actual model and how it is built within Excel or other spreadsheet programs. Later posts will deal with the common problems with the assumptions, emphasis, supporting the assumptions and the presentation format.
Avoiding the above list of mistakes will go a long way to convincing your banker, potential investor, or your management team that the model is reliable and that you appropriately understand the model building / financial projecting process.
