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.

Your answers might look like the following:

 

 

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

Your answers may look like the following:

 

 

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:

 

 

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

How to submit your LabWork to Dr. Santos (uploading to the Moodle)

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
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy