This is the first of six tabbed forms. They show formats identical
to the ones we saw for the Style settings and work the same way.
The Number form shown here is the most different from the one
seen for setting styles. Both the global and local settings give you
total control of how your model will look.
The default column-width setting in Excel is 8.5. You can change
the global setting for each sheet by doing the following:
1. Click Format on the menu bar.
2. Click on Column.
3. Click on Standard Width and change the width setting.
A width of 10 gives enough room for more numbers or
decimal places, without making the columns look too wide.
For the title column, a width of 30 or so will give you plenty
of room for entering the titles for each row.
Starting Out 37
In the figures in the following chapters, you will see shaded
areas in the spreadsheet illustrations. This is a way of showing
where the input cells are located. It is a good idea to highlight
the background of the cell, using a pale yellow, for example.
Additionally, you may want to set the fonts to bright blue.
If you use only a blue font on a white background, when the
model is empty of inputs, you (and especially other users of your
model) will have a hard time knowing which are the input areas
and which are not.
You set the shading (called â€˜â€˜patternâ€™â€™) for the cell by the
command Format > Cells, which will get you to the user form
shown in Figure 3-12. The pattern that is most pleasing to the
eye is the lightest yellow on the upper part of the grid, above the
line. The lower part is used for chart coloring, but because it is
not seen when you call up this palette using the Color Fill icon,
you should not use it if you want to take advantage of the quick
change using the icon.
It may be that the lightest yellow in the upper section (or
any of the colors you wish to choose) is not quite the right shade.
My own preference is that the yellow should be the shade of the
lower section. In this case, we can modify the color in the given
palette through the Tools > Options > Color command. You will
see a similar user form. (See Figure 3-13.)
By selecting a square of color in the palette and then clicking
on the Modify button, you can change the color for that square.
The change can be as dramatic as changing one color to another,
or as subtle as just changing the shading or hue.
When you click on Modify, you can change the colors in
either of two ways. The â€˜â€˜Standardâ€™â€™ tab gives you a large color
palette from which you can select the color just by clicking on
that color. A box at the bottom right shows the new color you
have selected against the current setting. (See Figure 3-14.) The
second way is to click on the â€˜â€˜Customâ€™â€™ tab. You can make
literally millions of colors by manipulating the red/green/blue
settings or by moving the cross hairs on the spectrum and
the arrowhead on the scale at the right. If you are trying to
Starting Out 39
determine a color, it helps to first set the arrowhead lower on the
scale to deepen the color. This makes it easier to determine
the color you want by just shifting the cross hairs (the deeper
color makes it easier to distinguish between hues) before setting
the arrowhead up again to get the shading you want. (See Figure
Once you have changed the colors in the basic palette, that
setting is saved in the file.
There are two settings on the Tools > Options > Edit tab that you
might want to consider changing. (See Figure 3-16.)
The first setting option is â€˜â€˜Edit directly in cell.â€™â€™ With this
checked, when you double-click on a cell with a formula in it,
the formula will appear overlaying that cell. It will also appear in
the formula bar at the top of the screen. Although it may be
a personal preference to see the formula this way, you lose one
benefit: if this is not checked, then double-clicking will make
Excel highlight the cells that are the precedent cells to the
active cell (i.e., the cells that feed into the current cell). In other
words, with this unchecked, you can have a quick audit function
of the elements of the current cell.
The second setting option is â€˜â€˜Move selection after Enter.â€™â€™
With this on, after you press Enter, the cursor will automatically
move to the next cell in the direction specified here. For certain
tasks when you are going to be entering a list of entries, this may
be helpful. But for general work, when you only want to stay at
the entry you have just made or move to a different direction,
you should uncheck the preset automatic move.
As default, Excel will calculate with the settings shown in Figure
3-17. Letâ€™s look at the settings in the top two sections.
Starting Out 41
Calculation is the mode for refreshing the file. The three
choices in this mode are as follows:
Automatic. There is a calculation refresh every time
you make a change to your file. For a small model
and/or a fast computer processor speed, you can leave
this on. But if you have a larger model and/or an old
computer, this refresh can take a discernableâ€”and
annoyingâ€”pause every time you make a change, however
small. When that happens, you might want to select
Automatic except tables. This is also an automatic refresh,
with the exception of the calculation of data tables. Data
tables are tables that you can set up in Excel in order to
make sensitivity tables. Excel creates the internal calcula-
tions for a table that can test, for example, the IRRs
(internal rate of return) for a set of cash flows based on
different exit years (for the x axis) at different exit
multiples (for the y axis). However, data tables are hugely
calculation intensive and can slow down the calculation
refresh of the whole model, even if the nondata table
calculations are fairly straightforward. When this happens
you should select this option. When you do want to
calculate the data tables, press F9.
Manual. This setting turns off the automatic calculation
refresh and may be the best if you are working with larger
models in slower machines. In order to have the number
in the file calculate correctly, you must press the F9 key.
An important point to remember with this setting is to
refresh the numbers before printing.
Under the Manual setting is a check box for automatically
calculating the file before you save it. This check box is enabled
only when the manual setting is on (since on the other settings,
the worksheet is always refreshed). The only reason for having
this checked is if you are working with external links and you
need to get the data in before you save it. Of course, if you
remember to press F9 before saving, you need not have this
Starting Out 43
In the context of Excel, iteration would be required if you have
circular references and you want the calculation to be performed
over the formulas in the â€˜â€˜loopâ€™â€™ again and again. Iteration, after
all, means â€˜â€˜to do something again or repeatedly.â€™â€™
Usually, circular references are to be avoided, but, for our
modeling work, circular references can be useful. A short circular
reference happens when Formula A reads Formula B, which
reads in turn Formula A. (The shortest is when Formula A
reads itself.) Longer circular references have more intervening
formulas, but the last one reads Formula A.
A circular reference is useful for converging into a solution.
Here is an example for the calculation of interest expense based
on the amount of debt required in a balance sheet:
1. You are calculating the amount of debt that a company
requires based on the â€˜â€˜plugâ€™â€™ number that fills the
financing gap between the assets and the nondebt liabil-
ities and shareholdersâ€™ equity. Letâ€™s say the plug is $100.
2. This new plug carries an interest rate of 10 percent or
$10 for the year.
3. The additional interest expense decreases net income
(letâ€™s assume no tax) and therefore shareholdersâ€™ equity
through retained earnings. This creates another finan-
cing gap of $10, so the plug is now $110.
4. The interest expense is now $1 more at $11 ($110 * 10%),
5. An additional financing gap of $1 is produced. Now the
plug is $111.
6. The interest expense is $11.10 ($111 * 10%).
7. The financing gap increases by $0.10.
8. The interest expense is $11.11 ($111.10 * 10%).
9. The financing gap increases by $0.01.
10. The interest expense is $11.111 ($111.11 * 10%).
11. And so on. . . .
In this example, the numbers are converging: they are
coming to a relationship with each other in which the changes