Lotus Spreadsheet Documentation and Referencing Guidelines by GAO, Lecture notes of Logic

Download Lotus Spreadsheet Documentation and Referencing Guidelines by GAO and more Lecture notes Logic in PDF only on Docsity!

GAO

United States General Accounting Omce

Information Management and

Technology Division

November 1987

Preparing,

Documenting,

and Referencing

Lotus

Spreadsheets

0-a Technical Guideline 3

Preparation of this Information Nanagement and Technology Division (IMTEC) Technical Guideline was undertaken by the Lotus l-2-3 IYsers’ Group. The principal authors were Richard Donaldson and Harriet Gan- son of the Boston Regional office. Major contributors were Amy Tidus and Barbara House, Los Angeles Regional Office; Ste\‘e Thummel. Kan- sas City Regional Office; and Stewart Seman, Chicago Regional Office.

This guideline is for Lotus 1-2-3 users who are familiar with its spread- sheet, data base, and graphic capabilities. A file (TEMPLATE.U’KS I. which provides a template of the documentation format suggested in this guideline, can be obtained by downloading it from the End-1 -set Systems Bulletin Board, 27.51050.

‘Lotus and Lotus l-2-3 are registered trademarks of the Lotus Development Corporatwn

A Methodology and Guidance Publication

We hope that the readers of this guideline will find the information pre- sented here beneficial in developing, using, and referencing electronic spreadsheets.

l& L G

----J

Ralph V. Carlone Werner Grosshans Director Director Information Management and Office of Policy Technology Division

Page 2 Technical^ Guideline^2

• Page 3 TechdcdWdellne

Contenta

Appendixes (^) Appendix I: Original Spreadsheet 24 Appendix II: Referencer-Reviewed Spreadsheet 30 Appendix III: Final Corrected Spreadsheet 36 Appendix IV: Spreadsheet Extract 42 Appendix V: Documentation Template 48 Appendix VI: Report Extract 50 Appendix VII: Referencing Review Sheet 51

Bibliography 52

Abbreviations

GAO IMTEC

General Accounting Office Information Management and Technology Division

Page 5 Technical Guidelines 3

Chapter 1

Purposeof This Guideline

Appropriate quality control and documentation are essential throughout an assignment. The purpose of this paper is to provide guidance to Lotus

users on how to prepare and document electronic spreadsheets that

meet GAO quality assurance standards. In addition, it provides guidance

on how to verify and reference Lotus spreadsheets.

This guidance is based on the following premises:

l Guidance should be consistent with all GAO rules and regulations.

. Guidance should have a minimal impact on the use of Lotus features for

an evaluator’s intended purpose.

. Guidance should facilitate supervisory review and the referencing

• process.

Evaluators should be able to use spreadsheets as direct support for checking the accuracy of report data. If Lotus spreadsheets are properly

prepared and reviewed, referencers should be able to satisfy themselves

as to the appropriateness and accuracy of the written product. On the

other hand, since referencers are responsible for determining the accu- racy of the written product, this guideline should not restrict them from doing whatever is needed to fulfill their duties.

This document offers guidance for the error-free preparation, documen-

tation, and referencing of spreadsheets. The appendixes provide exam-

ples to assist both users (evaluators or supervisors) and referencers.

Appendixes I, II, III, and IV are examples of a spreadsheet that has gone

through the progressive steps of supervisory review, referencing, and

find draft. (Each spreadsheet’s title is identified in cell A4.) Appendix I

is the original spreadsheet with errors, prior to the referencer’s review. Appendix II is the copy of the spreadsheet after the referencer has

reviewed it. Appendix III is the fiial corrected version of the spread-

sheet. Appendix IV is an extract of records from the original

spreadsheet.

Appendix V provides Lotus users with a template for documenting

spreadsheets according to the guidance contained in chapter 3. Appen-

dix VI contains indexed and referenced extracts from the hypothetical

report supported by the sample spreadsheets. Appendix VII is a Report Review Sheet (GAO Form 92) with sample referencer’s points.

Pye 6 Technical Guideline 3

Chapter 2 Spreadsheet Development

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.

Also, the validation plan should contain steps to verify the data. Sam- pling and validation criteria should be established for each individual spreadsheet based primarily on sound auditing principles and profes- sional judgment. Critical data should undergo more vigorous verifica- tion. (See page 14.) Some guidance on the minimum percentages to use in verifying data follow:

l Lotus files with less than 100 data elements:

All input items should be verified and all errors corrected.

. Lotus files with 101 to 500 data elements:

A 25-percent sample of input data should be verified. If the error rate is less than 1 percent of the sample, complete the verification by cor- recting the errors. If the error rate exceeds 1 percent, correct the errors and draw another 25-percent sample and repeat the procedure. If the error rate for the second sample is less than 1 percent, complete the ver- ification by correcting the errors. If the error rate for the second sample exceeds 1 percent, correct the errors and complete a loo-percent verifi- cation of the remaining data elements.

. Lotus files with over 500 data elements:

A lo-percent sample of input data should be verified. If the error rate is less than 1 percent of the sample, complete the verification by cor- recting the errors. If the error rate exceeds 1 percent, correct the errors and draw another lo-percent sample and repeat the procedure. If the error rate for the second sample is less than 1 percent, complete the ver- ification by correcting the errors. If the error rate for the second sample exceeds 1 percent, correct the errors and complete a loo-percent verifi- cation 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 com- pleted. The following quality assurance measures, implemented during the data entry and analysis stage, will help eliminate errors and facili- tate supervisory review and referencing.

Page 6 Technical Guideline 3

chapter 2 Spreadsheet Development

1. Minimize data entry errors by:

l using the pointer method to specify a cell or range rather than typing in cell addresses, and l copying formulas and then using the “F2” key to edit. 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 referencer’s review, prepare a list of all named ranges and their locations in the spreadsheet (using “Range Name Create”).
3. Protect formulas and key data: One of the most common errors is writing over a formula or key data. Using the Range 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 data base to ensure that the spread- sheet works as planned. Using a partial data base rather than a com- plete spreadsheet saves time and makes it easier to identify errors in logic.
5. Correct mistakes as soon as they are identified: If an error is not cor- rected 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: Lotus automatically rounds numbers to the next highest whole number. Using at least two decimals generally eliminates those cases where the total would exceed 100 percent.

Caution: This will not totally eliminate the possibility of generating totals exceeding 100 percent.

7. Write out macros: In the cells adjacent to macros, spell out what each macro command means (for example, “/wcs2 ” would be written out as Worksheet, Column Width, Set, 2).

Final Accuracy Check Once you have completed data entry^ and verified^ its accuracy, there are

Stage

several additional steps that may be taken to clarify the spreadsheet as well as ensure accuracy prior to printing. They are:

Page 9 Technical^ Guideline^3

Chapter 3

Documentation Guidelines

Documenting spreadsheets helps ensure accuracy and provides supervi- sors and the referencer with essential audit trail information. This guideline should facilitate quality assurance. It does not preclude the use of any other GAO guidelines. Rather, this guideline suggests some approaches that have worked and that meet the needs of referencing. The use of supervisory checklists, cross-indexed tables of contents, and other processes or procedures needed or desired that help ensure qual- ity, is encouraged.

Documentation requirements should not override the operational requirements of the spreadsheet. Some minimal documentation is required and should be on the disk, but this should not adversely affect the use of the spreadsheet. As much documentation as possible should be done during the development of the spreadsheet. However, some of the more detailed documentation steps can be entered after the spread- sheet layout, formulas, etc. have been finalized, thus minimizing the impact on the operation of the spreadsheet. The location of the docu- mentation on the spreadsheet should be established first, however, even if some of the information is not entered until the end of the assignment.

This guideline suggests that the left side of the spreadsheet, columns A through I, be used to document the contents of the spreadsheet. Under this approach, documentation is recorded beginning in cell A2. Data, for- mulas, and all use of the spreadsheet can then start in column J. To facilitate review and referencing, row 1 and column I should be used for row and column coordinates. (See appendix I.) The Spreadsheet Auditor will print row and column coordinates automatically, thus eliminating the need to enter spreadsheet row and column coordinates. Such a lay- out generally permits the operation of the spreadsheet without prepara- tion of the documentation adversely affecting the data and formulas or their presentation. For example, column widths or for-mating columns for data presentation can be set without altering the documentation.

Caution: Users must be careful when inserting or deleting rows in the spreadsheet. Such actions could adversely alter the documentation.

In small spreadsheets where memory is not a problem, a “step down” approach can be used. For example, the worksheet part of the spread- sheet would start in column J in the row immediately following the doc- umentation. This approach eliminates all problems related to adding or deleting both columns and rows in either the worksheet or the documen- tation area. In large spreadsheets, where memory is a problem, docu- mentation can be done externally using the word processing program

Page 11 Technical Guideline 3

Chapter 3 Documentation Guidelines

WordPerfect1 For example, the same areas in columns A through I are set aside for the documentation. After the spreadsheet is complete, the WordPerfect documentation can be merged into the documentation area. This also provides a hard copy of the documentation as a backup.

In all cases, a final check should be made to validate the integrity of the documentation, including the labels for worksheet row and column coordinates.

In the example spreadsheets used with this guideline (see appendixes I, II, III, and IV), the documentation is recorded in cells A2 to H138. Also, key items such as job title, code, filename, and source are located in spe- cific cells. If key items are always placed in the same location, it makes it easier for reviewers and referencers to locate them. Also, macros and templates can be used to create pro forma spreadsheets or operate on the cells containing this documentation information. (See appendix V.)

Some spreadsheets may require more extensive documentation than is practical to place within the spreadsheet itself. In such instances, exter- nal documentation can be used for detailed explanation of the spread- sheet. The internal documentation would still contain the elements listed below, with keys to the external documentation.

Computer-generated spreadsheets must meet all the quality assurance standards required by GAO. The procedures followed in describing a Lotus spreadsheet are similar to those followed in describing any work paper. The following discussion highlights critical documentation items. A cell reference, in parentheses, indicating where the information is located on the sample spreadsheets, is provided at the end of each item. (See appendix III for examples of each item.)

1. Job title and code: The job title and code should be entered just after the spreadsheet layout. (A22..D23)
2. Title of the spreadsheet: The title should be concise and convey the primary focus of the information included in the spreadsheet. (A25)
3. Work-paper index: The work-paper index title, similar to the indexing system used in paper files, will allow the reader to determine the exact location of the data and how it relates to other work papers. The file name should be used as the work-paper index; if a title other than the

1WordPerfect is a registered trademark of the WordPerfect Corporation.

Page 12 Technical^ Guidelines^3

Chapter 3 Documentation Guidelinea

However, if there are multiple sources, they should be identified in con- junction 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, a column could be inserted, with the corresponding sources next to the cells. (See appendix III, column K.) In some cases, the source of the spreadsheet could be another spreadsheet. For example, if a data query were done and the results of the query were saved as a separate file, this spreadsheet would have as its source the file name of the spread- sheet with the source data. Cell E M 1 of the sample spreadsheet (appen- dix IV) shows how this is done.

9. Data verification: This indicates data entry verification and control procedures. For example, if 10 percent of the data items have been veri- fied, describe the process used and the results. (A39..A40)

The extent of data verification can vary depending on the nature of the data and how critical the data is to a finding. If it is considered very important, loo-percent verification may be necessary.

10. Spreadsheet Layout: Use this space to document the cell addresses for each component of the spreadsheet-data entry, formula explana- tions, macro explanations and purpose, and range names and their cell addresses, for example. (All..F20)
11. Printing: W ide carriage printers and compressed print are recom- mended for printing the spreadsheet(s). Using the Borders Rows Option with a cell in column A as the borders range will print the column coor- dinates at the top of each page. (See appendixes I, II, III, and IV.)

The topics listed above are not meant to be all-inclusive. Depending on the complexity of your spreadsheet, more sophisticated documentation procedures may be required. W e recommend including the documenta- tion in the spreadsheet as soon as possible. If this creates an obstacle for the efficient use of a spreadsheet, the documentation could be temporar- ily placed in another part of the spreadsheet or completed at the end of the assignment. If this latter approach is used, some detailed external notes on the operation of the spreadsheet should be maintained to facili- tate the final documentation process.

The sample spreadsheets used with this guideline contain the documen- tation information in specific locations in the spreadsheet. W e recom- mend that these locations always be used unless they interfere with the

Page 14 TechnlulGtMeUne 3

Chapter 3 Documentation Guidelines

use of the spreadsheet. By reserving the same location on each spread- sheet, users and reviewers will always know where to locate the docu- mentation. This will also facilitate the use of the features of the computer to help us in our work. This approach a&soallows the develop- ment of templates to help the documentation and referencing process. Appendix V contains an example of such a template.

Page 15 Technical Guideline 3

Chaoter 5

Referencing Steps

A referencer’s involvement with Lotus spreadsheets begins when the written product is indexed to an analysis that was done using Lotus. If the analysis is simple enough, adequate support may be limited to a printout of the spreadsheet and the formulas along with the appropriate documentation. In these cases, a referencer may not have to use a com- puter for additional analysis. The key factor, however, is that the mate- rial provided to the referencer must adequately support the facts in the written product.

In most cases, however, the referencing process should be done with a computer. The use of a computer is required because printed copies of spreadsheets do not display error messages, such as ERR and CIRC, which inform the reader that errors have occurred. (See page 19, para- graph c.) Furthermore, all electronically-stored data needed in the refer- encing process must also be provided to the referencer in the form of hard copy products. The referencer should also be supplied with a disk containing the Lotus files that produced the hard copy so that he/she can analyze the Lotus material directly or with the Spreadsheet Auditor package. (See chapter 6).

Verify Supervisory

Review

Before staff can expect a referencer to evaluate the adequacy of spread- sheet evidence, the supervisor must ensure that the supporting spread- sheets are in proper condition for examination. Thus, the first step in the referencing process is to verify that the spreadsheet has received proper supervisory review. These reviews assist referencers in deter- mining that the logic implied or stated in the report is consistent with the analysis that generated the results. Referencers must determine that this logic is correct within the Lotus spreadsheets when they are used as the supporting analysis. Althwgh it would be too time consuming for a referencer to review an entire spreadsheet for correctness of logic, someone on the job should do it, usually the supervisor of the person who prepared the spreadsheet. When dealing with Lotus spreadsheets, the steps needed to verify supervisory review are as follows:

l Check the internal documentation part of the spreadsheet for evidence of supervisory review, such as a supervisor’s name and the date of the review entered in the spaces provided. Hard copy backup must include the supervisor’s signature. (See appendixes I, II, III, and IV.) l Compare the review date entered in the spreadsheet to the date on the directory of the disk on which the spreadsheet is stored. If they are dif- ferent, obtain some explanation. A directory date after the date in the

Page 17 Technical^ GuideUne^3

Chapter 6 Referencing Steps

spreadsheet may mean that the spreadsheet was changed after supervi- sory review.

Check Report Content

The referencer is responsible for ensuring that proper quality control

for Accuracy and

support

procedures were used and all points of fact in a written product are accurate. Thus, if the report index for a fact or figure is a Lotus spread- sheet, the referencer must trace those figures to the supporting spread- sheet. Some of the specific steps a referencer may follow are listed below.

1. Review the spreadsheet documentation, both internal and external, to become familiar with the purpose of the spreadsheet and the way it was designed. The spreadsheets used as examples with this guideline use col- umns A to I for documentation. (See chapter 3 for guidance on docu- menting the spreadsheet.)
2. Verify the formulas used to calculate report items or determine that verification has been performed. Verifying formulas involves checking ranges, the logic of the formulas, spreadsheet messages, and conducting tests-it does not require using a calculator to check the accuracy of the Lotus mathematics. This step is not a substitute for supervisory review of the soundness of the analytical approach and formulas used. How- ever, because a referencer is responsible for the accuracy of all facts in a report, additional verification steps may be followed when referencing important facts and figures. For example, using a computer or a printout of the formulas used to complete appendix I would disclose that the formula in cell U2 does not agree with the explanation shown in cell A109. Specifically, the referencer would note that the range of values included in the formula is U5..U78 instead of U5..U77.

a. Check the range of the formula used to calculate the numbers in the report. For example, in appendix I, there is a formula (cell U82) that calculates the number of hours the agency’s computer was down. A ref- erencer must ensure that the range specified in this formula is correct. That is, only the items that should be in the range are included. As noted above, the range specified in cell U82 includes an extra cell that inflates the down time hours. Thus, if the report states that in January 1986 there were 73 instances of down time during business hours for a total of 491 hours, then the statement is wrong. The report should state that there were 69 instances of down time during business hours with a total down time of 245 hours. There are 69 instances because four instances resulted in no down time during business hours. The total hours of down

Page 18 Technical Guideline 3