Inspiration Coaching

How to build effective spreadsheets

July 14, 2008 at 10:46 am | Posted in Business | 1 Comment
Tags: effective spreadsheets

Spreadsheets have become an important business tool. I first experienced them at university in the mid to late 1980s. I remember thinking, for the first time, that computers could really be useful. Since then some of the things that I have used them have been:

• Business reports

• Create financial forecast

• Run horse race sweeps

• Develop a betting program (unsuccessful) and

• Calculate average km times in races

Spreadsheets are very powerful and can be used for a variety of purposes. However, in business, they are prone to error and an incorrect formula or a spreadsheet designed poorly can result in poor business decisions being made.

I have used my own knowledge and an article written by Shahid Ansari and Richard Blockto to provide some rules to develop effective spreadsheets. The following examples help to demonstrate the benefits of the rules.

Option A

	Qtr 1	Qtr 2	Qtr 3	Qtr 4
Product 1 Volume	800	=+B2+B2*C3	=+C2+C2*D3	=+D2+D2*E3
Product 1 Volume % increase		0.01		0.02
Product 1 Price	10	=+B4+B4*C5	=+C4+C4*D5	=+D4+D4*E5
Product 1 Price % increase			0.05
	Qtr 1	Qtr 2	Qtr 3	Qtr 4
Sales	=+B2*B4	=+C2*C4	=+D2*D4	=+E2*E4

Option B

	Qtr 1	Qtr 2	Qtr 3	Qtr 4
Sales	=800*10	=800*1.01*10	=800*1.01*10*1.05	=800*1.01* 1.02*10*1.05

Both option A and option B produce the same results for the total sales of product 1 by quarter. However, option A will be the more effective spreadsheet in the future for the following reasons.

1. Identification and Segregation of Data: Option A clearly identifies each input. For example 800 is the volume of product 1 in quarter 1 and .01 is the % increase in volume of product 1 in quarter 2.

Each formula only has cell references in option A. Compare that with the formulae in option B that are a series of numbers. In option A it can be determined that the sales of product 1 in quarter 2 is the volume of product 1 by the price of volume 1. Alternatively in option B sales in quarter 2 are 800 by 1.01 by 10. The new user (or the original user a couple of weeks later) will have to guess what these numbers refer to.

2. Ease of making changes: Option A has highlighted the cells that can have direct input by giving them a yellow background. In addition the cells that should not be changed i.e. the cells that have a formula have been locked. The easiest way to unlock cells for input and to lock the remainder of the spreadsheet is to right click the cells that can have input, choose format and protection and uncheck the lock check box. Then choose the tools protection option from the main menu and click on protect the sheet.

3. Document Assumptions: Although it is not shown in the above example it is good practice to document the assumptions that you have made in developing the spreadsheet. For example some of the Option A assumptions were:

a. Product 1 Volume Qtr 1: Based on the prior year sales volume in the first quarter + 3 % which was the increase in the prior quarters sales volumes compared to last year.

b. Product 1 Volume % increase: Based on market research which indicates the total market will increase by 3 % in the next twelve months. The assumption is a 1% increase in quarter 2 and a further 2 % increase in quarter 4. This sales volume increase will be supported by a marketing campaign during which we also plan to increase our price by 5 %

These options should be documented in the workbook, preferably in a separate worksheet.

These are three simple rules to help develop effective spreadsheets and provide a better basis for business decision making.

1. Identify and segregate the data

2. Make making changes easy

3. Document the assumptions