The humble Excel spreadsheet is one of the most common software tools used by the business world. Consequently, many financial models are beset by the following problems, which undermine their accuracy and reliability.
#1 Referencing corrupt files
Often caused by model users sourcing data from an email, instead of from a file saved to a hard drive. This problem can be exacerbated thanks to a spaghetti soup of data links across a workbook, which are hard to trace and make auditing particularly problematic, if the source data or file has been erroneously changed and amended.
#2 Excel properties and add-ins features are not fully activated
Perceived spreadsheet errors might appear if auto calculation is set to “manual” & “enable iterative calculation” is ticked off. Further, ensure common Excel add-ins such as Analysis ToolPak are ticked, because many Excel formulas are dependent on this feature being activated.
#3 Overly complex Excel formulas
Avoid over-engineering your financial model. Sure it might be an impressive, cool and earth-shattering financial model that you have created; but it might be very difficult for other users to verify or operate it! One key tenet of best practice financial modelling is simplicity.
#4 Inconsistent cell formatting
An easy mistake to make. It is fundamental to uphold consistent financial reporting; for example ensure all numbers are written in the thousands across the entire workbook. Further, it is important to avoid “inconsistent or misspelt naming convention of business units, projects or assets”; which can be alleviated by introducing defined cell names. Finally, be mindful with the addition of conditional cell formatting and ensure it is applied accurately and consistently.
# 5 Excel’s Date values
Be aware of the way Excel treats date values. MS Excel and Excel for Mac both support the 1900 and 1904 date systems. However, whilst MS Excel defaults to 1900 date values, outputs sourced from Excel for Mac will default to 1904 date values.
For example, financial information dated 6th July 2011 and sourced from a default Excel for Mac spreadsheet (1904 values), and pasted into a MS Excel spreadsheet (default 1900 date values) will instead appear as 5th July 2007 – a difference of 1462 days (“Change the date system, format, or two-digit year interpretation”, Microsoft Office as at 31st January 2014).
# 6 Hidden cells, rows, columns or freeze panes
Financial model developers should avoid hiding items, as opposed to grouping rows or columns. Hiding or freezing panes could cause spreadsheet users to overlook important elements, or make erroneous changes or additions to the overall workbook.
# 7 Absolute or static formula referencing
Realise the potential issues caused by applying absolute cell referencing in formulas or VBA script, because any subsequent changes to the model could render such formulas as redundant. Further, the use of static CSE (Ctrl+Shift+Enter) formulas or pivot tables will be unable to update for changes made to the underlying model.
Remember, the creation of VBA code from macro recorders will also generate absolute cell referencing.
# 8 Spreadsheet politics
Often an understated driver of spreadsheet errors, whereby conflicts occur between users that causes version-control problems, users erroneously changing assumptions or altering calculations or outputs. Politics could result from the inability to obtain buy-in for a model build to replace or improve existing spreadsheets in a company.
# 9 Embedded macros
Most spreadsheet users have only a basic knowledge of VBA code or macros. Hence, be mindful of the inclusion of macros into a financial model; because some users’ security settings on their version of Excel may not enable macros.
# 10 Complex macros or user-defined functions (UDF)
Similar to #3 Overly complex Excel formulas. This could represent a costly and time-consuming problem for a corporation, because other model users will probably be unable to audit or verify the customised and esoteric VBA macros or UDF, which underpin the entire model’s credibility or purpose.