Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

848 Chapter 35

ABCDEFGH I

Growth 15%

Year Cash flow

1 100.00

2 115.00 <

=B5*(1+$B$2)

3 132.25

4 152.09

5 174.90

6 201.14

7 231.31

Cash Flow Graph When Growth = 15.0%

CHART TITLES THAT UPDATE AUTOMATICALLY

Chart title that follows contains text function

="Cash Flow Graph When

Growth = "&TEXT(B2,"0.0%")

Cash Flow Graph When

Growth = 15.0%

100

150

200

250

1234567

Year

Cash flow

ABCDEFGH I

Growth 5%

Year Cash flow

1 100.00

2 105.00 <

B6*(1+$B$3)

3 110.25

4 115.76

5 121.55

6 127.63

7 134.01

CHART TITLES THAT UPDATE AUTOMATICALLY

Cash Flow Graph When

Growth = 5.0%

100

150

1234567

Year

Cash flow

Once we have completed the necessary steps, changing the growth rate

will change both the graph and its title:

849 Some Excel Hints

To make graph titles update automatically, carry out the following

steps:

•

Create the graph you want in the format you want. Give the graph a

“proxy title.” (It makes no difference what, you’re going to eliminate it

soon.) At this stage your graph might look like this:

ABCDEFGH I

Growth 12%

Year Cash flow

1 100.00

2 112.00 <

=B5*(1+$B$2)

3 125.44

4 140.49

5 157.35

6 176.23

7 197.38

Cash Flow Graph When Growth = 12.0%

CHART TITLES THAT UPDATE AUTOMATICALLY

Chart title that follows contains text function

="Cash Flow Graph When

Growth = "&TEXT(B2,"0.0%")

asdfasdf

100

150

200

250

1234567

Year

Cash flow

•

Create the title you want in a cell. In this example, cell D21 contains

the following formula: =“Cash Flow Graph When Growth =

“&TEXT(B3,“0.0%”)

•

Click on the graph title to mark it, and then go to the formula bar and

insert an equal sign to indicate a formula. Then point at cell D22 with

the formula and click [Enter]. In the next picture, you see the chart title

highlighted and in the formula bar “=Changing chart titles!$D$22” indi-

cating the title of the graph.

850 Chapter 35

35.7 Getformula: A Useful Way of Annotating Spreadsheets

Throughout this book we have used a homemade Excel function to

annotate our spreadsheets by showing the formulas inside cells. The

formula, called Getformula,

is illustrated in the next spreadsheet:

1. I thank Maja Sliwinski and Benjamin Czaczkes for helping develop this formula.

ABCD E F

Year Cash flow

Discount

rate

Present

value

1 100 5% 95.24 <

=B3/(1+C3)^A3 <

=getformula(D3)

2 200 6% 178.00 <

=B4/(1+C4)^A4 <

=getformula(D4)

3 300 7% 244.89 <

=B5/(1+C5)^A5 <

=getformula(D5)

4 500 8% 367.51 <

=B6/(1+C6)^A6 <

=getformula(D6)

Present value 885.64 <

=SUM(D3:D6) <

=getformula(B8)

885.64 <

{=SUM(B3:B6/(1+C3:C6)^A3:A6)} <

=getformula(B9)

USING GETFORMULA IN A SPREADSHEET

851 Some Excel Hints

The annotation in cell E3 is generated by the formula Getformula(D3),

and the annotations in other cells are similarly generated.

Getformula has two interesting properties:

•

It is dynamic: If you change something in your spreadsheet, Getfor-

mula adjusts automatically. As a result, you will never have to worry

about showing the correct cell contents! Here’s an example: In the

following spreadsheet, we’ve added a column and a row to the

computations:

ABCDE F G

Year Cash flow

Discount

rate

Present value

1 100 5% 95.24 <

=C4/(1+D4)^B4 <

=getformula(E4)

2 200 6% 178.00 <

=C5/(1+D5)^B5 <

=getformula(E5)

3 300 7% 244.89 <

=C6/(1+D6)^B6 <

=getformula(E6)

4 500 8% 367.51 <

=C7/(1+D7)^B7 <

=getformula(E7)

Present value 885.64 <

=SUM(E4:E7) <

=getformula(C9)

885.64 <

{=SUM(C4:C7/(1+D4:D7)^B4:B7)} <

=getformula(C10)

USING GETFORMULA IN A SPREADSHEET

We've added a column and a row--Getformula automatically shows the adjusted

cell formulas

•

Getformula automatically adds curly brackets { } to array formulas. The

contents of cell C10 (which was cell B9 in the original example) illustrate

an array technique discussed in Chapter 34 for computing the present

value where each cash ﬂ ow has a separate discount rate:

()

∑

Getformula senses that this is an array formula and puts in the

brackets.

35.7.1 Adding Getformula to Your Spreadsheet

Getformula is a VBA user-deﬁ ned formula, a topic discussed in Chapter

36. To add such a formula to your spreadsheet, ﬁ rst open the spreadsheet

in which you want the formula to work. Push [Alt] + F11. This action will

open the VBA editor. The screen will look something like the

following:

852 Chapter 35

(Depending on all kinds of things, your screen may look different. Don’t

worry about the differences.)

Hit Insert|Module:

853 Some Excel Hints

Now insert the following text into the module window. You can copy this

text from any of the spreadsheets attached to this book.

Function getformula(r As Range) As String

Application.Volatile

If r.HasArray Then

getformula = “<– ” & “ {“ & _

r.FormulaArray & “}”

Else

getformula = “<– ” & “ ” & r.FormulaArray

End If

End Function

Function ggetformula(r As Range) As String

Application.Volatile

If r.HasArray Then

ggetformula = “ {“ & r.FormulaArray & ”}”

Else

ggetformula = r.FormulaArray

End If

End Function

Close VBA window (no need to save). The formula is now part of the

spreadsheet and will be saved along with it.

35.8 Putting Greek Symbols in Cells

How do we type Greek letters in a spreadsheet?

Initial stock price 30

Mean,

15%

Standard deviation,

20%

Delta,

0.004

GREEK AND SYMBOLS

IN CELLS

854 Chapter 35

This task is fairly simple, if you know the Latin = alphabet equivalents

for the Greek letters. (For example, m and s are lowercase m and s,

respectively; Σ and Δ are uppercase S and D.) For example, we ﬁ rst typed

“Deltat, Dt” into cell A5 and then marked the D in the formula bar:

We then changed the font from “Arial” to “Symbol”:

Pressing [Enter] produces the desired result.

35.9 Superscripts and Subscripts

It is not a problem to type subscripts or superscripts in Excel. Enter text

into a cell, and then mark the letters you want to turn into a subscript

or superscript:

855 Some Excel Hints

Now go to Format|Cells and mark the Superscript box:

Here’s the result:

856 Chapter 35

Cannot put superscript and

subscript one above the other

SUPERSCRIPTS/SUBSCRIPTS IN

CELLS

As you can see in cell A4, you cannot put a subscript and a superscript

on the same letter. That is, you cannot create x

35.10 Named Cells

It is sometimes useful to give a name to a cell. Here’s an example:

AB C

Income 15,000

Tax rate 33%

Tax paid 4,950 <-- =B3*B2

NAMED CELLS

We want to be able to refer to cell B3 by the name “tax.” To achieve

this purpose, we mark the cell and then go to the name tab on the

toolbar:

Typing in the word “tax” on the highlighted “B3” allows us to reference

cell B3 by this name anywhere in the Excel notebook:

857 Some Excel Hints

AB C

Income 15,000

Tax rate 33%

Tax paid 4,950 <

=tax*B2

NAMED CELLS

Sometimes Excel lets us use cell names without ever actually going

through the procedure just described. In the next example, Excel lets us

use the column headers as cell names:

AB C D

Sales Margin Profit

1000 20% 200 <

=Sales*Margin

5000 30% 1500 <

=Sales*Margin

35.11 Hiding Cells

In this text we have often hidden the cell contents of data table headers.

(This topic is discussed in detail in Chapter 30.) Here’s a simple data

table:

DCBA

Payment 100

Number of payments 15

Discount rate 15%

Present value $584.74 <

=PV(B4,B3,-B2)

PV of

payments

Data table

584.74 <

=B5 , data table header

0% 1,500.00

3% 1,193.79

6% 971.22

9% 806.07

12% 681.09

15% 584.74

18% 509.16

21% 448.90

HIDING CELLS

The data table header in cell C8 is necessary for the table to work, but

it is ugly and may be confusing if the table is copied into other docu-

ments. To hide the contents of C8, mark the cell and go to the Format|Cells

menu (or click on the right mouse button):