Feedback Form
Home Features Mastermind Videos About Advertise Blog Network Contact
   

Have A Suggestion?
Toronto Salsa Classes / Toronto Salsa Lessons Email us your ideas on how to make our website more valuable! Thank you Sharon from Toronto Salsa Lessons / Classes for your suggestions to make the newsletter look like the website and profile younger entrepreneurs like Jennifer Lopez and Sean Combs!
Have A Suggestion?

Featured Ebook


ebook Famous Entrepreneurs - Modern Empire Builders


Featured Ebook

More Evan Carmichael
Have A Suggestion?

Sales Lessons From Starbucks And Dell

How to Build Effective Spreadsheets



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 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
[Get Copyright Permissions] E-Mail | Print | More  


Related Articles 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 Forum Posts Related Forum Posts
Re: VALUE = ROI / Price (Cost) or Opportunty Spent Re: VALUE = ROI / Price (Cost) or Opportunty Spent
Re: Great start, but no finish Re: Great start, but no finish
How do you know if you have a good idea? How do you know if you have a good idea?
Starting A Business Starting A Business
Re: Money Does Not Guarantee Happiness Re: Money Does Not Guarantee Happiness
Supporters vs detractors Supporters vs detractors
Re: Anyone Uses Elance.com? Guru.com? Rentacoder.com? Re: Anyone Uses Elance.com? Guru.com? Rentacoder.com?
Hiring good people Hiring good people

Related Forum Posts Related Businesses - Evan Elite Authors

The Evan Elite Authors program is currently in beta phase. For details please contact us.


 
About the Author


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
Have A Suggestion?

View Author's Blog
Become An Author

View Author's Video
Become An Author

Free Downloads


Gerry Maguire's

Complete
List Of
Business-Coach
Articles

First Name
Last Name
Email
 
If you enjoyed this article, get Gerry Maguire's Complete List of Business-Coach Articles For FREE!

More Gerry Maguire
How to Build Effective Spreadsheets
How to Maintain Motivation
The Secret of Happiness
Are lawyers really worthy that money
What is coaching all about anyway
When is the right time to expand
How to trust yourself
Recession Proof Your Business and Your Life
Balanced Scorecard Measuring the Important Stuff
How to develop a career action plan
Become An Author