themanager.org

Spreadsheet errors could cause actuaries to misjudge risk and rate policies incorrectly

By Adrian Miric

Insurance companies are in danger of making the wrong decisions when it comes to determining premiums or the risk value of their clients, due to errors in spreadsheet calculations.

Adrian Miric, MD of Miricle Solutions, says most insurance companies use complex spreadsheets for modelling the riskiness of their clients and actuarially determining premiums. Actuarial models are run thousands of times on spreadsheets to determine risk under thousands of scenarios for a single policy. One small mistake, be it in an assumption or the underlying data, can make a big difference.

Many actuaries are forced to comply with the Financial Advisory and Intermediary Services (FAIS) Act, which is intended to protect consumers' rights when it comes to advice from financial advisors or institutions. Advice based on erroneous figures caused by spreadsheet mistakes could land insurance and financial companies in hot water.

Miric says: "Research shows over 80% of all spreadsheets contain mistakes. The spreadsheet horror stories being revealed on a monthly basis can affect and probably already have affected South African companies."

International building materials company, James Hardie Industries, experienced a $5 million actuarial overstatement resulting from advice it received from its consulting company in relation to the level of asbestos liabilities to which it was potentially exposed. The insurance recoveries were over-discounted due to spreadsheet errors. This resulted in a $5 million overstatement in the liability the consulting company recommended relative to that which it ought to have on the valuation basis.

Other industries also suffer from spreadsheet errors, says Miric.

Canadian listed company, TransAlta, disclosed a loss of $24 million, possibly due to one of the most common sources of errors - the copy and paste function, along with the use of absolute and relative referencing in spreadsheets. TransAlta's computer spreadsheet contained mismatched bids for transmission congestion contracts (TCCs). As a result, it bought more contracts at higher prices than intended. The actual value of TCCs was determined daily, based on changing supply, load and transmission conditions.

City officials in Richmond, Virginia, miscalculated the amount of sales taxes generated at Stony Point Fashion Park during the first couple of months of operation. The mistake inadvertently inflated the figures by tens of thousands of dollars, which in turn meant the total sales estimate was overblown by millions of dollars. The city blamed the mistake primarily on an error in a spreadsheet formula, which amplified a subtotal amount.

In another case, a company projected gains of $200 million on a deal. The gains fell to $25 million when it was discovered there were misplaced parentheses in some very long spreadsheet formulas.

In the US the Sarbanes-Oxley Act has forced companies to focus on corporate governance and the controls in place throughout the organisation. It makes specific mention regarding the use of spreadsheets, particularly because they are easily accessible, changes made to them cannot be tracked easily and they are prone to error when they become even a little complex. Foreign offices of international firms are also affected by the legislation, which means that South African companies with US parents may have to improve the controls around their spreadsheets.

Miric says: "Regulators will soon start addressing the risks inherent in spreadsheets. In the US, a PricewaterhouseCoopers report already highlights the risks of spreadsheets with regard to the Sarbanes-Oxley Act.

"The only way a company can ensure its spreadsheets meet corporate governance and financial reporting requirements is to use a spreadsheet auditing tool," he says. "Even something as simple as learning to use Excel's in-built auditing features will have a big impact."

--------------------------------

* More information and free software downloads can be found at www.AuditExcel.co.za

Contact

Adrian Miric, Miricle Solutions, 011 807 6718, info@auditexcel.co.za

Usman Aly, FHC Strategic Communications, (011) 608-1228, usman@fhc.co.za