Tweet

How to Build Effective Spreadsheets

Written by: Gerry Maguire

Article Overview: 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.

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

Related Articles
  What is their problem?
  Financial Management
  Are you Starting to Feel the Quickbooks Pain with 15 or More Employees?
  Work at Home as a Microsoft Excel Expert
  INTUITION An entrepreneurs biggest asset

Article Tags: 1980s, b2, b4, business reports, business tool, c4, cell references, d2 d2, d4, financial forecast, formulae, horse race, poor business decisions, segregation, shahid ansari, spreadsheet, spreadsheets, volume increase



Related Forum Posts
Re: Great start, but no finish - My advise is FOCUS on your business. Focusing is the main factor to succeed on business. If you have a great idea, then follow it only, don't look anywhere else. It is especially essential in an online business. Because there are a lot of mind blowing sales letters promising millions dolllars or overnight success, people find it difficult to concentrate on one business and they lose their energy. Concept is simple. 1- Have a good idea; 2- Build your business on it; 3- Monetize your business; 4- Promote it; 5- Build list of responsive customers; 6- Maintain You Business. Focusing and not fearing from competition (instead you can learn from competitiors) are main tips of billionaires ,BTW. Orxan
Re: How to do Link Building? - Be diligent. Build blogs.Pay attention to the contents.
How do you know if you have a good idea? - [quote="orxan":3118uboz]My advise is FOCUS on your business. Focusing is the main factor to succeed on business. If you have a great idea, then follow it only, don't look anywhere else. It is especially essential in an online business. Because there are a lot of mind blowing sales letters promising millions dolllars or overnight success, people find it difficult to concentrate on one business and they lose their energy. Concept is simple. 1- Have a good idea; 2- Build your business on it; 3- Monetize your business; 4- Promote it; 5- Build list of responsive customers; 6- Maintain You Business. Focusing and not fearing from competition (instead you can learn from competitiors) are main tips of billionaires ,BTW. Orxan[/quote:3118uboz] Thanks orxan! But how do you know if you truly have a good idea or not? If we ask ourselves, we may be suffering from tunnel vision. On the other hand, friends/family may try to discourage us from pursuing a small business because of their own fears, while others will simply give us an empty "you can do it!" line.
Re: Money Does Not Guarantee Happiness - Thanks for singling this point out, Chris. One book that comes to mind that covers it in an overall approach is "The 7 Habits of Highly Effective People" by Stephen Covey. He addresses our inner life and the way it can change our effectiveness home and at work. There are corporations who have taken this model and effectively molded their business around it.
Starting A Business - go get a customer! you can have a great idea but if you don't have a customer you'll never be successful. Build a product / service around someone who will pay you for what you can offer! It's the best way to build a business.


Share this article with your friends. Fund someone's dream.

Leave a comment below or share on the left and you'll help support entrepreneurs in Africa through our partnership with Kiva. Over $50,000 raised and counting - Please keep sharing! Learn more.

Tweet

Newsletter

Get advice & tips from famous business
owners, new articles by entrepreneur
experts, my latest website updates, &
special sneak peaks at what's to come!

Name:
Email:

Popular Articles

Fighting the Saw-Tooth Affect

Effective Leadership

Paint A Word Picture - Excite Your Customer

Suggestions

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.