麦肯锡公司估值模型教学.xls
《麦肯锡公司估值模型教学.xls》由会员分享,可在线阅读,更多相关《麦肯锡公司估值模型教学.xls(5页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
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.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 麦肯锡 公司 模型 教学
限制150内