Regulation and the Integrity of Spreadsheets in the Information Supply Chain

Copyright Cluster Seven Ltd 20 05. All Rights Reserved. Regulation and the Integr ity of Spreadsheets in the Information Su pply Chain Ralph Baxter, ClusterSeven, 10 Fashion Street, London E1 6PX rbaxter@clusterseven.com ABSTRACT Spread sheets pro vide ma ny of the key links between informa tion systems, closing the ga p between business n eeds an d the ca pability of cen tral systems. Rec ent regulatio ns ha ve brought th ese vuln erable parts of in formatio n supp ly chains into focus. Th e risk they present to th e orga nisation depends on the role that they fu lfil, with ge neric differences between th eir use as mod eling tools a nd as o perationa l applica tions. Four sectio ns of th e Sarba nes-Oxley Act (SOX) are pa rticula rly relevan t to the use of spreadsheets. Complian ce with ea ch of these sections is d epend ent on m aintaining the integ rity of tho se spread sheets acting as operatio nal app lications. Th is can b e achieved manua lly but at h igh cost. Th ere are a ra nge of comm ercially av ailable off-the-she lf solution s that can reduce this cost. Th ese may be divided in to those tha t assist in th e deb ugging of logic and m ore recently the a rrival of solu tions th at monitor th e chang e and user a ctivity tak ing place in business- c ritical spread sheets. Cluste rSeven provides o ne of these mon itoring solution s, highligh ting areas o f operation al risk whilst a lso estab lishing a databa se of informa tion to deliver new b usiness in telligence. 1. INTRODUCTION Section 2 of this paper describes the role that spreadsh eets play in business- critical operational environments, whilst Section 3 discuss es the difficulty of elim inating them. This leads to an understanding of the generic risks p resented by spreadsheets in Section 4 and the implications for the control requirem ents of SOX (Section 5). Section 6 then addresses the aspects of integrity that must be managed, with Se ction 7 describing the technical solutions that can assist. 2. SPREADSHEETS IN THE INFORMATION S UPPLY CHAIN Recent regulation has focused attention on the way th at information technology supports end- to-end business processes (for exam ple, PricewaterhouseCoopers, 2004). (In this context an end-to-end business process is seen as a set of busine ss activities that com plete the loop from the first initiation of an activ ity (e.g. a customer placing an order) to the closure of that activity (e.g. cash in the bank ). This commonly demonstrates that the ‘m arriage’ between systems and processes is not perfect (see Figure 1). Instead of one end- to-end IT system (or integrated set of systems) supporting the whole process, there are usually g aps where information is retrieved from one or more systems and manipulated before being tr ansferred to the next system in the process. Copyright Cluster Seven Ltd 20 05. All Rights Reserved. Figure 1: The Relationship between Information Sy stems and Business Processes The management of information in the gaps between f ormal systems is clearly critical to the integrity output of the inform ation chain – it being only as strong as its weakest li nk. End user computer applications, particularly spreadsheets tend to be one of the m ost pervasive support tools at these points (Howard, 2005). I t is these spreadsheets – used as operational applications (i.e. supporting regula r business tasks) – that are the main regulatory f ocus. 3. WHY DO SPREADSHEET S PERSIST? Given the large technology investm ents made by businesses it begs the question as to why they continue to use spreadsheets to support their info rmation supply chains. The reality is that central systems can only be as good as their original specifications. Since busi ness needs are always changing, the lag between specification and roll out will be at least 6 months (an d commonly much more). Hence, in prac tice, the central system s will always be out of date. Figure 2: The Evolution of Business Syst ems Functionality The difference between business needs and central systems capability (Figure 2) is most comm only fille d by spreadsheets. The number of these applications reflects the rate of evolution of business needs (more change equals more spreadsheets) and the age of, and investment in, existing systems (older systems and less recent investment leads to more spreadsheets ). Indeed, most new financial functionality is templated in spreadsheets before migration to formal systems – which may take years. It is therefore not surprising that much of the competitive edge of businesses (e.g. new financial products) is held in spreadsheet applications. Business needs Central systems Tim e Functionality Primar y Business Process 2 Primary Busi ness Process 1 Cash Central Syste ms Infrastructure Second ary Bus iness Process Second ary Bus iness Process Workbook Group Order Copyright Cluster Seven Ltd 20 05. All Rights Reserved. These aspects of business practice have been im plicitly accepted for many years. However, the growth of regulatory concern has brought them to the attention of compliance and audit functions. This can result in policies to elim inate spreadsheets through m igrati on to central systems. Inevitably, however, some are too v olatile to be immediately or too com plex to be replaced on a cost-effective basis. Other pol icies have attempted to ban the use of spreadsheets as part of business-critical activ ity – only to find that the business then does it anyway. The reality is that for many organisations elimination for any significant period of time is nearly impossible to achiev e. Other solutions must be found that can satisfy control requirements for risk m anagement without eliminating the business benefits of spreadshee ts. It is therefore necessary to understand the way in which spreadsheets are used in large organizations. 4. SPREADSHEET RISK In understanding the business use of spreadsheets it is instructive to consider the di fference between their use as a modeling tool versus their use as an operational business ap plication. In reality these aspects represent end mem bers of a spectrum but such a differentiation helps to identify the kind of risks that will exist an d the most efficient way of m itigating them. Table 1: Comparison betw een Spreadsh eet Usage as Modeling Tools versus Operational App lications Modeling Sp readsheets Operational S preadsheets User Typically b uilt and used by th e same individ ual Typically b uilt by develop ers before b eing moved a cross to o ther individuals or parts o f the busines s for usage. Persistence Models ( often extremel y complex) may be built o ver da ys or weeks, o nly to be made redundant soon after t he releva nt business deci sion has b een ma de. Models, both simple and comp lex become par t of the critica l information flow of the business. They commo nly persi st for many months or years. Structural/Functional Volatility High. T here are likely to be substantial str uctural re visions from one da y to the ne xt as m ajor elements of t he model ar e add ed or rep laced. Low to medium. All o f the key structural ele ments are likely t o be in place. Furt her evolu tion of the business pro cess will r equire maintenance c hanges – but onl y rare structural overhaul s. Data Volatility Medium. Pr imarily rela ted to the exploratio n of alternative scenarios. High. As t he applica tion is relatively mature the tr ansacti onal data be comes the key variables within the sp readsheet. Usage Likely to b e intensive for a sho rt period of time. Usage co mmo nly restricted to a single ind ividual or a small close -knit modeli ng tea m. Usage will d epend o n the indi vidual business pro cess bei ng suppor ted. Some will be hourl y or dail y. Others ma y only feat ure at we ek or month end. Usa ge wil l involve handover bet ween multiple individuals fulfillin g different tasks – not necessaril y in the same depar tment. Table 1 illustrates that the primary risks in spreadshee ts used for modeling are related to potential logic flaws in the creation of the workbook . However, in these business Copyright Cluster Seven Ltd 20 05. All Rights Reserved. environments the user comm only has a good understanding of the spreadsheet structure and of what answers ‘make sense’. Since decisions are ba sed on multiple iterative scenarios the risks of decisions based on incorrect processing are re latively low. For these reasons modeling spreadsheets receive less attention from regulators (Buckner, 2004). In contrast, the risks attached to operational spre adsheets depend on the ongoing maintenance of the logic integrity in the spreadsheet. These risks are increased by such factors as multiple users and their lack of detailed k nowledge of the spreadsheet structure. Risks are also increased where the output is the aggregation of many transactions where it is unlikely that anybody has a g ood understanding of what the ‘right number’ oug ht to be, even though it may be a key input to financia l control processes. It is for these reasons that operational spreadsheets are m uch more the focus of regulatory concern. 5. REGULATORY REQUIRE MENTS Sarbanes-Oxley represents just one face of the m ost recent focus on the operational risk and financial reporting of corporations. Whilst the specific requ irements of each piece of regulation may be open to interpretation, the overall di rection is clear: to ensure that businesses understand what is happening in their organisation; to be able to respond in a timely fashion to issues when they emerge; to have procedures in place that minimiz e the possibility of things going wrong in the first place; and, most personally, to hold the business and its key executives to account if they don’t do it. Executives will have to gain a deeper understanding of internal controls because their business decisions will be placing greater reliance on adequate internal controls and ensuring that they are deployed, maintained, ad justed and reported on as required. John Fla herty, former COSO chairman, said that this m eans “… that every division in a company needs to have a documented set of internal rules that cont rol how data is generated, manipula ted, recorded and reported …” For SOX there are four sections most relevant to sp readsheets and their controls: Table 2: SOX Implications for Operational Spr eadsheets Section Requirements Spreadsheet Implications 103 Auditing, quality control, and independence standards Independe nt auditors must inc lude an eva luation of t he Company’s i nternal co ntrols in their repo rt. T he evaluation will include a descrip tion of material weaknesses in i nternal control s and material non- compliance with the m. Un-monitore d sprea dsheets in the critica l infor mation supply chai ns will fail t his test. 302 Corporate responsibility for financial reports Executives must eval uate the effec tiveness o f internal controls ever y quarter . Financi al repo rts must include their concl usions abo ut internal controls and explain any significa nt changes to the m. All fra uds, no matter how small, must be disclosed to the Co mpany’s auditors and to the Audit Com mittee o f the Co mpany’s Board of Director s. It may be p ossible to eliminate spre adsheets temporar ily but unlikel y they can be eliminated for every quarterl y repo rt. Spreadsheets a re also a common sourc e of fraud . Copyright Cluster Seven Ltd 20 05. All Rights Reserved. 304 Forfeiture of certain bonuses and profits Accounting r estateme nts due to material noncompliance of the Co mpa ny with repo rting require ments of securitie s laws, and that are the result of misconduct, could result in the Executi ves having to reimburse t he Compan y for their bonuses, or for any profits they r ealize fro m the sa le of Co mpany securities. Spreadsheet error s have been the so urce of material financial mis-filings that would result i n triggeri ng this clause. 404 Management assessment of internal controls The SEC r equires that t he annual repor t contain an internal co ntrol rep ort, which: • States mana gement’s r esponsib ility for estab lishing and maintai ning an ad equate i nternal co ntrol structure and proce dures for fi nancial rep orting; and, • Assesses, as o f the end of the most r ecent fi scal year of the Compa ny, the e ffective ness of the i nternal control struct ure and p roced ures for fina ncial repor ting. The Compa ny’s auditor is required to atte st to and repor t on manageme nt’s assess ment of inter nal controls. Un-monitore d sprea dsheets in the critica l infor mation supply chai ns will fail t his test and are likely to r esult in qualified state ments o f control. It is clear from the risks identified above that spreadsheets have potentially significant failings against regulatory dem ands and more general tests of business control: • They are highly vulnerable to error and, occasiona lly, fraud • The information they contain and the user inte raction with them are not transpare nt to the rest of the organisation. • It takes significant time and effort to unde rstand unexpected changes and to respond and communicate them as appropriate In order to resolve these challenges organ izations must use processes and techno logy that can ensure the integrity of business critical spreadsheet s . Solutions may vary from entirely manual to strongly technolog ically enabled but all must focus on the possible causes of losing integrity of the spreadsheet output. 6. WHAT GOVERNS INTEG RITY? Full spreadsheet integrity (i.e. assurance that the ou tput is the expected processing of the input) is dependent on five key elements: 1. That the programmer of the spreadsheet logic m odel correctly understands the transactional process to be im plemented (i.e. correct specification) 2. That the programmer has created the required log ic without errors (i.e. no bugs) 3. That subsequent data inputs are valid (whether m anual or automatic) 4. That subsequent user and maintenance act ivity does not corrupt the original logic. 5. That where multiple user tasks are perform ed on a spreadsheet these are performed in the correct order. Although these challenges are relatively short to define the pervasiveness of sprea dsheets and their almost infinite flexibility m eans that solutions have taken much longer to emerge. Copyright Cluster Seven Ltd 20 05. All Rights Reserved. It is also apparent that the ‘culture’ around spread sheet within many large organizations does not contribute to avoiding or remov ing these problems. For exam ple spreadsheets are commonly seen as a temporary solution that w ill be replaced at the appropriate time by ‘proper’ investment in fully architected system s – and by im plication it is not sensible to make this investment in the spreadsheet app lication itself. This applies even in organizations where some spreadsheet models have persisted for m any y ears. It is ir onic that spreadshee ts are often viewed as a tactical solution when they are one of the longest standing parts of most enterprise information system s. 7. HOW CAN TECHNOLOGY HEL P? Several tools have been created to address the potent ial flaws in logic creation. These include Spreadsheet Professional, HMC&E SpAC E, Operis OAK, recent Microsoft Excel 2003 error tips and others. Clearly the wide variety of spreadsh eet structures and logic require ments means that none of these tools can be 100 % effective. They are reliant on looking for inconsistencies in successive cell form ulae or performing checks for logic elements that are known to be particularly error prone (e.g . nested IF form ulae). Despite the research demonstrating the prevalence of e rrors in almost all spreadsheet log ic it is clear that with (and without) logic- checking tools most organizations feel comfortable th at they can get their operational spreadsheets to be reliable at particular points of tim e (e.g. testing and audit). The next question is how to main tain this quality after the spreadsheet enters operational usage. Perhaps the most comm on solution is to impose some form of lockdown on spreadsheet change. This can be effectiv e in highly resourced environments (where developers are on hand to change, test and re-issue a revised version w ithin the timetable of business needs) or where the spreadsheet application has becom e very mature in its usage (i.e. business needs are not changing). However, in less resource- rich or mature environm ents this policy inevitably fails because it prevents the user exerc ising their own business knowledg e to resolve their own problems. As a result it is almost always circumv ented. A second option is to continue using the logic tools in the operational environm ent. However, these tools are usually inappropriate for such use as they are designed to look for log ic inconsistencies rather than track broader categories o f data behaviour. Moreover, they would have to be utilized after every user interaction (probab ly impractical) and must be interpreted by someone who understands the underlying structure of the spreadsheet (less comm on in operational spreadsheet usag e). A third option is now appearing. These are solu tions designed to specifically address operational usage of spreadsheets. C lusterSeven is one example of these new solut ions that focuses on the change m anagement and user interaction with business critical spreadsheets. I n so doing ClusterSeven can expose all of the tim e variant information contained within spreadsheets – be it data, functionality or usag e. This allows it to highlight areas of operational risk (when activ ity does not conform to expected patterns) and also to create business intelligence (such as reporting on the trends of da ta values for particular key performance indicators). Given the low take up of existing technolog y tools (com pared to the pervasiveness of Excel) it is appropriate to ask whether yet more technolog y will provide the answer. ClusterSeven believes that a number of factors are now converg ing to make this a reality: firstly auditors Copyright Cluster Seven Ltd 20 05. All Rights Reserved. and regulators are becoming increasingly vocal that the status quo is not satisfact ory; secondly the combination of logic tools and operationa l tools enables the whole spreadsheet lifecycle to be managed and thirdly there is growing acceptance (all be it grudgin g in places) that spreadsheets are here to stay, necessitating a s trategic approach to the problem . 8. CONCLUSIONS • Spreadsheets populate the inform ation supply chains of many large organizations. • As the ‘weakest link’ in the information supply chain they have become the target of regulatory concerns about financial repo rting. • Regulatory concerns can be addressed by adopting pro cesses that ensure the continuing integrity of key operational spreadshe ets. • Integrity can be maintained throug h the application of arduous m anual processes or via the assistance of technology . • Integrity also requires a shift in the culture of org anizations to see spreadsheet technology as a persistent strategic part of thei r infrastructure rather than a short te rm tactical fix. 9. REFERENCES Buckner, D (2004), Why Banks Use Spreadshee ts, Eusprig Proceedings 2004, Risk Reduction in Enterprise Com puting. Howard, P (2005), Spreadsheet Managem ent, A Briefing Paper by Bloor Research PricewaterhouseCoopers (2004), The Use of Spreadshe ets: Considerations for Section 404 of the Sarbanes-Oxley Act.