Now, I want to share a detailed example of the process of building an actual spreadsheet, mostly to get you to develop a style you can settle into that will allow you to proceed quickly and maintain a certain quality of standards in your work. The actual formulas used in the spreadsheet example are of almost no consequence. Pay attention, instead, to the process and the flow of building the spreadsheet and the things that are done to facilitate the spreadsheet construction.
Before you work your way through the example, here is an overview the fundamental motions to go through when you create new spreadsheets:
1. State your excel goal(s).
First, you’re going to have some original goal, which essentially will be unchanging.
2. Sketch a rough excel outline.
The outline should contain how you want your spreadsheet to look. It will easily suffice to write out a few notes, schematics, or both on a single sheet of paper. Basically, you want to identify the important inputs and outputs and perhaps a placeholder for something that would perform a computation to get to the output from the input.
3. Procure sample or representative data for your excel.
Start thinking about where you’re going to get the data for use in your
computations. You would do well to have a couple of “Use Cases” in mind. Sometimes, computations can get a little hairy. Knowing the results of one or several sample computations will go a long way toward constructing your worksheet.
4. Start building excel spreadsheet (with formulas).
At this point you should be more than ready to begin the spreadsheet.
With a blank spreadsheet or a pre-built template, start entering the basic
labels of the inputs and outputs as well as some sample/representative
data. Don’t yet worry about completeness or correctness of any of the formulas. Just have it follow along the lines of what you’d do on your paper scratch pad. Indeed, you should start thinking of your spreadsheet as an electronic scratch pad. Excel provides the ability to move and rearrange cell contents as well as insert/delete rows and columns. You should be putting this capability to use.
Now that you have some labels and some input numbers, start entering some of the spreadsheet formulas. Get them working to produce numbers that you know should be correct. If necessary, hardwire the formulas. There will be time to go back and clean up. Do you have all the information you need to complete the computation(s)? Perhaps, you need to insert a couple of rows in your spreadsheet for intermediate calculations. These interim calculations could simply be for your personal edification (and help you verify a complex chain of calculations), or you could present these interim results as part of your final spreadsheet. They may be appropriate subtotals that are to be incorporated into the final results.
For example, if your final result is a net gain or loss, you may have one set of computations for the revenue side and another set of computations for the expense or cost side. The final result is obtained by subtracting the expense/cost portion from the revenue portion. Presenting these interim numbers not only provides a helpful way to work through and validate the computation but also serves to represent meaningful accounting information that may well be worth presenting in the final spreadsheet.
5. Focus on getting correct results.
Don’t labor too much over how you want to present the final results.
Concentrate at this stage on getting correct results and validating your
formulas. You may have one way at arriving at a number and getting
your expected results. In reality, you know that you want to use an entirely different way of computing your results for the completed spreadsheet. It may be a simplification of some “megaformula.” Insert more rows and/or columns in your spreadsheet to accommodate your new calculations. Don’t be too quick to throw away your first set. In particular, if a quantity you’re computing is calculated in two different ways, then when you subtract one number from the other, the results should always be zero, right? If this is not the result you get, then somewhere, there may be an error in the logic that you need to identify.
6. Refine excel spreadsheet’s appearance.
After you have gained confidence in your basic spreadsheet model or set
of computations, start refining the appearance of your spreadsheet. You
should veer toward a consistent but easily modifiable and reproducible
look and feel, or branding. This appearance should be characteristically
the same throughout all your spreadsheets. The uniformity will not only
set up expectations amongst your colleagues but also help you to go past
the mechanics of production and focus your efforts where they’re needed most—getting the numbers right.
So, for uniform appearance, think about what elements you would want
to have in every spreadsheet you produce. I make the assumption that the
spreadsheets you prepare will at some point or another be reviewed by
some third party, whether your manager or boss, colleagues within your
company, or some auditor or adversarial party at a later date. In the latter
case, there’s a good chance you may not be anticipating such scrutinizing
at the time that you’re preparing the spreadsheet. Sorry for the sinister
tone, but business is business regardless of how well intentioned and conscientious you may be.
Sometimes the numbers can be rock solid, but if the presentation isn’t up
to par, your ability to convince others of your findings will be needlessly
weakened. The converse is true and even more extreme. If your spreadsheets look great and convincing but lack the rock-solid correctness, there is the potential that the spreadsheet will be applied in a manner in which it wasn’t originally intended. This result could be costly. The shortcomings may not be discovered until well after the fact.
7. Rework excel spreadsheets for maintainability.
After you get past the stage of establishing the basic spreadsheet appearance and computational correctness, you should rework the spreadsheet to make it easy to maintain. To do so, ask yourself these questions:
- Can the formulas be simplified?
- How about assigning names to important cell ranges and substituting
those names into the formulas? (Six months from now you’ll be thankful
that you put in names instead of cell references)
- Is something in your spreadsheet bound to change? The obvious item
that comes to mind is the range of dates. You may also have some scalability issues looming. Your spreadsheet in its current incarnation may need to track only a small number of items, perhaps ten or so; or, it
might be suited only to summarizing data that spans a week or two,
not months or years. The purpose of your spreadsheet may change over
time. You may want to rework portions of your spreadsheet as a preemptive measure.
- Do you want to be a victim of your own success? Your spreadsheet
might work so well for your department that a manager saddles you
with the burden of preparing a consolidated spreadsheet across multiple
departments. You don’t want to be dealing with two issues: increased
complexity and getting into the habit of preparing one-offs (a different
implementation for each department).
- For the time being, this is enough to be thinking about in advance. It’s time to get some hands-on involvement and work through the process of preparing the spreadsheet.