BUSINESS INFORMATION TOOLS & TECHNIQUES

Business Information Tools & Techniques

Business Information Tools & Techniques

Task 1: Spreadsheet Auditing and Design

Introduction

This paper will be discussing the auditing design and will be presenting a report for errors. It is now widely accepted that errors in spreadsheets are both common and potentially dangerous. Further research has taken place to investigate how frequently these errors occur, what impact they have, how the risk of spreadsheet errors can be reduced by following spreadsheet design guidelines and methodologies, and how effective auditing of a spreadsheet is in the detection of these errors.

The Excel Auditor

This software is produced by BYG Software. The Excel Auditor is an add-in for Excel and, despite its name, provides many functions outside the usual scope of auditing software. The Excel Auditor provides two primary and two secondary auditing tools. The primary tools being the Audit Map, which provides a traditional audit map of a worksheet, and the Worksheet Cell Analyser, which documents the contents of a worksheet's cells. Both of these tools produce a report on a separate workbook. The secondary tools are the Cell Roots and Circular Reference Analyst functions. On the test itself, The Excel Auditor performed poorly. Unlike the test of Excel's built in auditing functions, The Excel Auditor was not tested on a cell-by-cell inspection basis. This was because a primary aim of a supplementary auditing package should be to save the auditor the time that would be taken if a cell-by-cell inspection were necessary, and also to keep the test consistent with the other supplementary tools tested in this report. Unfortunately this meant that The Excel Auditor did not perform as well as the Excel functions due to the method of testing.

As a cell-by-cell inspection was not used with The Excel Auditor, the first four errors, concerning formulae overwritten with values, whilst clearly shown on the audit map, could easily be overlooked as they would require the use of the audit map alongside the original worksheet. In this case it could be argued that it is easier to move the cursor over each cell in the worksheet and watch the Excel input bar. As a result, The Excel Auditor only achieved an 'almost-failed' result for each of these errors. The Excel Auditor also achieved an 'almostfailed' result for the 'constant in formula' error as, whilst it clearly showed the constituents of the formula in the worksheet cell analyser, it failed to warn of any potential problems with this approach. The software failed to identify any problem with the 'illogical presentation' error and whilst the documentation function showed which worksheets had protection enabled, it could not do the same for individual cells.

Part A - Error Detection Method

The general idea for achieving error detection and correction is to add some redundancy (i.e., some extra data) to a message, which receivers can use to check consistency of the delivered message, and to recover data determined to be erroneous. Error-detection and correction schemes can be either systematic or non-systematic: In a systematic scheme, the transmitter ...