Spreadsheet Documentation Policy by d7qPyB


									Policy Title: Spreadsheet Documentation Policy
Effective Date: 07/31/06

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.

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.

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].

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
 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
•      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

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


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

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

To top