The Importance and Criticality of Spreadsheets in the City of London

1. The Importance and Criticality of Spreadsheets in the City of London Grenville Croll Managing Director Frontline Systems UK Ltd grenville@solver.com

2. Introduction • “How Important are Spreadsheets to Organisations” • Presented by Pat Cleary, EuSpRIG 2000 • Lack of funding so not completed, therefore as of end 2004, we still had no real notion of how important spreadsheets were or are to organisations • Personal experience in a City of London financial modelling team only gave a narrow view • Noticeable reticence of management to be open • No perceived interest in disclosure • Obvious problems with confidentiality

3. Introduction • Obtained some useful early feedback from the www.wilmott.com internet Forum • Decided to do a series of anonymous, unstructured half hour telephone interviews with 24 senior managers in the City (after MacMillan) • City is well defined, and was a natural follow up to an earlier paper documenting Spreadsheet Audit Methodologies in the City of London

4. City of London • £13 Bn current account contribution • 3% UK GDP • $500Bn daily foreign Exchange Turnover • 45% Global Foreign Equity Market • 70% Eurobonds traded in London • $275bn daily turnover in OTC derivatives • $844Bn daily futures turnover • $2000Bn per annum Metals trading • £2500Bn assets under management

5. Accountancy Banking Business Information Exchanges Finance Fund Management Government Insurance Management Consulting Maritime PPP/PFI Regulation Transnational Organisations Transport City of LondonMain Sectors

6. Auditors Accountants Actuaries Bankers Directors Insurers Lawyers Quants IT specialists Regulators Tax Inspectors Senior Managers Private Finance Professional Services Law Investment Banking Fund Management Banking Regulation Insurance Energy Government Business Services Maritime Interviewees 23 IN TOTAL FROM:

7. www.wilmott.com Original Forum Post: “….. though I have been involved with spreadsheets since 1984…….I have relatively less experience of how Excel is used in the City/Quant environment of today. If anyone could paint a picture of how Excel is used nowadays, it would be appreciated”

8. www.wilmott.com “Put simply and succinctly, despite the higher operational risk, Excel is everywhere - it is the primary front-line tool of analysis in the financial business. Most traders price deals in spreadsheets and enter them in large-scale deal capture systems afterwards”

9. www.wilmott.com “Excel is utterly pervasive. Nothing large (good or bad) happens without it passing at some time through Excel”

10. www.wilmott.com “.. if a customer wants to do a bespoke trade that cannot be handled in our designated booking system then we have to book it in a spreadsheet and add a few bips to cover operational risk capital charge (and additional maintenance). How many bips to add is subjective”

11. Defining Importance & Criticality • Six point Scale • Outlined by a Regulator • Generalised for wider use • May be adopted by regulator for future use

12. Defining Importance & Criticality • Critical Spreadsheet: if incorrect could • Compromise a government, a regulator, a financial market or other significant public entity • Cause Significant Breach of the Law • Cause Significant Breach of Individual or Collective Fiduciary Duty • Cause Possible Application of the Criminal Law • Might get jailed • Generate Significant Adverse Publicity

13. Defining Importance & Criticality • Key Spreadsheet: if incorrect could • Cause Significant Business Impact in terms of incorrectly stated Assets, Liabilities, Costs, Revenues, Profits or Taxation etc • Cause Breach of Company Law • Generate Adverse Publicity • Require Remediation through Civil Proceedings • Negligence • Breach of Contract • Might get fined • Initiate Internal Disciplinary Action

14. Defining Importance & Criticality • Important Spreadsheet: if incorrect • Could Significantly Impact the individual • Perceived job performance • Compromise Career Progression • But Limited Impact on the Business • Not all of: • Direct • Great • Immediate • Irreversible

15. Defining Importance & Criticality • Store & Retrieve • Spreadsheets used as databases • Expired • Spreadsheets over three years old, no longer required in the active management of the business • Personal • Spreadsheets used by the individual in the day to day performance of their duties where the impact of material error is low

16. Use of Spreadsheets in Professional Services firms • Findings • Extensive use of spreadsheet based financial modelling to support major commercial transactions • Many spreadsheets Key or Critical • Millions of pounds of time investment in some spreadsheets • Good error management in spreadsheets developed for clients • Less than 1,000 spreadsheets formally checked p.a.

17. Use of Spreadsheets in Professional Services firms • Concerns • No “Independence of mind” • Undue reliance on the spreadsheet commissioned by the banks or the project sponsor and checked by the firm • Oil & Gas – loss of knowledge held in spreadsheets as people retire • Key & Critical spreadsheets developed by clients not subject to same scrutiny as spreadsheets developed by professional services firms • Not clear to what extent spreadsheets used by clients for financial reporting are independently checked

18. Spreadsheets in Financial Trading and Fund Management “The whole industry is run on a spreadsheet” “Critical – couldn’t run the bank without them” “Spreadsheets are the user interface into the deal”

19. Spreadsheets in Financial Trading and Fund Management Regulator: “Spreadsheets are integral to the function and operation of the global financial system” Senior Investment Research Manager: “Agreed. In addition I would say that the majority of people who use the financial system are not appreciative of the role that spreadsheets play”

20. Spreadsheets in Financial Trading and Fund Management • Used to Value Financial Instruments of all types • Used to strategically run large institutions • Prime data source for complex transactions • Key data/information manipulation tool • Main mechanism for reporting capital adequacy • Most heavily used in the more innovative ie “more risky” parts of the market • Used to guide/make decisions on what to trade • Used to calculate fund’s daily P&L

21. Spreadsheets in Financial Trading and Fund Management • Spreadsheet users almost all self-taught • No wide appreciation of spreadsheet error risk • Spreadsheets near, at or beyond limits • Millions of untested uncontrolled spreadsheets • Endemic denial of the use and extent of use of spreadsheets at top management level • Little discernable interest in Spreadsheet Error Risk & related issues

22. Conclusions • Some parts of the City don’t use key & critical spreadsheets much – law, maritime • Some parts of the city use key & critical spreadsheets a great deal, and manage them well – professional services, clearing banks, PPP/PFI, limited recourse finance

23. Conclusions • Large parts of the City are critically dependant upon untested key and critical spreadsheets • Large parts of the City are almost unaware of the risks to which they are exposing themselves – financial trading, fund management, investment research, corporate financial reporting

24. Conclusions “If the uncontrolled use of spreadsheets continues to occur in highly leveraged markets and companies, it is only a matter of time before another “Black Swan” event occurs causing catastrophic loss”

25. Conclusions “It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution, significantly damaging the City of London’s reputation”

26. Recommendations • Identify, Prioritise, test, correct and control Key & Critical Spreadsheets to improve profitability • Improve Regulatory framework • Promote the emerging discipline of Spreadsheet Engineering • Train Management & Staff • Deploy software tools to assist • Develop next generation spreadsheet technology