Strategies for Addressing Spreadsheet Compliance Challenges

Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 17 S trategies for Addressing S preadsheet Compliance Challenges Microsoft Corporation 1 Microsoft Way. Redmond WA 98052 bweber@microsoft.com ABSTRACT Most organizations today use sprea dsheets in some form or another to suppo rt critical business processes. However the financial resources, and developmental rigor dedicated to them are often minor in comparison to other enterprise techno logy. The increasing focus on achieving regulato ry and other forms of compliance over key t echnology assets has made it clear that organizations must regard spreadsheets as an enterprise resource and account for them when developing an overall compliance strategy. This paper provide s the reader wit h a set of pract ical strategies for addressing spreadsheet compliance from an organiza tional perspective. It then presents capabilitie s offered in the 2007 Microsoft Offi ce System which ca n be used to help customer s address compliance challenges. 1. INTRODUCTION Because of their ease of use, flexibility, and power, spreadsheets support many critical business functions, and often fill roles where other solutions would be too slow or costly to implement. As a result, they have quietly becom e a key component in the analysis and reporting processes within most organizations, in cluding the mission critical area of financial reporting. In the US and around the world, there has been increasing focus on demonstrating regulatory compliance especially within corporate financial processes. This is due in part to new legislation, such as Sarbanes-Oxley, as well as an increased public scrutiny of corporate accounting practices, and has highlighted a need for stricter controls over the analysis supporting financial statements. Because spreadsheets are an enterprise resource that support key business processes, it is important to determin e how they fit into an overall strategy for regulatory compliance. This paper will provide the reader with a set of practical strategies for addressing spreadsheet compliance from both an organizational and technological level. While these strategies focus on financial analysis and reporting scenarios, they are not regulation specific and can be applied to spreadsheet environments across domains and industries. 2. REGULATORY COMPLIANCE 2.1. Background Regulatory compliance is now, more than ever, a top of m ind issue for organizations around the world. One area in particular that has r eceived much scrutiny in recent years is financial compliance, where new legislation has been wr itten to ensure that organizations’ financial analysis and reporting processes are both transp arent and accurate. The three most visible examples of this legislation are the Sarbanes- Oxley Act (United States, 2002), Data Protection Act (European Union, 1998) and the Basel Capital Accord (Basel II, 2006) which together, affect most publicly traded companies. Corpor ate finance, however, is just one of many areas Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 18 where compliance policies have been defined a nd enforced. The pharmaceutical and health industries, for example, have been subject to strict regulation for years. Compliance policies may also vary based on the location of the or ganization, with regional governm ents often requiring their own set of controls. An orga nization must take into account all applicable policies and requirements when developi ng a regulatory compliance framework. 2.2. Spreadsheets: An enterprise software resource Though they may not be thought of in the sa me manner as database or custom software systems, spreadsheets are a key enterprise asset for most organizations. In the words of auditor PricewaterhouseCoopers, spreadsheets are “an integral part of the information and decision-making framework for companies” i . However, evidence has shown that in some organizations there is the general perception that spreadsheets are a tactical tool without strategic importance. As a result, the resources dedicated toward the implementation and control of critical spreadsheets are small in co mparison to other information technology assets. These disparities represent the most significant ro ad block to spreadsheet co mpliance. Before controls can be implemented and enforced, ma nagement m ust acknowledge spreadsheets as a critical enterprise resource then budget and plan accordingly. 2.3. A process challenge One common misconception in organizations is that the solution for spreadsheet compliance is technology. While technology plays a role in any compliance strategy, the most important component is process. Critical spreadsheets, like other enterprise IT resources, require sound development and usage practices that include controlled testing, deployment, maintenance, and use. An effective plan will incorporate these steps into the larg er compliance framework for spreadsheets and other enterprise resources. The points listed below are prerequisite to developing such a plan. Executive buy-in The need for compliance policies must be recogni zed at all levels within the organization. Cooperation between the management of functio nal departm ents is im portant to defining robust controls that are in line with business objectives. Without executive-level commitment, the collaboration needed to create a broadly en forceable compliance strategy will be difficult if not impossible to achieve. Getting IT and business users on the same page A compliance strategy must take into account th e needs of the business and its users in order to be successful. A plan that fails to do so will come at the expense of business productivity and not be sustainable. This problem can be avoided by engaging users from the start when developing a compliance plan. User input is pa rticularly important when defining spreadsheet controls, as members of the business team ofte n serve as both the user and developer of critical spreadsheet applications. Allocate appropriate resources Implementing an effective compliance strategy takes time and effort. Human resources must be allocated from many different groups in or der to define controls that meet business objectives. These often include the IT, internal audit, and finance departments, but can involve others depending on the needs of the organization. Additionally, it may make sense to use software to facilitate the monitoring a nd controlling of spreadsheets, which can require financial and development resources as well. Once implemented, the control processes must be monitored and enforced by dedicated pe ople with an understanding of the compliance strategy. Every Situation is Unique Every organization is unique in how they use technology in their business, and each has its own set of challenges and goals to consider wh en developing a compliance framework. As a result there is no single prescriptive compliance so lution that satisfies the needs of all. An Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 19 effective strategy will take into account the operational requirements, business objectives, and specific regulations with which the organization must comply. The following section offers some specific steps for identifying and contro lling critical financial spreadsheets, but these strategies can be adapted to meet the particular needs of the organization’s larger compliance framework. 3. COMPLIANCE STRATEGIES F OR SPREADSHEETS Once an organization’s overall framework for regulatory compliance is in place, the following steps can be taken to identify and control critical spreadsheets in a way th at can be maintained into the future. The 3 key steps to implementing this process are: 1. Evaluate the current situation 2. Implement the appropriate controls 3. Develop a long term spreadsheet deve lopment and maintenance methodology This section will cover each of these in detail. 3.1. Evaluate the current situation Inventory relevant spreadsheets Before critical spreadsheets can be controlled th ey must first be identified. An inventory should be performed to count the population of spreadsheets in the organization which may impact compliance. In most organization’s this applies to spreadsheets concent rated in specific high risk departments. In the case of corporate finance it would include areas supporting the analysis and repor ting of financial accounting data. During the inventory each spreadsheet’s purpose and relationship to critical business processes should be noted. This information will be important when later de term ining the appropriate controls for each spreadsheet. Inventories can be carried ma nually by inspecting hard drives and shared folders, or in a more automated fashion using software that scans a corporate network to target spreadsheets. Identify the business-critical spreadsheets Not all spreadsheets in an organization require rigorous compliance controls. An inventory may return thousands, if not hundreds of thousa nds of spreadsheets, many of which will likely have little or no compliance impact. It woul d be overwhelming and unproductive for an organization to implement strict controls for each spreadsheet found in their enterprise. As a result, teams must identify and isolate th e spreadsheets which support critical business processes, where a lack of controls could lead to material errors. In corporate finance, a material error is typically defined as one that impacts 5% of the general ledger, but this definition should be tailored to satisfy th e organization’s com pliance requirements and departments involved. In m ost cases only a small percentage of an organization’s spreadsheets match these criteria. Case Study: Microsoft Corporation As an example, Microsoft Corporation’s Financial Compliance Group works with management, Internal Audit, and the External A uditors to perform an inventory of important spreadsheets used for financial reporting. A r ecent inventory yielded a total of 42 “business critical” spreadsheets in use. The following filtering criteria were used to identify these spreadsheets. Review criteria Microsoft uses the following criteria to determin e which inventoried spreadsheets need to be reviewed for further analysis. 1. A spreadsheet which documents a journal entry greater than a pertinent dollar threshold. This threshold is derived as a pe rcentage of materiality on a quarterly basis to support quarterly reporting. Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 20 2. A spreadsheet that serves as a recording ledger for an account with a balance greater than a pertinent dollar threshold. This threshold is derived as a percentage of materiality roughly four times greater than the threshold for supporting a journal entry. 3. A spreadsheet that directly supports a financial statement disclosure. Control criteria Control criteria are used to determine whether or not a spreadsheet that passes through the Review filter should be subject to control activities. These criteria are formed by an assessment based on the inherent and historical ri sk of the information contained within that spreadsheet. A risk assessment was conducted to identify the important risks pertaining to spreadsheets. Examples of control criteria used by Microsoft include: 1. Complexity of the spreadsheet: a high degree of formula and calculation complexity, connections to multiple worksheets or extern al data sources, the use of macros and other code, etc. 2. Whether or not the spreadsheet is well doc umented with an established history of changes made to it. 3.2. Implement the appropriate controls Once business critical spreadsheets have been iden tified and their risks defined, the next step is to implement the appropriate controls fo r each one. Often times this process cannot be implemented for the entire organization at once. In this case it’s necessar y to break the work up by division or business unit, addressing the most important areas first. The section below describes the risks and corresponding control ac tivities Microsoft uses to control its critical spreadsheets. Control Activities Control activities are the actual steps taken to mitigate risk and m eet the control objective. The control activities in use by Microsoft’s corporate controllers fall into 4 categories: • Preventative – Controls that prevent undesirable events from occurring • Detective – Controls that detect undesirable events which have already occurred • Directive – Controls that cause or enc ourage a desirable event to occur • Mitigating – Balancing employee empowerment and cost with alternative controls The table below specifies the potential compliance risks for business-critical spreadsheets and their corresponding control activities as defined by Microsoft’s Financial Compliance Group. Potential Spreadsheet Risk Control Activity Category Unauthorized access and modification of data or formulas may result in output / reporting error. Loss of the archived (prior period) spreadsheets may damage audit trail. Save spreadsheets to a location t hat enables restricted user access & provides regular back-ups. Preventative Unauthorized modification of historical data may damage audit trail. Spreadsheets from previous reporting periods should be converted to a “read-only” f ormat and securely archived for l ater retrieval. Detective Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 21 Potential Spreadsheet Risk Control Activity Category 1. Overall model mec hanics are tested before s preads heet is use d. Material changes are tested before spreadsheet is used. Spreadsheets are re-tested once a year. Preventative 2. Formula cells are loc ked to prevent inadve rtent change s. Preventative Spreadsheets may be initially set-up with incorrect formulas. Informal or inadvertent spreadsheet changes subsequent to development may degrade model integrity which results in output & reporting errors. 3. Mechanics are revi ewed every reporting period in suffici ent detail to detect inadvert ent change s. Preventative Miti ga ting Input data is not entered in its entirety and/or does not agree to the source which may result in output / reportin g error. Check cells are used to valida te data accuracy and the completeness of inputs. Detective Miti ga ting Spreadsheets are divided into three worksheets to separate Input Values, Formulas and Resulting calculat ions. Directive Inability to transfer knowledge regard ing how to properly use the spreadsheet degrades current and future ability to generate correct results and accurate reports Key elements of spreadsheets are documented: input cell s, formula cells, output cells, data sources, calculation methodology summary, and spreadshee t use procedur e summary. Directive Defining and implementing control activities, including those described above, is an important step in addressing the complia nce risks associated with business critical spreadsheets. The next section describes how an organization can apply these principles to new business critical spreadsheets by implementing a controlled developm ent process. 3.3. Develop a long term spreadsheet development and maintenance methodology In addition to the control processes describe d above, a long-term strategy for mitigating spreadsheet risk should include the use of a developm ent methodology for critical spreadsheets. Academic research indicates that spreadsheet development share s many of the same characteristics as traditional software development ii . Error rates tend to be similar, as do the benefits that can be gained from a so und development lifecycle that includes design, inspection and maintenance. Historically, sp readsheets have not received the level of developmental rigor given to other forms of en terprise software. As a result, spreadsheets driving key aspects of the business often lack important controls and thus introduce a compliance risk for the organization. The solution here is to treat business-critical spreadsheets like enterprise software and adhe re to a formal development methodology when creating them. Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 22 Below is a recommended development approach to creating spreadsheets iii : Define Requirements The development of a spreadsheet model should fi rst begin by defining its requ irements. This phase should include a detailed description of the spreadsheet’s business purpose, including the functions it will perform and its impact on the broader business process. It is wise to scope and define boundaries here as well, as th is will help prevent the spreadsheet from growing large and unwieldy dur ing the design and developmen t phases. Additionally, this phase should include the sign-off of spreadsheet users to ensure that the application will satisfy their business needs. Design The design phase maps out a detailed plan for implementing the business requirements defined in the first phase, and should result in a spreadsheet “blueprint.” This blueprint should describe the formulas and functions needed for the core logic as well as the layout of spreadsheet itself. Well-designed spreadsh eets include the following characteristics: • A clear, visual separation of inputs, outputs and calculation cells o This can be achieved through layout and placement as well as through formatting • Locking and protecting cells that should not be modified • The use of a standard organizational method o One common example is the top-down organization where formulas never refer to the cells located below them • The use of standard naming conventions throughout th e spreadsheet • The use names to reduce errors a nd increase the readability of formulas • The use of simple formulas o This can be achieved by breaking complex business logic into multiple cells • Extensive documentation throughout the spreadsheet o This is especially effective when comments are e mbedded throughout the spreadsheet o This might also include tables of contents and form atting legends to clarify the structure and layout of the spreadsheets Again, it is imperative to incorporate spreadsheet user feedback in the Design phase to ensure that the final “blueprint” is flexible enough to be used, but strict eno ugh to respond to organizational controls. Implement Once the “blueprint” has been created and validated, it is time to create the spreadsheet. If the Requirements and Design phases have been comple ted with care and a high level of detail, this step should simply assemble the pieces as described in the spreadsheet blueprint. Test & Verify Like any new piece of custom software, spread sheets will contain errors. As a result testing and verification of the spreadsheet’s calculation accuracy is critical to ensuring confidence in the model itself. There are a number of different ways to ‘test’ a spreadsheet including targeted audits, test case verification, scenario testing, and code inspection. Of these testing Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 23 methods code inspection has been shown to be the m ost complete for catching errors. Research indicates that code inspections tend to find on average over 80% of the errors in spreadsheets 1 . However, it is also the most resource intensive, involving teams of 1-3 reviewers with a firm understanding of the spr eadsheet to analyze it closely for logic and input errors. Regardless of the method, test passes should happen regularly throughout the implementation phase by individuals other than t hose that initially created the spreadsheet. In addition to good testing practices, 3 rd party testing tools can be used to help identify and fix spreadsheet errors. Deploy When deploying, the owner must determine and apply the controls needed for the particular spreadsheet. Examples of spreadsheet controls were given in the previous section of this paper. The controls needed for complian ce will vary depending on the complexity and importance of the spreadsheet. Other activ ities to considering when deploying are: • A Formal transition to a production environm ent o Source files are backed up o Solution is stored in a secure location, with strong file access controls o Sign-off from development, test, and business users • Training and education for users of the solution o Creation of a detailed user manual o Training courses that educate users and ve rify proficiency with the solution. Maintain & Document Maintenance and documentation are critical to the longevity of a spreadsheet, without these its life span will be limited and its overall value reduced. Continued testing and verification of all changes made to the spreadsheet after it is deployed will help ensure that the logic remains correct. Documentation is important as it allows users, developers, and testers to understand the purpose and function of the spreadsh eet. This will reduce the amount of future testing needed and minimize user error. Documentation for critical spreadsheets should include the following elements • A detailed description of the spreadsheet’s purpose • A log of changes made to the spreadsheet that include who made them and how they affected the spreadsheet • An explanation of input cells using cell comments o Description of all data inputs o Description of formula cell calculations using cell comments o Any standard, defined spreadsheet naming conventions • A legend that explains the form atting used in the spreadsheet • A user’s manual that explains the proper use of the spreadsheet wit h example input and output values. • Contact information for the person who created and is responsible for the spreadsheet. 4. HOW THE 2007 MICROSOFT® OFFICE SYSTEM CAN HELP ADDRESS COMPLIANCE CHALLENGES While technology alone cannot ensure spreadsheet compliance, organizations should take full advantage of the tools and technology av ailable to help fulfill the compliance recommendations outlined above. Risks to spreadsheet compliance can be mitigated by implementing controls on important elements of business-critical spreadsheets, so that only authorized users are able to view content, make changes, and share information. 1 Panko, Raymond R., Ordway Nicholas. “Sarbanes-Oxley : What about All the Sprea dsheets?” University of Hawaii, 2005 Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 24 This section presents a set of technologies included in the 2007 release of the Microsoft Office System that can be used in conjunc tion with a sound compliance strategy to address compliance challenges regarding spreadsheets, including: • Preventing unauthorized access to spreadsheets • Managing & monitoring spreadsheet changes • Retaining & archiving spreadsheets • Developing robust spreadsheet models Some of the capabilities that will be described are available in the curent release of the Microsoft Office System. 4.1. Preventing unauthorized access to spreadsheets As the complexity and importance of a spreadsheet increases, so to does the cost of errors and innaproriate disclosures of data. The 2007 Mi crosoft Office systems offers a number of different options for securing critical spreadsheets on both the client and server from unauthorized access and modification. This secti on will take a closer look at the following four technologies. 1. Microsoft Office Sharepoint® Server 2007 permissions 2. Sharing Spreadsheets Using Excel Services 2007 3. Information Rights Management 4. Workbook encryption in Excel 2007 4.1.1. Office SharePoint Server 2007 Permissions Office SharePoint Server 2007 is a scalable enterprise portal, content management, and collaboration server built on Microsoft Windows® SharePoint Services. Organizations can use Office SharePoint Server 2007 to store, protect, share, and track important documents and information through a single Web-based portal. All interactions within Office SharePoint Server 2007 are protected and monitored by a single sign-on s ystem to safeguard against unauthorized access to critical documents. Office SharePoint Server 2007 uses a security model based on site groups and rights. Site groups are groups of users with related security requirements. Site owners can assign Security rights to each security group. An organization can customize the rights assigned to these site groups or add new site groups as needed. By default, Office SharePoint Server 2007 includes six site groups: Administrator, Web Designer, Contributor, Reader, Guest, and Viewer. Once groups and permissions have been define d, Office SharePoint Server 2007 safeguards the sites and documents stored within the portal using this permission structure. 4.1.2. Sharing Spreadsheets Using Excel Services Excel Services is a new server-based technol ogy that supports loading, calculating, and rendering Microsoft Office Excel spreadsheet s in a Web browser. Excel Services comprises two primary interfaces: Microsoft Office Excel Web Access allows customers to view spreadsheets in a Web browser and the Excel application programming interface (API) allows developers to share Excel features among appli cations. With the Microsoft Office system, customers can publish spreadsheets and view them with any modern browser, without t he need to install software on the local computer. This allows organizations to share spreadsheets without exposing sensitive business logic. Finally, because Excel Services is part of Office SharePoint Server 2007, it takes full advantage of document m anagement and workflow capabilities to help maintain cont rol over critical spreadsheets. Controlling What Users Can See Publishing a spreadsheet to Office SharePoint Server 2007 saves the entir e spreadsheet to the server to allow for data refreshes and recalcula tion. However, the parts of the spreadsheet accessible to viewers and available for downlo ad through the Web browser are controlled by the author of the spreadsheet. Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 25 Microsoft Office Excel 2007 spreadsheet software provides three options for controlling the viewable area of the spreadsheet on the server: • The entire workbook (default). Users can vi ew the entire workbook and download it to the desktop. • A subset of sheets. The workbook author permits users to view and download a subset of sheets. This does not affect ho w the spreadsheet appears when opened in Office Excel 2007, only how it appears when viewed on the server. This mode is useful when workbooks contain numerous “behind the scenes” worksheets that hold intermediate calculations, source data, etc., but only a few sheets that users should see. • A set of named items, such as Named Ranges, charts, tables, and PivotTable® and PivotChart® dynamic views. In this mode, users can only view and download specific items selected by the workbook author. Users access these items through a drop-down menu in their Web browser. The View Item Right Office SharePoint Server 2007 adds a new f eature for spreadsheets (and other documents) stored in SharePoint document libraries. With this View Item Right, spreadsheet administrators can restrict user access to vi ewing and executing on the server. Users cannot download a copy of the spreadsheet or acces s any areas that were n ot published to be viewable on the server. This feature can hide and make inaccessible proprietary information contained in the workbook, such as specific formul as, the proprietary model, the external data connections, and hidden elements. The View Item Right affects the way Excel Web Access and the Excel API allow access to a workbook. 4.1.3. Information Rights Management Organizations can use Information Rights Manage ment (IRM) to protect and maintain greater control over their digital information, incl uding confidential and sensitive spreadsheets. IRM relies on Microsoft Windows Rights Management Service (RMS) technolog y in Microsoft Windows Server 2003. RMS must be installed in an Active Directory domain in which the domain controllers are running Windows Server 2000 with Service Pack 3 (SP3) or later. By taking advantage of IRM, organizations and individual users can set policies that set greater control over who can open, copy, print, or forward information created in Excel 200 7. IRM in Office Excel 2007 With IRM, users can set different levels of f ile protection—balancing the needs to efficiently share information and help protect privacy. • Set file permissions at different levels and change the level for specific users and groups of users. • Assign permissions according to roles and responsibilities. For example, set different permissions for a viewer, a reviewer, or a file editor. • Restrict file printing to reduce the number of printed copies of a sensitive spreadsheet that might be produced. • Set expiration dates to provide a time lim it after which a spreadsheet file can no longer be opened or used by others. • Help prevent forwarded files from being opened by an unauthorized recipient. Unintended recipients cannot open files protected with IRM. Instead, a message informs them that they do not have access rights. Optionally, the file owner can include an e-mail address. Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 26 IRM and Office SharePoint Server 2007 Sharepoint document libraries are also hi ghly integrated with Information Rights Management. Using IRM, Sharepoint can apply policy to protect spreadsheets automatically as they are downloaded to a user’s laptop. Off line use is unhindered, but as needed rights such as forwarding the spreadsheet, printing, or editi ng can be disallowed on a user-by-user basis. Finally, Sharepoint Server 2007 can leverage IRM to expire content after a specified time. This helps reduce the problem of having out-of-d ate versions of a spreadsheet floating around in email, causing confusion. 4.1.4. Workbook Encryption in Excel 2007 Customers without Sharepoint Server 2007 de ployed can use the Password Protect workbook functionality in Excel 2007 to get a basic level of file security. Password Protect workbook allows users to specify a password required in order to open the workbook. The password is encrypted using a symmetric encryption routine known as 40-bit RC4. 4.2. Managing & Monitoring Spreadsheet Changes using SharePoint Server 2007 Critical Spreadsheets are living applications that inevitably change over tim e. A sound compliance strategy will include some level of on-going change manageme nt and monitoring for critical spreadsheets. In this section we will take a closer look three new capabilities in SharePoint Server 2007 that allow customers to better manage the im portant spreadsheets and documents in their organization without sacrificing productivit y. Versioning Office SharePoint Services has a robust check-i n/check-out and versioning mechanism that allows users to check in changes under a new major (1.0 to 2.0) or minor (1.8 to 1.9) version. Sharepoint will keep as many back versions as is needed with full version history showing who and when each version was created. Auditing Office System SharePoint Server 2007 allows ad ministrators to audit key events within document libraries. While there is no built-in capability to audit the changes within spreadsheets themselves, events such as Open, Create, Modify , and Delete, of spreadsheets are all logged to a centralized audit log, and th ere are several built-in reports to analyze that log, as well as mechanisms to generate custom Excel reports. Workflow With Office SharePoint Server 2007 customers can build workflow s that map to their important business processes. These capabilities enable more manageable collaboration, enforcable and measurable business processes, and more intelligent records m anagement. Microsoft Office SharePoint Server includes a set out-of-the-box workflows around approval, gathering feedback, gathering signatures, and ot hers designed to map closely to the most often-used business processes in most organizations. Using either Sharepoint Designer or Microsoft® Visual Studio® 2005 new custom workflows can be created that codify crucial processes within the business. 4.3. Retaining & Archiving Spreadsheets Spreadsheet archival is just one component of a larger records manageme nt process which includes the collection, management, and dispo sal of corporate records (inform ation deemed important for the history, k nowledge, or legal defense of a company) in a consistent and uniform manner based on company policies. The 2007 Microsoft Office System can help companies ensure that vital corporate record s including critical spreadsheets ar e properly Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 27 retained for legal, compliance, and business purpo ses and then properly disposed of when no longer needed. This section details th e new capabilities around records management provided with Office SharePoint Server 2007. 4.3.1. Office SharePoint Server 2007 Record Repository The core of the records management implemen tation in Office SharePoint Server 2007 is a stable, scalable, and efficient repository. Office SharePoint Server 2007 includes a specialized site template, known as the Records Repository, designed for records management. The following capabilities, new to Office SharePoint Server 2007, help customers fulfill the requirements of records management. Vault abilities The Records Repository has several features that en sure the integrity of the files stored within it. First, it ensures that records are never auto matically modified by the system. This means that records that are uploaded to a records repository and then downloaded again later will always be identical, byte for byte. Second, it h as default settings that prevent direct tampering of records, by versioning any changes made to document contents and by auditing specific types of changes. Third, it allows records ma nagers to add and main tain m etadata on items separately from the record’s metadata, so that information such as “who manages this item” can be changed without modifying the underlying record. Information management policies These policies provide controls that consistently and uniformly enforce the labeling, auditing, and expiration of records. Policies can be confi gured for a specific storage location or content type. Hold The Records Repository lets IT, records manage rs and legal authorities apply one or more holds that suspend the records management polic ies on items to ensure that they are kept unchanged during litigation, audits , or ot her investigations. Record Collection Interface Records repositories provide a set of services that aid in content collection. They let people and automated systems easily submit conten t to a records repository without necessarily having access or permission to any of the contents of the site. Record routing When content is submitted to a records reposito ry, it can be routed t o its proper location within the records management system based on its content type. 4.4. Developing Robust Spreadsheet Models The capabilities of Microsoft Office Excel can be used to create a robust spreadsheet model that meets compliance challenges, and enhances productivity. The following capabilities in Microsoft Office Excel 2007 can help an organiza tion deploy spreadsheet models that make it easier to become, and stay, compliant. 1. Cell Styles 2. Lock important cells 3. Tables as a native structure 4. Defined Names 5. Formula auditing tools Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 28 4.4.1. Cell Styles Complex spreadsheets with multiple contributor s can lack clarity and readability. Users interpret the spreadsheet’s information differe ntly, make errors based on assumptions, and are unable to quickly interpret or analyze the data. Cell formatting is an important tool that can be used to visually clarify the structure of a spreadsheet with color, font, borders, and data formats. Excel 2007 allows users to quickly define reusable cell formatting styles that make it easy to clearly indicate input cells, formulas, outputs and other key components. Changes made to the style are automatically applied to all cells using that style to making formatting updates simple. The resulting spreadsheet easier to read and less error prone. Cell styles help distinguish inputs from calculation cells 4.4.2. Lock important cells In addition to making the spreadsheet more understandable. Organizations can reduce user errors by password protecting (or locking) specific cells, ranges, or sheets. This is a key step in the development of a robust spreadsheet. Protect Sheet Protect sheet allows an author to password prot ect selected cells within the worksheet, as well as prevent different types of changes to cells and other elements in the worksheet. This feature can be used to lock important areas of a spreadsheet preventing users from modifying the values or formulas in those cells. Allow users to Edit Ranges Similar to Protect sheet this command allows customers to lock down specific areas of a spreadsheet. With this feature however, an author can grant edit permissions to specific groups, users, or computers based on Windows NT authentication. 4.4.3. Tables as a native structure One of the most common elements found spreadsh eets today is the table, it is the standard method for organizing and displaying structured data. Excel 2007 now recognizes Tables as a native object in Excel spreadsheets, enabling user s to create robust tables that maintain their structure and are significantly easier to interact with. Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 29 A Table consists of three pieces: The Table feature increases productivity fo r many common tasks in Excel, such as: • Manual data entry • Copy / paste or drag / drop of data • Sorting / Filtering data • Selecting, navigating and scrolling around data Tables also make more advanced tasks both easier to perform and more robust. As data is added to a Table, anything that is associated with that Table automatically adjusts in sync with the Table. Formatting will apply to ne w rows and Formulas will update to include new data. Charts, Pivot Tables, Conditional Forma tting, and Data Validation will also all update to fit the new data. Formulas that reference the data in a Table can do so by name (the name of the column, e.g. “Sales”) rather than by an undecipherable A1 -style address (e.g. D1:D10). This type of referencing is called “Structu red Referencing” and increase s the readability of formulas making them easier to maintain and edit later. Structured referencing in Excel 2007 With Excel 2007 Tables, formatting features beha ves in intelligent ways. For example, if alternate-row formatting is enabled on a Tabl e, Excel 2007 will maintain the alternating format rule through actions that would have traditionally disrupted this layout, such as filtering, hiding rows, or manual rearranging of rows and columns. Additionally Excel 2007 ships with a large number of professionally desi gned table styles that look beautiful out of the box. Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 30 Excel 2007 tables support complex row and column banding that automatically adjusts with the data 4.4.4. Defined Names Defined names have always simplified writing formulas in complex spreadsheets, especially those shared among several people. However, wh en a spreadsheet contains hundreds or even thousands of defined names, it becomes more challenging to perform tasks like deleting multiple names, renaming names, and finding br oken names. The new Name Manager dialog is designed specifically for viewing and managi ng the names in a spreadsheet makes it much easier to do the following tasks: • View important details including the na me’s reference, value, and scope. • Create and scope Names. • Rename existing names • Delete multiple names at once • Sort and Filter the Name List by common criteria including scope, type, and if the name returns an error. The Name Manager dialog 4.4.5. Formula Auditing tools Regulatory compliance legistlation requires auditable and transparent practices for spreadsheets used in financial reporting. Excel 2007 provides auditing tools that , along with a consistent use of cell styles and naming conven tions, can accelerate the testing of spreadsheet models, and reduce the risk of error once a spr eadsheet is in product ion. Auditing tools in Excel 2007 provide the ability to: • graphically display (or “trace”) the rela tionships between cells and formulas • trace a cell's precedents (the cells that provide the cell's data ) • trace a cell's dependents (the cells that depend on the cell's value) • check for errors in a formula Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 31 A formula showing its precedents using auditing arrows 5. CONCLUSION Spreadsheets are commonly used as a critical resource in most organizations, yet they often receive little budgetary resources or sound management policies. This can result in an unnecessary exposure to regulatory compliance risks. As a result it is important for organizations to develop a spreadsheet compliance framework that includes rigorous process controls around the development, testing and use of business-critical spreadsheets. When these controls are combined with the curr ent and forthcoming capabilities in the 2007 Microsoft Office System, companies will have greater success in implementing and enforcing spreadsheet policies. 6. LINKS Information about the 2007 Microsoft Office System 2007 Microsoft Office system website http://www.microsoft.com/office/preview/default.mspx Excel 2007 Blog http://blogs.msdn.com/excel/ Strategies for Addressing Spreadsheet Compliance Challenges: Weber Page 32 Financial Regulation Documentation Sarbanes Oxley, 2003 http://www.sec.gov/rules/final/33-8238.htm Data Protection Act, 1998 http://www.opsi.gov.uk/acts/acts1998/19980029.htm Basel II: International Convergence of Ca pital Measurement and Capital Standards http://www.bis.org/publ/bcbs118.htm EUSPRIG http://www.eusprig.org/ i PricewaterhouseCoopers. (2004), “The Use of Spreadshee ts: Considerations for S ection 404 of the Sarbanes- Oxley Act” ii Panko, Raymond R., Ordway Nicholas. (2005) “Sarbanes-Oxley: What about All the Spreadsheets?” University of Hawaii. iii Adapted from the waterfall soft ware development lifecycle, a d escription can be found at http://en.wikipedia.org/wiki/W aterfall_model . 11/13/05