|
Close
adherence to a few simple rules can make your spreadsheet work easier for you,
more professional in appearance, and easier for others to read and/or evaluate.
Note that there are number of things here that are non-negotiable, including but
not restricted to cell referencing, sloppy printing, circular references, and
references to worksheets that I do not have, Attention to these will save you
MUCH grief.
-
ALWAYS use
cell referencing. NEVER enter the same value more than once, and NEVER use a value in a formula. The only exceptions are those things which
are “standard” or common knowledge [e.g., weeks have 7 days, days have 24
hours, years have 12 months, there are 12 inches to a foot, etc.]. This use
of cell referencing will ensure that your model will recalculate if any of
the parameters change. When you submit Excel files to me, I will “test” your
model using alternative values for some parameters. Models that do not
recalculate correctly get zero credit. If a value CAN be derived, ALWAYS
derive it; this will avoid much potential pain. For example, I have seen
many situations in which the printed sum of a column of numbers is
inconsistent with the detail. In a case like that, if you enter the sum as
“given,” you are perpetuating the error, and if you make any changes in the
details, the sum will not recalculate.
Compare the two screenshots
below. Both are from the same assignment during a prior semester. In the
first example, note the values in the cells [highlighted in yellow].
Even if every calculation had been correct, this student would have received
a failing grade on the assignment simply because the spreadsheet model was
not reliable. Any changes in revenues, variable costs, or fixed costs could
not have been reflected in the model. Now look at the second example. Every
formula is dependent on cell references, either within the same worksheet,
or to other worksheets. Any change in any parameter [e.g., December revenue
from Data Sheet!F13] will be reflected immediately throughout the model.
The FIRST thing I will
do when you submit an Excel file is to open it and do a "control-grave"
[that's the accent mark, not what we find in a cemetery, and it is to the
left of the digit 1 on the keyboard]. Control-grave turns "view cell
formulas" on and off. If I see cells that look like those in the first
example above, I will stop grading your work, record a failing grade, and
move to the next file. You can spare yourself much grief by doing a
control-grave yourself before you turn in your work. Replace any values in
formulas with appropriate cell references.
-
DO NOT
bury extended calculations in a single cell. In principle, you could
calculate net income [as in an income statement] within a single cell. If
you were consistent, the answer would be numerically correct, but it would
be impossible for a reader to tell how the number was derived. Lay your work
out as you would on pencil and paper, step by step. Use column labels [and
row headings] to “explain” what is happening.
-
Excel has a
spell check capability. Use it! Work with spelling errors that would be
caught by spell check warrant a failing grade.
-
Compare the
data input in the "These are OK" column on the left with those that get zero
credit on the right:
-
Here is another data input example:
-
FORMAT
cells appropriately. Use appropriate symbols for currency, percentages,
commas for thousands, and so on. Think about whether showing the decimal
places makes sense. If we have numbers in the thousands, showing one or two
decimal places is probably a waste of space; the decimals are irrelevant.
Note that formatting does NOT truncate the values that Excel uses in
calculations. Formatting has only a cosmetic impact. Use the rounding
functions when you really do need to round numbers to the nearest whole
number, nearest thousand, etc.
-
LAY OUT
your spreadsheet so that you don’t have really
wide
and narrow items in the same column.
You can often use the wrap text capability on a column heading so that the
width of the numbers in the column determines the width of the column. Look
how much difference there is in the widths of columns B and C in the
screenshot below.
-
ALWAYS use print
preview to see what your printed output will look like. If one or
two rows [or columns] hang over onto a separate page, do what is necessary
to [1] either reorganize your work so it fits better, or [2] move page
breaks so that they come at more logical locations. Alternatively, specify
rows to repeat at the top [or columns to repeat at the left edge] of
successive pages so that readers will be able to tell which rows and columns
they are looking at. You can also specify the number of pages of width or
height that you want, but sometimes that just doesn’t solve the problem. Be
wary of automatically shrinking everything to fit on a single page; some
folks will not be able to read your work. A good general rule is: If you
wouldn't give it to "the boss" [or a recruiter, or whoever], please don't
give it to me. Sloppy printing results in a failing grade.
-
Use multiple worksheet
tabs within the same workbook [file] to organize different segments of your
work. Some things simply fit better together than others. Some things
require wider [or narrower] columns. Use of multiple worksheet tabs can make
printing and reviewing a lot easier. Use descriptive tab names rather than
the default Sheet1, Sheet2, etc. Shorter names
are better, because you will be able to see more tabs at the same time.
Avoid the use of spaces in worksheet tab names, since spaces make cell
referencing across worksheets more difficult.
-
It’s a good idea to save your work in stages
[e.g., XYZ case revision A.xls; XYZ case revision B.xls; XYZ case revision
C.xls, etc…]. If you mess up somewhere, you can always go back to the
immediately prior revision. This will minimize your time reconstructing your
work. Also set the autorecovery [Tools, Options, Save…] feature in Excel to
an interval like 10 minutes.
If Excel
gives you a message that you have a circular reference, FIX the problem
before you send the file to someone for review. Here is an example. I
entered =SUM(C2:C7); the formula should be =SUM(C2:C6). There is a circular
reference in cell C7, which effectively becomes dependent on
itself. Circular references lead to other errors which may not be traceable,
and therefore warrant a failing grade.
-
You can create links between and among Excel files. However, this can
cause problems when you send someone one of the files but not the other.
Say you have a link between file1.xls and file2.xls. You email me
file1.xls. When I open it, I will get a message telling me that there are links
to other files. However, I will not be able to update the links because I don’t
have the “other” file. If this happens, I will stop grading and enter
a failing grade on the assignment, since I have no way of verifying the
accuracy of your work.
-
Never truncate values. Suppose you need to use the fraction “one third”
in a calculation. You might decide to enter 0.3333. Because Excel carries
calculations to 14 decimal places, any subsequent calculations
which depend on the “one third” will incorporate a rounding error caused by the
difference between 0.3333 and 0.33333333333333. Small numbers have a way of
adding up to big numbers. See the illustration below. The difference of 4,115 is
not huge [less than 1%], but if you are trying to reconcile two sets of numbers,
you can spend hours looking for an error that is easily avoided with careful
modeling.
- To simplify your work when cell referencing
between or among complex worksheets, print the sheet[s] you have developed with the row and column headings [File,
Page setup, Sheet, then check the row & column
headings box]. You will easily be able to track of where things are and speed up
your work.
- Use the
Tools, Formula auditing, Track Precedents [or Track Dependents] commands to verify cell relationships and
worksheet structure. You can display all the formulas in a worksheet by
simultaneously pressing the control key and the grave accent key [to the left of
the digit 1 at the top of the keyboard]. Pressing Control-grave again turns the
formula display off.
|
