Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 27, Excel introduction page 25
Adding more sheets
To add more sheets, go to Insert|Worksheet
You can also delete a sheet (Edit|Delete sheet). This is an irreversible action, so we
suggest you save the workbook before you doing this.
27.7. Using a function
Excel contains many functions. In this section we illustrate a few of these.
3
We’ll go
back to the spreadsheet in Section 27.3. In cell B17 we’ll calculate the average value of the cells
B5:B15 (this has very little economic meaning ... ). The final product will look like this:
3
The discussion in this section is really preliminary and intended to give you a taste of how Excel functions work.
In this book we use many Excel functions. Chapter 29 discusses most of the functions used in the book and Chapter
31 discusses Excel’s date functions.
PFE Chapter 27, Excel introduction page 26
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
AB C
Interest 7%
Year
0 $1,000.00
1 $1,070.00 <-- =B5*(1+$B$2)
2 $1,144.90 <-- =B6*(1+$B$2)
3 $1,225.04 <-- =B7*(1+$B$2)
4 $1,310.80 <-- =B8*(1+$B$2)
5 $1,402.55 <-- =B9*(1+$B$2)
6 $1,500.73 <-- =B10*(1+$B$2)
7 $1,605.78 <-- =B11*(1+$B$2)
8 $1,718.19 <-- =B12*(1+$B$2)
9 $1,838.46 <-- =B13*(1+$B$2)
10 $1,967.15 <-- =B14*(1+$B$2)
Average $1,434.87 <-- =AVERAGE(B5:B15)
COMPOUND INTEREST
To do this:
• In cell A17 we type “Average.” This is known as “annotating the spreadsheet.” In
simple English—tell yourself what you’re doing, because otherwise you’ll forget.
In cell B17, we type “=Average(”, and then hit the f
x
sign on the toolbar:
You’ll see a function dialogue box:
PFE Chapter 27, Excel introduction page 27
You cursor is already in a box labeled Number1. Put the mouse on cell B5, push down
the left mouse button, and drag the cursor to B15. Here’s what you’ll see:
Now let the cursor go:
PFE Chapter 27, Excel introduction page 28
Now press OK in the dialog box. Here’s the result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
AB C
Interest 7%
Year
0 $1,000.00
1 $1,070.00
2 $1,144.90
3 $1,225.04
4 $1,310.80
5 $1,402.55
6 $1,500.73
7 $1,605.78
8 $1,718.19
9 $1,838.46
10 $1,967.15
Average $1,434.87 <-- =AVERAGE(B5:B15)
COMPOUND INTEREST
Suppose you didn’t want to average all the numbers, but only those from years 5-10.
There are two ways to do this:
• You can double click on cell B17, and change the range in the formula to
=Average(B10:B15).
PFE Chapter 27, Excel introduction page 29
• You can double-click B17, and re-click the f
x
sign on the toolbar. This reopens the dialog
box. Now click the
next to the range currently being averaged:
You can now indicate the range (B10:B15) you want to average. A couple of [Enters] should
give you the result.
Practice makes perfect
The exercises to this chapter let you practice with a few functions that work like
Average.
27.8. Printing
You’ve just completed your beautiful first spreadsheet and you want to print it. Press
File|Print. This brings up the following screen:
PFE Chapter 27, Excel introduction page 30
Before printing, press the Preview box:
Notice that the graph is a bit cut off at the right edge. Press Setup and explore the various tabs:
In the Page tab, choose
to fit everything onto one sheet. (You could
also put it on Landscape paper, using the
button on the same tab.)
PFE Chapter 27, Excel introduction page 31
On the Sheet tab, you can choose to print the spreadsheet using Gridlines and Row and
column headings (these are the settings we’ve used for most of the spreadsheets in this book).
Now click OK to see what the printing will look like:
If this suits your purpose, press Print.
Summary
In this chapter we’ve explored the preliminaries of Excel—how to set up a spreadsheet,
save it, type in a formula, use a function, and print your results. The following chapters explore
more advanced Excel techniques.
PFE Chapter 27, Excel introduction page 32
Exercises
Here are three functions which work just like Average:
• Sum—this function adds numbers in a range of cells
• Count—counts the number of non-blank cells in a range
• Countblank—counts the number of blank cells in a range
Play with these functions and see how they work.
PFE Chapter 28, Charts and graphs in Excel page 1
CHAPTER 28: GRAPHS AND CHARTS IN EXCEL
*
This version: August 25, 2004
Chapter contents
Overview..............................................................................................................................2
28.1. The basics of Excel charts..........................................................................................2
28.2. Creative use of legends ............................................................................................11
28.3. Graphing non-contiguous data.................................................................................12
28.4. Graph titles that update ............................................................................................15
Summary............................................................................................................................18
Exercises ............................................................................................................................19
*
This is a preliminary draft of a chapter of Principles of Finance with Excel. © 2001 – 2004 Simon Benninga
(benninga@wharton.upenn.edu
).
PFE Chapter 28, Charts and graphs in Excel page 2
Overview
Excel has extensive facilities to do graphs.
1
If you’re like most Excel users and finance
majors, you’ll be using these facilities a lot.
In this short chapter, we’ll discuss the basics of graphing, assuming that—by and large—
you already know how to make a chart in Excel. We will also discuss some less well-known
techniques that have to do with charts:
• Making a graph with non-contiguous data series
• Changing the axis parameters of a chart
• Making a chart where the title changes when the data changes
28.1. The basics of Excel charts
Every Excel chart has its origins in the data on a spreadsheet:
1
2
3
4
5
6
7
8
9
10
11
12
ABCD
Dividends
Purchase
of
treasury
stock
Proceeds
from
exercise
of stock
options
1991 893 184 48
1992 1,064 863 52
1993 1,174 371 83
1994 1,434 705 139
1995 1,540 1,571 264
1996 1,729 2,493 442
1997 2,040 2,573 413
1998 2,253 3,626 490
1999 2,590 3,582 323
2000 2,798 3,545 641
MERCK & CO. 1991-2000
1
In “Excelese” graphs are called “charts.” We will use both words interchangeably.