UK Gaming and Lottery Organisation
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.
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.