10 ways to improve your financial spreadsheets

Simon Selkrig, Strategize Financial Modelling, Montreal, QC, simon@strategizefm.com

1)    Simplify them. Reduce the complexity of the Excel workbook.

2)    Don’t over engineer your spreadsheet or the formulae; avoid the use of complex formula or macros where possible.

3)    Error checks. Cross check and cross reference your calculations to ensure they are correct.

4)    Logical flow. Ensure there is a methodical flow of information/inputs, calculations and outputs across the workbook.

5)    Avoid a spaghetti soup of external references across the spreadsheet. Try to restrict all external cell referencing to only a few import worksheets.

6)    Incorporate a simple user guide in a worksheet. This will assist users of your spreadsheet to leverage its value and the time you spent building it!

7)    Data validation or drop-down boxes. Helps to “bullet-proof” your financial model from users typing in negative values or text strings in a unit volume cell, which is used to compute your gross sales based on units sold.

8)    Avoid hiding rows, columns or worksheets unless clearly stated to users. Instead use the “Group” feature, which clearly shows spreadsheet users when rows or columns are hidden.

9)    Uniformity. If the spreadsheet has a time series of data, be disciplined and consistent across all worksheets; if the first period is located in column F of the income statement worksheet, make sure this is the case in the balance sheet and cash flow worksheets also.

10) Visually and cosmetically appealing. Ensure the spreadsheet is easy for the user to digest the relevant information, without getting distracted by the fonts or other formats of the cells or worksheets.