*PLEASE HELP* This question includes how to solve for the cost of debt, cost of equity, debt/value, unlevered beta, and WACC on excel.
I need help plugging the formulas into the excel. If you could help with that, that would be great. You can either tell me the formulas or open the spreadsheet here and post the answer below-> http://bit.ly/chestnutfoods
TAX RATE = 37%
CHESTNUT FOODS Cost of Capital Estimation Based on WACC of Industry Comparables (all data as of the end of 2013; all dollar figures in millions) Cost of Cost of Debt equity Kd | Ke S&P Equity Bond Beta | Rating Total Equity (Book Total Debt| Value) Total Equity (Market Value) Debt/Value WACC 0.55 958 B+ BBB+ 0.00% 0.60 298 4,832 9,590 593 13,223 13,805 0.70 BBB- 0.75 B- 578 Food Products Boulder Brands Campbell Soup ConAgra Foods Diamond Foods Flowers Foods General Mills Hormel Foods Kellogg J. M. Smucker Tyson Foods Average 0.00% 2.78% 2.93% 3.18% 3.25% 923 0.50 0.55 0.65 0.60 BBB- BBB+ A BBB+ 355 1,349 5,472 167 1,076 6,633 3,311 3,545 5,168 6,285 4,429 31,245 11,759 21,841 10,904 11,469 8,645 250 7,358 2,241 1,942 3.47% 0.70 BBB+ 3.84% 4.11% 0.80 BBB 1.06 BBB-* 197 Instruments Badger Meter Dresser-Rand Flowserve Honeywell | 1.40 BB 89 1,287 1,200 8,829 723 4,549 10,767 74,330 1.30 1,297 1,870 17,467 BBB- 1.25 A Equity Beta Total Equity (Market Value) Unlevered Total Debt Debt / Value Chestnut Foods 10.90 461 1 1,840 Food Processing Boulder Brands Campbell Soup ConAgra Foods Diamond Foods Flowers Foods General Mills Hormel Foods Kellogg J. M. Smucker Tyson Foods 0.55 298 958 0.60 4,832 13,223 0.70 9,590 13,805 0.75 593 578 0.50 923 4,429 0.55 8,645 31,245 0.65 250 11,759 0.60 7,358 21,841 0.70 L2,241 10,904 0.80 1,942 11,469 Food Processing Average Instruments Badger Meter Dresser-Rand Flowserve Honeywell Idex Measurement Specialties Mettler-Toledo Wendell Instruments 1. 06 8 9 723 1.40 1,287 4,549 1.30 1,200 10,767 1.25 8,829 74,330 1.15 774 5,933 1.35 129 944 1.10 413 7,154 0.5210 230 Instruments Average (w/o Wendell) WACC Estimation Food Products Instruments 0.37 0.37 Avg debt/value Tax rate Implied debt/equity Estimated credit rating Implied cost of debt Risk-free rate Market risk premium Unlevered equity beta Levered equity beta Implied cost of equity WACC CHESTNUT FOODS Capital Market Data, December 2013 30-Day Treasury Bill 10-Year Treasury Bond Yield 0.1% 2.8% A+ 10-Year Corporate Bonds of Industrial Companies AAA 2.8% 2.9% 3.2% 3.3% A- 3.5% BBB+ 3.8% 4.1% BBB- 4.6% BB+ 5.8% 6.5% BB- 6.5% B+ 6.8% 8.4% 9.0% BB B- Historical Market Risk Premium Equity Market Index Less Government De 6.0% Exhibit 4 CHESTNUT FOODS Financial Data for Industry Comparables, December 2013 (dollar figures in millions) S&P Bond Equity Beta Rating Total Total Equity Total Equity Debt (Book Value) (Market Value) Chestnut Foods 0.90 A- 461 1,544 1,840 0.55 0.60 355 1,349 5,472 Food Processing Boulder Brands Campbell Soup ConAgra Foods Diamond Foods Flowers Foods General Mills Hormel Foods Kellogg J. M. Smucker Tyson Foods 167 B+ BBB+ BBB- B- BBB- BBB+ oooooo 298 4,832 9,590 593 923 8,645 250 7,358 2,241 1,942 958 13,223 13,805 578 4,429 31,245 11,759 21,841 10,904 11,469 1,076 6,633 3,311 3,545 5,168 6,285 0.60 0.70 0.80 BBB+ BBB+ BBB 1.06 BBB-* BB BBB- 723 4,549 1.40 Instruments Badger Meter Dresser-Rand Flowserve Honeywell Idex Measurement Specialties Mettler-Toledo Wendell Instruments 89 1,287 1,200 8,829 197 1,297 1,870 17,467 1,573 331 10,767 BBB BBB* 774 1.15 1.35 1.10 0.52 129 74,330 5,933 944 7,154 230 A* 935 NA * Identifies bond ratings that are estimated by case writer. CHESTNUT FOODS Cost of Capital Estimation Based on WACC of Industry Comparables (all data as of the end of 2013; all dollar figures in millions) Cost of Cost of Debt equity Kd | Ke S&P Equity Bond Beta | Rating Total Equity (Book Total Debt| Value) Total Equity (Market Value) Debt/Value WACC 0.55 958 B+ BBB+ 0.00% 0.60 298 4,832 9,590 593 13,223 13,805 0.70 BBB- 0.75 B- 578 Food Products Boulder Brands Campbell Soup ConAgra Foods Diamond Foods Flowers Foods General Mills Hormel Foods Kellogg J. M. Smucker Tyson Foods Average 0.00% 2.78% 2.93% 3.18% 3.25% 923 0.50 0.55 0.65 0.60 BBB- BBB+ A BBB+ 355 1,349 5,472 167 1,076 6,633 3,311 3,545 5,168 6,285 4,429 31,245 11,759 21,841 10,904 11,469 8,645 250 7,358 2,241 1,942 3.47% 0.70 BBB+ 3.84% 4.11% 0.80 BBB 1.06 BBB-* 197 Instruments Badger Meter Dresser-Rand Flowserve Honeywell | 1.40 BB 89 1,287 1,200 8,829 723 4,549 10,767 74,330 1.30 1,297 1,870 17,467 BBB- 1.25 A Equity Beta Total Equity (Market Value) Unlevered Total Debt Debt / Value Chestnut Foods 10.90 461 1 1,840 Food Processing Boulder Brands Campbell Soup ConAgra Foods Diamond Foods Flowers Foods General Mills Hormel Foods Kellogg J. M. Smucker Tyson Foods 0.55 298 958 0.60 4,832 13,223 0.70 9,590 13,805 0.75 593 578 0.50 923 4,429 0.55 8,645 31,245 0.65 250 11,759 0.60 7,358 21,841 0.70 L2,241 10,904 0.80 1,942 11,469 Food Processing Average Instruments Badger Meter Dresser-Rand Flowserve Honeywell Idex Measurement Specialties Mettler-Toledo Wendell Instruments 1. 06 8 9 723 1.40 1,287 4,549 1.30 1,200 10,767 1.25 8,829 74,330 1.15 774 5,933 1.35 129 944 1.10 413 7,154 0.5210 230 Instruments Average (w/o Wendell) WACC Estimation Food Products Instruments 0.37 0.37 Avg debt/value Tax rate Implied debt/equity Estimated credit rating Implied cost of debt Risk-free rate Market risk premium Unlevered equity beta Levered equity beta Implied cost of equity WACC CHESTNUT FOODS Capital Market Data, December 2013 30-Day Treasury Bill 10-Year Treasury Bond Yield 0.1% 2.8% A+ 10-Year Corporate Bonds of Industrial Companies AAA 2.8% 2.9% 3.2% 3.3% A- 3.5% BBB+ 3.8% 4.1% BBB- 4.6% BB+ 5.8% 6.5% BB- 6.5% B+ 6.8% 8.4% 9.0% BB B- Historical Market Risk Premium Equity Market Index Less Government De 6.0% Exhibit 4 CHESTNUT FOODS Financial Data for Industry Comparables, December 2013 (dollar figures in millions) S&P Bond Equity Beta Rating Total Total Equity Total Equity Debt (Book Value) (Market Value) Chestnut Foods 0.90 A- 461 1,544 1,840 0.55 0.60 355 1,349 5,472 Food Processing Boulder Brands Campbell Soup ConAgra Foods Diamond Foods Flowers Foods General Mills Hormel Foods Kellogg J. M. Smucker Tyson Foods 167 B+ BBB+ BBB- B- BBB- BBB+ oooooo 298 4,832 9,590 593 923 8,645 250 7,358 2,241 1,942 958 13,223 13,805 578 4,429 31,245 11,759 21,841 10,904 11,469 1,076 6,633 3,311 3,545 5,168 6,285 0.60 0.70 0.80 BBB+ BBB+ BBB 1.06 BBB-* BB BBB- 723 4,549 1.40 Instruments Badger Meter Dresser-Rand Flowserve Honeywell Idex Measurement Specialties Mettler-Toledo Wendell Instruments 89 1,287 1,200 8,829 197 1,297 1,870 17,467 1,573 331 10,767 BBB BBB* 774 1.15 1.35 1.10 0.52 129 74,330 5,933 944 7,154 230 A* 935 NA * Identifies bond ratings that are estimated by case writer
