Writing Safer Spreadsheets

If you really want to use a spreadsheet, then it MUST be designed and documented. The steps you have to go through are those that a compiler program would use in creating a spreadsheet from a set of equations.

You must also provide a description of what you have done so that anyone wishing to use or check the spreadsheet know exactly what it is supposed to do. unlike a program written in a proper programming language it is not practical for anyone to work this out from the spreadsheet itself. This documentation is at least as important as the spreadsheet.

A useful guide to the steps you must go through is the input form for our web based modelling tool. This was developed from a tool which created spreadsheets automatically. Each section of the form represents a set of actions which you must carry out. You should also read the section on how to construct models properly.

The the things which you must do specifically to create a spreadsheet are as follows.

Model Formulation

Write down all your equations in full. Do this in `computer notation' rather than conventional algebraic notation if you prefer. They will have to be written in this form eventually.
Identify all the symbols you have used for names of quantities in the equations. Write these out in two lists:
- parameters - quantities which for which you know values, even if you wish to change them.
- variables - quantities which you do not know and which are to be calculated.
Check thet the number of equations and variables is the same. If they are not, the model cannot be solved, and you have not formulated it correctly.
At this point you have in effect completed the first two boxes of the form. What follows next depends on whether a straightforward calculation or a trial and error procedure is required.
Rearrange your equations into formulas:
- For a straightforward calculation, all equations must be rearranged, each for a separate variable.
- For a trial and error calculation using `goal seeking' identify which variable is to be adjusted. This variable will not appear on the left hand side of a formula. One equation will thus not be used to calculate a variable but will be used a criterion for goal attainment. It is best to rearrange this equation so that it will evaluate to zero when the adjusted variable has the correct value.
You will probably find it convenient to write the rearranged equations in the order in which they are to be used. This is necessary with the web tool, and although a spreadsheet will do the rearrangement automatically, writing them this way provides an additional check on correct model formulation.

You have now defined your model.

Design the Spreadsheet

This involves deciding which variablea and parameters go in which cells.

It is up to you how you wish to arrange the cells on the spreadsheet matrix. However the following guidelines should be followed.

Put all the parameters together at or near the top. It is useful to divide them into those which you may wish to change and those which will always be the same.
Group together variables which are logically related.
Every cell must be identified. You can put text containing the name used in the original equation or something at least as descriptive in an adjacent cell. If variables are logically grouped you can label rows and/or columns. Write down the cells where you are going to put these labels, making sure that they do not conflict with cells used for variables, etc.
Identify any `goal' and `adjustment' cells clearly.

Go to your list of variables and parameters and write beside each the name of the cell in which it is stored. Check again that numeric and label cells do not conflict.

You may find it helpful to plan the layout of the sheet on squared paper. DO NOT TRY TO CREATE IT ON THE COMPUTER.

Translate the Equations

This is an essentially mechanical task of translating your formulas so that the names of variables and parameters are replaced by the names of the cells. Bear in mind that you are much less able to do this job reliably than a computer can.

Go to your list of equations.
Refering to your lists of variable and parameter cells carefully rewrite each beside the original with the names replaced by cell names.
Go back and check that you have done this correctly.

Create and Check your Spreadsheet

Only now can you go to the computer and type in each cell contents. If you find that you have made a mistake, e.g. tried to use the same cell for both a formula and a label, then you MUST return to the previous stage, correct your design. and update your lists. DO NOT try to correct it `on the fly'!

Go though the sheet and check each cell contents against you list. This is very tedious, but it is much easier to do it now, immediately after you have created the sheet than to come back to it later.

Solve a test problem to which you know the answer.

Complete your Documentation

This consists of:

Your original equations.
Your lists of cells and variable or parameter names.
Your formulas written with the original names and, along side them, rewritten with cell names.
A description of your test example and its results.

Once again - this is as important as the spreadsheeet itself. If the spreadsheet is being used to solve, e.g. a design workshop problem, and you do not provide this documentation, then the marker is entitled to assume that your answers are meaningless, as he has no way of checking them. He is not going to waste his time examining the contents of cells in your spreadsheet. You could in principle be given zero marks for the entire exercise. Be warned!