Spreadsheet Models for Managers
Demonstrations for Session 1
Introduction to Spreadsheet Models
We have four demonstrations (
2000-4 or
2007+) for this session:
In this demonstration page, and in the others like it for other
sessions, the headings below correspond to the names of the worksheets
in the demonstration workbook.
Running Sums and Differences (2000-4) or (2007+)
[Sheet: RunningSumsAndDifferences]
In this first example, Row 8 contains a stream of numbers that represents the hiring plan for the first six months of the
year. Row 9 is a running sum of Row 8, with the assumption of an initial head count of 5. This illustrates the use of a
running sum, and shows the effect of the initial value.
The block just below, in Rows 13 through 15, illustrates the use of a running difference. Row 14 is again the running sum
of Row 12, with an initial headcount of 5. Row 15 recovers the hiring plan from Row 14 again, by computing the running
difference of the headcount with an initial headcount of 5.
In both of these examples we've "wired in" the initial headcount, by explicitly using a "5" in the first cell of the
running sum or difference. This is a dangerous practice, which I call parameter sprinkling. We avoid these dangers by introducing a named parameter as shown in Row 22.
We've named the cell C22 InitHdct, and used that name to compute
the running sum in Row 20.
In Row 28, we compute the same running sum, this time using the names Lt and Up,
which are named relative references to the left and above.
Scope of Names in Excel (2000-4) or (2007+)
[Sheet: MarketingDept]
This example illustrates the scope of names, and shows the use of local (sheet-level) and global
(book-level) names. Here we have a different hiring plan, and we compute the total headcount as a running sum in Row 8. Row
11 refers to Row 8 by means of its name, Headcount, which is a local name. Row 12 refers to the global name
Headcount, which is defined on the sheet RunningSumsAndDifferences. Notice that the two names
Headcount are different, and that we can still access either one.
Once you have names defined, and you want to use them in formulas, you don't have to actually type them unless you want
to. In Windows, Excel offers a keyboard shortcut, F3, valid only when you're typing in the formula bar, which
presents a dialog from which you can select the name that you want to be inserted into the formula bar. Try it. On the Mac,
use the menu command Insert>Name>Paste…. By default on the Mac, this command lacks a keyboard
shortcut, but the Mac OS makes it easy for you to create one for it. The complete list of keyboard shortcuts for Excel is
available in on-line help: search for keyboard. I've prepared these help entries as PDF files for you:
Excel 2003 (Windows), Excel 2007 (Windows), and
Excel 2004 (MacOS). There are far more
shortcuts than you can remember or use. But if you find yourself doing something very often, check to see if a shortcut
exists.
Organizing Named Parameters in a Workbook (2000-4) or (2007+)
[Sheet: ParameterBlock]
Here's an example of a parameter block.
This example is taken from
a real model I once developed for a client. All of the parameters
of the model of their business are collected in one place, where
they can readily be reviewed and modified. Yellow indicates user-modifiable.
Absolute, Relative and Mixed References (2000-4) or (2007+)
[Sheet: ReferenceExamples]
This example lets you explore how the four different kinds of references behave
when you fill or copy/paste them. Each of the four blocks is
made from one kind of reference. The center cell of each block
points to C8. By replicating that cell
onto its eight neighbors you can see the effects of mixed and relative references.
By selecting a cell in one of the blocks you
can see what happened to the formula of the center cell of that block when it was replicated.
For more about my spreadsheet consulting
and training services, visit SpreadsheetAce.com.
We run our companies, in part, based on spreadsheets, yet we often wonder
if they're really right.
Learn more about improving the reliability and effectiveness of your
financial models.
With dozens of solved problems and special course tools, this course can dramatically increase your
spreadsheet skills. For just USD 199.00 you receive three months of access with unlimited
email support.
Learn more about improving your spreadsheet sophistication and effectiveness.
Identical to the edition you're viewing, but with dozens of solved problems
and special course tools, and
it stands alone — no Internet connection required. For just USD 199.00, it includes
three months of unlimited
email support.
Learn more about improving your spreadsheet sophistication and effectiveness.
f you use Excel to model businesses, business processes, or
business transactions, this course will change your life. You'll learn how to create tools for yourself that will amaze
even you. Unrestricted use of this material is available in two ways.
