当前位置: 首页 > >

麦肯锡公司估值模型教学

发布时间:

MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)

A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

B C D E F TABLE 1. HISTORICAL INCOME STATEMENTS -6 197.6 -175.4 -12.8 -----9.4 0.0 -0.1 -----9.3 -5 222.3 -205.8 -9.3 -----7.2 0.0 -0.4 -----6.8 -4 272.3 -249.6 -11.2 -----11.5 0.0 -0.8 -----10.7 -3 299.5 -274.7 -13.0 -----11.8 0.0 -1.0 -----10.8 -2 350.0 -320.5 -15.0 -----14.5 0.9 -3.4 -----12.0

G

H

I J K L M TABLE 5. FORECASTED INCOME STATEMENTS 0 1 598.6 -538.8 -28.7 -----31.1 0.0 -10.4 -----20.7 0.0 -8.1 -----12.6 ====== 2 690.6 -621.5 -32.0 -----37.1 0.0 -10.4 -----26.6 0.0 -10.4 -----16.2 ====== 3 768.2 -691.4 -36.9 -----39.9 0.0 -11.7 -----28.2 0.0 -11.0 -----17.2 ====== 4 846.7 -762.0 -41.0 -----43.7 0.0 -12.3 -----31.4 0.0 -12.2 -----19.1 ====== 5 924.4 -831.9 -45.1 -----47.3 0.0 -13.3 -----34.0 0.0 -13.3 -----20.8 ======

N

O

P

Q

R

S

T

Income statement Revenues Operating expenses Depreciation Operating income Interest income * Interest expense Earnings before taxes Revaluation of deferred income taxes Income taxes Net income

-1 418.9 -383.6 -17.7 -----17.6 0.7 -4.1 -----14.2

6 999.7 -899.7 -49.1 -----50.8 0.0 -14.3 -----36.5 0.0 -14.3 -----22.3 ======

7 1070.9 -963.8 -53.0 ------54.1 0.0 -15.2 ------38.9 0.0 -15.2 ------23.7 ======

8 1136.1 -1022.5 -56.5 ------57.1 0.0 -16.0 ------41.0 0.0 -16.0 ------25.0 ======

9 1193.6 -1074.2 -59.7 ------59.6 0.0 -16.8 ------42.9 0.0 -16.7 ------26.1 ======

10 1229.4 -1106.5 -62.5 ------60.5 0.0 -17.3 ------43.2 0.0 -16.8 ------26.3 ======

11 1266.3 -1139.7 -64.0 ------62.6 0.0 -17.5 ------45.1 0.0 -17.6 ------27.5 ======

12 1304.3 -1173.9 -64.5 ------65.9 0.0 -17.8 ------48.1 0.0 -18.8 ------29.4 ======

505.4 -467.4 -26.4 -----11.6 0.6 -10.1 -----2.1

-3.3 -----6.0 ======

-2.4 -----4.4 ======

-3.8 -----6.9 ======

-4.2 -----6.6 ======

-5.0 -----7.0 ======

-6.1 -----8.1 ======

-0.7 -----1.4 ======

Statement of retained earnings Beginning retained earnings Net income Common dividends Ending retained earnings * direct forecast

-6

-5

-4

-3

-2

-1

0

1

2

3

4

5

6

7

8

9

10

11

12

62.5

62.5 4.4 -3.2 -----63.7

63.7 6.9 -4.8 -----65.8

65.8 6.6 -3.8 -----68.6

68.6 7.0 -5.8 -----69.8

69.8 8.1 -3.9 -----74.0

74.0 1.4 -2.9 -----72.5

72.5 12.6 0.0 -----85.1

85.1 16.2 0.0 -----101.4

101.4 17.2 0.0 -----118.6

118.6 19.1 -6.7 -----131.0

131.0 20.8 -9.7 -----142.1

142.1 22.3 -12.1 -----152.3

152.3 23.7 -14.7 ------161.3

161.3 25.0 -17.5 ------168.9

168.9 26.1 -20.3 ------174.8

174.8 26.3 -24.3 ------176.8

176.8 27.5 -25.5 ------178.7

178.7 29.4 -23.3 ------184.8

TABLE 2. HISTORICAL BALANCE SHEETS -6 Operating cash Excess marketable securities * Trade receivables Other receivables Inventories Prepaid expenses Current assets Gross property, plant and equipment Accumulated depreciation Net property, plant 4.0 10.9 17.9 1.5 1.9 4.3 -----40.5 -5 4.4 3.0 24.4 2.0 2.1 5.1 -----41.0 -4 5.4 20.5 33.0 2.7 2.8 5.3 -----69.7 -3 6.0 10.3 33.3 2.7 2.5 6.0 -----60.8 -2 6.0 0.0 43.9 6.2 9.0 2.4 -----67.5 -1 8.4 5.8 49.8 4.9 10.9 4.4 -----84.2 0 10.1 0.0 57.7 5.7 11.9 5.0 -----90.4

TABLE 6. FORECASTED BALANCE SHEETS 1 11.7 0.0 67.7 6.5 9.7 9.5 -----105.1 2 13.5 0.0 78.1 7.5 11.1 11.0 -----121.2 3 15.0 0.0 86.8 8.4 12.4 12.2 -----134.9 4 16.6 0.0 95.7 9.2 13.7 13.5 -----148.6 5 18.1 0.0 104.5 10.1 14.9 14.7 -----162.3 6 19.6 0.0 113.0 10.9 16.1 15.9 -----175.5 7 21.0 0.0 121.0 11.7 17.3 17.1 ------188.0 8 22.2 0.0 128.4 12.4 18.3 18.1 ------199.5 9 23.4 0.0 134.9 13.0 19.3 19.0 ------209.5 10 24.1 0.0 139.0 13.4 19.8 19.6 ------215.8 11 24.8 0.0 143.1 13.8 20.4 20.2 ------222.3 12 25.5 0.0 147.4 14.2 21.0 20.8 ------229.0

100.0 -37.7 ------

117.7 -42.3 ------

128.2 -48.7 ------

155.6 -56.5 ------

204.7 -71.9 ------

272.5 -86.9 ------

297.6 -103.4 ------

334.2 -121.8 ------

381.1 -140.4 ------

422.7 -159.7 ------

463.5 -178.9 ------

502.6 -197.6 ------

539.1 -215.4 ------

572.1 -232.0 -------

600.7 -246.9 -------

624.1 -259.9 -------

638.1 -270.7 -------

649.5 -278.8 -------

668.8 -287.1 -------

1

MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)

52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102

A and equipment Total assets

B 62.3 -----102.8 ====== 0.3 7.3 13.9 -----21.5 5.5 8.7 4.6 62.5 -----67.1 -----102.8 ====== 70.7 8.2%

C 75.4 -----116.4 ====== 0.8 11.0 13.5 -----25.3 11.5 11.0 4.9 63.7 -----68.6 -----116.4 ====== 88.9 13.8% 8.7%

D 79.5 -----149.2 ====== 0.9 11.9 18.2 -----31.0 16.2 12.6 23.6 65.8 -----89.4 -----149.2 ====== 98.6 17.3% 9.6%

E 99.1 -----159.9 ====== 1.5 10.5 18.5 -----30.5 21.7 15.5 23.6 68.6 -----92.2 -----159.9 ====== 120.6 19.2% 9.2%

F 132.8 -----200.3 ====== 11.5 14.2 21.4 -----47.1 40.2 19.6 23.6 69.8 -----93.4 -----200.3 ====== 164.7 31.4% 8.9%

G 185.6 -----269.8 ====== 12.5 16.2 27.8 -----56.5 90.6 25.1 23.6 74.0 -----97.6 -----269.8 ====== 220.0 46.9% 8.1%

H 194.2 -----284.6 ====== 20.7 18.9 28.8 -----68.4 94.8 25.3 23.6 72.5 -----96.1 -----284.6 ====== 236.9 48.8% 3.2%

I 212.4 -----317.5 ====== 19.0 24.1 38.0 -----81.0 97.0 30.8 23.6 85.1 -----108.7 -----317.5 ====== 255.4 45.4% 9.9%

J 240.8 -----362.0 ====== 19.4 27.8 43.8 -----91.0 110.6 35.4 23.6 101.4 -----125.0 -----362.0 ====== 290.4 44.8% 10.0%

K 263.1 -----397.9 ====== 22.1 30.9 48.7 -----101.8 114.7 39.3 23.6 118.6 -----142.2 -----397.9 ====== 318.3 43.0% 9.3%

L 284.7 -----433.3 ====== 22.9 34.1 53.7 -----110.7 124.8 43.1 23.6 131.0 -----154.6 -----433.3 ====== 345.5 42.8% 9.2%

M 305.0 -----467.3 ====== 25.0 37.2 58.7 -----120.8 133.9 46.9 23.6 142.1 -----165.7 -----467.3 ====== 371.5 42.8% 9.1%

N 323.7 -----499.2 ====== 26.8 40.2 63.4 -----130.5 142.3 50.5 23.6 152.3 -----175.9 -----499.2 ====== 395.5 42.8% 9.0%

O 340.1 ------528.1 ====== 28.5 43.1 68.0 ------139.5 149.8 53.8 23.6 161.3 ------184.9 ------528.1 ====== 417.0 42.8% 8.9%

P 353.7 ------553.2 ====== 30.0 45.7 72.1 ------147.8 156.2 56.7 23.6 168.9 ------192.5 ------553.2 ====== 435.3 42.8% 8.9%

Q 364.2 ------573.7 ====== 31.2 48.1 75.7 ------155.0 161.2 59.2 23.6 174.8 ------198.4 ------573.7 ====== 449.9 42.8% 8.8%

R 367.5 ------583.3 ====== 32.2 49.5 78.0 ------159.7 162.7 60.5 23.6 176.8 ------200.4 ------583.3 ====== 455.8 42.8% 8.4%

S 370.6 ------592.9 ====== 32.5 51.0 80.4 ------163.9 164.9 61.9 23.6 178.7 ------202.3 ------592.9 ====== 461.6 42.8% 8.6%

T 381.7 ------610.7 ====== 33.0 52.5 82.8 ------168.2 170.3 63.7 23.6 184.8 ------208.4 ------610.7 ====== 475.5 42.8% 9.0%

Short-term debt Accounts payable Other current liabilities Total current liabilities Long-term debt Deferred income taxes Common stock * Retained earnings Total common equity Total liabilities and equity

Invested capital Debt/invested capital NOPLAT/invested capital (ROIC) * direct forecast

TABLE 3. HISTORICAL FREE CASH FLOW Free cash flow Revenues Operating expenses Depreciation EBIT Taxes on EBIT Revaluation of deferred income taxes Change in deferred income taxes NOPLAT Add back depreciation Gross cash flow Change in working -5 222.3 -205.8 -9.3 -----7.2 -2.6 -4 272.3 -249.6 -11.2 -----11.5 -4.1 -3 299.5 -274.7 -13.0 -----11.8 -4.6 -2 350.0 -320.5 -15.0 -----14.5 -6.0 -1 418.9 -383.6 -17.7 -----17.6 -7.4 0 505.4 -467.4 -26.4 -----11.6 -4.4

TABLE 7. FORECASTED FREE CASH FLOW 1 598.6 -538.8 -28.7 -----31.1 -12.1 0.0 2 690.6 -621.5 -32.0 -----37.1 -14.5 0.0 4.6 -----27.2 32.0 -----59.2 3 768.2 -691.4 -36.9 -----39.9 -15.6 0.0 3.9 -----28.2 36.9 -----65.1 4 846.7 -762.0 -41.0 -----43.7 -17.0 0.0 3.9 -----30.5 41.0 -----71.5 5 924.4 -831.9 -45.1 -----47.3 -18.5 0.0 3.8 -----32.6 45.1 -----77.7 6 999.7 -899.7 -49.1 -----50.8 -19.8 0.0 3.6 -----34.6 49.1 -----83.7 7 1070.9 -963.8 -53.0 ------54.1 -21.1 0.0 3.3 ------36.3 53.0 ------89.3 8 1136.1 -1022.5 -56.5 ------57.1 -22.3 0.0 2.9 ------37.7 56.5 ------94.3 9 1193.6 -1074.2 -59.7 ------59.6 -23.3 0.0 2.5 ------38.9 59.7 ------98.6 10 1229.4 -1106.5 -62.5 ------60.5 -23.6 0.0 1.3 ------38.2 62.5 ------100.7 11 1266.3 -1139.7 -64.0 ------62.6 -24.4 0.0 1.3 ------39.6 64.0 ------103.5 12 1304.3 -1173.9 -64.5 ------65.9 -25.7 0.0 1.9 ------42.1 64.5 ------106.6

2.3 -----6.9 9.3 -----16.2

1.6 -----9.0 11.2 -----20.2

2.9 -----10.1 13.0 -----23.1

4.1 -----12.6 15.0 -----27.6

5.5 -----15.7 17.7 -----33.4

0.2 -----7.4 26.4 -----33.8

5.5 -----24.4 28.7 -----53.2

2

MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)

103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

A capital (+ if increase) Capital expenditures (+ if positive) Gross investment Free cash flow

B

C 5.1 22.4 -----27.5 ------11.3 ====== -5

D 5.6 15.3 -----20.9 ------0.7 ====== -4

E 2.4 32.6 -----35.0 ------11.9 ====== -3

F 10.4 48.7 -----59.1 ------31.5 ====== -2

G 2.5 70.5 -----73.0 ------39.6 ====== -1

H 8.3 35.0 -----43.3 ------9.5 ====== 0

I 0.3 47.0 -----47.3 -----5.9 ====== 1

J 6.6 60.3 -----66.9 ------7.7 ====== 2

K 5.6 59.2 -----64.8 -----0.3 ====== 3

L 5.6 62.6 -----68.2 -----3.3 ====== 4

M 5.6 65.5 -----71.1 -----6.7 ====== 5

N 5.4 67.8 -----73.2 -----10.5 ====== 6

O 5.1 69.4 ------74.5 ------14.8 ====== 7

P 4.7 70.2 ------74.9 ------19.4 ====== 8

Q 4.1 70.2 ------74.3 ------24.2 ====== 9

R 2.6 65.7 ------68.3 ------32.4 ====== 10

S 2.6 67.1 ------69.8 ------33.8 ====== 11

T 2.7 75.6 ------78.4 ------28.2 ====== 12

Financial cash flow Incr(+)/Decr(-) in excess marketable securities After-tax interest income (-) Incr(-)/Decr(+) in shortand long-term debt After-tax interest expense (+) Common dividends (+) Incr(-)/Decr(+) in common stock Financial cash flow Effective tax rate on EBIT

-7.9 0.0 -6.5 0.2 3.2 -0.3 ------11.3 3.6%

17.5 0.0 -4.8 0.5 4.8 -18.7 ------0.7 21.8%

-10.2 0.0 -6.1 0.6 3.8 0.0 ------11.9 14.3%

-10.3 -0.5 -28.5 2.1 5.8 0.0 ------31.5 12.9%

5.8 -0.4 -51.4 2.5 3.9 0.0 ------39.6 10.9%

-5.8 -0.4 -12.4 6.2 2.9 0.0 ------9.5 36.3%

0.0 0.0 -0.4 6.3 0.0 0.0 -----5.9 21.5%

0.0 0.0 -14.1 6.4 0.0 0.0 ------7.7 26.5%

0.0 0.0 -6.8 7.1 0.0 0.0 -----0.3 29.3%

0.0 0.0 -10.9 7.5 6.7 0.0 -----3.3 30.2%

0.0 0.0 -11.1 8.1 9.7 0.0 -----6.7 31.0%

0.0 0.0 -10.3 8.7 12.1 0.0 -----10.5 32.0%

0.0 0.0 -9.2 9.3 14.7 0.0 ------14.8 32.9%

0.0 0.0 -7.8 9.8 17.5 0.0 ------19.4 33.8%

0.0 0.0 -6.2 10.2 20.3 0.0 ------24.2 34.8%

0.0 0.0 -2.5 10.6 24.3 0.0 ------32.4 36.8%

0.0 0.0 -2.5 10.7 25.5 0.0 ------33.8 36.9%

0.0 0.0 -5.9 10.8 23.3 0.0 ------28.2 36.2%

TABLE 4. HISTORICAL RATIOS FOR FORECAST ASSUMPTIONS -6 Operations Real growth (g ) Inflation (i ) Revenue growth (c ) Operating expenses/ revenues Working cap/revenues Operating cash/revenues Trade receiv's/revenues Other receiv's/revenues Inventories/revenues Prepaid exp's/revenues Accounts payable/rev's Other curr liab's/rev's Property, Plant and Equipment (PPE) PPE economic life (n ) F _sub_g (see Sect 6) -5 -4 -3 -2 -1 0

TABLE 8. FORECAST ASSUMPTIONS 1 2 3 4 5 6 7 8 9 10 11 12

12.5% 88.8% 92.6%

22.5% 91.7%

10.0% 91.7%

16.9% 91.6%

19.7% 91.6%

20.6% 92.5%

15.0% 3.0% 18.5% 90.0%

12.0% 3.0% 15.4% 90.0%

8.0% 3.0% 11.2% 90.0%

7.0% 3.0% 10.2% 90.0%

6.0% 3.0% 9.2% 90.0%

5.0% 3.0% 8.2% 90.0%

4.0% 3.0% 7.1% 90.0%

3.0% 3.0% 6.1% 90.0%

2.0% 3.0% 5.1% 90.0%

0.0% 3.0% 3.0% 90.0%

0.0% 3.0% 3.0% 90.0%

0.0% 3.0% 3.0% 90.0%

2.0% 9.1% 0.8% 1.0% 2.2% 3.7% 7.0%

2.0% 11.0% 0.9% 0.9% 2.3% 4.9% 6.1%

2.0% 12.1% 1.0% 1.0% 1.9% 4.4% 6.7%

2.0% 11.1% 0.9% 0.8% 2.0% 3.5% 6.2%

1.7% 12.5% 1.8% 2.6% 0.7% 4.1% 6.1%

2.0% 11.9% 1.2% 2.6% 1.1% 3.9% 6.6%

2.0% 11.4% 1.1% 2.4% 1.0% 3.7% 5.7%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

2.0% 11.3% 1.1% 1.6% 1.6% 4.0% 6.3%

10 10.0000

3

MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)

154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204

A F _sub_c (see Sect 6) Real PPE/rev's (K ) M (see Section 6) H (see Section 6) Net PPE/revenues Depreciation/net PPE * Retirements/net PPE * Taxes Tax rate PPE tax life (q ) J (see Section 8) Timing diff's/net PPE Interest rate items Real borrowing rate Nominal borrowing rate Real cost of equity Nominal cost of equity Book value target for financial strength This year's short-term/last year's long-term debt * last year's net PPE

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

31.5%

33.9% 14.9% 7.5%

29.2% 14.9% 6.4%

33.1% 16.4% 6.5%

37.9% 15.1% -0.4%

44.3% 13.3% 2.0%

38.4% 14.2% 5.3%

35.5% 14.8% 5.3%

34.9% 15.1% 6.3%

34.2% 15.3% 7.3%

33.6% 15.6% 8.3%

33.0% 15.8% 9.3%

32.4% 16.1% 10.3%

31.8% 16.4% 11.2%

31.1% 16.6% 12.2%

30.5% 16.9% 13.2%

29.9% 17.2% 14.2%

S 8.7861 0.580 0.510 0.426 29.3% 17.4% 15.2%

T

29.3% 17.4% 15.2%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

39.0%

35.8%

37.4%

40.6%

40.1%

37.8%

34.7%

33.4%

37.1%

37.7%

38.3%

38.8%

39.4%

40.0%

40.5%

41.1%

41.7%

42.2%

39.0% 5 2.160 42.8%

39.0%

42.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

6.0% 9.0% 11.8% 14.8%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

57.2%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

20.0%

VALUE CALCULATIONS 1 1. Free Cash Flow Excess marketable securities (at year start) Interest-bearing debt (at year start) Free cash fl (at year end) WACC Computed value of oper assets (at year start) Computed equity value (at year start; including deferred taxes) Desired E/(D+E) year 12 Result E/(D+E) year 12 Result E/(D+E) all years Drive to 0 by Goal Seek (vary year 1 Book value 2 3 4 5 6 7 8 9 10 11 12

0.0 115.5 5.9 10.1% 198.5

0.0 115.9 -7.7 10.1% 212.8

0.0 130.0 0.3 10.1% 242.0

0.0 136.8 3.3 10.1% 266.2

0.0 147.7 6.7 10.1% 290.0

0.0 158.8 10.5 10.1% 312.7

0.0 169.1 14.8 10.1% 333.9

0.0 178.3 19.4 10.1% 353.0

0.0 186.2 24.2 10.1% 369.4

0.0 192.4 32.4 10.1% 382.6

0.0 194.9 33.8 10.1% 389.1

0.0 197.4 28.2 10.1% 394.8

83.0 50.0% 50.0% 41.8%

96.8

112.0

129.4

142.3

153.9

164.8

174.7

183.2

190.2

194.2

197.4

45.5%

46.3%

48.6%

49.1%

49.2%

49.4%

49.5%

49.6%

49.7%

49.9%

50.0%

4

MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)

205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226

A target for financial strength (I175)!) 2. Abnormal Earnings Interest-bearing debt (at year start) Book equity value (at year start) Net income (at year end) Cost of equity Abnormal earnings Computed equity value (at year start) Desired E/(D+E) year 12 Result E/(D+E) year 12 Result E/(D+E) all years Drive to 0 by Goal Seek (vary year 1 Book value target for financial strength (I175)!)

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

115.5 96.1 12.6 14.8% -1.6 86.2 50.0% 50.0% 42.7%

115.9 108.7 16.2 14.8% 0.1 98.9

130.0 125.0 17.2 14.8% -1.3 113.6

136.8 142.2 19.1 14.8% -1.9 130.4

147.7 154.6 20.8 14.8% -2.1 143.0

158.8 165.7 22.3 14.8% -2.2 154.5

169.1 175.9 23.7 14.8% -2.3 165.3

178.3 184.9 25.0 14.8% -2.4 175.0

186.2 192.5 26.1 14.8% -2.3 183.5

192.4 198.4 26.3 14.8% -3.0 190.3

194.9 200.4 27.5 14.8% -2.1 194.2

197.4 202.3 29.4 14.8% -0.6 197.4

46.0%

46.6%

48.8%

49.2%

49.3%

49.4%

49.5%

49.6%

49.7%

49.9%

50.0%

5




友情链接: