Five best practice financial modelling tips to reduce spreadsheet risk

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

The additional time spent in creating a best practice financial model will be well worth it in the long run. Here are five ways to overcome problems besetting your financial spreadsheet:

1. Error checks are a must! Not only should you check source financial information (i.e. gross profit) from an external source or database, but you should re-check financial numbers calculated across your spreadsheet.
2. Time series/column consistency. As I discussed in 10 ways to improve your financial spreadsheets, a financial spreadsheet with a time series of financial information must be uniform and consistent. If the first period, January 2012, is located in column F for the Profit & Loss statement, then guarantee this is the same with the Balance Sheet and Cash Flow Statements.
3. Simplicity. Keep it simple. Avoid over-engineering your spreadsheet with complex formulae or nested IF statements. If need be, break up a complex formula or calculation into a handful of separate formulae.
4. External data. Restrict external referencing in your financial spreadsheet to select worksheets, and avoid a spaghetti soup of external references across your entire workbook.
5. No hard coding. As mentioned in Avoiding financial spreadsheet mistakes: Structure, hard-coding, the hard coding of numbers is both an audit risk and a time killer, because other spreadsheet users will spend more time at a later date trying to comprehend the rationale of the hard coded number.

So there you have it! If you can implement error checks, time series/column consistency, simplicity, avoid hard coding and restrict external data referencing to select worksheets, you will greatly alleviate spreadsheet risk in your best practice financial model.