# Project #97082 - Finance

How to prepare and present your excel labwork

Please collect all your lab worksheets (pages) on the same Excel Workbook (as if it is a book that has its pages as Excel worksheets). Normally, you are provided three worksheets (Sheet 1, Sheet 2, and Sheet 3). When you need more worksheets, insert a new one. If you want to delete one, right click on the tab at the bottom of your worksheet gives you the delete option. Rename each worksheet with the section numbers (NAME, Sec1, Sec2, Sec3,…, etc., or NAME, S1, S2, S3,…, etc.,). NAME tab is used to write your name and other identification information. Please, color worksheet tabs (using right-click) so that it will be easy to distinguish them. Organization of a typical Excel workbook might look like the following:

Section1: DGM application with dividend data (intrinsic price forecast)

·         Copy and paste the data provided below into a new worksheet.

·         Market price for PG was \$69.17 on 7/18/2012 (the last dividend payment date).

·         Use =COUNT(.) function to find the number of quarters.

·         Assume that the required return is given and it is 15% for PG Co.

·         Find Quarterly Geometric Average Growth by using one of the following formulas:

A) =GEOMEAN(1+g1,1+g2,…,1+gN)-1  (Algebraically).

B) =(XN/X0)^[1/(N-1)] – 1 (Algebraically) where X refers to original dividend data.

·         Convert Quarterly Geometric Average into Annual Geometric Average by using = (1+quarterly geometric average)^4-1

·         Find INTRINSIC PRICE by using DGM model:

where  is equal to the sum of the last 4 quarterly dividends:

use =SUM(.)*(1+annual g)/(k - annual g).

·         Graph quarterly dividends over the time.

Your findings might look like the following:

DIVIDEND DATE FOR PG

 Date Dividends 7/18/2012 0.562 4/25/2012 0.562 1/18/2012 0.525 10/19/2011 0.525 7/20/2011 0.525 4/27/2011 0.525 1/19/2011 0.482 10/20/2010 0.482 7/21/2010 0.482 4/28/2010 0.482 1/20/2010 0.44 10/21/2009 0.44 7/22/2009 0.44 4/22/2009 0.44 1/21/2009 0.4 10/22/2008 0.4 7/16/2008 0.4 4/16/2008 0.4 1/16/2008 0.35 10/17/2007 0.35 7/18/2007 0.35 4/25/2007 0.35 1/17/2007 0.31 10/18/2006 0.31 7/19/2006 0.31 4/19/2006 0.31 1/18/2006 0.28 10/19/2005 0.28 7/20/2005 0.28 4/20/2005 0.28 1/19/2005 0.25 10/20/2004 0.25 7/21/2004 0.25 4/21/2004 0.25 1/21/2004 0.2275 10/22/2003 0.2275 7/16/2003 0.2275 4/15/2003 0.205 1/22/2003 0.205 10/16/2002 0.205 7/17/2002 0.205 6/3/2002 0.345 4/17/2002 0.19 1/16/2002 0.19 10/17/2001 0.19 7/18/2001 0.19 4/18/2001 0.175 1/17/2001 0.175 10/18/2000 0.175 7/19/2000 0.175 4/18/2000 0.16 1/19/2000 0.16 10/20/1999 0.16 7/21/1999 0.16 4/21/1999 0.1425 1/20/1999 0.1425 10/21/1998 0.1425 7/22/1998 0.1425 4/22/1998 0.126 1/21/1998 0.126 10/22/1997 0.126 7/16/1997 0.12625 4/16/1997 0.1125 1/22/1997 0.1125 10/16/1996 0.1125 7/17/1996 0.1125 4/17/1996 0.1 1/17/1996 0.1 10/18/1995 0.1 7/19/1995 0.1 4/17/1995 0.0875 1/13/1995 0.0875 10/17/1994 0.0875 7/18/1994 0.0875 4/18/1994 0.0775 1/14/1994 0.0775 10/18/1993 0.0775 7/19/1993 0.0775 4/19/1993 0.06875 1/15/1993 0.06875 10/19/1992 0.06875 7/20/1992 0.06875 4/20/1992 0.06875 1/17/1992 0.0625 10/11/1991 0.0625 7/15/1991 0.0625 4/15/1991 0.0625 1/14/1991 0.0625 10/15/1990 0.0625 7/16/1990 0.05625 4/16/1990 0.05625 1/12/1990 0.05625

Section2: DGM application to forecast intrinsic price

·         Copy and paste the data provided below into a new worksheet.

·         Market price for General Electric Company (GE) was \$24.28 on 10/15/14.                                                                                                                                                   General Electric Company was \$49.12 on 7/18/2012 (the last dividend payment date).

·         Use =COUNT(.) function to find the number of quarters.

·         Assume that the required return is given and it is 15% for GE.

·         Find Quarterly Geometric Average Growth by using one of the following formulas:

A) =GEOMEAN(1+g1,1+g2,…,1+gN)-1  (Algebraically)

B) =(XN/X0)^[1/(N-1)] – 1 (Algebraically) where X refers to original dividend data.

·         Convert Quarterly Geometric Average into Annual Geometric Average by using = (1+quarterly geometric average)^4-1

·         Find INTRINSIC PRICE by using DGM model:

where  is equal to the sum of the last 4 quarterly dividends:

use =SUM(.)*(1+annual g)/(k - annual g) where r is the required return and g is the growth rate of dividends.

·         Graph quarterly dividends over the time.

DIVIDEND DATA FOR GE

 Date Dividends 9/18/2014 0.22 6/19/2014 0.22 2/20/2014 0.22 12/19/2013 0.22 9/19/2013 0.19 6/20/2013 0.19 2/21/2013 0.19 12/20/2012 0.19 9/20/2012 0.17 6/21/2012 0.225 2/23/2012 0.17 12/22/2011 0.17 9/15/2011 0.15 6/16/2011 0.15 2/24/2011 0.14 12/22/2010 0.14 9/16/2010 0.12 6/17/2010 0.1 2/25/2010 0.1 12/23/2009 0.1 9/17/2009 0.1 6/18/2009 0.1 2/19/2009 0.31 12/24/2008 0.31 9/18/2008 0.31 6/19/2008 0.31 2/21/2008 0.31 12/20/2007 0.31 9/20/2007 0.28 6/21/2007 0.28 2/22/2007 0.28 12/21/2006 0.28 9/21/2006 0.25 6/22/2006 0.25 2/23/2006 0.25 12/22/2005 0.25 9/22/2005 0.22 6/23/2005 0.22 2/24/2005 0.22 12/22/2004 0.22 9/23/2004 0.2 6/24/2004 0.2 2/26/2004 0.2 12/29/2003 0.2 9/25/2003 0.19 6/26/2003 0.19 2/26/2003 0.19

Section3: Solving 2-stage Dividend Growth Model (DGM) problems

·         There are two examples below showing how to solve 2-stage DGM problems, please analyze the solutions on the tables and answer the following two questions in a similar fashion using Excel and tables:

Example 1: (two-stage growth model)

Fido’s Foods just paid their annual dividend of \$1.20 per share. They are projecting dividends of \$1.30, \$1.45, and \$1.70 over the next three years, respectively. After that, the company expects to pay a constant dividend of \$1.50 a share. What is the maximum amount you are willing to pay for one share of this stock if your required return is 12 percent.

a. \$12.06

b. \$12.42

c. \$12.50

d. \$16.67

e. \$16.95

 A B C D E F G H 1 IRREGULAR REGULAR 2 STAGE 1 STAGE 2 => g = 0% 3 PERIODS D0 D1 D2 D3 D4 D5 => ∞ 4 DIV 1.20 1.30 1.45 1.70 1.50 1.50 1.50 5 CV 12.50 6 DIV + CV 1.20 1.30 1.45 14.20 7 INT. PRICE \$12.42

CV means continuing value.

UNDERLYING FORMULAS BELOW:

 A B C D E F G H 1 IRREGULAR REGULAR 2 STAGE 1 STAGE 2 => g = 0% 3 PERIODS D0 D1 D2 D3 D4 D5 => ∞ 4 DIV 1.20 1.30 1.45 1.70 1.50 1.50 1.50 5 CV =F5/0.12 6 DIV + CV =SUM(B5:B6) =SUM(C5:C6) =SUM(D5:D6) =SUM(E5:E6) 7 INT. PRICE =NPV(0.12,C7:E7)

Example 2: (two-stage growth model)

ABC Inc., just paid \$1 of dividend per share and planning to increase by 50% for the next years, and afterwards starting at 3 rd period, it will increase dividends only by 3 percent forever. If the required return is 14% for ABC, then what is the intrinsic price?

a.         \$17.29

b.         \$18.62

c.         \$19.26

d.         \$20.67

e.         \$21.95

 A B C D E F G 1 REGULAR => g = 50% REGULAR => g = 3% 2 STAGE 1 STAGE 2 => ∞ 3 PERIODS D0 D1 D2 D3 D4 => ∞ 4 DIV 1.00 1.50 2.25 2.32 2.39 2.46 5 CV 21.07 6 DIV + CV 1.00 1.50 23.32 7 INT. PRICE \$ 19.26

UNDERLYING FORMULAS BELOW:

 A B C D E F G 1 REGULAR => g = 50% REGULAR => g = 3% 2 STAGE 1 STAGE 2 => ∞ 3 PERIODS D0 D1 D2 D3 D4 => ∞ 4 DIV 1.00 =1.5*B5 =1.5*C5 =D5*(1+0.03) =E5*(1+0.03) =F5*(1+0.03) 5 CV =E5/(0.14-0.03) 6 DIV + CV =SUM(B5:B6) =SUM(C5:C6) =SUM(D5:D6) 7 INT. PRICE =NPV(0.14,C7:D7)

QUESTIONS:

1. Jones Brothers Clothing does not expect to pay any dividends for the next two years. Three years from now they hope to pay \$.25 a share and increase that amount by 100 percent per year for the following two years. After that, the dividend is expected to increase in value by 2.5% annually. What is the value of this stock today if the required return is 9 percent?

a.     \$10.19

b.     \$11.45

c.     \$12.60

d.     \$13.26

e.     \$15.34

2. Carson Electric has paid a constant dividend of \$1.50 a share. Yesterday, they announced that the dividend will increase by 10 percent next year and will stay the same for the following year, after which time the dividends will increase by 2 percent annually. The required return on this stock is 7 percent. What is the current value per share?

a.     \$29.40

b.     \$30.70

c.     \$32.38

d.     \$33.66

e.     \$36.96

Section4: Solving 2-stage Dividend Growth Model (DGM) problems

Example:

Fido’s Foods just paid their annual dividend of \$1.20 per share. They are projecting dividends of \$1.30, \$1.45, and \$1.70 over the next three years, respectively. After that, the company expects to pay a constant dividend of \$1.50 a share. What is the maximum amount you are willing to pay for one share of this stock if your required return is 12 percent?

a. \$12.06

b. \$12.42

c. \$12.50

d. \$16.67

e. \$16.95

 A B C D E F G H 1 IRREGULAR REGULAR 2 STAGE 1 STAGE 2 g = or >0% 3 PERIODS D0 D1 D2 D3 D4 D5 => ∞ 4 DIV 1.20 1.30 1.45 1.70 1.50 1.50 1.50 5 CV 12.50 6 DIV + CV 1.20 1.30 1.45 14.20 7 INT. PRICE \$12.42

CV means continuing value.

UNDERLYING FORMULAS ARE SHOWN BELOW:

 A B C D E F G H 1 IRREGULAR REGULAR 2 STAGE 1 STAGE 2 g = or >0% 3 PERIODS D0 D1 D2 D3 D4 D5 => ∞ 4 DIV 1.20 1.30 1.45 1.70 1.50 1.50 1.50 5 CV =F5/0.12 6 DIV + CV =SUM(B5:B6) =SUM(C5:C6) =SUM(D5:D6) =SUM(E5:E6) 7 INT. PRICE =NPV(0.12,C7:E7)

QUESTIONS:

·        Copy and paste the following table into a new worksheet:

 1. Starskeep, Inc., is a fast growing technology company. The firm projects a rapid growth of 40 percent for the next two years and then a growth rate of 20 percent for the following two years. After that, the firm expects a constant-growth rate of 8 percent. The firm expects to pay its first dividend of \$1.25 a year from now. If your required rate of return on such stocks is 20 percent, what is the current price of the stock? a. \$15.63 b. \$4.70 c. \$30.30 d. \$22.68 2. BioSci, Inc., a biotech firm has forecast the following growth rates for the next three years: 30 percent, 25 percent, and 20 percent. The company then expects to grow at a constant rate of 7 percent for the next several years. The company paid a dividend of \$2.00 last week. If the required rate of return is 16 percent, what is the market value of this stock? a. \$51.03 b. \$36.86 c. \$56.12 d. \$46.37 3. Grant, Inc., is a fast growth stock and expects to grow at a rate of 25 percent for the next four years. It then will settle to a constant-growth rate of 10 percent. The first dividend will be paid out in year 3 and will be equal to \$5.00. If the required rate of return is 18 percent, what is the current price of the stock? a. \$85.94 b. \$97.19 c. \$50.59 d. \$65.68 4. Stag Corp. will pay dividends of \$4.75, \$5.25, \$5.75, and \$7 for the next four years. Thereafter, the company expects its growth rate to be at a constant rate of 7 percent. If the required rate of return is 15 percent, what is the current market price of the stock? a. \$69.41 b. \$93.63 c. \$57.54 d. \$80.29

Section5: Finding a firm’s enterprise value using forecasted FCFs

·         Copy and paste the following table to a new worksheet and solve the problems by using a 2-stage model illustrated in the following table:

 A B C D E F G H 1 IRREGULAR REGULAR 2 STAGE 1 STAGE 2 g = 0% 3 PERIODS FCF0 FCF1 FCF2 FCF3 FCF4 FCF5 => ∞ 4 FCF 1,200,000 1,300,000 1,450,000 1,700,000 1,500,000 1,500,000 1,500,000 5 CV 12,500,000 6 FCF + CV 1,300,000 1,450,000 14,200,000 7 INT. VALUE \$12,420,000

 Independent Cloud Corporation (ICC) generated a free cash flow (FCF) of \$1.25 million last year. The company’s expected growth rates over the next four years are as follows: 25 percent, 30 percent 35 percent, and 30 percent because the firm was successful to obtain many contracts for several years. The company then expects to have a 0% growth rate annually. If the required rate of return is 12 percent, what is the firm (enterprise) value of ICC? A. \$10.25 million B. \$16.46 million C. \$18.37 million D. \$26.11 million Marisco Corporation is a fast growing technology company. The firm projects a rapid growth of 40 percent for the next two years and then a growth rate of 20 percent for the following two years. After that, the firm expects a 0% of growth rate. The firm expects to have a free cash flow (FCF) of \$1.25 million a year from now. If your required rate of return on such stocks is 20 percent, what is the firm (enterprise) value of Marisco? A. \$10.76 million B. \$14.70 million C. \$30.30 million D. \$22.68 million Biodynamics Corporation has a forecast about its growth rates for the next three years: 30 percent, 25 percent, and 20 percent. The company then expects to grow at 0% percent for the future years. The company earned a free cash flow of \$2.00 million last year. If the required rate of return is 16 percent, what is the market (enterprise) value of Biodynamics Corporation? A. \$21.03 million B. \$16.86 million C. \$22.77 million D. \$19.37 million Stagger Corporation is expecting to have free cash flows (FCF) of \$4.75 million, \$5.25 million, \$5.75 million, and \$7 million for the next four years. Thereafter, the company expects its growth rate to be at a 0%. If the required rate of return is 15 percent, what is the current market (enterprise) value of Stagger Corporation? A. \$69.41 million B. \$42.56 million C. \$57.54 million D. \$80.29 million

Your findings may look like the following:

-----------------------------------------------------------------------------------------------------------

Save your Excel workbook named as “FullName_Lab2” and upload your Excel workbook to the DROPBOX 2 on the Moodle.

----------------------------------------------------------------------------------------------------------

 Subject Mathematics Due By (Pacific Time) 12/03/2015 05:00 pm
TutorRating
pallavi

Chat Now!

out of 1971 reviews
amosmm

Chat Now!

out of 766 reviews
PhyzKyd

Chat Now!

out of 1164 reviews
rajdeep77

Chat Now!

out of 721 reviews
sctys

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews
topnotcher

Chat Now!

out of 766 reviews
XXXIAO

Chat Now!

out of 680 reviews