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
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
How to Build Effective Spreadsheets - To learn more about this author, visit Gerry Maguire's Website.
Like this article? Share it with your friends
 |
Related Articles |
|
 How to Build 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 o...
|
|
Financial Management
|
| |
Most entrepreneurs I know have taken few, if any, business courses. They are creative, imaginative, and ambitious to no end, but they couldn’t tell you the first thing about what sound financial management means. If...
|
|
What is their problem?
|
| |
Sales professionals depend on knowing how to ask the right questions to discover the real needs of our clients. Often our clients tell us what they would like us to do, such as, "I need a solution on change manageme...
|
|
Work at Home as a Microsoft Excel Expert
|
| |
These days, many companies will outsource their data entry jobs because it is becoming more cost-effective to do so. They are looking for Excel experts to gather, process and organize vast amounts of data that they ...
|
|
INTUITION An entrepreneurs biggest asset
|
| |
I don’t know how many times I have heard Roger Hamilton the chairman of XL say “Why is it that some people find themselves flying for 4 hours to another country just because they know they should be there?” The answ...
|
 |
Related Businesses - Evan Elite Authors |
|
The Evan Elite Authors program is currently in beta phase. For details please contact us.
|
|
|
Gerry Maguire
(Visit Gerry's Website)
Gerry was born and raised in Melbourne,
Australia. He obtained his Bachelor of
Commerce from the University of Melbourne.
He holds an MBA from Deakin University. He
is a fellow of the CPA Australia and a
lecturer in Business Finance at James Cook
University.
In 1987, Gerry embarked on a career in the
world of finance and commerce. He quickly
advanced and held senior Management
positions within the mining industry with
BHPB Billiton and WMC Resources.These
positions included being the senior
finance manager for businesses with a
turnover of $A500m.
Always seeking a challenge, Gerry followed
his career path to such exciting locations
as the outback Australia, San Francisco,
the Canadian Arctic as well as tropical
North Queensland.
Gerry’s ability to thrive in diverse
communities and embrace adventure makes
him a powerful motivator.
His website is www
.inspirationcoaching.com.au
|
|
|
|
![[Get Copyright Permissions]](http://license.icopyright.net/images/icopy-w.gif){kind=small}
