Principles of Finance with Excel (Основы финансов c Excel)

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

PFE Chapter 28, Charts and graphs in Excel page 11

Merck Dividends, 1991-2000

500

1,000

1,500

2,000

2,500

3,000

1991 1992 1993 1994 1995 1996 1997 1998 1999 2000

Year

Dividends

28.2. Creative use of legends

If you build your XY chart with data that includes legends, then Excel will generally

transfer them in the proper way to the graph. Here’s an example: We’ve marked the data to

include the column headings:

PFE Chapter 28, Charts and graphs in Excel page 12

Here’s the resulting graph:

500

1,000

1,500

2,000

2,500

3,000

3,500

4,000

1990 1992 1994 1996 1998 2000 2002

Dividends

Purchase of treasury

stock

28.3. Graphing non-contiguous data

Suppose you want to make a graph of columns A, C and D of the Merck data. To mark

these three columns:

• Mark the first column (that is, press the left mouse button and “paint” cells A3:A12)

• Press the [Ctrl] key and mark columns C and D (again, pressing the left mouse button).

At this point your spreadsheet looks like this:

PFE Chapter 28, Charts and graphs in Excel page 13

You can now follow the regular graphing procedure to create the following chart:

Merck Treasury Stock and Option Exercise

500

1,000

1,500

2,000

2,500

3,000

3,500

4,000

1990 1992 1994 1996 1998 2000 2002

Year

Purchase of

treasury stock

Proceeds from

exercise of stock

options

Fine-tuning—changing font size so that the axis labels fit

Look at the x-axis above: It goes from 1990 to 2002 even though the data only goes from

1991 – 2000. This often happens when Excel creates an x-axis for a graph. We’ve already

PFE Chapter 28, Charts and graphs in Excel page 14

shown how to use the Format axis menu to change the axis. But this time when we do this, the

x-axis labels don’t fit properly:

Merck Treasury Stock and Option Exercise

500

1,000

1,500

2,000

2,500

3,000

3,500

4,000

1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001

Year

Purchase of

treasury stock

Proceeds from

exercise of stock

options

Go back into the dialog box and hit the Font tab to change the size of the x-axis font:

Now the graph looks fine:

PFE Chapter 28, Charts and graphs in Excel page 15

Merck Treasury Stock and Option Exercise

500

1,000

1,500

2,000

2,500

3,000

3,500

4,000

1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001

Year

Purchase of

treasury stock

Proceeds from

exercise of stock

options

(There are other ways to accomplish this trick also—if you make the chart bigger, for

example.)

28.4. Graph titles that update

You want to have the graph title change when a parameter on the spreadsheet changes.

For example, in the next spreadsheet, you want the graph title to indicate the growth rate.

This section makes (largely self-explanatory) use of the Text function, which is discussed in Chapter 29.

PFE Chapter 28, Charts and graphs in Excel page 16

ABCDEFGH I

Growth 15%

Year Cash flow

1 100.00

2 115.00 <-- B6*(1+$B$3)

3 132.25

4 152.09

5 174.90

6 201.14

7 231.31

GRAPH TITLES THAT UPDATE AUTOMATICALLY

Cash Flow Graph When

Growth = 15.0%

100

150

200

250

1234567

Year

Cash flow

Once we have completed the necessary steps explained below, a change in the growth rate will

change both the graph and its title:

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

GRAPH TITLES THAT UPDATE AUTOMATICALLY

Cash Flow Graph When

Growth = 5.0%

100

150

1234567

Year

Cash flow

To make graph titles update automatically, carry out the following steps:

PFE Chapter 28, Charts and graphs in Excel page 17

• Create the graph you want in the format you want it. 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:

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%

GRAPH TITLES THAT UPDATE AUTOMATICALLY

asdf

100

150

200

250

1234567

Year

Cash flow

• Create the title you want in a cell. In the example above, cell D20 contains the formula:

="Cash Flow Graph when Growth = "&TEXT(B2,"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 D20 with the formula and click [Enter]. In the

picture below, you see the chart title highlighted and in the formula bar “=Titles that

update!$D$20” indicating the title of the graph. Note that “Titles that update” is the

name of the spreadsheet.

PFE Chapter 28, Charts and graphs in Excel page 18

Summary

There’s lots more you can do with Excel charts, but we’ve covered the essentials. The

exercises to this chapter will show you some more variations.

PFE Chapter 29, Functions in Excel page 1

CHAPTER 29: EXCEL FUNCTIONS

This version: March 27, 2003

Chapter contents

Introduction..................................................................................................................................... 1

29.1. Financial functions................................................................................................................ 3

29.2. Math functions .................................................................................................................... 11

29.3. Conditional functions.......................................................................................................... 21

29.4. Text functions ..................................................................................................................... 24

29.5. Statistical functions............................................................................................................. 27

Summary....................................................................................................................................... 30

Exercises ....................................................................................................................................... 31

Introduction

In this chapter we discuss the principal Excel functions a financial analyst needs to know.

There is some overlap between the discussion here and in other chapters (for example, the NPV

function is discussed in Chapter 1). We also discuss some functions that are not used in this

book, but that are so handy that we include them for reference.

Notice: This is a preliminary draft of a chapter of Principles of Finance by Simon Benninga

(benninga@wharton.upenn.edu

). Check with the author before distributing this draft (though

you will probably get permission). Make sure the material is updated before distributing it. All

the material is copyright and the rights belong to the author.

PFE Chapter 29, Functions in Excel page 2

Here are the functions discussed in this book. Not all are in this chapter; the table below

indicates the functions and where they are discussed (if not in this chapter).

Financial functions

IRR

NPV

PMT

RATE

NPER

( XIRR and XNPV are

discussed in Chapter 31 on

Date and Time functions)

Date and time functions

Discussed in Chapter 31

Math functions

Exp

Round

RoundDown

RoundUp

Truncate

Sqrt

Sum

SumIf

SumProduct

Statistical functions

(all these functions are

discussed in Chapter ?? on

doing statistics in Excel)

Average

Correl

Count, CountA, CountIf

Covar

Frequency

Intercept, Slope, Rsq

Max, Min

Median

Stdev, StdevP

Var, VarP

Large() and Rank()

Lookup functions

HLookup

VLookup

Database functions

DAverage

DSum

DCount

DStdev

DStdevp

DVar

DVarp

DProduct

(these functions are

discussed in Chapter 33 on

data manipulation in Excel)

Text functions

Text

Left, Right, Mid

Combining text in cells

Logical functions