Cash Flow Analysis and Forecasting: The Definitive Guide to Understanding and Using Published Cash Flow Data

20

Good Practice Spreadsheet Development

DATA ENTRY, FORMATTING AND SPREADSHEET ERROR

Now that you are familiar with the innate vulnerabilities of spreadsheets and the ease and flexibility with which a spreadsheet can be made to appear exactly as the user wishes, we need to consider how to try to minimise the risk of spreadsheet error arising in our cash flow forecasts and models.

We will start by considering some general strategies to keep our spreadsheet efforts manageable and keep the risk of spreadsheet error to a minimum.

LAYOUT

There are certain conventions used when developing spreadsheets carried over from the original pen and paper world, which are good practice. This is to start with a logical and consistent layout, which is then used throughout a particular model even if it contains many different worksheets. (A worksheet is a single spreadsheet page, a complex spreadsheet model may consist of a number of linked worksheet pages in one or more files.)

Laying Out a Worksheet

Typically the first three or more lines of the sheet will contain headings about who, what and when. Who is it for, what is it, and when is the timescale or period covered? The financial headings will then usually run vertically down the sheet with time being dealt with horizontally across the sheet (Table 20.1).

Table 20.1 Good practice layout illustration

When we commence laying out the data and ...