Spreadsheets as a Program

The key to designing quality spreadsheets is Planning.

You must have a clear understanding of the problem to be solved before attempting to develop the spreadsheet.

At this stage you should start by drafting it on paper.

It is often useful to start with the Outputs required and work backwards through the Processing to the Inputs required.

Often existing reports can be used as a guide to the inputs, processing and outputs required.

Your spreadsheet can be laid out in the same manner with separate Input, Processing and Output areas.

These may be on the same sheet or different sheets as shown below.

Notice that Input , Processing and Output have been placed on separate sheets.

Notice also that we have attempted to provide some documentation regarding the spreadsheet, such as who created it and when.

In this example we would also protect all the cells except for B7 and B8 where the user will enter values.

Testing, Testing, Testing

Once you have built the spreadsheet it is vitally important that you validate your spreadsheet by testing it.

Spreadsheets lend themselves to producing masses of neatly typed and presented figures of worse than useless value.

You should develop suitable test data with known results.

You should also print out two copies of the spreadsheet,

• one showing the labels and numbers and
• one showing the labels, numbers AND formulas

This will enable you to desk check the formula to ensure they are operating on the right range of cells.

And remember even computers sometimes make mistakes.....................

---