# In these spreadsheets by EQ0v8QPm

VIEWS: 10 PAGES: 40

• pg 1
```									Ross, Westerfield, and Jordan's Spreadsheet Master
Essentials of Corporate Finance, 7th edition
by Brad Jordan and Joe Smolira
Version 7.0

Chapter 7
In these spreadsheets, you will learn how to use the following Excel f

Formatting values as text
Concatenation
Web queries
Stock charts

The following conventions are used in these spreadsheets:

1) Given data in blue
2) Calculations in red

NOTE: Some functions used in these spreadsheets may require that
the "Analysis ToolPak" or "Solver Add-In" be installed in Excel.
To install these, click on the Office button
then "Excel Options," "Add-Ins" and select
"Go." Check "Analysis ToolPak" and
the following Excel functions:

Chapter 7 - Section 1
Common Stock Valuation

Constant Dividend Growth

Unlike bond pricing, Excel does not have built-in functions for stock pricing, so we need to create our own equation
we have a stock with the following:

Current dividend:                        \$      2.40
Dividend growth rate:                          5.00%
Required return:                              13.00%

With this growth rate, the dividend next year will be:

Dividend next year:                      \$          2.52

So, the stock price today with the constant dividend growth model is:

Stock price today:                       \$     31.50

The constant dividend growth equation is just the present value of a growing perpetuity, but we could caution that
Using the same information from above, we can calculate the stock price for various growth rates.

g                Stock price
0.00%           \$           18.46
1.00%           \$           20.20
2.00%           \$           22.25
3.00%           \$           24.72
4.00%           \$           27.73
5.00%           \$           31.50
6.00%           \$           36.34
7.00%           \$           42.80
8.00%           \$           51.84
9.00%           \$           65.40
10.00%          \$           88.00
11.00%          \$          133.20
12.00%          \$          268.80

Graphing the stock price versus the growth rate, we can see the impact of the growth rate:

Stock Price for Various Dividend Growth Rates
\$300.00
\$300.00

\$250.00

\$200.00

Stock Price
\$150.00

\$100.00

\$50.00

\$-
0.00%       2.00%   4.00%          6.00%           8.00%
Dividend Growth Rate

Nonconstant Growth

Suppose a stock will pay the following dividends and has the following required return:

Year 1:          \$                             1.00
Year 2:          \$                             2.00
Year 3:          \$                             2.50
Required return:                              10.0%

After the third year, the dividends will grow at:                          5.0%

What is the price of the stock? First, we need to find the price of the stock when it begins a constant growth rate, w

Price in Year 3:                 \$            52.50

The price today is the present value of the future dividends, plus the present value of the future price, so:

Price today:                     \$            43.88

Supernormal Growth

With supernormal dividend growth, it is simply a matter of inputting the correct equations in Excel. Because the eq
t , then use this stock price in the second part of the equation.

Example 7.4: Supernormal Growth
Chain Reaction, Inc., is growing at a phenomenal rate because of its rapid expansion and explosive sales. You want
have gathered the following information:

Initial growth rate:                           30.0%
Years of initial growth rate:                      3
Second growth rate (perpetual):               10.00%
Required return:                              20.00%
Dividends just paid:                  \$    5,000,000

First, we need to find the equity value when the dividends achieve a constant growth rate forever, which will be:

The equity value in year 3 will be:   \$ 120,835,000

So, the equity value today is:

Total equity value today:             \$ 87,569,444

If we know that the number of shares is:

Number of shares:                         20,000,000

Then the stock price per share today is:

Share price today:                    \$          4.38

RWJ Excel Tip

So what's unique about the above calculation? Change the number of years of initial growth in cell D90 and careful
After an equal sign at the beginning of a cell, using quotes causes Excel to treat anything inside the quotes as text.
(&) as a concatenating operator, that is, an operator that joins the contents of two or more cells. We have an ampe
concatenate twice. In between the ampersands, we used the TEXT function to tell Excel to go to the specified cell,
middle of the sentence. The TEXT function used two arguments: Value, which is the cell or value you want to conve
want to use. You can use any format available in the Format menu. We chose pound (#) to have it display as a gene
a whole number. If the number could be a decimal, #.# would make Excel display the decimal in the output.

Three-Stage Growth

Suppose we have a stock that the dividend grows in three (or even more) stages? We can set up a more general mo
important to remember that the price of a share of stock is nothing more than the present value of the future divid

Current dividend:                     \$          1.50
Required return:                                  11%
Perpetual growth rate:                            6%

Dividend growth     Dividend    PV of dividend
1             28.00% \$        1.92 \$          1.73
2             28.00%          2.46            1.99
3             28.00%          3.15            2.30
4             17.00%          4.03            2.65
5             17.00%          4.71            2.80
6             17.00%          5.51            2.95
7             17.00%          6.45            3.11
8             17.00%          7.55            3.27
9             17.00%          8.83            3.45
10              17.00%         10.33            3.64
11              17.00%         12.08            3.83
12               6.00%         14.14            4.04
13               6.00%         14.99            3.86
14               6.00%         15.89            3.69
15               6.00%         16.84            3.52
Present value of first 15 dividends:               \$        46.83

We have the present value of the first 15 dividends. Now, we need to calculate the present value of the future stoc

Stock price in year 15:                                \$       357.00

And the present value of this future stock price is:

Present value of future stock price:                   \$        74.62

The current stock price is simply the present value of the known dividends, plus the present value of the future sto

Current stock price:                                   \$       121.44

You may wonder why we chose to calculate the dividends for the first 15 years, then use the dividend growth mod
project supernormal growth for more than 15 years. In other words, this spreadsheet should accommodate most s
extend for more than 15 years, we could easily alter the spreadsheet.
need to create our own equations. We will begin with constant growth in dividends. Suppose

petuity, but we could caution that the equation is very sensitive to the growth rate estimate.
us growth rates.

wth rate:

nd Growth Rates
8.00%            10.00%          12.00%

turn:

t begins a constant growth rate, which is in Year 3. The price of the stock in Year 3 will be:

e of the future price, so:

quations in Excel. Because the equation is so involved, we will calculate the stock price at time
on and explosive sales. You want to know the current value of the equity of the company and

wth rate forever, which will be:

tial growth in cell D90 and carefully watch cell B97 when you make the change. Neat, huh?
ything inside the quotes as text. To refer to a number in another cell, we use an ampersand
o or more cells. We have an ampersand before and after the TEXT function in order to
Excel to go to the specified cell, grab the number there, convert it to text, and include it in the
he cell or value you want to convert to text, and Format_text, which is the number format you
nd (#) to have it display as a general number. This is fine since we know the number of years is
the decimal in the output.

We can set up a more general model in Excel to handle more growth rate changes. It is
e present value of the future dividends. Suppose we have a stock with the following:
e present value of the future stock price. The price of the stock in year 15 will be:

he present value of the future stock price, or:

en use the dividend growth model. The reason is that it would be somewhat unusual to
heet should accommodate most stock pricing models. If the supernormal growth rate did
Chapter 7 - Section 3
The Stock Markets

Whenever a stock market is open, stock prices will most likely be changing. While we have used Excel in a way that
Excel will also go directly to the internet and update data you select from a website. We wanted to input a stock qu
stock quote from finance.yahoo.com is:

Change:          Up 0.86 (2.74%)
Prev Close:                  31.35
Open:                        31.56
Bid:             N/A
1y Target Est:               41.35

Day's Range:     31.25 - 32.29
52wk Range:      16.42 - 47.50
Volume:                12,241,812
Avg Vol (3m):            7,873,390
Market Cap:      13.37B
P/E (ttm):                   13.88
EPS (ttm):                    2.32
Div & Yield:     0.56 (1.70%)

RWJ Excel Tip
Excel has a built-in Web Query that will grab data from a website. The really useful thing about the Web Query is th
update the data while you are in Excel. To set up a Web Query, go to the Data menu, select "Get External Data," th
This will bring up a New Web Query window that looks something like this:
The web browser used in the Web Query is not as sophisticated as most other web browsers. For example, you can
"Favorite." Anything brought up in the New Web Query window with an arrow like this          is a data table that c
Simply click on as many data tables as you want to download, which will change the arrow to a check mark in a gre
"Import" box on the bottom right of the New Web Query window. This will automatically import the data into Exce
data in calculations. When you are in the spreadsheet and want to update the data, go to the Data tab, and select
automatically go to the website and update all of the numbers in the data table.

NOTE: When you bring a web query into an Excel spreadsheet, if the format of the web page changes, the web que
you attempt to refresh the web query we have done here, if Yahoo! Finance changes the format of this page, you w
data. In this case, you can construct a new web query by following the instructions above.

Suppose you own shares of Best Buy stock and want to know the value of your investment at the current stock pric
refresh the data, Excel automatically updates the following calculation.

Shares owned:                   175
\$
Total value of stock:       5,636.75

A group of analysts called technical analysts examine historic stock prices in an attempt to find patterns in the stoc
it is possible to trade on these stock price patterns and make a profit. Whether technical analysis is actually profita
the stock price charts that are built into Excel's graphing functions. To the right of this spreadsheet you will find pri

Below you will see an open-high-low-close, or candlestick, chart for Google during January 2009.

Candlestick Chart for Google - January 2009
\$370

\$350
\$350

\$330

\$310

\$290

\$270

\$250

RWJ Excel Tip

To graph the candlestick chart, we selected the data for the opening price, high price of the day, low price of the da
order in the table. We then went to Insert, Other Charts, and selected the open-high-low-close chart. We also mad
axis and changed the range for the stock price. Next, in the default setting, Excel graphs down days as a black cand
down day, selected Format down bars, and changed the color to red. We followed the same procedure for the up d

Candlestick charts show you how the stock performed on a particular day and over the entire period. The high and
vertical line, respectively. The open and close are shown as the horizontal lines for each day. For example, you can
Google stock closed up for the day, so the top vertical line is the closing price of the day, while the bottom vertical
down on January 14th, the top horizontal line is the opening price of the day and the bottom horizontal line is the
price charts. We'll leave these for you to explore.
we have used Excel in a way that we can change the inputs,
. We wanted to input a stock quote for Best Buy (BBY). The

thing about the Web Query is that you can automatically
u, select "Get External Data," then select "From the Web."
browsers. For example, you cannot save a website as a
this        is a data table that can be imported into Excel.
e arrow to a check mark in a green box, then click on the
tically import the data into Excel, where you can use the
, go to the Data tab, and select "Refresh All." Excel will

web page changes, the web query will no longer work. When
es the format of this page, you will not be able to refresh the
above.

stment at the current stock price. Notice that when you

mpt to find patterns in the stock price. If patterns are discovered, technical analysts argue that
hnical analysis is actually profitable we will leave to a later time. For now, we will show one of
his spreadsheet you will find price information for Google stock for January 2009.

anuary 2009.

January 2009
ce of the day, low price of the day, and closing price. Excel requires that these values be in this
h-low-close chart. We also made a couple of changes to the original chart. We selected the Y
aphs down days as a black candlestick and up days as a white candlestick. We right clicked on a
the same procedure for the up days and made the up candlesticks green.

the entire period. The high and low price of the day are shown as the top and bottom of the
each day. For example, you can see the range of prices on January 5th. At the end of the day,
e day, while the bottom vertical line is the opening price of the day. Since Google stock closed
he bottom horizontal line is the closing price of the day. Excel has several other default stock
Date    Open      High     Low     Close
1/2/2009    308.6   321.82    305.5   321.32
1/5/2009     321    331.24     315    328.05
1/6/2009   332.98    340.8   326.39   334.06
1/7/2009   328.32   330.91   318.75   322.01
1/8/2009   318.28   325.19   317.34   325.19
1/9/2009    327.5    327.5    313.4   315.07
1/12/2009   316.31   318.95   310.23   312.69
1/13/2009   311.77    320.6   310.39   314.32
1/14/2009     310     313.8   297.75   300.97
1/15/2009   297.57   303.58   286.79   298.99
1/16/2009   305.02   308.25    295.7   299.67
1/20/2009   299.14    299.5   282.75   282.75
1/21/2009   288.35    303.5   288.35   303.08
1/22/2009   298.04   309.35   295.15    306.5
1/23/2009   309.27   331.96   304.22    324.7
1/26/2009   324.85     328    320.56   323.87
1/27/2009   326.45   333.87   324.27   331.48
1/28/2009   337.98   352.33   336.31   348.67
1/29/2009   344.54   345.05   340.11   343.32
1/30/2009   344.69    348.8     336    338.53
3610500     321.32
4889000     328.05
6425200     334.06
4494500     322.01
3600700     325.19
4340500     315.07
3304300     312.69
4432500   314.32
5467900   300.97
5934500   298.99
5224400   299.67
5048200   282.75
4924500   303.08
8226900    306.5
10721100    324.7
4610700   323.87
4927300   331.48
7691400   348.67
7283800   343.32
4672000   338.53
Chapter 7 - Master it!

In practice, the use of the dividend discount model is refined from the method we presented in the textbook. Man
estimate a perpetual growth rate at some point in the future, typically 10 years. Rather than have the dividend gro
period, linear interpolation is applied. That is, the dividend growth is projected to fall by an equal amount each yea
years and the dividends are expected to fall to a 5 percent perpetual growth rate 5 years later, the dividend growth

The Value Line Investment Survey provides information for investors. Below, you will find information for Boeing fo

2008 dividend:                        \$           1.62
5-year dividend growth rate:                      9.5%

Although Value Line does not provide a perpetual growth rate or required return, we will assume they are:

Perpetual growth rate:                            5.0%
Required return:                                 11.0%

Assume that the perpetual growth rate begins 11 years from now and use linear interpolation between the h
a.
table that shows the dividend growth rate and dividend each year. What is the stock price at Year 10? What i

How sensitive is the current stock price to changes in the perpetual growth rate? Graph the current stock pri
b.
out.

Instead of applying the constant dividend growth model to find the stock price in the future, analysts will ofte
ratio valuation, often with the PE ratio. Remember that the PE ratio is the price per share divided by the earn
can solve for the stock price. Suppose we also have the following information about Boeing:

Payout ratio:                                      30%
PE ratio at constant growth rate:                   15

Use the PE ratio to calculate the stock price when Boeing reaches a perpetual growth rate in dividends. Now f
c.
value of the dividends during the supernormal growth rate and the price you calculated using the PE ratio.

How sensitive is the current stock price to changes in PE ratio when the stock reaches the perpetual growth r
d.
in 11 years to find out.
sented in the textbook. Many analysts will estimate the dividend for the next 5 years and then
r than have the dividend growth fall dramatically from the fast growth period to the perpetual growth
y an equal amount each year. For example, if the high growth period is 15 percent for the next 5
rs later, the dividend growth rate would decline by 2 percent each year.

nd information for Boeing found in the 2009 edition of Value Line :

will assume they are:

nterpolation between the high growth rate and perpetual growth rate. Construct a
ock price at Year 10? What is the stock price today?

Graph the current stock price against the perpetual growth rate in 11 years to find

the future, analysts will often combine the dividend discount method with price
er share divided by the earnings per share. So, if we know what the PE ratio is, we
out Boeing:

wth rate in dividends. Now find the value of the stock today finding the present
ulated using the PE ratio.

aches the perpetual growth rate? Graph the current stock price against the PE ratio
Master it! Solution

a.   The dividend growth rates, dividends, and stock price are:

Year                              1            2             3              4              5
Dividend growth:
Dividend:

Price at Year 11:
Price today:

b.   To graph the stock price for different growth rates, we need to calculate the price for various growth rates. U

Growth rate         Stock price
0%
1%
2%
3%
4%
5%
6%
7%
8%
9%
10%
c.   The earnings and price in year 11 will be:

Year 11 PE ratio:
Year 11 earnings:
Year 11 price:

So, the stock price today with this valuation method is:

Price today:

d.   Using a one-way data table, the stock price today at different PE ratios is:

PE ratio        Stock price
10.00
11.00
12.00
13.00
14.00
15.00
16.00
17.00
18.00
19.00
20.00
6              7              8             9           10   11

e for various growth rates. Using a one-way data table, we get the following:

```
To top