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 rightclick) 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+g_{1},1+g_{2},…,1+g_{N})1 (Algebraically).
B) =(X_{N}/X_{0})^[1/(N1)] – 1 (Algebraically) where X refers to original dividend data.
· Convert Quarterly Geometric Average into Annual Geometric Average by using = (1+quarterly geometric average)^41
· 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+g_{1},1+g_{2},…,1+g_{N})1 (Algebraically)
B) =(X_{N}/X_{0})^[1/(N1)] – 1 (Algebraically) where X refers to original dividend data.
· Convert Quarterly Geometric Average into Annual Geometric Average by using = (1+quarterly geometric average)^41
· 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 2stage Dividend Growth Model (DGM) problems
· There are two examples below showing how to solve 2stage 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: (twostage 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: (twostage 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.140.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 2stage 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 constantgrowth 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 constantgrowth 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 2stage 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 
Tutor  Rating 

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.. 