WARNING - SPREADSHEETS IN USE

Computerized Spreadsheets have been around for many decades (way more than I have been around). And as technology evolves so do spreadsheets. The use of spreadsheets has also changed, from simple record keeping to more complex analysis and presentations.  But this increase in complexity brings its own element of risks.  In this article, we are going to look at the five most common types of Spreadsheet risk. If there are risks there are also ways to control them. So we will also look at some simple controls you can put in place to minimize these risks.

Spreadsheet Risk 1

The first risk is the risk or threat by the unskilled users. Spreadsheets can be easily changed. They may lack certain internal controls and are vulnerable to human errors.  Spreadsheet training is not just for beginners.  in fact lack of training will result in poor spreadsheets such as improper referencing and inaccurate formulas.  This, of course, will result in poor and unreliable output results.  Remember the old saying “Garbage in; Garbage out”.

The best control for this is to provide regular training to ensure users up to date with the latest versions of Excel in use.

Spreadsheet Risk 2

The second risk is the lack of guidelines for spreadsheet preparation.  If the policies and procedures for spreadsheet preparation and risks are inadequate, errors will become more common and lack of consistency will show up. So you should document style, content and accountability s in the company’s policies and you should prepare a procedure for best practice on spreadsheet preparation.

Some examples of best practice on spreadsheet design and development would include:

• Workbooks should contain a how-to or explanatory page.
• Inputs and assumptions should be kept on a separate sheet to calculations, outputs should also be on a separate sheet.
• Keep a log sheet - detailing changes to the workbook. This will help other users track and understand any changes.
• Name and number your worksheets, for example, 1_P&L, 2_Balancesheet, 3_Cashflow.
• Keep timelines consistent.  If a number of your worksheets have timelines, keep these timelines in the same place on each worksheet.
• Navigation: Keep a contents page that links to the worksheets and the worksheets should link back to the contents page.
• Keep formulas simple and at the lowest level.  Don’t be afraid to use the results from one formula as criteria or value for another formula. The lower level formula will allow other users to understand the workbooks better.
• Security covers a number of elements.  Not all employees need access to every spreadsheet.  If they do have access to spreadsheets, lock cells so that formulas cannot be changed.

Spreadsheet Risk 3

The Third risk is the use of inherited and reused spreadsheets and human errors.  Spreadsheets are often re-used and passed down within a company. But after cutting and pasting, spreadsheets may not be as robust as they were in the first instance. Formulas may get damaged, even overwritten and links broken. We are all human right! We all chat, we all take breaks and these are common reasons we make data entry errors such as skipped entries and transposed numbers.

Some good controls for this include using inputs sheets separate to that of the calculation sheet, try use data validation to restrict the user inputs.  Use control totals and batch totals to verify that results are correct. Use password protection to ensure that templates are not changed. Locking access to certain cells can also protect valuable formulas.

Spreadsheet Risk 4

Loss of data is also a spreadsheet risk.  As with any other software system, failure to back up and save spreadsheets may result in the loss of hours of data entry and spreadsheet construction.  It is easier to retrieve information from a backup file that redo the entire spreadsheet. The autosave function in excel is a reliable means for preventing loss of data.

It is also a good idea to save changes to a workbook as new versions and to keep a versions log.  This will enable you to roll back if changes made are not required.

Spreadsheet Risk 5

The final risk we are going to discuss is Fraud.  Spreadsheet fraud has caused the collapse of some companies and institutes including the entire collapse of the Jamaican Banking system in the late 1990s.

Knowing what to look out for when auditing a spreadsheet will help you uncover and identify potential risks in spreadsheets. For this reason, we are going to look at the five most common types of spreadsheet fraud.

Types of Spreadsheet Fraud

Data fraud

This is where input data is replaced by false data values. For example, excel spreadsheet links may be redirected to different data sources changing the spreadsheet outputs.  This was the basis of the $690m AIB All first fraud.  All first would not pay the $10K fee for the direct data feed from Reuters to the risk control section.  Instead, they got Rusnak to download his exchange feeds into a spreadsheet.  Rusnak then substituted links to his private manipulated spreadsheet. The total losses hidden by the fraud were almost $700m and Rusnak received exaggerated bonuses.  Just to note, this data fraud is also possible where values are manually updated.

Incremental fraud

This is seen in companies and institutes where bonuses are calculated on the value of a changing portfolio (e.g. trading). Over time the fraudster sequentially adds a small amount to cells hidden in the detail of the workbook. The incremental approach avoids sudden output changes that might generate suspicion. Over time the adjustments contribute a material difference and lead to the payment of the performance bonus. After that, the increments are then removed also on a gradual basis. By the end of the process, all evidence of the manipulation has been removed but the trader has retained their bonus. 

Burial fraud

 Here fraudulent changes are made to a key transaction in a list. The user then sorts the list using standard Excel spreadsheet sorting functions. With thousands of rows of data in the spreadsheet, these type of changes are virtually impossible to locate manually.

Function Fraud

This makes use of the extensible nature of Excel to create new functionality beyond standard cell-based formulas. It includes the fraudulent manipulation of macros or UDF (user defined functions) that are difficult for an average user to understand. In extreme circumstances, this functionality may be located on hidden worksheets to avoid discovery.

Presentation Fraud

This is increasingly common and involves modifying the way a spreadsheet is viewed.  Sometimes whole lines of data are made invisible. Negative values are formatted to show as positive values and the color of the font used is the same as the background color. This was shown in the ProQuest fraud - which resulted in a loss of market cap to the tune of $437 million. Hirth’s account reconciliation spreadsheets contained “hidden rows”. This meant that false account entries were not visible when printed in hard copy. Hirth also used “white font” in these spreadsheets. This placed false information in the white-color text so that they were invisible.

Auditing Excel Spreadsheets

There are many software auditing tools available on the markets to audit spreadsheets.  Excel its self contains some age-old functions such as the auditing tools and GOTO special.  In MS Office Professional Plus 2013, Excel also comes with Spreadsheet Analysis and Spreadsheet Compare. These tools are and an excellent aid when carrying out an audit of Spreadsheets.  Microsoft also introduced some server level controls.  One of which is the Discovery and Risk assessment software.  This software will search for all Excel (or Access) files on your network and then categorize the files by risk.  The risk is calculated using complexity and materiality tests which you can define.

You can read more spreadsheet horror stories here: http://www.eusprig.org/horror-stories.htm

Interested in learning more about spreadsheet auditing? Guess what....?  We have a course you just might be interested in....Auditing Excel Spreadsheets

SIGN UP NOW for Weekly Newsletter and get the new learn and earn activity to your inbox

Learn and Earn Activity

To earn STEEM tokens as a reward for learning on this post, in the comments section below, answer the following questions:

What risks do you think spreadsheets pose to your organization and what steps have been implemented to reduce that risk?

What steps do you take to Audit an Excel Spreadsheet?

Take a FREE course with us




TAKE FREE COURSE NOW

 

LEARN HOW TO AUDIT EXCEL SPREADSHEETS

Explore Auditing Excel Spreadsheets

 

Sign up for my newsletter 

SIGN UP NOW

BEST VALUE

Access All Areas - Unlimited Learning




ACCESS ALL AREAS

Now there is value in Learning Excel - Find out about our Excel Lean and Earn Activities



We are the first Excel Blog in the world where you can earn while you learn
 
Sign up for my newsletter 

SIGN UP NOW

---

Cross posted from my blog with SteemPress : http://theexcelclub.com/spreadsheet-risk/

---