Microsoft Office Tutorials and References
In Depth Information
Basic Spreadsheet Types
own by using VBA. Custom worksheet functions can often simplify your formulas and make your
spreadsheet easier to maintain.
In Chapter 10, you’ll find everything you need to know about creating custom
worksheet functions, including lots of examples.
By a single-block budget, I mean a spreadsheet (not necessarily a budget model) that essentially
consists of one block of cells. The top row might contain names that correspond to time (months,
quarters, or years), and the left column usually contains categories of some type. Typically, the
bottom row and right column contain formulas that add the numbers together. There may or
may not be formulas that compute subtotals within the block.
This is a very common type of spreadsheet. In most cases, simple single-block budget models are
not good candidates for applications because they are simple to begin with, but there are
exceptions. For example, you might consider converting such a spreadsheet into an application if the
model is an unwieldy 3-D spreadsheet, needs to include consolidations from other files, or will be
used by departmental managers who may not understand spreadsheets.
Many consider the what-if model category to be the epitome of spreadsheets at their best. The
ability to instantly recalculate thousands of formulas makes spreadsheet software the ideal tool
for financial modeling and other models that depend on the values of several variables. If you
think about it, just about any spreadsheet that contains formulas is a what-if model (which are
often distributed as templates). Changing the value of a cell used in a formula is akin to asking
“what if . . .?” My view of this category, however, is a bit more sophisticated. It includes
spreadsheets designed exclusively for systematically analyzing the effects of various inputs.
What-if models often benefit from additional work to make them more user-friendly, especially if
the model will be used for a lengthy period of time. Creating a good user interface on an
application can make it very easy for anyone to use, including computer-illiterates. As an example, you
might create an interface that lets users provide names for various sets of assumptions and then
lets them instantly view the results of a selected scenario and create a perfectly formatted
summary chart with the click of a button.
Data storage and access spreadsheets
A large percentage of Excel workbooks consist of one or more database tables (sometimes
known as lists ). These tables are used to track just about anything you can think of. Most people
find that it’s much easier to view and manipulate data in a spreadsheet than it is using normal
database software. If the tables are set up properly, they can be summarized with a pivot table.