"Spreadsheet Documentation Policy"
Policy Title: Spreadsheet Documentation Policy Effective Date: 07/31/06 PURPOSE: The purpose of this policy is to provide guidance to users on how to develop electronic spreadsheet documentation. It also provides guidance on how to verify and reference spreadsheets. If spreadsheets are properly prepared, reviewers/readers should expect, with reasonable assurance, an accurate and reliable spreadsheet product/report. Appropriate quality control and documentation are essential elements of a solid internal control system and maintaining an accurate and reliable financial reporting system. POLICY: Company will implement and maintain the internal controls necessary to demonstrate with a reasonable level of assurance that data which is compiled, analyzed and reported through the use of electronic spreadsheets will be accurate and free of errors. To that end, the Company has established guidance on spreadsheet materiality, significance and complexity which should be used to determine the level of documentation required for any internally- generated spreadsheet. SCOPE: This policy applies to all Company employees, consultants, and contractors who utilize financial spreadsheets. All policies and standard operating procedures referenced in this document can be obtained from the [internal company information home page deleted]. Definitions: Complex spreadsheets are spreadsheets that have one or more of the following characteristics: • Use of more sophisticated techniques such as macros, visual basic, look up tables, etc. • Multiple formula calculations, results of which are subsequently used in other formula calculations. • Multiple sheets (tabs) that contain data linked to other sheets (tabs), or other workbooks. • Shear magnitude of volume or size of data contained in the file (e.g., file size greater than 500KB). Version Control Compliance with the Spreadsheet Naming Convention to ensure that only current versions are used. See Exhibit B. Access Control - Drive Limit access to the file by storing in a network departmental restricted drive Access Control - Password Password protection has been implemented to restrict access and/or formula cells have been locked to avoid inadvertent changes. Access Control - Segregation Roles and responsibilities for ownership, review, usage, testing, etc have been assigned. Documentation Documentation has been developed and is maintained as to the business objective and specific function(s) of the spreadsheet. Documentation includes a historical log of changes made (and who made / approved the changes) of each spreadsheet version. Independent Testing Spreadsheet, including formulas and macros, has been tested at origination and has been tested by a person other than the spreadsheet originator to ensure the formulas and logic. Such a person should have sufficient knowledge of spreadsheet applications and the related business functions/processes to give credibility to the testing procedure. Testing - Analytics Periodically generate values from both the “master” copy and the “working” copy and then compare the results. If results are not equal, test the “working” copy to ensure that formulas, links, etc are correct. Archiving A master version of the spreadsheet file is maintained in a separate drive or file folder in locked / archived / “read only” format to be used during testing. The “master” copy should have been tested and is maintained separately from the "working" copying. Back Up Spreadsheets are backed-up on a routine and regular basis. Change Control Compliance with Company’s change control procedures related to requesting, implementing and testing changes. (Note: As change control is difficult to achieve for spreadsheets, it is limited to only the highly complex spreadsheets upon which the Company is materially reliant within its financial reporting process.) Documentation Level • Level 1 – spreadsheets that are integral to the financial reporting process of the Company and its subsidiaries. • Level 2 – spreadsheets that are significant in size, used frequently and/or complex in nature, which may be used for analytical tools, economic evaluations, modeling, budgeting, forecasting and reporting. • Level 3 – spreadsheets that are ad hoc in nature, filling a one-time need, or relatively simple in logic. Required Controls and Documentation Level 1 Level 2 Level 3 Required Controls Spreadsheet Spreadsheet Spreadsheet Version Control Yes No No Access Control - Drive Yes Yes Yes Access Control - Password Yes No No Access Control - Segregation Yes Yes No Documentation Yes Yes No Independent Testing Yes No No Testing Analytics Yes Yes No Archiving Yes Yes No Back Up Yes Yes Yes Change Control Yes No No DOCUMENTATION GUIDELINES: General Documenting spreadsheets helps ensure accuracy and provides supervisors and the reviewer with essential audit trail information. This guideline should facilitate quality assurance. This guideline suggests some approaches that have worked and that meet the needs of referencing. Although this guideline should be regarded as the minimum information to be documented on each spreadsheet, it does not preclude the use of supervisory checklists, cross-indexed tables of contents, and other processes or procedures needed or desired that help ensure quality. Documentation requirements should not override the operational requirements of the spreadsheet. The minimal documentation that is required should not adversely affect the use of the spreadsheet. Documentation should be done during the development of the spreadsheet. However, some of the more detailed documentation steps can be entered after the spreadsheet layout; formulas, etc. have been finalized, thus minimizing the impact on the operation of the spreadsheet. It may prove helpful to identify the location of the documentation on the spreadsheet early in the development, even if some of the information is not entered until the latter stages of completion. Required Documentation Items The following items are required for documentation. These items should be prominently located within the spreadsheet to ensure visibility and accessibility. For workbooks that contain multiple spreadsheets, the first spreadsheet in the workbook should contain the necessary documentation. Title of the spreadsheet: The title should be concise and convey the primary focus of the information included in the spreadsheet/workbook. Each individual spreadsheet within a workbook should be titled appropriately. Purpose: The purpose of the workbook and individual spreadsheets should be described briefly to provide a reviewer or user with an understanding of the major topics covered in the spreadsheet, how it fits into a process and/or the logic and assumptions underlying spreadsheet development. Data Source: A critical component of spreadsheet documentation is identifying the sources of data entered. If all the data were extracted from a single source, this source should be included in the title area. However, if there are multiple sources, they should be identified in conjunction with the data. For example, if all the data in that column and row are from the same source, the source could be listed immediately under the related column or row heading. If the sources are cell specific and are not apparent from cell formulas, a column could be inserted with the corresponding sources next to the cells or comments identifying the data source could be added to the appropriate cells. In some cases, the source of the spreadsheet could be another spreadsheet. For example, if a data query was done and the results of the query were saved as a separate file, this spreadsheet would have as its source the tile name of the spreadsheet with the source data. Specific Instructions: If the spreadsheet is used as a template for data gathering (e.g., budget information) and contains pre-populated data and formulas, instructions should be provided as to how to enter the data being requested. Preparer and Date Prepared: The name of the individual preparing the spreadsheet and the date of preparation / update should be noted in upper right corner of the spreadsheet or in a header or footer. Footer: Every spreadsheet must also have a footer that incorporates the filename, storage path, date and time printed, and page number. Example: File: &[File] Date Printed: &[Date] Page: &[Page] Save: &[Path] Time Printed: &[Time] Required Documentation of Critical and/or Complex Spreadsheets In addition to the required documentation items listed above, spreadsheets used to support the financial reporting process, critical decisions and/or are of a complex nature require additional documentation. As it may not be practical to place the additional documentation within the spreadsheet itself, a separate worksheet within the workbook should be used for detailed explanation of the spreadsheet. Cell references, in parentheses at the end of each item, (i.e., column and row) should be used to indicate where the information is to be located on the spreadsheet. Additional documentation should include: Details of mathematical logic and complex algorithms and functions in the spreadsheet. A “caution” statement regarding the effect of adding/deleting lines/columns to the accuracy of the output. Details of data entry procedures (e.g., unprotected cells vs. protected cells). Details of any validation procedures or expected results. Information on the use of macros (multi-function operations affected through a single key stroke) and the ability to disengage that function. Spreadsheet Storage Requirement To insure that all spreadsheets are properly backed-up on a daily basis, all spreadsheets must be stored on a network drive. This does not prevent users from maintaining a copy on their local hard drives. Users are encouraged to frequently save their work throughout a spreadsheet session. Spreadsheet Development: Best Practices Please note Exhibit A for a compendium of suggested guidelines for developing spreadsheet documentation. EXHIBIT A Spreadsheet Development: Best Practices The following procedures and practices will facilitate the development, documentation and referencing process. They may be implemented during the initial planning stage, data entry and analysis stage, or during the final accuracy stage of spreadsheet development. Benefits of Standardized Spreadsheet Documentation When key items are always placed in the same location, it makes it easier for reviewers and other users to locate the information. Macros and templates can be used to create pro forma spreadsheets or operate on the cells containing this documentation information. Initial Planning Stage The initial planning stage primarily involves planning activities that will help ensure effective and accurate spreadsheets. 1. Mapping out the spreadsheet: Before entering any data, draw a map of what the spreadsheet should look like. For example, areas for documentation, data entries, macros, and spreadsheet formulas should be defined. Further, it is a good idea to update the map as the spreadsheet changes or expands. A record of changes should be maintained for important data. 2. Developing an analysis plan: Develop an analysis plan for the spreadsheet before entering data to ensure that the data required for analysis are included and entered in a format amenable to the analytic techniques planned. The analysis plan should be documented and included in the work papers. If particularly complex, the plan, with risk assessment and tests of logic should be part of the planning stage of the assignment. If necessary, technical assistance should be obtained. Further, if the analysis involves using the database functions, the requirement for using these features should be built into the spreadsheet plan. 3. Developing a validation plan: A validation plan, which includes using applicable quality controls throughout the job, will help ensure error-free spreadsheets. The most frequent errors in spreadsheets are data entry errors. One method of minimizing errors is to designate a specific area within the spreadsheet for data entry. All data should be entered in that area and verified before the data are used. 4. Cell references enable users to transfer the data to other areas of the worksheet without reentering the data, thus eliminating possible data entry errors. The extract and combine features of Excel provide another excellent way of transferring the data to related spreadsheets without additional data entry, thus minimizing errors. 5. It is also a good idea to denote the parameters of the data entry area in the worksheet. One way of doing this is to mark the area in which data are to be entered with a repeating series of periods. 6. Also, the validation plan should contain steps to verify the data. Sampling and validation criteria should be established for each individual spreadsheet based primarily on sound auditing principles and professional judgment. Critical data should undergo more vigorous verification. Suggested guidance on the minimum percentages to use in verifying data follow: Spreadsheet files with less than 100 data elements – all input items should be verified and all errors corrected. Spreadsheet files with 101-500 data elements – a 25% sample of input data should be verified. If the error rate is less than 1% of the sample, complete the verification by correcting the errors. If the error rate exceeds 1%, correct the errors and draw another 25% sample and repeat the procedure. If the error rate for the second sample is less than 1%, complete the verification by correcting the errors. If the error rate for the second sample exceeds 1%, correct the errors and complete a 100% verification of the remaining data elements. Spreadsheet files with over 500 data elements – A 10% sample of the input data should be verified. If the error rate is less than 1% of the sample, complete the verification by correcting the errors. If the error rate exceeds 1%, correct the errors and draw another 10% sample, and repeat the procedure. If the error rate for the second sample is less than 1%, complete the verification by correcting the errors. If the error rate for the second sample exceeds 1%, correct the errors and complete a 100% verification of the remaining data elements. Data Entry and Analysis Stage Lack of attention to data accuracy and clarity can make it difficult to understand or construct an audit trail on a spreadsheet after it is completed. The following quality assurance measures, when implemented during the data entry and analysis stage, will help eliminate errors and facilitate supervisory review and referencing. 1. Minimize data entry errors by: Using the pointer method to specify a cell or range rather than typing in cell addresses, and Copying formulas and then editing. Take the time to verify the formula before copying it. 2. Use range names: Range names are a good way to identify cells. To facilitate the review, prepare a list of all named ranges and their locations in the spreadsheet. 3. Protect formulas and key data: One of the most common errors is writing over a formula or key data. Using the protect command will ensure that this will not happen. 4. Test the model: Test the spreadsheet’s features, including its formulas and macros; with a small part of the database to ensure that the spreadsheet works as planned. Using a partial database rather than a complete spreadsheet saves time and makes it easier to identify logic errors. 5. Correct mistakes as soon as they are identified: If an error is not corrected immediately, the task of locating the cell or cells causing the error message becomes more difficult as the spreadsheet becomes larger. This is especially true for circular references. 6. Format cells using two decimals: Spreadsheets automatically round numbers to the next highest whole number. Using at least two decimals generally eliminates those cases where the total would exceed 100%. (Note: This won’t completely eliminate the possibility of generating totals exceeding 100%). 7. Write out macros: In the cells adjacent to macros, spell out what each macro command means. Final Accuracy Check Stage Once you have completed data entry and verified its accuracy, there are additional steps, which may be taken to clarify the spreadsheet as well as ensure accuracy prior to printing. They are: Use footing and cross footing: By adding an extra row and column of formulas that bracket the totals, the accuracy of the preliminary results can be checked. Use hash totals: To verify that all records are included in the spreadsheet, various hash totals (a total arrived at by adding up the data elements to be used in the subsequent analysis) can be used. Use Automatic Recalculation: Each spreadsheet should be set on Automatic Recalculation for a final recalculation before the information is used in the report and the spreadsheet is given to a reviewer to be used as support for a statement of fact. Protect your spreadsheet: After verification, when no further changes are anticipated to the spreadsheet, the entire spreadsheet should be protected using the Global Protect feature. Use the MS-Excel Formula Auditing feature located under the Tools drop-down menu. Please contact the Company Support Desk for assistance with any of the above Excel features. Exhibit B Spreadsheet Naming Conventions Spreadsheet naming should adhere to the Network Hierarchy Chart. (See attachment.) This format should be used: DriveName/Location/Department/Area/Spreadsheet Name. o Revision History: Revision Date Description of changes Requested By 0 Initial Release