by Dinesh Bhola (Managing Director, DSB Financial Solutions Ltd)
While it may be easy to spot 1+1 is not equal to 3, this may not always be the case when dealing with large numbers in a multi-page report with several charts, rows, columns and subtotals.
The most logical question is…if the numbers are generated by a computer, then why are we still having a discussion on accuracy?
When conducting Information Technology audits, we typically go through a “scoping and information gathering phase” where we list out all of the key business processes and the IT systems used to support them. So while sophisticated Accounting Packages and Point of Sale software tend to feature prominently for routine data processing, most small businesses still rely heavily on spreadsheets for the tail end of the accounting cycle (i.e. for Financial Reporting). Typically, generic system reports (e.g. balance sheet, profit & loss, trial balances, aged payables, aged receivables, etc) are exported off the core systems. These then go through some tailoring and summarizing on excel to arrive at the final Financial Reports.
Spreadsheets are attractive as they are relatively inexpensive and easy to use in comparison to more sophisticated software. They are flexible allowing reports to be quickly customized to the individual needs of your business, especially when the reporting needs of management tend to change frequently.
Spreadsheets are however, prone to error as formulae may contain deliberate or accidental errors. Unauthorized changes can occur to standing data and this may be very difficult to detect. Access controls may be compromised as files can be saved without passwords and can be shared to unauthorized users via email or cloud services. In some cases, data or the entire file can be accidentally deleted all together.
It is important to note that Accuracy goes beyond simple mathematics.
Accuracy also has to do with whether the individual data elements are inputted and recorded correctly. This is of particular importance when making changes to standing data (e.g. a pricing list or vendor master file).
There is a popular saying “Garbage in, Garbage out”. An invoice with a wrong price is just as bad as an invoice which does not mathematically add down. I once came across a company with an error in their product costing which effectively caused them to set a selling price below cost giving rise to significant losses over several months.
Similarly, in the inventory receiving process, if an accurate amount of inventory is received, but the wrong item code is selected from the picking list, the system will produce the wrong results.
The use of Spreadsheets goes beyond just the Financial Reports. Important calculations such as tax computations, business valuations and employee separation packages may be done on spreadsheets using data from the accounting or payroll systems. Imagine the consequences if a Tax Computation was not properly updated for changes to taxation rates, or if there is an error in a Discount Factor calculator in a Business Valuation or if the wrong start dates, end dates or employee pay rates were used in an employee separation package.
The issues relating to inaccurate data input may arise in an environment with sophisticated accounting software and a less formalized/spreadsheet environment.
In any operating environment, it is important to have key controls over the maintenance of standing data. Such controls may include (but are not limited to):
- Access to amend standing data and formulae should be restricted to authorized personnel only
- Data Amendment Forms should be prepared and authorized prior to making the amendment
- Edit checks may then be performed by a supervisor to ensure the data on the system matches the data amendment form
- Data validation fields should be activated (e.g. reject data if it does not meet certain criteria)
- Drop down lists should be utilized to prevent incorrect data input.
- Have routine IT backups and recovery testing performed
- Access to files and applications should be protected via passwords
- Only Administrative Users should be allowed to install software
- Employee handbooks should have designated sections on IT Security and Confidentiality
In addition to the above, there are several controls that can be applied to improve the reliability of data, specifically in a spreadsheet environment.
- Protect certain cells or worksheets or entire files using passwords to prevent modification. Consider transferring all “background calculations” in a calculation sheet and have that sheet hidden.
- Clearly identify by color coding or otherwise those input cells from formula cells
- Where possible, export data from the core system to a spreadsheet to avoid transposition errors
- Have control formulae checks at various points throughout the spreadsheet to identify formula errors (e.g. IF Assets – Liabilities – Equity is not equal to ZERO, then flag to user using conditional formatting)
- Ensure spreadsheets are regularly reviewed and audited by someone with the requisite experience.
- Reduce reliance on spreadsheets all together. If practical, have the core accounting system produce most of the reports by having the Business Reporting module activated and tailored. Also, certain processes e.g. payroll may be best done using the special purpose off-the-shelf software designed by those with experience in the field. Some payroll packages are very inexpensive and may actually be worthwhile to implement when considering the amount of time spent in a manual environment on supervision, review and correction of errors. Of course, the cost vs benefits should be assessed before implementing any solution.
It should be noted that accuracy errors do occur in even in the most sophisticated accounting environment with the most sophisticated Accounting or Payroll software. So it doesn’t end if you decide to abandon spreadsheets.
The level of risk may be dependent on the type of software used. Software can be off-the-shelf or customized or developed from the ground up.
Off-the-shelf software, without the ability to customize, provide more reliance as it is likely that the software may have gone through several cycles of testing and feedback from a large number of users on an ongoing basis.
Some off-the-shelf software allow users to customize certain elements. This increases the risk of error, if the person makes an error in the customization process.
Software specially developed for a company from the ground up may have a higher risk of error as it may not have been exposed to the levels of testing that may exist in the off-the-shelf environment. Also software vendors may have live access to the production environment as part of their business support contract, which can result in unauthorized amendment or data leakage.
Companies with customized or developed software may choose to have these audited or reviewed by suitably qualified information systems auditors/reviewers.
In closing, we appreciate that even in 2016, in a sophisticated environment, as long as there is an element of manual input or intervention, accuracy will always be an issue. This risk can be mitigated by having compensating automated controls in place together with an appropriate amount of Supervision and Review. In fact, some element of Supervision and Review may always be required irrespective of the level automated technology utilized. As a small business owner, it is important to achieve the appropriate balance between technology and cost.
Dinesh Bhola is the Managing Director at DSB Financial Solutions Ltd, a company providing accounting, audits, taxation and business advisory services. The above is for general informational purposes only and is not meant to serve as a substitute for formal advice. We urge you to consult with your service provider or us if you require further advice or recommendations.
