How to Build Effective Spreadsheets
How to Build Effective Spreadsheets
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
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
 | |
 |
No article feedback found. |
| |
Leave Your Feedback |
 | |
| |||
|
I've created this section on my site to share some of the incredible tools that I've used to build my business. I hope you too can benefit from them and look forward to hearing your feedback on the reviews! - Visit Evan Carmichael's Website |
|||
Dianne CramptonDianne Crampton is an executive leadership coach, team consultant, author and president of TIGERS Success Series, Inc. Dianne has been helping CEO's and Executives connect their employees to their core values and goals for over 20 years using the trademarked TIGERS team culture process, which stands for trust, interdependence, genuineness, empathy, risk and success. To download a free white paper on behaviors that build strong teams and behaviors that will predictably tear them down go here. - Visit Dianne Crampton's Website |
|||
Leanne Hoagland-SmithAre your sales where you want them to be? Will you be one of the few who achieves sales or business success or one of the many who have failed to change? Are you tired of being told you are like everyone else? Then you may find my first book on sales of interest. Be the Red Jacket in the Sea of Gray Suits, The Keys to Unlocking Sales available at Amazon or at http://www.processspecialist.com/red-jacket.htm. This book is a reflection of my no-nonsense approach to improving sales to overall business results. If you are truly committed to making sustainable changes, then I can help you secure a positive return on your investment because I focus on executable solutions not telling you the problems you already know you have. From training to corporate (group) coaching to executive one on one coaching, my approach is to assess, create awareness, build a goal driven action plan and then execute. The bottom line question is "Not do you or your employees know it, but do you or they want to do it?" Please call for a free strategy session at 219.759.5601. - Visit Leanne Hoagland-Smith's Website |
|||
|
To learn more about the Evan Elite Author Program please contact us. | |||
 | |
|  |
Subscribe to Gerry's articles
 | |
|
|  |
 | |
|
| |
 | |||||||
|
 | ||
 |
||
 |
||
 |
||
|
| ||
 |
| Have you written articles that would be of value to entrepreneurs? Become an expert on our site by publishing them! Expose yourself to a wide audience, drive more traffic to your website and get more sales! Click Here for details. |
|
|
 |
| Modeling the Masters: Learn the true secrets behind Walt Disney's business success factors & grow your company! Video produced by Phanta Media |
|
|
 |
"Learn straight from Evan how you can Make a Full Time Income (And More) from a Website"
Click Here To Learn More |
|
|
|
|
Get advice & tips from famous business owners, new articles by entrepreneur experts, my latest website updates, & special sneak peaks at what's to come!
|
|
|
 Referral Strategies Customer Retention / Attraction Business Coaching Ideal Website Template Management vs. Leadership |
|
 | ||
 |
Top 50 Social Media Blogs
Top 50 Social Media Blogs | |
 |
Top Social Business Blogs
Top Social Entrepreneur Blogs | |
| ||
 | ||||
| ||||
|
| ||||
| ||||
|
|
|
|
|
||||||||||||
|
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!
|
|
|
| ||||||||||||
| ||||||||||||
|
|
||||||||||||