Arguably the biggest driver of business cost due to erroneous spreadsheets, is the risk caused by human error in financial spreadsheets. There are many ways financial modellers can undermine the accuracy and credibility of a financial model. Some examples of human risk include:
- Incorrect data or formula inputs. A classic example is the use of unrealistic forecast assumptions in a valuation, or applying the wrong type of corporate valuation approach i.e. Sales Multiplier rather than an Asset Pricing approach like a discounted cashflow.
- Unskilled or inexperienced spreadsheet users. Young professionals are left to create, operate and manage an important financial model, without a reasonable level of oversight from a manager or executive.
- Too complex or cell errors. A common problem in larger corporations where the financial model is over engineered and is almost impossible to audit by third parties.
- Creating static functionality. Certain Excel features such as a pivot table, CSE (control shift enter) formulas.
- Linking to redundant files or temporary internet files.
- Financial Modelling fatigue. An array of factors like lack of sleep, suffering from stress/pressure or boredom, and not eating
Human Risk → solutions
It is paramount to adopt measures, either at the commencement of a financial model build or immediately with legacy financial spreadsheets, to immunise a spreadsheet from human errors.
Greater attention to detail
Financial modellers are only human, it is only natural for anyone to make a mistake or overlook an error. A strong attention to detail will thwart some of the errors, which plague corporate spreadsheets.
Error and alert checks
Adding checks to a financial model may appear as mundane and not worthwhile, but they help to validate numbers composed or financials updated during a financial model’s existence. Build checks to certify source financials from an accounting system, as well as cross-check subtotals or high-level totals from either detailed financial statements or the model’s dashboard summaries.
Keep it simple and easy for users to use. Avoid complex formulae, long-winded formulae (i.e. multiple IF statements), and VBA macros to create and manage a financial spreadsheet. From an audit perspective, it is better to break down a complex formula into a few separate formulae, which model users will be able to understand more clearly.
Unlike the pre 2007 version, Excel (2010) can now handle 1,048,576 rows and 16,384 columns of data, so there is capacity to build as much detail into a financial model when necessary.
Spreadsheet audit or peer review
Not an option but a must. A second set of eyes, which are also fresh, which can verify the technical features of the financial model. The audit will safeguard corporate governance; in terms of vetting model outputs are accurate and error-free. Vitally the exercise will compel a knowledge transfer, and prevent the financial model being developed, managed and owned by the one person (one gatekeeper).
Skilled professional used
A financial model must be overseen by appropriate financial professionals. Appropriate education, commercial experience and professional certification (CA, CPA, CFA etc.) are paramount, because the model developer must be able to interpret and understand financial and accounting elements of a spreadsheet.
Centralise data inputs
Avoid a spaghetti soup of random links from external files and emails. Dedicate an exclusive area in the financial model to data inputs and referencing external data sources.
Averting Financial modelling fatigue
Get some sleep! Unless you have an urgent deadline, try to avoid pushing through, but instead take a break or get some sleep. Stay calm if you are feeling stress or pressure.
Get some food into you if you are hungry, because this can greatly impact performance and bring on headaches. Switch modelling duties if you are bored. If you can’t balance the balance sheet, then turn your attention to the forecast assumptions or dashboard presentation to senior executives. If you are spinning wheels, then take a break. It is amazing how more effective you are after a time-out
Averting Human Risk
Various studies have deduced close to 90% of spreadsheets contain errors. One of the drivers of business costs totaling between $10,000 and $100,000 per error per month (Howard 2005) in due to human errors. Implementing spreadsheet checks, simplifying the financial model, employing suitable personnel, preventing human fatigue, ensuring strong attention to detail and adopting a spreadsheet review or audit, will all reduce the risk of spreadsheet errors caused by human factors.