The client
UK Gaming and Lottery Organisation
The work
We were requested by the client to audit the functional integrity of the profit and loss and balance sheet 15 year forecasts, comprising of 14 different scenarios that were used for business planning. The forecasts were prepared on a spreadsheet that had grown over the years to over 20MB. Although the formulas and macros used in the spreadsheet were of medium complexity, the logic flow between different data sets and subsidiary calculations were quite complex, and the client was keen to obtain assurance that the spreadsheet, given its importance to the business strategy, was still fit for purpose.
How we helped
We undertook a detailed manual review of the logic and calculations within the spreadsheet. This included looking at all the elements of standing data and input assumptions and following these through to final output. We identified all the instances at which the data was subjected to change or manipulation and validated that this change was appropriate. In addition to the detailed manual review, we used a licensed automated spreadsheet auditing tool to identify instances in the spreadsheet that required further analysis e.g. hidden data or worksheets, cells without dependents, failed formulas, inconsistencies in otherwise cloned formulas and text cells containing numbers.
Value added
We identified a number of issues which
we discussed with the spreadsheet author
prior to the submission of the final report.
The spreadsheet contained significant
errors in the calculations that resulted in
the overstatement of profit before tax of
between £50m and £160m, approximately
10% of forecasted profit in any one year of
the period under review. Our findings were
used by the Client to amend its projections
and provide more accurate forecasts. More
importantly, the findings had significant
contractual implications in the Client’s
negotiations with its regulators.