VIEWS: 10 PAGES: 40 POSTED ON: 2/8/2013 Public Domain
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 "Solver Add-In," then click "OK." the following Excel functions: adsheets: 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: Last Trade: 32.21 Trade Time: 4:00pm ET Change: Up 0.86 (2.74%) Prev Close: 31.35 Open: 31.56 Bid: N/A Ask: 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 Volume Adj Close 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: