Home
Web Sites
Consulting
Accounting
Digital Life
Articles
Projects
eLearning
Contact Us
|
The Golden Rules for Correct Calculations
Written on September 13, 1998 by Robert & Karen Vanderzweerde
Appeared in Greenmaster Magazine on October/November 1998
What’s a spreadsheet? Spreadsheet software is the
computerized version of a tool used by accountants in "olden days" – a
large piece of paper with many columns. Accountants used columnar paper (legal size
paper with up to 8 columns ruled on the page) to summarize data. When they needed
more room, they would tape two (or more) pieces of this paper together. Eventually,
paper manufacturers started to make the 17" x 14" paper so the taping was no
longer required. This special paper was nicknamed a "spreadsheet" because
it had to be folded to fit in a file and then spread open to review the data. If you
wanted to summarize monthly data for an entire year, then you had to use a spreadsheet.
Fortunately, the computerized version of this large piece of paper is easier to use.
Taping is not required, the size is unlimited, and the computer will perform
computations for you! Change a number, and the computer will automatically update
your entire spreadsheet. Sensitivity analysis, where you explore the impact of
different factors (often best case, worst case, and most likely scenarios) is now
painless.
The first computerized spreadsheet was a program called "Visicalc", and it
revolutionized personal computing. Prior to Visicalc, there was no serious business
software to run on the new personal computers designed by companies such as Apple and
Osborne. Visicalc changed the market. Large computers running business
applications (mainframes and minicomputers) had nothing like Visicalc. Since
budgeting, forecasting and analysis are important but time consuming tasks in most
businesses, this wonderful new tool fueled business spending on computers that had
previously been targeted to home applications. Spreadsheets even preceded word
processing software.
Spreadsheets have evolved from the early, limited versions of the 1980’s.
The two most popular spreadsheet software programs in use today are Excel (part of the
Microsoft Office Suite) and Lotus 1-2-3 (part of the Lotus SmartSuite), although there are
others.
On the electronic spreadsheet, rows are identified by numbers; columns by letters.
A1 is the first entry, or cell, and it is in the upper left corner. Beside A1
is B1; under A1 is A2. The pattern repeats. After column Z comes AA, etc.
The cursor will look like a box the width of a column in a spreadsheet program.
You can enter numbers, text, or formulas in each cell. The commands may vary,
depending on the software you use, but all have similar functionality.
How you design your spreadsheet will determine how easy it is to update and whether it
will calculate properly. Some tips to prevent common design problems follow:
Anticipate changes in your formulas
If you have three or four lines and need to total them, how you define the formula for
the total will determine if your spreadsheet will still add if you add another row or
column. Learn how to use the SUM function in your software, and make sure the range
is large enough so that if you insert a new row or column at the end, it will still be
included in the range specified in the SUM function. Since SUM is probably the most
used function in spreadsheets, there is a shortcut key for it in the standard toolbar.
In Excel, the sum function button looks like å .
Remember that if your spreadsheet has totals in both directions (down and across), if you
insert a new line or column the SUM function will only be updated in one of the two
directions. You must copy the formula into the other.
Manually spot check your work
It is easy to type B13 when you mean B113. Whenever you first build a
spreadsheet, print it and test check the adds and key calculations. You can then
rely on your spreadsheet if you make minor changes, but if you make major modifications,
re-check it.
Keep changes to a minimum
Sometimes there are factors that are used repeatedly in a spreadsheet, such as interest
rates, gross margin percentages, commission rates, inflation factors, etc. If you
play with these factors to see the impact on your budget, you must find each spot in your
spreadsheet where the factor is used and change it. This can easily create an error
if you miss some of the places where the factor is used. An easier way is to specify
the factor in one spot of your spreadsheet and then refer back to the one cell every time
you need the factor in your calculation. For example, if your budget calls for
interest calculations, put the interest rate in cell B1. Assume the interest rate is
7% on a $200,000 loan. On the interest line of your budget, rather than using the
formula =200000*7%/12, use the formula =200000*$B$1/12. Copy the formula across all
the months of your budget. Do the same thing every time you need to calculate
interest. If you need to make the interest rate 8%, then just change the number
entered in B1 and the spreadsheet will update itself.
Understand relative and fixed references
When you enter a formula, it is assumed to be a relative reference. This means
that if you copy the formula to another cell, the formula will be changed accordingly.
A formula to total a column of numbers might be, for example, =SUM(B5:B20).
This would total rows 5 to 20 in the second column – in a typical budget spreadsheet,
the first column is used for text, and the second column, column B, is the first month of
the year. This formula is also true for columns C, D, etc. for the remaining months
of the year. You can enter the formula in column B and then copy it to the remaining
columns. Because the computer assumes a relative reference, it will change the
formula to read =SUM(C5:C20) in column C, =SUM(D5:D20) in column D, and so on.
Sometimes, this is not the result you need. In our interest example, we want the B1
in the formula to always stay B1, and not become C1, D1, etc. By putting dollar
signs around the column and row names ($B$1), the reference will remain fixed.
Thus, when we copy our formula =200000*$B$1/12 into another column, the formula will
always stay the same. You can have appropriate variations as required: $B1, B$1,
$B$1. When preceded by $, the row or column will stay the same; without $, the row
or column will change when copied.
Don’t re-copy numbers
Any time you have to manually re-copy numbers in your spreadsheet, you set yourself up
for errors. Learn how to link spreadsheets together (it’s easy!) if you need to
prepare summaries of more detailed spreadsheets, or if you need to combine numbers from
different spreadsheets. Learn how to use the copy command so that you don’t
endlessly re-type.
Format your spreadsheet
Take a few moments to make your spreadsheet easy to read. Blank lines,
underlines, and appropriate numeric formats make your spreadsheets easier to read.
Use the right align button to make column headings line up with your numbers. Use
spell check. Adjust the column width on your columns to make sense for what the
column contains. Adjust the row height to draw attention to important totals.
Use the chart wizard
Sometimes a picture is worth a thousand numbers.
Document
Take a few moments, and a few lines of your spreadsheet, to document what it does, key
formulas, and key assumptions. You can exclude the documentation from your print
range. If your spreadsheet is important, back it up on diskette. Use
meaningful file names so you will know what each spreadsheet does – the latest
versions of the software allow names longer than eight characters.
Save the environment
Use the print preview function to see if your spreadsheet will print the
way you want. Adjust column sizes and margins to make it fit on the page, or use the
Scaling "adjust to fit" command in your Page Setup. If you have many
columns, print landscape (sideways) on legal paper (if your printer can do this). If
you have many rows, print portrait (regular) on legal paper.
|
