花旗银行DCF估值模型模板

1、peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! self-studytraining the street self-study course packs deliver the high-powered materials financial accountingand instruction style featured at live seminars in an innovative combination of a corporate v

2、aluation reference book, on-demand online companion and dynamic excel modeling financial modeling drills. our materials are designed to equip or enhance your financial modeling merger modeling and corporate valuation skills to best prepare you for an interview, a new excel best practicesprofession o

3、r a specific job function. and more. for more detail https:/ open enrollment, corporate valuation and financial modeling workshops ideal for industry evening & weekend professionals, students and perspective new hires. everyone is eligible for an classes additional refund for referring a friend. htt

4、p:/ public comparables, acquisition comparables, financial modeling discounted cash flow, merger modeling, leveraged buyouts tts macros list?do you want us to send you our latest updates? email: peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! trainin

5、g the street, inc. 275 madison avenue suite 1201 new york, ny 10016 usa tel: +1 (800) 887-1320 (in the united states) +1 (704) 927-9688 (outside the united states) fax:+1 (704) 547-3261 email: and instruction style featured at live seminars in an innovative combination of a peter casper ibd australi

6、a copyright 2011 by training the street, inc.for training purposes only! company namemccormick & co. inc. cell f3 is named name sub-headerdollars in millions, except per share data cell f5 is named subheader last fiscal year end11/30/2010 cell is named fye number of days in a year365 cell is named d

7、ays final inputs201120122013step sales growth rate6.1%6.1%6.1%0.0% gross margin46.5%47.0%47.5%0.5% sg&a margin28.2%28.6%28.9%0.4% effective tax rate31.0% accounts receivable, net (collection period in days)42.042.042.00.0 inventories (days outstanding)96.096.096.00.0 other current assets (as % of sa

8、les)3.0%3.0%3.0%0.0% accounts payable (days outstanding)61.061.061.00.0 other current liabilities (as % of cost of sales)24.0%24.0%24.0%0.0% capex$95.0$101.4$107.6 capex as a % of sales2.7%2.7%2.7%0.0% asset sales and write-offs0.00.00.0 stock-based compensation expense$11.9$11.9$11.9 assumed curren

9、t year p/e multiple16.0 x16.0 x16.0 x0.0 x shares repurchased - millions4.0004.0004.0000.000 new shares issued from options - millions2.5002.5002.5000.000 average strike price$32.01$32.01$32.01 dividends$152.0$164.8$179.3 dividend growth rate0.0%0.0%0.0% minimum cash balance$50.0$50.0$50.0 revolver

10、interest rate0.50%0.75%1.00% long-term debt interest rate5.27%5.27%5.27% cash balances interest rate0.25%0.25%0.25% 201520152015 ebitdaebiteps $847.9$746.2$4.10 this area is not needed until your model is finished peter casper ibd australia copyright 2011 by training the street, inc.for training pur

11、poses only! income statement for mccormick & co. inc.street case (case 2) dollars in millions, except per share data 2010 - 2015 historical year ending november 30,projected year ending november 30,cagr 20082009201020112012201320142015 sales$3,176.6$3,192.1$3,336.8$3,540.3$3,756.3$3,985.4$4,228.6$4,

12、486.56.1% cost of goods sold (excluding depreciation) (1)1,804.21,774.91,822.51,894.11,990.82,092.42,198.82,310.5 gross profit1,372.41,417.21,514.31,646.31,765.51,893.12,029.72,175.97.5% sg&a expenses (excluding amortization) (2)864.7837.8904.4998.41,072.41,151.81,236.91,328.0 ebitda507.7579.4609.96

13、47.9693.0741.3792.9847.96.8% depreciation (3)79.787.591.688.189.691.794.597.9 amortization4.04.0 ebit (4)422.1490.6514.8555.8599.5645.7694.5746.27.7% interest expense56.752.849.343.741.134.728.122.9 interest (income) 0.00.00.0(0.1)(0.3)(0.3)(0.2)(0.2) other non-operating (income) /

14、 expense (5)(5.1)(2.4)(2.2)(2.2)(2.2)(2.2)(2.2)(2.2) pretax income370.5440.2467.7514.4560.9613.5668.8725.79.2% income taxes (6)115.8140.8133.5159.5173.9190.2207.3225.0 minority interest expense / (equity income in affiliates)(18.6)(16.3)(25.5)(25.0)(25.0)(25.0)(25.0)(25.0) net income $273.3$315.7$35

15、9.7$379.9$412.0$448.3$486.5$525.77.9% diluted weighted average shares (in millions)131.800132.300134.700134.350132.850131.350129.850128.350(1.0%) earnings per share$2.07$2.39$2.67$2.83$3.10$3.41$3.75$4.108.9% marginsstep functioncase gross margin43.2%44.4%45.4%46.5%47.0%47.5%48.0%48.5%0.5%2 upside c

16、ase (case 1)46.5%47.3%48.0%48.8%49.5%0.8% street case (case 2)46.5%47.0%47.5%48.0%48.5%0.5% base case (case 3)46.5%46.8%47.0%47.3%47.5%0.3% sg&a margin27.2%26.2%27.1%28.2%28.6%28.9%29.3%29.6%0.4% upside case (case 1)28.2%28.4%28.6%28.8%29.0%0.2% street case (case 2)28.2%28.6%28.9%29.3%29.6%0.4% base

17、 case (case 3)28.2%28.8%29.4%30.0%30.6%0.6% ebitda margin16.0%18.2%18.3%18.3%18.3%18.3%18.3%18.3% ebit margin13.3%15.4%15.4%15.7%16.0%16.2%16.4%16.6% net income margin8.6%9.9%10.8%10.7%11.0%11.2%11.5%11.7% growth rate analysisstep function sales growth rate0.5%4.5%6.1%6.1%6.1%6.1%6.1%0.0% upside cas

18、e (case 1)6.3%6.3%6.3%6.3%6.3%0.0% street case (case 2)6.1%6.1%6.1%6.1%6.1%0.0% base case (case 3)5.9%5.9%5.9%5.9%5.9%0.0% ebitda growth rate14.1%5.3%6.2%7.0%7.0%7.0%6.9% ebit growth rate16.2%4.9%8.0%7.9%7.7%7.6%7.4% net income growth rate15.5%13.9%5.6%8.4%8.8%8.5%8.1% eps growth rate15.1%11.9%5.9%9

19、.7%10.0%9.8%9.3% effective tax rate31.3%32.0%28.6%31.0%31.0%31.0%31.0%31.0%0.0% peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! mccormick & co. inc.street case (case 2) weighted average cost of capital analysis leveredtotalmkt. valuedebt /debt /margi

20、nal unlevered comparables:tickerbetadebtequitymkt. cap.total cap.tax ratebeta associated british foods plcabf.l0.723$1,894.9$13,147.414.4%12.6%27.0%0.654 campbell soup companycpb0.6083,259.010,928.029.8%23.0%35.0%0.509 conagra foods inc.cag0.7073,600.010,176.135.4%26.1%37.6%0.579 danone groupbn0.733

21、8,370.842,164.719.9%16.6%34.4%0.649 general mills, inc.gis0.5787,361.224,881.829.6%22.8%35.7%0.486 h.j. heinz companyhnz0.6884,294.316,462.426.1%20.7%31.8%0.584 hershey companyhsy0.6001,992.813,250.015.0%13.1%38.2%0.549 kellogg companyk0.5986,371.020,371.731.3%23.8%32.3%0.494 kerry group plckrz0.629

22、1,877.96,938.627.1%21.3%23.9%0.522 kraft foods inc.kft0.66531,459.058,398.253.9%35.0%30.9%0.485 nestle sanesn.vx0.73222,879.4191,153.712.0%10.7%25.3%0.672 sara lee corp.sle0.7052,678.011,645.123.0%18.7%35.0%0.613 the j. m. smucker companysjm0.6681,644.78,665.119.0%16.0%36.1%0.596 treehouse foods, in

23、c.ths0.7301,014.42,179.346.5%31.8%34.0%0.558 average0.669$7,049.8$30,740.227.3%20.9%32.7%0.568 median0.6783,429.513,198.726.6%21.0%34.2%0.569 mccormick & co. inc.mkc0.642$970.2$6,363.115.2%13.2%32.7%0.582 assumptions risk free rate3.38%equity size premium1.01% market risk premium6.70%cost of debt6.0

24、0% mccormick & co. inc. capital structurewacc: based on average unlevered betas of comparable companies debt/market capital15.2%unleveraged beta0.568cost of debt (after tax)4.04% debt/total capital13.2%leveraged beta0.626cost of equity8.58% equity/total capital86.8%marginal tax rate32.7%wacc7.98% wa

25、cc: based on median unlevered betas of comparable companies unleveraged beta0.569cost of debt (after tax)4.04% leveraged beta0.627cost of equity8.59% marginal tax rate32.7%wacc7.99% wacc: based on the mccormick & co. inc. beta unleveraged beta0.582cost of debt (after tax)4.04% leveraged beta0.642cos

26、t of equity8.69% marginal tax rate32.7%wacc8.08% peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! wacc sensitivity analysis market risk premium 6.00%6.20%6.40%6.60%6.80%7.00%7.20%7.40% 0.4807.1%7.2%7.3%7.4%7.5%7.6%7.6%7.7% 0.5107.3%7.4%7.5%7.6%7.7%7.8

27、%7.9%8.0% unlevered0.5407.4%7.5%7.6%7.8%7.9%8.0%8.1%8.2% beta0.5707.6%7.7%7.8%7.9%8.1%8.2%8.3%8.4% 0.6007.8%7.9%8.0%8.1%8.2%8.4%8.5%8.6% 0.6308.0%8.1%8.2%8.3%8.4%8.6%8.7%8.8% 0.6608.1%8.3%8.4%8.5%8.6%8.8%8.9%9.0% 0.6908.3%8.4%8.6%8.7%8.8%9.0%9.1%9.2% based on average comparable companies peter caspe

28、r ibd australia copyright 2011 by training the street, inc.for training purposes only! mccormick & co. inc.street case (case 2) five year discounted cash flow analysis terminal ebitda multiple approach projected year ending november 30, dollars in millions, except per share data20112012201320142015

29、net income$379.9$412.0$448.3$486.5$525.7 net interest expense / (income)43.640.834.427.922.7 equity (earnings) / loss in uncons. subs.(25.0)(25.0)(25.0)(25.0)(25.0) loss on debt redemption0.00.00.00.00.0 loss on preferred redemption0.00.00.00.00.0 marginal tax rate32.7%32.7%32.7%32.7%32.7% financing

30、 tax shield(14.3)(13.3)(11.2)(9.1)(7.4) minority interest in net income0.00.00.00.00.0 earnings before interest$384.3$414.5$446.4$480.2$516.0 depreciation88.189.691.794.597.9 amortization of intangibles4.04.0 increase in deferred taxes0.00.00.00.00.0 change in net working capital(9.9)(17.4)

31、(18.6)(19.9)(21.3) additions to intangibles(5.0)(5.0)(5.0)(5.0)(5.0) capital expenditures(95.0)(101.4)(107.6)(114.2)(121.1) unlevered free cash flow$366.4$384.2$410.9$439.5$470.3 terminal ebitda$847.9 terminal adjusted free cash flow$493.5 terminal ebitda multiple rangeterminal ebitda multiple range

32、 9.5x 9.8x 10.0 x 10.3x 10.5x 9.5x 9.8x 10.0 x 10.3x 10.5x terminal valueimplied terminal p/e multiple $8,056$8,267$8,479$8,691$8,90314.7x 15.1x 15.5x 15.9x 16.3x waccfirm value as of 11/30/10waccequity value as of 11/30/10 (a) 8.0%$7,120$7,265$7,409$7,553$7,6988.0%$6,303$6,447$6,591$6,736$6,880 8.5

33、%6,9737,1147,2557,3967,5378.5%6,1556,2966,4376,5786,719 9.0%6,8296,9677,1057,2437,3809.0%6,0126,1506,2876,4256,563 9.5%6,6906,8246,9597,0947,2289.5%5,8726,0076,1416,2766,411 10.0%6,5536,6856,8176,9487,08010.0%5,7365,8685,9996,1316,262 waccimplied perpetuity growth ratewaccequity value per share as o

34、f 11/30/10 (b) 8.0%1.8%1.9%2.1%2.2%2.3%8.0%$46.73$47.80$48.87$49.94$51.00 8.5%%45.6346.6847.7248.7749.81 9.0%%44.5745.5946.6147.6348.65 9.5%%43.5344.5345.5346.5347.53 10.0%%42.5243.5044.4745.4546.43 (a) equity value equals firm

35、 value less total straight and convertible debt, straight and convertible preferred stock and minority interest, plus cash, options and warrants proceeds and investments in unconsolidated subsidiaries. (b) equity value per share equals equity value divided by diluted shares outstanding via treasury

36、stock method peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! terminal ebitda multiple approach present value present valuepresent valueof terminal value discountof 2011of 2012 to 2015 ebitda multiple offirm value ebitda multiple of ratecash flowcash

37、flows9.8x 10.0 x 10.3x 9.8x 10.0 x 10.3x 8.5%$337.7$1,278.0$5,498$5,639$5,780$7,114$7,255$7,396 9.0%336.21,257.75,3735,5115,6496,9677,1057,243 9.5%334.61,237.85,2525,3865,5216,8246,9597,094 plus:plus:plus:less:less: discountfirm value ebitda multiple of excessopt./warr.inv. in totaltotalequity value

38、 ebitda multiple of rate9.8x 10.0 x 10.3x cashproceedsunc. subs.debt (a)preferred (b)9.8x 10.0 x 10.3x 8.5%$7,114$7,255$7,396$6,296$6,437$6,578 9.0%6,9677,1057,243$41$0$121($979)$06,1506,2876,425 9.5%6,8246,9597,0946,0076,1416,276 terminal value / firm valueimplied perpetuity growth rateequity value

39、 per share discount ebitda multiple of ebitda multiple of ebitda multiple of rate9.8x 10.0 x 10.3x 9.8x 10.0 x 10.3x 9.8x 10.0 x 10.3x 8.5%77.3%77.7%78.2%2.4%2.5%2.7%$46.68$47.72$48.77 9.0%77.177.678.02.93.03.145.5946.6147.63 9.5%77.077.43.644.5345.5346.53 (a) total debt includes straight

40、and out-of-the-money convertible debt plus minority interest. (b) total preferred includes straight and out-of-the-money convertible preferred stock. peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! perpetuity growth approach perpetuity growth rateper

41、petuity growth rate 2.0%2.3%2.5%2.8%3.0%2.0%2.3%2.5%2.8%3.0% 2015 free cash flow $503$505$506$507$508 waccfirm value as of 11/30/10waccequity value as of 11/30/10 (a) 8.0%$7,348$7,611$7,898$8,212$8,5578.0%$6,531$6,794$7,080$7,394$7,740 8.5%6,7666,9857,2237,4817,7628.5%5,9496,1686,4056,6636,945 9.0%6

42、,2686,4536,6526,8677,1009.0%5,4505,6355,8356,0506,283 9.5%5,8365,9946,1636,3456,5409.5%5,0195,1775,3465,5275,723 10.0%5,4595,5955,7405,8956,06110.0%4,6414,7774,9225,0775,243 waccterminal valuewaccequity value per share as of 11/30/10 (b) 8.0%$5,710$5,973$6,260$6,574$6,9198.0%$48.42$50.36$52.49$54.82

43、$57.38 8.5%5,1515,3705,6075,8656,1478.5%44.1045.7347.4949.4051.49 9.0%4,6744,8595,0585,2735,5079.0%40.4141.7843.2544.8546.58 9.5%4,2644,4224,5914,7724,9689.5%37.2138.3839.6340.9842.43 10.0%3,9074,0434,1884,3434,50910.0%34.4135.4236.4937.6438.87 waccterminal value / present valueas % of firm valuewac

44、cimplied terminal ebitda multiple 8.0%77.7%78.5%79.3%80.1%80.9%8.0%9.9x 10.4x 10.8x 11.4x 12.0 x 8.5%76.176.977.678.479.28.5%10.410.9 9.0%74.675.376.076.877.69.0%9.610.0 9.5%73.173.874.575.276.09.5%8.99.2 10.0%71.672.373.073.774.410.0%8.28.6 (a) equity value equal

45、s firm value less total straight and convertible debt, straight and convertible preferred stock and minority interest, plus cash, options and warrants proceeds and investments in unconsolidated subsidiaries. (b) equity value per share equals equity value divided by diluted shares outstanding via tre

46、asury stock method peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! balance sheet for mccormick & co. inc.street case (case 2) dollars in millions, except per share data historical year ending november 30,projected year ending november 30, 20082009201

47、020112012201320142015 cash$38.9$39.5$50.8$64.0$166.6$50.0$111.1$50.0 accounts receivable, net380.7365.3386.7407.4432.2458.6486.6516.3 inventories439.0457.6477.6498.2523.6550.3578.3607.7 other current assets109.7108.1100.8106.2112.7119.6126.9134.6 total current assets:968.3970.51,015.91,075.71,235.11

48、,178.51,302.81,308.6 pp&e, net$461.1$489.8$488.0$494.9$506.7$522.6$542.3$565.5 definite life intangibles99.534.936.237.338.5 indefinite life intangibles275.3202.4199.4199.4199.4199.4199.4199.4 goodwill1,230.21,479.71,417.41,417.41,417.41,417.41,417.41,417.4 other long-term assets127.6142

49、.1144.7144.7144.7144.7144.7144.7 equity in affiliates58.368.4121.2128.7136.2143.7151.2158.7 total assets:$3,220.3$3,387.8$3,419.7$3,494.9$3,674.7$3,642.5$3,795.2$3,832.8 accounts payable$266.1$298.7$302.7$316.5$332.7$349.7$367.5$386.1 other current liabilities414.0403.4431.7454.6477.8502.2527.7554.5

50、 total current liabilities:$680.1$702.1$734.4$771.1$810.5$851.8$895.2$940.7 revolver303.10.034.30.047.5 long-term debt936.1889.9880.1779.9779.6529.3528.9328.3 other long-term liabilities238.2351.1342.3342.3342.3342.3342.3342.3 total liabilities:2,157.52,044.31,957.01,893.31,932.41,757.81,

51、766.41,658.8 total stockholders equity1,062.81,343.51,462.71,601.61,742.21,884.72,028.72,174.0 total liabilities and equity:$3,220.3$3,387.8$3,419.7$3,494.9$3,674.7$3,642.5$3,795.2$3,832.8 parity check (a = l+e)0.0000.0000.000(0.000)(0.000)0.000(0.000)(0.000) total debt$1,239.2$991.1$880.3$779.9$779

52、.6$563.6$528.9$375.8 total book capitalization2,302.02,334.62,343.02,381.52,521.82,448.32,557.62,549.8 total debt / total book capitalization53.8%42.5%37.6%32.7%30.9%23.0%20.7%14.7% ebitda$507.7$579.4$609.9$647.9$693.0$741.3$792.9$847.9 total debt / ebitda244.1%171.1%144.3%120.4%112.5%76.0%66.7%44.3

53、% interest expense$56.7$52.8$49.3$43.7$41.1$34.7$28.1$22.9 ebitda coverage9.0 x11.0 x12.4x14.8x16.9x21.4x28.2x37.0 x peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! projected cash flow statement for mccormick & co. inc.street case (case 2) dollars in

54、 millions, except per share data projected year ending november 30, 20112012201320142015 operating activities net income$379.9$412.0$448.3$486.5$525.7 stock-based compensation expense11.911.911.911.911.9 depreciation88.189.691.794.597.9 amortization 4.04.0 (income) from unconsolidated affil

55、iates, net of dividends(7.5)(7.5)(7.5)(7.5)(7.5) (increase) / decrease in working capital(9.9)(17.4)(18.6)(19.9)(21.3) change in other long-term assets and liabilities0.00.00.00.00.0 cash flow from operating activities466.5492.6529.7569.3610.5 investing activities capital expenditures(95.0)(101.4)(1

56、07.6)(114.2)(121.1) additions to definite life intangibles(5.0)(5.0)(5.0)(5.0)(5.0) cash flow from investing activities(100.0)(106.4)(112.6)(119.2)(126.1) cash flow available for financing activities366.5386.2417.1450.1484.4 financing activities issuance / (repayment) of revolver(0.2)0.034.3(34.3)47

57、.5 issuance of long-term debt0.00.00.00.00.0 (repayment) of long-term debt(100.2)(0.3)(250.3)(0.4)(200.6) repurchase of equity(181.0)(198.5)(218.4)(239.8)(262.1) dividends(152.0)(164.8)(179.3)(194.6)(210.3) option proceeds80.080.080.080.080.0 cash flow from financing activities(353.3)(283.6)(533.7)(

58、389.1)(545.5) net change in cash13.2102.6(116.6)61.1(61.1) beginning cash balance50.864.0166.650.0111.1 ending cash balance$64.0$166.6$50.0$111.1$50.0 peter casper ibd australia copyright 2011 by training the street, inc.for training purposes only! working capital schedule for mccormick & co. inc.st

59、reet case (case 2) dollars in millions, except per share data historical year ending november 30,projected year ending november 30, 20082009201020112012201320142015 sales$3,176.6$3,192.1$3,336.8$3,540.3$3,756.3$3,985.4$4,228.6$4,486.5 cost of goods sold (excluding depreciation)1,804.21,774.91,822.51,894.11,990.82,092.42,198.82,310.5 working capital balances accounts receivable, net$380.7$365.3$386.7$407.4$432.2$458.6$486.6$516.3 inventories439.0457.6477.6498.2523.6550.3578.3607.7 other current assets109.7108.1100.8106.2112.7119.6126.9134.6 total non-cash current assets:$929.4$931.0$965.1$1,01