Financial Analysis, Planning and Forecasting Theory and
Download
Report
Transcript Financial Analysis, Planning and Forecasting Theory and
Financial Analysis, Planning and
Forecasting
Theory and Application
Chapter 22
Long-Range Financial Planning –
A Linear-Programming Modeling Approach
By
Alice C. Lee
San Francisco State University
John C. Lee
J.P. Morgan Chase
Cheng F. Lee
Rutgers University
Outline
22.1 Introduction
22.2 Carleton’s model
22.3 Brief discussion of data inputs
22.4 Objective-function development
22.5 The constraints
22.6 Analysis of overall results
22.7 Summary and conclusion
Appendix 22A. Carleton’s linear-programming model:
General Mills as a case study
Appendix 22B. General Mills’ actual key financial data
22.2
Carleton’s
model
22.2
Carleton’s
model
22.2
Carleton’s
model
22.2
Carleton’s
model
22.2 Carleton’s model
22.2 Carleton’s model
22.3 Brief discussion of data inputs
22.3 Brief discussion of data inputs
22.3 Brief discussion of data inputs
22.3 Brief discussion of data inputs
(Cont.)
22.4 Objective-function development
P0 T 1
Dt
PT
,
t
t
N 0 t 1 N 0 (1 K )
N T (1 K )
where
(22.1)
22.4 Objective-function development
Pt
E N ( ),
Nt
''
t
N j 1
N j 1
1
Nj
N j 1
E nj 1
Pj 1
or
~n
E j 1
Pj 1
Nj
N j 1
(22.2)
,
(22.3)
Pj 1 E nj 1
Pj 1
.
(22.3a)
22.4 Objective-function development
N j Pj 1
or Pj D j
.
N j N j N j 1 (1 K )
N j 1 1 K
Pj
Dj
Pj 1
Pj D j
Pj D j
~n
( Pj 1 E j 1 )
~n
D j 1 E j 1
(1 K )
(1 K )
D j 2
.
~n
E j 2
(1 K ) 2
(22.4)
~
PT ETn
...
.
T j
(1 K )
Pj
P0
D0
D1
...
.
j
N0 N0 N1 (1 K )
N j (1 K )
(22.5)
22.4 Objective-function development
~
Dj
D j 1 E jn1
P0
D0
D1
...
...
N 0 N 0 N1 (1 K )
(1 K )
N j (1 K ) j
~
PT ETn
.
(1 K ) T j
(22.6)
P0
D0
D1
E1''
D2
Max
N 0 N 0 N1 (1 K )
N 0 (1 K )(1 C ) N 0 (1 K ) 2
D3
E3''
E 2''
N 0 (1 K ) 2 (1 C ) N 0 (1 K ) 3 N 0 (1 K ) 3 (1 C )
D4
E 4''
4
N 0 (1 K )
N 0 (1 K ) 4 (1 C )
(22.7)
P3
E5''
N 0 (1 K ) 5 N 0 (1 K ) 5 (1 C )
Max0.018D1 0.0196E1 0.015D2 0,017E 2 0.013D3
0.0144E3 0.011D4 0.0125E 4 0.015E5
(22.7a)
22.5 The constraints
Definitional
Policy
constraints
constraints
22.5 The constraints
Fig. 22.1 Structure of the optimizing financial planning model. (From Carleton, W. T., C. L. Dick,
Jr., and D. H. Downes, "Financial policy models: Theory and Practice," Journal of Financial
and Quantitative Analysis (December 1973). Reprinted by permission.)
22.5 The constraints
AFCt ATPt Pfdivt SAt .
(22.8)
Z
t
'
ATPt (1 ) t eAt aAat iz ( Lz ,0 Cz ,t ) it DLs
z 1
s 1
B1B2 ( I1 eAt 1 ) (1 )(aAat eAt ).
(22.9)
Because General Mills has no preferred stock or extraordinary items,
AFC = ATP:
22.5 The constraints
,
22.5 The
i1 0constraints
i2 0.085
371.32
431.9
L1 497.7
570.8
651.2
,
0
0
~
P1 i2 L1 0
0
0
66.8
77.7
L2 89.6
102.7
117.2
5.678
6.6045
~
P2 i2 L2 7.616
8.7295
9.962
22.5 The constraints
22.5 The constraints
L3,0 C3,t
L3
79
80
81
82
83
0
0
0
0
0
30
30
30
20
20
11.3
9.8
8.3
6.3
4.6
18.7 17.3 15.9 14.5 13.1
73.1 68.1 63.1 58.1 53.1
.
93.7 88.7 83.1 78.1 73.1
R3 7% 4.25% 8% 4.875% 8.875% 8%
22.5 The constraints
22.5 The constraints
To get the interest payment on long-term debt
22.5 The constraints
~ 3 ~
~L
~
~
AFC (1 ) Pi i'DL B1 B2[ I1 eA ].
i 1
~
~
P2
~
P3'
~
DL
311.9 5.678 17.04425
DL1
357.9 6.6045 16.04225
DL2
AFC (1 0.51) 406.53 7.616 14.96225 (0.09) DL3
460
.
53
8
.
7295
13
.
46525
DL
4
519.34 9.962 12.24475
DL5
~
~
I
AL
243.6
1613
303.15
1856.6
(0.07) (0.36) 329.1 (0.033) 2159.4
365
.
1
2488
.
5
383.15
2854
22.5 The constraints
DL1 149.17
AFC1
DL2 173.45
AFC2
AFC3 0.0441 DL3 198.22
05
.
226
4
DL
4
AFC
DL5 255.62
AFC5
AFC1+0.00441DL1=149.17
(22.10a)
AFC2+0.00441DL2=173.45
(22.10b)
AFC3+0.00441DL3=198.22
(22.10c)
AFC4+0.00441DL4=226.05
(22.10d)
22.5 The constraints
t
t 0,t s' ( I s )
where
s 1
(22.11)
22.5 The constraints
C0
I t
C1
C0
1
C1
C0
At 1
C1
t 1
A0
(22.12a)
t
A0
(22.12b)
22.5 The constraints
Z
It AFCt 1 Dt 1 CLz ,t DLt DTLt 1 Etn
z 1
where
Etn
(22.13)
22.5 The constraints
Z
I t AFC L D L CLz DL DLL DTLL E
z 1
AFC0 105.79
AFC
1
AFC L
AFC2
AFC3
AFC4
Z
AFC0 D0 DL1 E1 I t CLz
z 1
22.5 The constraints
AFC0 (1 )[ 0 iz Lz ,0 ].
0 245.2,
[iz Lz ,0 ] 29.3.
D0 48.2.
~ ~L
CL LT LT .
22.5 The constraints
696.82
744.4
~
LT 809.2
872.0
953.5
641.63
696.82
~L
LT . 744.4
809.2
872.0
22.5 The constraints
55.19
47.58
CL. 64.80.
62.80
81.50
AFC0 (1 0.51)[254.2 29.3] 105.79.
105.79 48.2 DL1 E1 243.6 55.19,
DL1 E1 130.82
22.5 The constraints
Z
I AFC L D L CLz DL DLL DTLL E ,
z 1
243.6 105.79 48.2 55.19 DL1 0 E1
303.15 AFC1 D 47.58 DL2 DL1 E
1
2
329.1 AFC2 D2 64.80 DL3 DL2 E3
365.1 AFC3 D3 62.80 DL4 DL3 E 4
383.15 AFC4 D4 81.50 488.4 DL4 E5
22.5 The constraints
DL1 E1 131.38.
(22.10e)
AFC1 D1 DL2 DL1 E2 255.7 (22.10f)
AFC2 D2 DL3 DL2 E3 264.3(22.10g)
AFC3 D3 DL4 DL3 E4 302.3 (22.10h)
AFC4 D4 DL4 E5 182.15
(22.10i)
22.5 The constraints
t
Z
i ( L z , 0 C z ,t ) i
z 1 z
5.678 17.04
6.6045 16.04
~ ~
P2 P3 7.616 14.96
8
.
7295
13
.
47
9.962 12.24
'
t
t
s 1
DL s
X,
DL1
DL2
(0.09) DL3
DL4
488.4
(22.14)
22.5 The constraints
.
311.9
357.9
~
406.53
460.53
519.34
~ ~
~
~
X [P2 P3 0.09DL ].
22.5 The constraints
~ ~
~
~
6.35[P2 P3 0.09DL ].
~ ~
~
~
6.35[P2 P3 ] 0.5715DL,
5.678 17.04
311.9
DL1
357.9
DL 2
6.6045
16.04
6.35
0.5715
.
406.53
DL3
7.616 14.96
8.7295 13.47
460.53
DL 4
22.5 The constraints
DL1.LE.293.33,*
(22.15a)
DL2 .LE.374.64,
(22.15b)
DL3.LE.460.49,
(22.15c)
DL4 .LE.559.17,
(22.15d)
22.5 The constraints
Z
(L
z 1
Z
z ,0
C z ,t ) DLs At ,
z 1
(22.16)
( Lz ,0 Cz ,t ) Liabilitiest DL0 ;
DL1 .LE.243.6
DL2 DL1 .LE.303.15
(.LE. " " ) (22.17a)
(.GE. " " ) (22.17b)
22.5 The constraints
DL3 DL2 .LE.329.1
(22.17c)
DL4 .GE.101.15
(22.17d)
Dt t Dt 1 0.
(22.19)
22.5 The constraints
1
~n
Pt E t t Pt .
(22.19a)
Nt
,
N t 1
N t 1
1
1
1 .
Nt
N t
1
1 .
Nt
(22.19b)
22.5 The constraints
5
Z
'
AFC5 (1 ) 5 i z ( Lz 0 C z 5 ) i5 DLs B 1 B2 ( I 5 e 4 )
z 1
s 1
(519.34 22.20 43.96)(0.49)
(0.07)(0.36)[383.15 (0.033)(2854)]
234.087.
D3
D2
D4
P1 D1
2
(1 K ) (1 k )
(1 k ) 3
E3n
E1n
E 2n
2340
4
(1 c) (1 c)(1 k ) (1 c)(1 k ) 2
(1 k )
E5n
E 4n
.
3
4
(1 c)(1 k )
(1 c)(1 k )
22.5 The constraints
E1
( 1) P1
0,
1 c
1
0.0566D1 0.0486D2 0.0417D3 0.0358D4
1.174E1 0.0539E 2 0.0463E3 0.0387E 4 0.034E5
2340
(1 0.165) 4
1
1
71.9
1.06
(22.17f)
22.5 The constraints
0.0566 D2 0.0486 D3 0.0417 D4 1.1728E2
0.0539E3 0.0463E4 0.0387E5 .LE.83.8.
0.0566 D2 0.0486 D3 1.1728E2 0.0539E4 0.0463E5 .LE.97.6.
0.0566 D2 1.728E4 0.0539E5 .LE.113.69.
1.1728E5 .LE.132.44.
D1.GE.51.092 ( D0 is given)
D2 1.06 D1.GE.0,
D3 1.06 D2 .GE.0,
D4 1.06 D3 .GE.0.
22.5 The constraints
D5
0.36;
AFC5
Z
5
'
AFC5 (1 ) 5 iz ( Lz 0 C z 5 ) i5 DLs
z 1
s 1
B 1 B2 ( I 5 e 4 )
22.5 The constraints
i2 0.085
b2 117.2
i2 b2 9.962
i3
b3
i3 b3
0.0425
20
0.085
0.08
4.3
0.344
0.04875
13.1
0.638625
0.08875
53.1
4.712625
0.08
73.7
5.886
i3 b3 12.44125
DL5 488.4
AFC5 (0.49)519.34 9.962 12.44125 (0.09)(488.41)
(0.07)(0.36)383.15 (0.033)(2854)
221.96 12.03 233.99,
D5 AFC5 (0.36) 84.24.
22.5 The constraints
D4. 79.74
(22.17o)
22.5 The constraints
22.5 The constraints
Dt 1 AFCt 0 (t 0, ,5),
Dt 2 AFCt 0 (t 0,
,5),
D1 0.75AFC1 .LE.0
D1 0.15AFC1.GE.0;
D2 0.75AFC2 .LE.0
D2 0.15AFC2.GE.0;
D3 0.75AFC3 .LE.0
D3 0.15AFC3.GE.0;
D4 0.75AFC4 .LE.0
D4 0.15AFC4.GE.0.
22.5 The constraints
D1 0 AFC1 D2 0 AFC2 D3 0 AFC3
D4 0 AFC4 D5 0 AFC5 0
D1 0.4 AFC1 D2 0.4 AFC2 D3 0.4 AFC3
D4 0.4 AFC4 .LE.9.36.
(22.17f)
22.5 The constraints
22.5 The constraints
22.5 The constraints
22.5 The constraints
22.6 Analysis of overall results
22.6 Analysis of overall results
22.7 Summary and conclusion
In this chapter, we have considered Carleton's linearprogramming model for financial planning. We have also
reviewed some concepts of basic finance and accounting.
Carleton's model obtains an optimal solution to the wealthmaximization problem and derives an appropriate financing
policy. The driving force behind the Carleton model is a
series of accounting constraints and firm policy constraints.
We have seen that the model relies on a series of estimates
of future factors. In making these estimates we have
reviewed our growth-estimation skills from Chapter 6.
In the next chapter, we will consider another type of
financial-planning model, the simultaneous-equation models.
Many of the concepts and goals of this chapter will carryover
to the next chapter. We will, of course, continue to expand
our horizons of knowledge and valuable tools.
NOTES
4.
Dn
1
AFCn
1 Dn
g
AFCn AFCn
En
NOTES
6.
5.678 + 17.04 + (131.38)(0.09) = 34.542
(1979)
6.605 + 16.04 + (225.18)(0.09) = 42.911
(1980)
7.616 + 14.96 + (297.65)(0.09) = 49.365
(1981)
8.730 + 13.47 + (406.89)(0.09) = 58.820
(1982)
9.962 + 12.24 + (488.40)(0.09) = 66.158
(1983)
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
PROBLEM SPECIFICATION
MPOS VERSION 4.0
NORTHWESTERN UNIVERSITY
MP0S
VERSION 4.0
MULTI-PURPOSE OPTIMIZATION SYSTEM
***** PROBLEM NUMBER 1 *****
MINIT VARIABLES
Dl D2 D3 D4 El E2 E3 E4 E5 AFC1 AFC2 AFC3 AFC4 DL1 DL2 DL3 DL4
MAXIMIZE
.018Dl-.0196El+.015D2-.017E2+.013D3-.0144E3+.011D4-.0125E4-.015E5
CONSTRAINTS
1.
AFC1+.0441DLl .EQ. 149.17
2.
AFC2+.0441DL2 .EQ. 173.45
3.
AFC3+.0441DL3 .EQ. 198.22
4.
AFC4+.0441DL4. EQ. 226.05
5.
DL1+E1 .EQ. 131.38
6.
AFC1-D1+DL2-DL1+E2 .EQ. 255.7
7.
AFC2-D2+DL3-DL2+E3 .EQ. 264.3
8.
AFC3-D3+DL4-DL3+E4 .EQ. 302.3
9.
-AFC4+D4+DL4-E5 .EQ. 182.15
10.
DL1 .LE. 284 .42
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
PROBLEM SPECIFICATION (Cont.)
11.
DL2 .LE. 374.1
12.
DL3 .LE. 460
13.
DL4 .LE. 558.7
14.
DL1 .LE. 243. 6
15.
DL2-DL1 .LE. 303.15
16.
DL3-DL2 .LE. 329.1
17.
DL4-DL3 .LE. 365.1
18.
DL4 .GE. 101.15
19.
-.0566D1-.0486D2-.0417D3-.0358D4+1.1740El+.0539E2+.0463E3+.0387E4 +.034E5 .LE. 71.8
20.
-.0566D2-.0486D3-.04 17D4+.1728E2+.0539E3+.0463E4+.0397E55 .LE. 83.8
21.
-.0566D3-.0486D4+1.1728E3+.0533E4+.046E5 .LE. 97.6
22.
-.0566D4+1.7280E4+.0539E5 .LE. 113.69
23.
1.1728E5 .LE. 132.44
24.
Dl .GE. 51.092
25.
D2-1.06D1 .GE. 0
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
PROBLEM SPECIFICATION (Cont.)
26.
D3-1.06D2 .CE. 0
27.
D3-1.06D3 .GE. 0
28.
D4 .LE. 79.47
29.
D1-.75AFC1 .LE. 0
30.
D2-.75AFC2 .LE. 0
31.
D3-.75AFC3 .LE. 0
32.
D4-.75AFC4 .LE. 0
33.
Dl-. 15AFC1 .GE. 0
34.
D2-.15AFC2 .GE. 0
35.
D3-.15AFC3 .GE. 0
36.
D4-.15AFC4 .GE. 0
37.
Dl-.4AFCl+D2-.4AFC2+D3-.4AFC3+D4-.4AFC4 .LE. 9.36
,
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
SOLUTION
MPOS VERSION 4.0
NORTHWESTERN UNIVERSITY
PROBLEM NUMBER
USING MINIT
SUMMARY OF RESULTS
VARIABLE NO.
VARIABLE
NAME
BASIC NON-BASIC
ACTIVITY LEVEL
OPPORTUNITY COST
1
Dl
B
51.0920000
--
2
D2
B
54.1575200
--
3
D3
B
57.4069712
--
4
D4
B
60.8513895
--
5
El
NB
--
.0015408
6
E2
B
69.6152957
--
7
E3
B
82.4681751
--
8
E4
B
65.3689022
--
9
E5
B
77.4902713
--
10
AFC1
B
143.3761420
--
11
AFC2
B
163.5195372
--
12
AFC3
B
185.0936187
--
ROW NO.
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
SOLUTION (Cont.)
VARIABLE NO.
VARIABLE
NAME
BASIC NON-BASIC
ACTIVITY LEVEL
OPPORTUNITY COST
ROW NO.
13
AFC4
B
208.1059384
--
14
DL1
B
131.3800000
--
15
DL2
B
225.1805623
--
16
DL3
B
297.6503700
--
17
DL4
B
406.8948203
--
18
--SLACK
B
153.0400000
--
( 10)
19
--SLACK
B
148.9194377
--
( 11)
20
--SLACK
B
162.3496300
--
( 12)
21
--SLACK
B
151.8051797
--
( 13)
22
--SLACK
B
112.2200000
--
( 14)
23
--SLACK
B
209.3494377
--
( 15)
24
--SLACK
B
256.6301923
--
( 16)
25
--SLACK
B
255.8555497
--
( 17)
26
--SLACK
B
305.7448203
--
( 18)
27
--SLACK
B
69.1612264
--
( 19)
28
--SLACK
NB
--
.0002527
( 20)
29
--SLACK
NB
--
.0018351
( 21)
30
--SLACK
NB
--
.0018840
( 22)
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
SOLUTION (Cont.)
VARIABLE NO.
VARIABLE
NAME
BASIC NON-BASIC
ACTIVITY LEVEL
OPPORTUNITY COST
31
--SLACK
B
41.5594098
--
( 23)
32
--SLACK
NB
--
-.0087826
( 24)
33
--SLACK
NB
--
-.0089493
( 25)
34
--SLACK
NB
--
-.0069790
( 26)
35
--SLACK
NB
--
-.0039896
( 27)
36
--SLACK
B
18.6686105
--
( 28)
37
--SLACK
B
56.4401065
--
( 29)
38
--SLACK
B
68.4821329
--
( 30)
39
--SLACK
B
8l.4132428
--
( 31)
40
--SLACK
B
95.2280643
--
( 32)
41
--SLACK
B
29.5855787
--
( 33)
42
--SLACK
B
29.6295894
--
( 34)
43
--SLACK
B
29.6429284
--
( 35)
ROW NO.
Appendix 22A. Carleton’s linear-programming
model: General Mills as a case study
SOLUTION (Cont.)
VARIABLE NO.
VARIABLE
NAME
BASIC NON-BASIC
ACTIVITY LEVEL
OPPORTUNITY COST
44
--SLACK
B
29.6354987
--
( 36)
45
--SLACK
B
65.8902139
--
( 37)
46
- -ARTIF
NB
--
.0172964
( 1)
47
--ARTIF
NB
--
.0165658
( 2)
48
--ARTIF
NB
--
.0158661
( 3)
49
--ARTIF
NB
--
.0151960
( 4)
50
--ARTIF
NB
--
-.0180592
( 5)
51
--ARTIF
NB
--
-.0172964
( 6)
52
--ARTIF
NB
--
-.0165658
( 7)
53
--APTIF
NB
--
-.0158661
( 8)
54
--ARTIF
NB
--
.0151960
( 9)
MAXIMUM VALUE OF THE OBJECTIVE FUNCTION =
-1,202792
CALCULATION TIME WAS .0670 SECONDS FOR 21 ITERATIONS.
ROW NO.
Appendix 22B. General Mills’ actual key
financial data
Appendix 22B. General Mills’ actual key
financial data