Drivers of the Cost of Spreadsheet Audit

Project finance is an informal term for making loans on terms which\ include agreement that opportunities for recourse in the event of default will be limited to the assets being financed. Developed initially in the natural resources industry, project finance has in the last decade been applied increasingly to the funding of infrastructure projects, such as airports, roads, bridges, power stations, prisons, hospitals and schools. Of the projects identified by InfraNews, a news service specialising in the subject, as reaching financial close around the world in 2009, a role in over half was performed by Operis, a specialist in project finance based in London.

Decisions whether to provide more general corporate finance are typically made on the basis of financial statements. Those essentially record what has happened in the past. What is distinctive about project finance is the centrality in the credit evaluation process of projections of financial performance in the future. The projections are derived from financial models.

Operis develops these financial models, both as a stand-alone service and as part of a wider remit as financial adviser, and provides training in the development of models of this kind. It is best known for conducting formal audits of models that others have prepared. It also sells software (OAK, the Operis Analysis Kit) relevant to these activities.

Operis is therefore exposed to the issues surrounding the auditing of spreadsheets used to price, structure and illustrate substantial transactions on a significant scale. The objective of a full formal audit is to become sufficiently confident that material defects have been removed from a spreadsheet to deliver a letter, addressed to the prospective lenders, that they can rely on the figures presented to them. The liability cover provided if such an opinion later turns out to be ill-founded can run to the tens of millions of pounds.

As part of its continuous quest for improved productivity in this activity, Operis records the effort it takes to complete these reviews in a time recording system, custom designed to track not only to which projects hours are devoted, but how they are accounted for by the different tasks on the workplan. Tasks that, over a number of assignments, consistently take longer than expected from standard measures are candidates for investigation, which might lead to improved training, partial automation of the task or process reengineering aimed at doing away with the step altogether.

Operis has analysed the data recorded on its timesheets for the audits it has recently conducted. Operis has then correlated the data with the value of the transaction, as a proxy for deal size, and various measures of the size of spreadsheet.

• How large the document is in megabytes

• How many worksheets it contains*

• How many unique formulae are involved*

• The maximum and average complexities of the formulae, measured by the number of operators and functions they contain.*

The starred items were extracted using Operis’s software product, OAK v4.

The transaction values are exact for projects that were structured in Euros. They are approximate for projects involving other currencies, as they were converted to Euros at the rates prevailing at the time of writing this paper rather than the rate that actually applied when the project reached financial close; but it still gives some measure of the scale of the asset being modeled.

Also included in the database is the number of different versions, or iterations, of the spreadsheet that were reviewed. The significance of this detail is explained in section 6, Interpretation.

The database used for the analysis covers 75 assignments, concerning spreadsheets responsible for the structuring of over €54bn of financing 1 . Attention has been confined to audits completed in recent months, so that they are reasonably consistent in terms of audit process as it evolves in light of experience over time.

It is necessary to exclude some samples from the database for a variety of reasons.

• Some models have been audited already, and are submitted for re-examination because they have been adjusted to reflect some change in the deal. This can happen several

• Sometimes a standard template model is applied to a series of similar transactions. Again, the economics of the later audits are flattered by the ability to reuse earlier work.

• Some assignments are for a smaller scope of work than a full formal audit. Operis terms such exercises High Level Reviews.

• Some assignments ended prematurely because the initiative to which they were directed collapsed or was cancelled.

It

The relationship between the items listed above as logged for each assignment is set out below, as measured by R-squared. All R-squareds are below 0.2 except for the ones linking

• average and maximum formula complexity: this may be dismissed as trivial as the maximum drives the average;

• number of iterations to hour

This content is AI-processed based on open access ArXiv data.