Week 5 Finc 351 and Finc 440
barkersbaseball
homework_9.15.14_dfcf_valuation_model_0714.xlsFree Cash Flow Valuation Model
| Name of Company | McCormick & Company (MKC) 2010 (02/05/11/2011) | ||||||||||||||||||||
| Estimate Basic Information & Growth Rates | |||||||||||||||||||||
| Initial Free Cash Flow | $337.00 | Computer Calculates | |||||||||||||||||||
| Initial Period Annual Growth Rate Estimate | 4.00% | See Note 1 | |||||||||||||||||||
| Terminal Annual Growth Rate Estimate | 2.00% | See Note 2 | |||||||||||||||||||
| How Long is Initial Growth Period (Years) | 10 | Years 0 Up to 20 | |||||||||||||||||||
| Number of Shares of Common Stock | 120.40 | ||||||||||||||||||||
| Tax Rate Estimated During Future Periods | 30.00% | ||||||||||||||||||||
| Total Interest-Bearing Permanent Debt Outstanding Estimated During Period | $1,000.00 | ||||||||||||||||||||
| Total Assets | $3,500.00 | ||||||||||||||||||||
| Estimate Free Cash Flow | |||||||||||||||||||||
| From Current Sales ($) Estimate Future Annual Sales ($) | $3,400.00 | ||||||||||||||||||||
| From EBIT Margin (%) Estimate Future EBIT Margin (%) | 15.00% | ||||||||||||||||||||
| EBIT | $510.00 | Computer Calculates | |||||||||||||||||||
| Plus - Future Annual Estimated Depreciation Expense | $100.00 | ||||||||||||||||||||
| Less - Future Annual Estimated Capital Expenditures | $100.00 | ||||||||||||||||||||
| Less - Net Annual Estimated Change (Invest.) in Working Capital | $20.00 | ||||||||||||||||||||
| Equals Operating Cash Flow | $490.00 | Computer Calculates | |||||||||||||||||||
| Less Taxes | $153.00 | Computer Calculates - See Note 3 | |||||||||||||||||||
| Equals Free Cash Flow | $337.00 | Computer Calculates | |||||||||||||||||||
| Estimate Weighted Average Cost of Capital | |||||||||||||||||||||
| Cost of Equity Capital | 8.600% | Computer Calculates | |||||||||||||||||||
| Before Tax Cost of Debt Estimated During Future Periods | 6.500% | ||||||||||||||||||||
| Market Risk Premium (RM-RF) Estimated During Future Periods | 6.000% | ||||||||||||||||||||
| Debt Ratio (Total Debt/Total Assets) Estimated During Future Periods | 28.571% | Computer Calculates | |||||||||||||||||||
| Beta Estimated During Future Periods | 0.600 | ||||||||||||||||||||
| Risk-free Rate (Use L-T Treasury Rate) Estimated During Future Periods | 5.000% | ||||||||||||||||||||
| = Weighted Average Cost of Capital | 7.443% | Computer Calculates | |||||||||||||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| Free Cash Flow | $0 | $350 | $364 | $379 | $394 | $410 | $426 | $443 | $461 | $480 | $499 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Terminal Value | $9,348 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||||||||||
| Total FCF | $0 | $350 | $364 | $379 | $394 | $410 | $426 | $443 | $461 | $480 | $9,847 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Discounted FCF | $0 | $326 | $316 | $306 | $296 | $286 | $277 | $268 | $260 | $251 | $4,803 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| Net Present Value of FCF | $7,390 | ||||||||||||||||||||
| Net Present Value of FCF Minus Debt | $6,390 | ||||||||||||||||||||
| Per Share Value | $53.07 | ||||||||||||||||||||
| Notes: | |||||||||||||||||||||
| 1. Terminal Value is growing perpetuity of final year's Free Cash Flow [(FCF) / (WACC - Growth Rate)] | |||||||||||||||||||||
| 2. If Number of years of projected growth is "0", then Net Present Value of Free Cash Flows is growing perpetuity of Free Cash Flow [(FCF*(1+Growth Rate) / (WACC - Growth Rate) | |||||||||||||||||||||
| 3. Taxes are determined by multiplying the Tax Rate times EBIT. |
Note: Terminal Growth Rate Must be less than WACC for projections of 1 or more years; for valuations based on 0 years of projections, WACC must be > Growth Rate
FREE CASH FLOW VALUATION MODEL
