《麦肯锡公司估值模型教学.xls》由会员分享，可在线阅读，更多相关《麦肯锡公司估值模型教学.xls（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS(FILE MCK_1.XLS)TABLE 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.313
2、04.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.137.139
3、.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.941.042.94
4、3.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 retainedearnings-6-5-4-3
5、-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.3-25.5-23.3-En
6、ding 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.022.223.424.124.82
7、5.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.2Inventories1.92.12.82.59.
8、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 equipment100.0117.7128.2155.6204
9、.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 ABCDEFGHIJKLMNOPQRST12345678910111213141516171819202122232425262728293031323
10、33435363738394041424344454647484950511MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS(FILE MCK_1.XLS)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.6317.5362.0397.9433.3467.3499.25
11、28.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.848.753.758.763.468.072.175.778.
12、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.125.330.835.439.343.146.950.553.856.
13、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.7125.0142.2154.6165.7175.9184.9192.5198.
14、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/invested capital 8.2%13.8%17.3%19.2%31.4%46.9%
15、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 cash flow-5-4-3-2-10123456789101112Revenues22
16、2.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.0-17.7-26.4-28.7-32.0-36.9-41.0-45.1-49.1-53.
17、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.00.00.00.00.00.00.0Change in deferred income tax
18、es2.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.223.127.633.433.853.259.265.171.577.783.789.394.398.6
19、100.7103.5106.6Change in workingABCDEFGHIJKLMNOPQRST5253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011022MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS(FILE MCK_1.XLS)capital(+if increase)5.15.62.410.42.58.30.36.65.65.65.65.45.14.
20、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-31.5-39.6-9.55.9-7.70.33.36.710.514.819.424.232.433.828.2=Fin
21、ancial 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.0Incr(-)/Decr(+)in short-and long-term debt-6.5-4.8-6.1-28.5-51.4-1
22、2.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(-)/Decr(+)in common stock-0.3-18.70.00.00.00.00.00.00.00.00.00.00.00.00.0
23、0.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.HISTORICAL RATIOS FOR FORECAST ASSUMPTIONSTABLE 8.FORECAST ASSUMPTIONS-6-5-4
24、-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%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
25、.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/revenues9.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%1
26、1.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 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
27、%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 and Equipment(PPE)PPE economic life(n)10 F_sub_g(see Sect 6)10.0000ABCDEFGHIJKLMNOPQRS
28、T1031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521533MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS(FILE MCK_1.XLS)F_sub_c(see Sect 6)8.7861 Real PPE/revs(K)0.580 M(see Section 6)0.
29、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.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
30、%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.160 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.
31、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%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%
32、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%20.0%20.0%20.0%20.0%20.0%20.0%20.0%20.0%20.0%*last years net PPEVALUE CALCULATIONS1234567891011121.Free Cas
33、h 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 cash 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%
34、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/
35、(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 by Goal Seek (vary year 1 Book valueABCDEFGHIJKLMNOPQRST1541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032044MCKAY
36、 VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS(FILE MCK_1.XLS)target for financial strength(I175)!)2.Abnormal EarningsInterest-bearing debt (at year start)115.5115.9130.0136.8147.7158.8169.1178.3186.2192.4194.9197.4Book equity value (at year start)96.1108.7125.0142.2154.6165.7175.9184.91
37、92.5198.4200.4202.3Net income(at year end)12.616.217.219.120.822.323.725.026.126.327.529.4Cost 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%Abnormal earnings-1.60.1-1.3-1.9-2.1-2.2-2.3-2.4-2.3-3.0-2.1-0.6Computed equity value (at year start)86.298.9113.6130.4143.0154.5165.317
38、5.0183.5190.3194.2197.4Desired E/(D+E)year 1250.0%Result E/(D+E)year 1250.0%Result E/(D+E)all years42.7%46.0%46.6%48.8%49.2%49.3%49.4%49.5%49.6%49.7%49.9%50.0%Drive to 0 by Goal Seek (vary year 1 Book value target for financial strength(I175)!)ABCDEFGHIJKLMNOPQRST2052062072082092102112122132142152162172182192202212222232242252265