South African Actuarial Hard Coding Error In Excel

Following on the article that a spreadsheet error was one of the reasons for the significant drop in a fine involving Eskom and Optimum mine, another South African example has emerged involving a hard coding error in a spreadsheet based actuarial calculation.

Per iol, a actuary was responsible for R40m in overpayments made to existing members of a fund.

As per the article

The overpayments, made over a number of years, stemmed from a “hard-coding error” in the spreadsheet system the actuary had been using

Hard Coding Error in Spreadsheets

We have no knowledge of exactly what is meant by their ‘hard coding error’ but hard coding is probably the number 1 reason for errors in Excel spreadsheets. It is effectively where you add a number to a cell, either overwriting a formula or adding it to the formula. Once the number is included it will always affect the calculation whether you want it to or not.

You can read more about this issue in the Financial Modelling best practice lesson on hardcoding, and below a video clip explaining the issue for clarity.

Do your best never to hard code. Rather create a separate cell with the number and link it in. This way it should be more obvious.

For more best practice suggestions, have a look at the free section in the Financial Modelling course which lists what best practice in spreadsheets is and how it helps.