麦肯锡公司价值评估详细教学-附表

1、MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)1TABLE 1. HISTORICAL INCOME STATEMENTSTABLE 5. FORECASTED INCOME STATEMENTSIncome statement-6-5-4-3-2-10123456789101112Revenues197.6222.3272.3299.5350.0418.9505.4598.6690.6768.2846.7924.4999.71070.91136.11193.61229.41266

2、.31304.3Operating expenses-175.4-205.8-249.6-274.7-320.5-383.6-467.4-538.8-621.5-691.4-762.0-831.9-899.7-963.8-1022.5-1074.2-1106.5-1139.7-1173.9Depreciation-12.8-9.3-11.2-13.0-15.0-17.7-26.4-28.7-32.0-36.9-41.0-45.1-49.1-53.0-56.5-59.7-62.5-64.0-64.5 - -Operating income9.47.211.511.814.517.611.631.

3、137.139.943.747.350.854.157.159.660.562.665.9Interest income *0.00.00.00.00.90.70.60.00.00.00.00.00.00.00.00.00.00.00.0Interest expense-0.1-0.4-0.8-1.0-3.4-4.1-10.1-10.4-10.4-11.7-12.3-13.3-14.3-15.2-16.0-16.8-17.3-17.5-17.8 - -Earnings before taxes9.36.810.710.812.014.22.120.726.628.231.434.036.538

4、.941.042.943.245.148.1Revaluation of deferred income taxes0.00.00.00.00.00.00.00.00.00.00.00.0Income taxes-3.3-2.4-3.8-4.2-5.0-6.1-0.7-8.1-10.4-11.0-12.2-13.3-14.3-15.2-16.0-16.7-16.8-17.6-18.8 - -Net income6.04.46.96.67.08.11.412.616.217.219.120.822.323.725.026.126.327.529.4=Statement of retainedea

5、rnings-6-5-4-3-2-10123456789101112Beginning retained earnings62.563.765.868.669.874.072.585.1101.4118.6131.0142.1152.3161.3168.9174.8176.8178.7Net income4.46.96.67.08.11.412.616.217.219.120.822.323.725.026.126.327.529.4Common dividends-3.2-4.8-3.8-5.8-3.9-2.90.00.00.0-6.7-9.7-12.1-14.7-17.5-20.3-24.

6、3-25.5-23.3- -Ending retained earnings62.563.765.868.669.874.072.585.1101.4118.6131.0142.1152.3161.3168.9174.8176.8178.7184.8* direct forecastTABLE 2. HISTORICAL BALANCE SHEETSTABLE 6. FORECASTED BALANCE SHEETS-6-5-4-3-2-10123456789101112Operating cash4.04.45.46.06.08.410.111.713.515.016.618.119.621

7、.022.223.424.124.825.5Excess marketable securities *10.93.020.510.30.05.80.00.00.00.00.00.00.00.00.00.00.00.00.0Trade receivables17.924.433.033.343.949.857.767.778.186.895.7104.5113.0121.0128.4134.9139.0143.1147.4Other receivables1.52.02.72.76.24.95.76.57.58.49.210.110.911.712.413.013.413.814.2Inven

8、tories1.92.12.82.59.010.911.99.711.112.413.714.916.117.318.319.319.820.421.0Prepaid expenses4.35.15.36.02.44.45.09.511.012.213.514.715.917.118.119.019.620.220.8 - -Current assets40.541.069.760.867.584.290.4105.1121.2134.9148.6162.3175.5188.0199.5209.5215.8222.3229.0Gross property, plant and equipmen

9、t100.0117.7128.2155.6204.7272.5297.6334.2381.1422.7463.5502.6539.1572.1600.7624.1638.1649.5668.8Accumulated depreciation-37.7-42.3-48.7-56.5-71.9-86.9-103.4-121.8-140.4-159.7-178.9-197.6-215.4-232.0-246.9-259.9-270.7-278.8-287.1 - -Net property, plant ABCDEFGHIJKLMNOPQRST1234567891011121314151617181

10、92021222324252627282930313233343536373839404142434445464748495051MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)2 and equipment62.375.479.599.1132.8185.6194.2212.4240.8263.1284.7305.0323.7340.1353.7364.2367.5370.6381.7 - -Total assets102.8116.4149.2159.9200.3269.8284

11、.6317.5362.0397.9433.3467.3499.2528.1553.2573.7583.3592.9610.7=Short-term debt0.30.80.91.511.512.520.719.019.422.122.925.026.828.530.031.232.232.533.0Accounts payable7.311.011.910.514.216.218.924.127.830.934.137.240.243.145.748.149.551.052.5Other current liabilities13.913.518.218.521.427.828.838.043

12、.848.753.758.763.468.072.175.778.080.482.8 - -Total current liabilities21.525.331.030.547.156.568.481.091.0101.8110.7120.8130.5139.5147.8155.0159.7163.9168.2Long-term debt5.511.516.221.740.290.694.897.0110.6114.7124.8133.9142.3149.8156.2161.2162.7164.9170.3Deferred income taxes8.711.012.615.519.625.

13、125.330.835.439.343.146.950.553.856.759.260.561.963.7Common stock *4.64.923.623.623.623.623.623.623.623.623.623.623.623.623.623.623.623.623.6Retained earnings62.563.765.868.669.874.072.585.1101.4118.6131.0142.1152.3161.3168.9174.8176.8178.7184.8 - -Total common equity67.168.689.492.293.497.696.1108.

14、7125.0142.2154.6165.7175.9184.9192.5198.4200.4202.3208.4 - -Total liabilities and equity102.8116.4149.2159.9200.3269.8284.6317.5362.0397.9433.3467.3499.2528.1553.2573.7583.3592.9610.7=Invested capital70.788.998.6120.6164.7220.0236.9255.4290.4318.3345.5371.5395.5417.0435.3449.9455.8461.6475.5Debt/inv

15、ested capital 8.2%13.8%17.3%19.2%31.4%46.9%48.8%45.4%44.8%43.0%42.8%42.8%42.8%42.8%42.8%42.8%42.8%42.8%42.8%NOPLAT/invested capital (ROIC)8.7%9.6%9.2%8.9%8.1%3.2%9.9%10.0%9.3%9.2%9.1%9.0%8.9%8.9%8.8%8.4%8.6%9.0%* direct forecastTABLE 3. HISTORICAL FREE CASH FLOWTABLE 7. FORECASTED FREE CASH FLOWFree

16、 cash flow-5-4-3-2-10123456789101112Revenues222.3272.3299.5350.0418.9505.4598.6690.6768.2846.7924.4999.71070.91136.11193.61229.41266.31304.3Operating expenses-205.8-249.6-274.7-320.5-383.6-467.4-538.8-621.5-691.4-762.0-831.9-899.7-963.8-1022.5-1074.2-1106.5-1139.7-1173.9Depreciation-9.3-11.2-13.0-15

17、.0-17.7-26.4-28.7-32.0-36.9-41.0-45.1-49.1-53.0-56.5-59.7-62.5-64.0-64.5- -EBIT7.211.511.814.517.611.631.137.139.943.747.350.854.157.159.660.562.665.9Taxes on EBIT-2.6-4.1-4.6-6.0-7.4-4.4-12.1-14.5-15.6-17.0-18.5-19.8-21.1-22.3-23.3-23.6-24.4-25.7Revaluation of deferred income taxes0.00.00.00.00.00.

18、00.00.00.00.00.00.0Change in deferred income taxes2.31.62.94.15.50.25.54.63.93.93.83.63.32.92.51.31.31.9- -NOPLAT6.99.010.112.615.77.424.427.228.230.532.634.636.337.738.938.239.642.1Add back depreciation9.311.213.015.017.726.428.732.036.941.045.149.153.056.559.762.564.064.5- -Gross cash flow16.220.2

19、23.127.633.433.853.259.265.171.577.783.789.394.398.6100.7103.5106.6Change in workingABCDEFGHIJKLMNOPQRST525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS (FILE MCK_1.XLS)3 capital (

20、+ if increase)5.15.62.410.42.58.30.36.65.65.65.65.45.14.74.12.62.62.7Capital expenditures (+ if positive)22.415.332.648.770.535.047.060.359.262.665.567.869.470.270.265.767.175.6- -Gross investment27.520.935.059.173.043.347.366.964.868.271.173.274.574.974.368.369.878.4- -Free cash flow-11.3-0.7-11.9-

21、31.5-39.6-9.55.9-7.70.33.36.710.514.819.424.232.433.828.2=Financial cash flow-5-4-3-2-10123456789101112Incr(+)/Decr(-) in excess marketable securities-7.917.5-10.2-10.35.8-5.80.00.00.00.00.00.00.00.00.00.00.00.0After-tax interest income (-)0.00.00.0-0.5-0.4-0.40.00.00.00.00.00.00.00.00.00.00.00.0Inc

22、r(-)/Decr(+) in short- and long-term debt-6.5-4.8-6.1-28.5-51.4-12.4-0.4-14.1-6.8-10.9-11.1-10.3-9.2-7.8-6.2-2.5-2.5-5.9After-tax interest expense (+)0.20.50.62.12.56.26.36.47.17.58.18.79.39.810.210.610.710.8Common dividends (+)3.24.83.85.83.92.90.00.00.06.79.712.114.717.520.324.325.523.3Incr(-)/Dec

23、r(+) in common stock-0.3-18.70.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.0- -Financial cash flow-11.3-0.7-11.9-31.5-39.6-9.55.9-7.70.33.36.710.514.819.424.232.433.828.2Effective tax rate on EBIT3.6%21.8%14.3%12.9%10.9%36.3%21.5%26.5%29.3%30.2%31.0%32.0%32.9%33.8%34.8%36.8%36.9%36.2%TABLE 4. HISTO

24、RICAL RATIOS FOR FORECAST ASSUMPTIONSTABLE 8. FORECAST ASSUMPTIONS-6-5-4-3-2-10123456789101112Operations Real growth (g)15.0%12.0%8.0%7.0%6.0%5.0%4.0%3.0%2.0%0.0%0.0%0.0% Inflation (i)3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0% Revenue growth (c)12.5%22.5%10.0%16.9%19.7%20.6%18.5%15.4%11.2%10.2

25、%9.2%8.2%7.1%6.1%5.1%3.0%3.0%3.0% Operating expenses/ revenues88.8%92.6%91.7%91.7%91.6%91.6%92.5%90.0%90.0%90.0%90.0%90.0%90.0%90.0%90.0%90.0%90.0%90.0%90.0% Working cap/revenues Operating cash/revenues2.0%2.0%2.0%2.0%1.7%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0% Trade receivs/revenue

26、s9.1%11.0%12.1%11.1%12.5%11.9%11.4%11.3%11.3%11.3%11.3%11.3%11.3%11.3%11.3%11.3%11.3%11.3%11.3% Other receivs/revenues0.8%0.9%1.0%0.9%1.8%1.2%1.1%1.1%1.1%1.1%1.1%1.1%1.1%1.1%1.1%1.1%1.1%1.1%1.1% Inventories/revenues1.0%0.9%1.0%0.8%2.6%2.6%2.4%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6% Prepaid

27、exps/revenues2.2%2.3%1.9%2.0%0.7%1.1%1.0%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6%1.6% Accounts payable/revs3.7%4.9%4.4%3.5%4.1%3.9%3.7%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0% Other curr liabs/revs7.0%6.1%6.7%6.2%6.1%6.6%5.7%6.3%6.3%6.3%6.3%6.3%6.3%6.3%6.3%6.3%6.3%6.3%6.3%Property, Plant

28、 and Equipment (PPE) PPE economic life (n)10 F_sub_g (see Sect 6)10.0000ABCDEFGHIJKLMNOPQRST103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS

29、 METHODS (FILE MCK_1.XLS)4 F_sub_c (see Sect 6)8.7861 Real PPE/revs (K)0.580 M (see Section 6)0.510 H (see Section 6)0.426 Net PPE/revenues31.5%33.9%29.2%33.1%37.9%44.3%38.4%35.5%34.9%34.2%33.6%33.0%32.4%31.8%31.1%30.5%29.9%29.3%29.3% Depreciation/net PPE *14.9%14.9%16.4%15.1%13.3%14.2%14.8%15.1%15.

30、3%15.6%15.8%16.1%16.4%16.6%16.9%17.2%17.4%17.4% Retirements/net PPE *7.5%6.4%6.5%-0.4%2.0%5.3%5.3%6.3%7.3%8.3%9.3%10.3%11.2%12.2%13.2%14.2%15.2%15.2% Taxes Tax rate 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%39.0%39.0% PPE tax life (q)5 J (see Section 8)2.16

31、0 Timing diffs/net PPE35.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%42.8%42.8%Interest rate items Real borrowing rate6.0%6.0%6.0%6.0%6.0%6.0%6.0%6.0%6.0%6.0%6.0%6.0% Nominal borrowing rate9.0%9.0%9.0%9.0%9.0%9.0%9.0%9.0%9.0%9.0%9.0%9.0% Real cost of equity11.8%

32、11.8%11.8%11.8%11.8%11.8%11.8%11.8%11.8%11.8%11.8%11.8% Nominal cost of equity14.8%14.8%14.8%14.8%14.8%14.8%14.8%14.8%14.8%14.8%14.8%14.8%Book value target for financial strength57.2%57.2%57.2%57.2%57.2%57.2%57.2%57.2%57.2%57.2%57.2%57.2%This years short-term/last years long-term debt 20.0%20.0%20.0

33、%20.0%20.0%20.0%20.0%20.0%20.0%20.0%20.0%20.0%* last years net PPEVALUE CALCULATIONS1234567891011121. Free Cash FlowExcess marketable securities (at year start)0.00.00.00.00.00.00.00.00.00.00.00.0Interest-bearing debt (at year start)115.5115.9130.0136.8147.7158.8169.1178.3186.2192.4194.9197.4Free ca

34、sh fl (at year end)5.9-7.70.33.36.710.514.819.424.232.433.828.2WACC10.1%10.1%10.1%10.1%10.1%10.1%10.1%10.1%10.1%10.1%10.1%10.1%Computed value of oper assets (at year start)198.5212.8242.0266.2290.0312.7333.9353.0369.4382.6389.1394.8Computed equity value (at year start; including deferred taxes)83.096.8112.0129.4142.3153.9164.8174.7183.2190.2194.2197.4Desired E/(D+E) year 1250.0%Result E/(D+E) year 1250.0%Result E/(D+E) all years41.8%45.5%46.3%48.6%49.1%49.2%49.4%49.5%49.6%49.7%49.9%50.0%Drive to 0