Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area: The Case of Constant – Growth (Gordon) Model

Following to article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area” on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html, some professional people asked me about the application of the Least Squares Fitting for a Limited Area. In this article, I am willing to bring a simple example which is the case of constant – growth model.

The Case of Constant – Growth (Gordon) Model

As an example, I would like to refer you to my article of “Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION (CON): A New Financial Simulation Model” on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html. The case issue is: How can we cope with the Constant- Growth Model as our dividend policy and our decision making?

Lawrence J. Gitman (2009) in his book of “Principles of Managerial Finance (Twelfth edition)” stated:

“The most widely cited dividend valuation approach, the constant – growth model, assumes that dividends will grow at a constant rate, but a rate that is less than the required return.”

If we simplify the equation of the constant – growth model, we will have below formula:

P0 = D1 / (rs – g)

Where:

P0 = value of common stock

D1 = the most recent dividend per – share

rs = required return on common stock ( In this case, I consider it as the Cost of Capital (WACC) for GAINESBORO MACHINE TOOLS CORPORATION)

g = the constant – growth rate

If we multiply all outstanding shares to above equation, the formula shows us the enterprise value of Gainesboro.

We can re-write above formula as follows:

WACC = (D1/ EV) + g

Where:

WACC = Gainesboro’s Cost of Capital

D1 = total dividend projected in 2005

EV = enterprise value in accordance with financial model mentioned on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html

In the reference with the article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area” on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html, we can define the area as follows:

WACC = y

 D1/ EV = b

g = x

m = 1

Then we have:

y = x + b

Referring to the article of “Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION (CON): A New Financial Simulation Model” posted on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html, I obtained the range for “WACC” and “g” as follows:

4% <= WACC <= 11%

And

0 <= g <= 3%

It means that, our definition for area is:

0.04 <= y <= 0.11

0 <= x <=3

Now, I try to calculate “b” as the Least Squares Fitting for a Limited Area by using the application of Pascal’s Triangular and Monte Carlo Simulation (referred to link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html) below cited:

As we can see:

D1/ EV = 0.06

Since the range of “g” is between 0 and 3%, WACC should be always equal or more than 6% and equal or less than 9%. Therefore we have:

WACC = g + 0.06, If and only if 6% < = WACC < = 9%

In this case, I have made four scenarios which will cover all results.

Now, I am ready to use from Financial Model posted on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html :

How can we work with this financial model for this example?

1) I added the item D1/ EV on my spreadsheet (financial model)

2) I used from sensitivity analysis for D1 as independent variable and  D1/ EV as dependent variable and I obtained D1 for each scenario for D1/ EV = 0.06

3) I added D1 for 2005 year as dividend payout and formula = D1 * (1+g) for 2006 to 2011.

The final results of each scenario are as follows:

Scenario (1)

WACC = 6%

g = 0%

Scenario (2)

WACC = 7%

g = 1%

Scenario (3)

WACC = 8%

g = 2%

Scenario (4)

WACC = 9%

g = 3%

As we can see, the range of changes for D1 and current share price (P0) in the all scenarios are negligible.

Conclusion

The final answer to the issue of this case is that the Constant – Growth (Gordon) Model as a dividend policy and decision making for Gainesboro should be completely rejected, if the range of “WACC” is between 4% to11percent and so the range of “g” is between 0 to3percent. Because by using of Constant – Growth (Gordon) Model, expected share price will go down under current share price that the average is equal to $29.15 for 2004 year.

Let me start another case as the new example as follows:

The Case of Terminal Value

One of the most crucial problems to use the discounted cash flow analysis is to find the appropriate the Terminal Value because this methodology is very sensitive to the Terminal Value Growth Rate and the firm’s Cost of Capital. The formula for terminal value is as follows:

Terminal value = [FCFn * (1+g)] / (WACC – g)

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this method as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225. Please be informed these spreadsheets are not free of charge.”

To be continued……..

Applications of Internet

Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area

The purpose of this article is to prove the accuracy of the simulation model made by the combination of Pascal’s Triangular method and Monte Carlo Simulation model included in the article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Appraise the Wisdom of Crowds” and posted on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_08.html in which I am willing to prove this simulation model as well as works. The most important findings are to find the least squares fitting and linear function for a limited area (XY) and so to obtain the point which has the deviation approximately equal to zero where we have the infinity points on the plane of XY. I think that we can also generate the best numerical solution for 3D Laplace Equation and so we will be able to find the best fitting plane given a set of N points in a 3D space by using of this simulation model.

Nowadays, all fields of engineering and science such as Social science, medicine, finance, strategic management and so on are using the method of the Last Squares Fitting. One of the most important applications of two variables function’s minimizing is to utilize the least squares method for fitting of direct line (y = mx + b).

Assume you have infinity points (x1, y1), (x2, y2),……(xn, yn) that you have found them by your observation or survey questionnaire in which all points are limited into the area of x = a and y = b and “n” reach to infinity. There are two amounts of “y” for each observed amount of “x” (xobs) which are the observed amount of “y” (yobs) and the amount of “y” that it is predicted by linear function of y = mxobs + b where we have:

Predicted amount – observed amount = (mxobs + b) - yobs

We call this amount a deviation (d) in which there are the infinity deviations of “d1, d2,…. dn”  as follows:

Set (A) = [(d1= (mx1 + b) – y1), (d2= (mx2 + b) – y2)……… (dn= (mxn + b) – yn)] and “n” reach to infinity.

We can say the line will be the perfect fitting if and only if all deviations will be equal to zero. Of course, it is impossible. Therefore, we will find the best fitting line by minimizing below formula:

SUM (d^2) = d1^2 + d2^2+…..+ dn^2         (1) 

Or  

SUM (d^2) = [(mx1 + b) – y1]^2 + [(mx2 + b) – y2]^2+…+ (mxn + b) – yn]^2    (2)

Methodology

The methodology is the same with my previous article.

Note: “Since I want to prove my simulation model in this article, I do not explain more about it.  Therefore, please read carefully my article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Appraise the Wisdom of Crowds” and posted on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_08.html before the continuation of this article.”

Finding and discussion

Let me start my discussion by using an example as follows:

Assume we want to get the best fitting line among infinity points into a rectangular area where: 


x = 0  to  x = 4    and     y = 1   to   y = 5

I apply all steps mentioned in my previous article in which I want to compare the final result between 200 trials (n = 200) and 1000 trials (n =1000) as follows:

1) For n = 200, we will have below algorithm to find all outcomes:

n	200
Number of outcomes of "x"	201
Range of  "X"	201
Amount of "X"(min)	0
Amount of "X" (max)	4
max - min	4
delta	0.02
n	200
Number of outcomes of "y"	201
Range of  "Y"	201
Amount of "Y"(min)	1
Amount of "Y" (max)	5
max - min	4
delta	0.02

Therefore, the outcomes for “x” will be started by 0 then 0.02 and will be finished by 4 and so the outcomes for “y” will be started by 1 then 1.02 and will be finished by 5.

After all steps, we will have below Cut – offs (accumulative probability) for “x” and “y” as follows:

Cut Offs	x
0	0.02
7.72E-45	1.68
0.01	1.82
0.1	1.96
0.4	2.18
0.9	3.08
1	4

Cut Offs	y
0	1.02
7.72E-45	2.68
0.01	2.82
0.1	2.96
0.4	3.18
0.9	4.08
1	5

For Least Squares Fitting, I used RAND formula accompanied by VLOOKUP for “x” and “y” simultaneously where we will have 201 cells on row filled by them.

Then, I applied below algorithm to calculate “SUM (d^2):

(xi)^2

SUM (xi)

SUM(xi)^2

SUM (yi)

xi * yi

SUM(xi*yi)

m

b

d0

d0^2

SUM(d^2)

Where I calculated “m” and “b” by using of below formulas:

[(SUM(xi)^2) *m ] + [(SUM (xi)) *b = SUM(xi*yi)

[(SUM (xi)) * m] + (n*b) = SUM (yi)

To calculate “d0”, I utilized from below formula:

d0 = ((m*x0) + b) – y0

The final results of two – ways sensitivity analysis were as follows:

	b
-	3.167558
Ave	3.166511
STDEV	0.159445
AVE	0.158023
CV	0.049904

	m
-	0.002501
AVE	0.000201
STDEV	0.073112
AVE	0.07178

	SUM (d^2)
-	22.8858892
AVE	22.88723508
STDEV	1.721733542
AVE	1.665178887
CV	0.072755791

Finally, I obtained below point which generated the least SUM (d^2) among all points:

“x” for min[SUM (d^2)] = 1.96

“y” for min[SUM (d^2)] = 3.18

2) For n = 1000, we will have below algorithm to find all outcomes:

n	1000
Number of outcomes of "X"	1001
Range of  "X"	1001
Amount of "X"(min)	0
Amount of "X" (max)	4
max - min	4
delta	0.004
n	1000
Number of outcomes of "Y"	1001
Range of  "Y"	1001
Amount of "Y"(min)	1
Amount of "Y" (max)	5
max - min	4
delta	0.004

Therefore, the outcomes for “x” will be started by 0 then 0.004 and will be finished by 4 and so the outcomes for “y” will be started by 1 then 1.004 and will be finished by 5.

After all steps, we will have below Cut – offs (accumulative probability) for “x” and “y” as follows:

Cut Offs	x
0	0.004
6.6E-219	1.852
0.01	1.92
0.1	1.984
0.4	2.08
0.9	2.496
1	1

Cut Offs	y
0	1.004
6.6E-219	2.852
0.01	2.92
0.1	2.984
0.4	3.08
0.9	3.496
1	5

For Least Squares Fitting, I used RAND formula accompanied by VLOOKUP for “x” and “y” simultaneously where we will have 1001 cells on row filled by them.

Then, I applied the same algorithm above mentioned to calculate “SUM (d^2) where the final results were as follows:

	b
-	3.074636
AVE	3.076022
STDEV	0.06411
AVE	0.066586

	m
-	0.002325
AVE	0.000143
STDEV	0.031485
AVE	0.03185

	SUM(d^2)
ave	21.87308
AVE	21.74568
STDEV	3.527124
AVE	3.45824
CV	0.159031

Finally, I obtained below point which generated the least SUM (d^2) among all points:

“x” for min[SUM (d^2)] = 2.08

“y” for min[SUM (d^2)] = 3.08

How can we prove above results?

There are two ways to prove above results and finally to be proved this simulation model as follows:

      A) First way: Referring to formula (2), we have:

SUM (d^2) = [(mx1 + b) – y1]^2 + [(mx2 + b) – y2]^2+……+ (mxn + b) – yn]^2  

Let me consider below function:

f (x, y) = [(mx + b) – y]^2 

Total sum of this curve on below area will be equal double integral f (x,y) as follows:  


x = 0  to  x = 4    and     y = 1   to   y = 5

I made an algorithm on my excel spreadsheet to solve this double integral as follows:

1) For n = 200, we will have:

p1	1
p2	5
q1	0
q2	4
m	0.002501
b	3.167558
Delta (p)	4
Delta (q)	4
(p1)^3	1
(p2)^3	125
(q1)^3	0
(q2)^3	64
(p1)^2	1
(p2)^2	25
(q1)^2	0
(q2)^2	16
m^2	6.25E-06
b^2	10.03342
f1	0.000534
f2	160.5347
f3	165.3333
f4	0.507006
f5	-0.48019
f6	-304.086
SUM(d^2)	21.8099

2) For n = 1000, we will have:

p1	1
p2	5
q1	0
q2	4
m	0.000143
b	3.076022
Delta (p)	4
Delta (q)	4
(p1)^3	1
(p2)^3	125
(q1)^3	0
(q2)^3	64
(p1)^2	1
(p2)^2	25
(q1)^2	0
(q2)^2	16
m^2	2.04E-08
b^2	9.461912
f1	1.74E-06
f2	151.3906
f3	165.3333
f4	0.028133
f5	-0.02744
f6	-295.298
SUM(d^2)	21.4265

By using of try and error around m = 0.000143 and b = 3.076022, we can find the least squares fitting by calculating double integral where we will have:

m = 0

b = 3

p1	1
p2	5
q1	0
q2	4
m	0
b	3
Delta (p)	4
Delta (q)	4
(p1)^3	1
(p2)^3	125
(q1)^3	0
(q2)^3	64
(p1)^2	1
(p2)^2	25
(q1)^2	0
(q2)^2	16
m^2	0
b^2	9
f1	0
f2	144
f3	165.3333
f4	0
f5	0
f6	-288
SUM(d^2)	21.33333

You can see that 1000 trials (n = 1000) is closer to the result of double integral. (Please check red colour)

  B) Second way: Referring to formula (2), we have:

SUM (d^2) = [(mx1 + b) – y1]^2 + [(mx2 + b) – y2]^2+……+ (mxn + b) – yn]^2 

 

Let me consider below function for deviation square of each random point:

f (x, y) = [(mx + b) – y]^2

 If we want to have the least amount of f (x, y), we should simultaneously solve below equations: 

I replaced “m”, “b”, and “x” for min[SUM (d^2)]  and “y” for min[SUM (d^2)]  in each trial into above formulas and I calculated the final result by using of excel spreadsheet as follows:

v  n = 200 trials:

n	200
x	1.96	1.96
y	3.18	3.18
d1	1.59E-07	6.36
d2	0.001275	-0.00079
d3	-0.00128	-6.33302
SUM	-5.3E-06	0.026189

v  n = 1000 trials

n	1000
x	2.08	2.08
y	3.08	3.08
d1	8.5E-08	6.16
d2	0.000879	-0.00059
d3	-0.00088	-6.15204
SUM	-1.1E-06	0.007361

You can see that the final results for n = 1000 trials are very closer to zero.

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this method as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225. Please be informed these spreadsheets are not free of charge.”