Hello there,

I need help with my project and homework assignments. Please see attached templates and work problems for more details.

Problem 9-4: Traditional WACC Valuation Given Solution Legend Debt beta Levered equity beta Market Risk Premium Risk free rate Borrowing rate (before tax) Number of common shares Tax rate 0.20 1.60 5% 7% 8% 2,000 30% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution 8.00% 0.2 a. What is Canton's cost of equity capital? What is the after-tax cost of debt for the firm? After-tax cost of debt 5.60% Levered cost of equity 15.00% b. Calculate the equity free cash flows for Canton for each of the next four years. Equity Free Cash Flow Calculation (Firm) Free Cash Flow Less: Interest (1 - Tax Rate) (Equity) Free Cash Flow $ Years 2 100,000.00 $ 22,000.00 (6,600.00) $ 15,400.00 8,000.00 $ 3 100,000.00 $ 22,000.00 (6,600.00) $ 15,400.00 8,000.00 $ 4 100,000.00 22,000.00 (6,600.00) 15,400.00 8,000.00 $ (8,000.00) 15,400.00 $ (8,000.00) 15,400.00 $ (8,000.00) 15,400.00 $ (8,000.00) 15,400.00 $ Sales Operating income (Earnings Before Interest and Taxes) Less: Cash tax payments Net operating profits after taxes (NOPAT) Plus: Depreciation expense Less: Investments in Net Working Capital in new Capital (CAPEX) Firm free cash flow (FCF) 1 100,000.00 $ 22,000.00 (6,600.00) $ 15,400.00 8,000.00 $ 1 15,400.00 (1,400.00) 14,000.00 $ 2 15,400.00 (1,400.00) 14,000.00 $ 3 15,400.00 (1,400.00) 14,000.00 $ 4 15,400.00 (1,400.00) 14,000.00 $ $6,624.25 $18,375.75 25,000.00 $ $ $39,969.70 53,363.64 93,333.33 118,333.33 $ $ Debt Valuation Present value of interest for Years 1-4 Present value of principal in Year 4 Debt Value Equity Valuation Present value of EFCFs for Years 1-4 Present value of EFCFs for Years 5 and beyond Equity Value Enterprise Value = Debt value + Equity value c. Using the market values of Canton's debt and equity and the after-tax cost of capital calaulate its WACC. Market Value Proportion After-Tax Cost Long-term Debt $ 25,000.00 0.2113 0.0560 Equity 93,333.33 0.7887 0.1500 WACC = Total $ 118,333.33 1.0000 Product 0.0118 0.1183 0.1301 d. What are the firm free cash flows for Canton for years 1-4? See the answer to part b above. e. Estimate the enterprise value of Canton using the Traditional WACC method. Present value of firm free cash flows: Planning horizon (4 years) Residual value In year 4 Today Enterprise Value = Value of invested capital f. What is the value per share of equity for Canton? Enterprise Value Less: Debt Equals: Shareholder value No. of shares (000) Value per share 25,000.00 2,000 Problem 9-6: Traditional WACC Valuation a. Computing Canton's unlevered cost of equity Risk free rate Market risk premium Levered equity beta Debt beta Unlevered beta Unlevered cost of equity To unlever the equity beta where the level of debt is fixed (as in this case) and debt is assumed to have a beta of zero, we solve the following equation: 7% 5% 1.60 0.20 #DIV/0! b. Unlevered FCFs for Years 1-4 Unlevered FCFs = Firm FCFs (Firm) Free Cash Flow 1 2 c. Interest Tax Savings = Interest Expense x Tax Rate d. Estimated APV Present value of Unlevered FCFs Present value of Interest Tax Savings Estimated APV e. Estimated per share value #DIV/0! - 3 4 APV (firm value) less: Debt Equals: Shareholder value No. of shares (000) Value per share $ (25,000.00) 2,000 Sales Cost of goods sold Gross profit Operating expenses (excluding depreciation) Depreciation expense Operating income (Earnings Before Interest and Taxes) Less: Interest expense Earnings before taxes Less: Taxes Net income $ $ $ $ $ 1 100,000.00 (40,000.00) 60,000.00 (30,000.00) (8,000.00) 22,000.00 (2,000.00) 20,000.00 (6,000.00) 14,000.00 Pro Forma Income Statements ($000) 2 3 $ 100,000.00 $ 100,000.00 (40,000.00) (40,000.00) $ 60,000.00 $ 60,000.00 (30,000.00) (30,000.00) (8,000.00) (8,000.00) $ 22,000.00 $ 22,000.00 (2,000.00) (2,000.00) $ 20,000.00 $ 20,000.00 (6,000.00) (6,000.00) $ 14,000.00 $ 14,000.00 $ $ $ $ $ Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 4 100,000.00 (40,000.00) 60,000.00 (30,000.00) (8,000.00) 22,000.00 (2,000.00) 20,000.00 (6,000.00) 14,000.00 Sales $ 0 Current Assets Property, Plant & Equipment Total Accruals & Payables Long-term debt Equity Total $ $ 15,000 40,000 55,000 5,000 25,000 25,000 55,000 Invested Capital $ 50,000 $ $ $ $ $ $ $ 1 100,000 $ Projected Sales Revenues 2 3 100,000 $ 100,000 $ 1 Actual 0 100,000 $ 2 15,000 40,000 55,000 5,000 25,000 25,000 55,000 $ $ $ $ 50,000 $ 4 100,000 $ Proforma Balance Sheets ($000) 3 4 15,000 $ 15,000 $ 15,000 40,000 40,000 40,000 55,000 $ 55,000 $ 55,000 5,000 $ 5,000 $ 5,000 25,000 25,000 25,000 25,000 25,000 25,000 55,000 $ 55,000 $ 55,000 50,000 $ 50,000 $ We use the equity account to make the balance sheet balance. The added equity is raised either through the retention of earnings or sale of new common shares. Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 5 100,000 5 $ $ 15,000 40,000 55,000 5,000 25,000 25,000 55,000 50,000 $ 50,000 $ $ PROBLEM 8-1 Given Sale price Square footage Selling price/sq ft Time on the market $ $ Comp #1 240,000.00 $ 2,240 107.14 $ 61 days Solution a. Average price per square foot Estimated Value b. c. Solution Legend Comp #2 265,000.00 2,145 123.54 32 days 2121 Tartar Circle 3,000 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 8-2 Solution Legend Current year EPS Growth rate in EPS for next year P/E Multiple (range) Estimated Value (current EPS) Estimated Value (forward EPS) $ 2.50 20% 10 15 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 8-3 THOUGHT QUESTION a. Parry Electronics is a regional electronics wholesaler and distributor which earned $1,250,000 in EBITDA this year based on revenues of $4,000,000. The enterprise values of publicly traded firms that operate in the same industry currently are valued at 5-6 times their current EBITDA. What is your estimate of the enterprise value of Parry Electronics? If Parry is small relative to the size of the comparison firms with assets only onetenth the size of the largest firm in the industry, how would this influence your valuation estimate? Explain. Answer: Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer re = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output THOUGHT QUESTION b. Suppose we have two companies, A and B which produce identical products using slightly different production processes. The process used by company A requires more capital equipment, which is already paid for, and can produce the the product at lower per unit costs. Now, assume that you have been asked to value Company B which is privately held and that you want to use Company A, which is publicly traded, as the basis for your valuation. Discuss how differences in the production processes of these firms affect both their multiples and discount rates. Relate your answer to the discussion of the valuation of the two office buildings discussed in the chapter. Answer: THOUGHT QUESTION c. In the tech sector, the price of an IPO is often stated as a multiple of its sales, which is then compared to the price/sales ratio of comparable firms. Why do you think that analysts use price/sales ratios in this setting rather than price/earnings ratios? Answer: Answer: The problem is that many of these firms do not have positive earnings, so sales is the best valuation metric that can be used. It should be noted that sales multiples are not particularly informative in most cases, and are only used when there are not good alternatives . PROBLEM 8-4 Given Solution Legend Per Square Foot A Building size (Sq. ft.) Rent Maintenance (fixed cost) Net Operating Income % Change in NOI Selling Price Information Sales multiple for NOI/sq. ft. Capitalization rate (1/Sales multiple) Estimated property value $ B 100 $ (23) 77 $ $ 120 $ (30) 90 $ A B ? ? ? 6 16.67% 462 $ Total Square Footage A B 80,000 90,000 8,000,000 $ 10,800,000 (1,840,000) (2,700,000) 6,160,000 $ 8,100,000 A $ 6 16.67% 36,960,000 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output B ? ? ? Solution a. Using the multiple of operating income, Building B can be valued at 6 x 8,100,000 = $48,600,000. b. Recognize that Building B has higher fixed costs (as a percentage of revenues) and therefore higher operating leverage. Per Square Foot Alternative Valuation Procedure Risk free rate Implied value of maintenance costs Implied revenue value Implied revenue multiple Implied revenue cap rate Property value/sq. ft. Implied multiple Implied cap rate A B 5.5% 5.5% Total Square Footage A B 5.5% 5.5% Building A % Change in Revenues Revenues Maintenance (fixed cost) Net Operating Income % Change in Revenues % Change in NOI Building B -20% 0% 20% -20% 0% 20% (1,840,000) (1,840,000) (1,840,000) (2,700,000) (2,700,000) (2,700,000) -20.00% 0.00% 20.00% -20.00% 0.00% 20.00% PROBLEM 8-5 Given Financial Information (Millions) Revenues EBITDA Net Income Earnings per Share Interest Bearing Debt Common Equity Total Assets $ O'Reilly Advance Auto Zone 2,120.00 $ 4,400.00 $ 5,890.00 321.86 544.38 1,130.00 171.62 240.16 562.44 1.507 2.183 7.301 120.00 560.00 1,720.00 1,145.77 939.51 641.16 1,713.90 2,615.73 4,401.85 Financial Ratios Debt to Equity Gross Margins Operating Margins Expected Growth in EPS (5 yrs) Market Valuations (Millions) Market Capitalization Enterprise Value 0.105 43.95% 12.47% 18.50% 2.683 49.09% 16.77% 13.00% 3,040.00 3,600.00 $ 6,290.00 8,010.00 6.61 13.30 11.21 1.79 7.09 11.56 10.21 1.25 12.47% 14.98% 9.42% 25.56% 16.77% 87.72% 8.10% 123.69% 149.59% 5.46% 168.21% 278.42% 9.55% 133.81% 686.55% 14.98% 25.56% 87.72% 3,240.00 $ 3,360.00 Operating Margin Return on Equity Profit Margin Asset Turnover Leverage Product Solution b. 0.596 47.25% 9.42% 16.00% 10.44 19.42 15.24 1.24 $ Valuation Ratios Enterprise Value/EBITDA P-E Ratio (Trailing) P-E Ratio (Forward) Beta a. Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 8-6 Given Cost of goods sold/Revenues Fixed operating costs Variable operating costs/Revenues Depreciation expense Salary adjustments Annual outsourcing savings/Revenues $ $ $ Solution Legend 65% 350,000 10% 50,000 100,000 10% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Historical Incomes Statements for Toys 'n Thing, Inc. 2014 Revenues $ 2,243,155 $ Cost of goods sold (1,458,051) Gross profits 785,104 General and Administrative Expenses* (574,316) Net Operating Income $ 210,789 $ 2013 2,001,501 $ (1,300,976) 700,525 (550,150) 150,375 $ 2012 2,115,002 (1,374,751) 740,251 (561,500) 178,751 *Includes depreciation expense of $50,000 per year. Solution a. Net Operating Income Plus: Depreciation expense EBITDA Valuation $ EBITDA Multiple 3 4 2012 178,751 50,000 228,751 2014 $ Years 2014 2013 210,789 $ 150,375 $ 50,000 50,000 260,789 $ 200,375 $ 2013 2012 2014 260,789 $ 100,000 2013 200,375 $ 100,000 2012 228,751 100,000 2014 2013 2012 Average b. EBITDA Plus: Salary adjustments Plus: Outsourcing savings Adjusted EBITDA Valuation Average Asking price = 5 x 2010 Unadjusted EBITDA Estimated value after adjustments $ EBITDA Multiple 3 4 PROBLEM 8-7 Given Cost of goods sold/Revenues Fixed operating costs Variable operating costs/Revenues Depreciation expense Salary adjustments Annual outsourcing savings/Revenues Solution Legend 55.00% 250,000 10.00% 50,000 - $ $ $ $ Solution a. Revenues Cost of goods sold Gross profits General and Administrative Expenses* Net Operating Income *Includes depreciation expense of $50,000 per year. $ Scenario 1 Scenario 2 Scenario 3 1,000,000 $ 2,000,000 $ 4,000,000 Percentage change b. Fixed operating costs Variable operating costs/Revenues Revenues Cost of goods sold Gross profits General and Administrative Expenses* Net Operating Income *Includes depreciation expense of $50,000 per year. $ $ 50,000 30% Scenario 1 Scenario 2 Scenario 3 1,000,000 $ 2,000,000 $ 4,000,000 Percentage change Case (a), which had higher fixed costs but lower variable costs, has a higher operating leverage. This effect transaltes into a higher degree of sensitivity to changes in sales. As can be seen above, in case (a), when sales jump fro $2 million to $4 million, NOI jumps up by 156%. In case (b), the same change in sales results in only a 22% increase in NOI. Higher opertaing leverages will result in higher betas (assuming sales changes are correlated to the market). = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 8-8 Given Levered equity beta Risk free rate (10 year US Treasury bond Market risk premium Estimated earnings for 2007 Dividend payout ratio Stock price (12/07/06) 5-year growth rate estimate 1.27 5.02% 5.00% $ $ 5.69 0.0264620393 dates 40.00% 86.01 10.00% Description Market Cap Sector: Industrial Goods Industry: Industrial Equipment & Components Emerson Electric Co. $34.61B Parker-Hannifin Corp. 9.81B Roper Industries Inc. 4.44B Pentair Inc. 3.23B Walter Industries Inc. 2.19B P/E 16.606 15.900 19.276 14.150 24.685 17.943 23.537 Given Long-term Debt to Price to Book Equity Value 0.87 50.471 0.649 10.11 0.494 4.257 0.308 2.298 0.603 3.122 0.485 1.974 4.036 2.731 Net Profit Price To Free Margin Cash Flow 5.40% 75.481 7.90% -134.900 9.54% 65.156 8.25% 34.392 11.89% 232.735 4.48% 147.667 7.38% -10.682 = Value given in problem Emerson Comparison to Industry = Formula/Calculation/Analysis required P/E ROE Dividend Yield LTD to Equity Price to Book Net Profit Margin Price to Cash Flow = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output b. Estimated cost of equity Estimated growth rate DCF Estimate of Share Price Impute growth rate Dividend Yield % 1.48% 1.41% 2.40% 1.20% 0.50% 1.70% 0.30% Solution Legend a. c. Imputed growth rate Return on Equity % 14.94% 18.40% 23.72% 18.16% 14.27% 11.56% 15.70% using projected dividends using historical dividend yields PROBLEM 8-9 Given Beta Dividend payout ratio EPS for 2007 Stock Price (12/07/06) Anticipated growth rate in EPS (5 years) Description Sector: Technology Industry: Semiconductor - Broad Line Intel Corp. Texas Instruments Inc. STMicroelectronics NV Advanced Micro Devices Inc. Analog Devices Inc. Maxim Integrated Products Inc. National Semiconductor Corp. 1.66 48% 1.13 20.88 12% $ $ = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Market Cap 5344.81B 252.89B 120.51B 44.62B 16.35B 11.79B 11.48B 10.28B 8.04B P/E Return on Equity Long-term Debt % Dividend Yield % to Equity 27.716 14.77% 1.90% 0.691 19.9 16.20% 1.30% 0.096 17.622 19.63% 1.90% 0.064 11.08 22.94% 0.50% 0.004 24.959 7.81% 0.70% 0.209 21.152 12.61% 0.00% 0.138 22.667 15.42% 1.90% NA 23.025 16.93% 1.90% NA 18.049 25.67% 0.60% 0.012 Solution Intel Comparison to Industry a. P/E ROE Dividend Yield LTD to Equity Price to Book Net Profit Margin Price to Cash Flow b. Estimated cost of equity Estimated growth rate DCF Estimate of Share Price c. Imputed growth rate d. Estimated future dividends Year 2007 $ 2008 2009 2010 2011 Future growth rate Value of Intel Shares (2-stage) 2007-2011 2011 and beyond Estimated equity value Solution Legend Earnings 1.13 Dividends The growth rate in earnings that makes the value of Intel's shares $20.88 is found using Goal Seek. Price to Book Value Net Profit Margin 5.588 10.39% 3.42 15.50% 3.437 18.72% 3.71 18.67% 1.764 8.24% 2.088 10.13% 3.342 21.48% 3.681 21.39% 4.481 22.18% Price To Free Cash Flow 55.435 193.3 121.039 -5577.55 -11.219 -58.916 311.392 NA 154.483 PROBLEM 8-10 Solution Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Given Exhibit P9-11.2 Solution Legend Various share prices Expected IPO share price Diluted shares outstanding (millions) Equity value (millions) Plus: net debt (millions) Enterprise value (millions) $ 2005E reserves 2006 EBITDAX 2007 EBITDAX Free Cash Flow (FCF) $ $ $ $ $ 20.00 $ 51.6 1,032.00 $ 740 $1,772 700 302 280 191 22.00 $ 51.6 1,135.20 $ 688 $1,824 24.00 $ 51.6 1,238.40 $ 637 $1,875 26.00 $ 51.6 1,341.60 $ 585 $1,927 28.00 $ 51.6 1,444.80 $ 534 $1,978 30.00 51.6 1,548.00 482 $2,030 = Value given in problem = Formula/Calculation/Analysis requ = Qualitative analysis or Short answ = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output million million million million Solution Part a. Expected IPO share price Enterprise Value/2005E reserves Enterprise Value/2006 EBITDAX Enterprise Value/2007 EBITDAX Enterprise Value/FCF Part b. Part c. Industry $ 20.00 $ 22.00 $ 24.00 $ 26.00 $ 28.00 $ 30.00 Mean Median Maximum Minimum 2.83 2.95 3.39 2.05 6.33 6.40 7.96 5.12 6.96 7.15 7.94 5.90 8.68 9.77 10.62 6.08 uired wer required PROBLEM 8-12 Given Solution Legend EXHIBIT 1 Financial Information 2003 Shares Outstanding 2003 Fiscal Close Stock Price Market Capitalization Short Term Debt Long Term Debt Cash & Equivalents Short Term Investments EBITDA Net Income Calculated EPS $ $ $ $ $ $ $ $ $ Earthlink ELNK 159,399,000 10.00 1,593,990,000 900,000 349,740,000 89,088,000 218,100,000 (62,200,000) (0.39) $ $ $ $ $ $ $ $ $ Yahoo eBay YHOO EBAY 655,602,000 $ 646,819,000 $ 45.03 $ 64.61 $ 29,521,758,060 $ 41,790,975,590 $ $ 2,800,000 $ 750,000,000 $ 124,500,000 $ 713,539,000 $ 1,381,513,000 $ 595,975,000 $ 340,576,000 $ 455,300,000 $ 818,200,000 $ 237,900,000 $ 441,800,000 $ 0.36 0.68 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Microsoft MSFT 10,800,000,000 25.64 276,912,000,000 6,438,000,000 42,610,000,000 14,656,000,000 9,993,000,000 0.93 Solution Price to Earnings Enterprise Value EBITDA multiple (25.63) $1,245,150,000 5.71 124.09 $29,558,219,060 64.92 94.59 $40,536,762,590 49.54 27.71 $270,474,000,000 18.45 Average 55.19 34.66 Part a. Google EBITDA Cash Debt Net income Shares EPS IPO proceeds $ $ $ $ $ $ 800,000,000 430,000,000 10,000,000 400,000,000 271,219,643 1.47 1,670,000,000 Earthlink Imputed IPO price per share from PE ratio Impute EV from EBITDA multiples Owner's equity Impute IPO price per share Part b. Part c. Yahoo eBay Microsoft Average PROBLEM 8-13 Given Exhibit P6-11.1 Income Statement and Balance Sheet values are in Thousands XTO Energy Chesapeake Energy Ticker PERIOD ENDING Income Statement ($000) Total Revenue Cost of revenue Gross Profit Operating Expenses Selling, general, and administrative Depreciation, depleletion, and amortization Others Operating income or loss Income from Continuing Operations Total other income/expenses (net) Earnings before interest and taxes Interest expense Income before tax Income tax expense Net income from continuing operations Nonrecurring Events Effect of accounting changes Net income Preferred stock and other adjustments Net income applicable to common shares Balance Sheet ($000) Assets Current Assets Cash and cash equivalents Short-term investments Net receivables Inventory Other current assets Total current assets Long-term investments Property, plant, and equipment Goodwill Other assets Deferred long-term asset charges Total assets Liabilities Current Liabilities Accounts payable Short/Current long-term debt Other current liabilities Total current liabilities Long-term debt Other liabilities Deferred long-term liability charges Total Liabilities Stockholders' Equity Preferred stock Common stock Retained earnings Treasury stock Capital surplus Other stockholders' equity Total stockholders' equity Total liabilities and stockholders' equity Other Financial Data Exploration expenses (thousands) Shares Outstanding (millions) Year-end 2004 Closing Price Market Capitalization (millions) Devon Energy Apache Burlington Resources XTO 31-Dec-04 CHK 31-Dec-04 DVN 31-Dec-04 APA 31-Dec-04 2,709,268 204,821 2,504,447 9,189,000 1,535,000 7,654,000 5,332,577 946,639 4,385,938 5,618,000 1,040,000 4,578,000 165,092 414,341 11,880 919,290 896,290 615,822 992,335 1,616,000 2,334,000 3,704,000 173,194 1,270,683 162,493 2,779,568 215,000 1,137,000 640,000 2,586,000 919,290 93,661 825,629 317,738 507,891 (20,081) 972,254 167,328 804,926 289,771 515,155 64,000 3,768,000 475,000 3,293,000 1,107,000 2,186,000 857 2,780,425 117,342 2,663,083 993,012 1,670,071 2,586,000 282,000 2,304,000 777,000 1,527,000 507,882 507,882 515,155 515,155 2,186,000 (10,000) 2,176,000 (1,317) 1,668,754 (5,680) 1,663,074 1,527,000 1,527,000 9,700 14,713 364,836 47,716 436,965 5,624,378 49,029 6,110,372 6,896 51,061 477,436 32,147 567,540 136,912 7,444,384 95,673 8,244,509 1,152,000 968,000 1,320,000 - 143,000 3,583,000 753,000 19,346,000 5,637,000 417,000 29,736,000 111,093 1,022,625 157,293 57,771 1,348,782 13,860,359 189,252 104,087 15,502,480 2,179,000 994,000 124,000 158,000 3,455,000 11,033,000 1,054,000 202,000 15,744,000 425,173 75,534 259 500,966 2,053,911 199,753 756,369 3,510,999 872,539 91,414 963,953 3,076,405 107,395 933,873 5,081,626 1,722,000 1,378,000 3,100,000 7,796,000 366,000 4,800,000 16,062,000 1,158,131 21,273 103,487 1,282,891 2,619,807 1,022,880 2,372,481 7,298,059 1,182,000 2,000 415,000 1,599,000 3,887,000 851,000 2,396,000 8,733,000 3,484 1,239,553 (24,917) 1,410,135 (28,882) 2,599,373 6,110,372 490,906 3,169 262,987 (22,091) 2,440,105 (12,193) 3,162,883 8,244,509 1,000 48,000 3,693,000 9,087,000 845,000 13,674,000 29,736,000 98,387 209,320 4,017,339 (97,325) 4,106,182 (129,482) 4,098,239 11,396,298 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output BR 31-Dec-04 1,947,601 436,998 1,510,603 Solution Legend 5,000 4,163,000 (2,208,000) 3,973,000 1,078,000 7,011,000 15,744,000 599,500 332.9 $ 35.38 $ $ 11,778.00 $ 184,300 279,000 2,300,000 258,000 253.2 482.0 327.5 392.0 16.50 $ 38.92 $ 50.57 $ 45.00 4,177.80 $ 18,759.44 $ 16,561.68 $ 17,640.00 This is not the year-end price for BR. It is an estimate based on the 2004 price range. Solution (All values in thousands) a. XTO Energy Chesapeake Energy Devon Energy Apache Average Multiple for Comps Enterprise Value (EV) EBITDA EBITDAX EV/EBITDA Multiple EV/EBITDAX Multiple P/E Multiple EV based on EBITDA for BR using comps Plus: Cash Less: Interest-bearing debt Equity value Equity value per share EV based on EBITDAX for BR using comps Plus: Cash Less: Interest-bearing debt Equity value Equity value per share Equity value per share based on P/E multiple XTO Interest-bearing debt (ST<) Enterprise Value Calculations CHK DVN APA BR Burlington Resources (BR) - Actual Burlington Resources (BR) - 2005 Forecast Common equity (price x shares outstanding) Less: Cash and equivalents Equals: Enterprise value b. c. PROBLEM 8-14: Mini-Case "Dick's Sporting Goods IPO" Given Shares outstanding Offering Price 10/16/02 $ Solution Legend 9.40 million 12.25 Exhibit P6-12.3 Dick's Sporting Goods Financial Data ($ millions) Revenues Gross Profit EBIT Depreciation & Amortization EBITDA Balance Sheet Data 8/3/02 Checks Drawn Current Portion of Long Term Debt Revolving Bank Line of Credit Long Term Debt & Capital Leases Total Debt Cash Stockholder's Equity = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required $ $ $ $ $ 1,173.794 298.453 55.899 13.499 69.398 $ $ $ $ $ $ $ 33.584 0.211 90.299 3.466 127.560 13.874 78.984 = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output This represents the balance of checks written that have not cleared the firm's bank account. Debt/Capitalization 62% Debt/EBITDA 1.84x Source: Dick's Sporting Goods Prospectus S-1 dated September 27, 2002 Solution a. Implied Enterprise Valuation for DKS Based on Market Comparables Average Comps DKS Statistic Implied EV Revenue Multiple 0.37x $ 1,173.794 $ 434.304 EBITDA Multiple 5.20x $ 69.398 $ 360.870 EBIT Multiple 8.30x $ 55.899 $ 463.962 b. Determine DKS' Implied Equity Value by subtracting Net Debt Revenue Multiple EBITDA Multiple EBIT Multiple Implied DKS Implied Equity Enterprise Value Net Debt Value $ 434.304 $ (113.686) $ 320.618 $ 360.870 $ (113.686) $ 247.184 $ 463.962 $ (113.686) $ 350.276 Implied IPO Value Per Share $ 34.11 $ 26.30 $ 37.26 Dick's IPO priced at $12.25. Its market multiples were valued at a significant discount to the comparables. Equity Market Capitalization based on 9.47 million shares DKS Net Debt DKS Enterprise Value at IPO actual price of $12.25 Implied Multiple based on IPO Price EV/Revenue Multiple EV/EBITDA Multiple EV/EBIT Multiple Analysis: Discount to Comps Given Info - Conoco Phillips Gas Acquisition Project ConocoPhillips's Cost of Capital for project Project life 15.00% 10 years Solution (a) Years 0 1,200,000 145,000 Project Investment Increase in Net Working Capital (NWC) Depreciation (MACRS-7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) 1 2 3 4 5 6 8 9 10 14.29% 6 900 365 3.00 0.65 $ (1,345,000) $ NPV IRR 24.49% 6 720 3.00 0.65 17.49% 6 576 ### 3.00 0.65 12.49% 6 461 3.00 0.65 8.93% 6 369 ### 3.00 0.65 8.93% 6 295 3.00 0.65 8.93% 6 236 3.00 0.65 4.45% 6 189 3.00 0.65 - - 6 151 ### 3.00 0.65 6 121 3.00 0.65 1,971,000 985,500 213,525 171,480 600,495 240,198 360,297 171,480 Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow 1,576,800 788,400 170,820 293,880 323,700 129,480 194,220 293,880 1,261,440 630,720 136,656 209,880 284,184 113,674 170,510 209,880 1,009,152 504,576 109,325 149,880 245,371 98,148 147,223 149,880 807,322 403,661 87,460 107,160 209,041 83,616 125,425 107,160 645,857 322,929 69,968 107,160 145,801 58,320 87,480 107,160 516,686 258,343 55,974 107,160 95,209 38,083 57,125 107,160 413,349 206,674 44,779 53,400 108,495 43,398 65,097 53,400 330,679 165,339 35,824 129,516 51,806 77,710 - 264,543 132,272 28,659 103,613 41,445 62,168 145,000 207,168 531,777 $ 488,100 $ 380,390 $ 297,103 $ 232,585 280,051 22.43% Recommendation: Chris should recommend that the project be undertaken because the NPV is positive, and the IRR is greater than the required rate of return. However, the recommendation is contingent on volume only declining by 20%/year, and wellhead price per MCF remaining fixed at $6.00. Solution (b) Current Values Best Case Most Likely Case Worst Case Changing Cells Nat Gas Price Production Rate 6 900 8 1,200 280,051 22.43% 3,209,250 #REF! 6 900 3 700 Result Cells NPV IRR #REF! #REF! (138,432) #REF! Breakeven Sensitivity Analsyis (c) Students should use Goal Seek in Excel to answer this question. a.) Breakeven nautral gas price for an NPV = 0 $ 4.98 b.) Breakeven natural gas volume in Year 1 for an NPV = 0 c.) Breakeven investment for an NPV = 0 7 704 $ 1,573,795 Recommendation (d) I would recommend based on the positive NPV and because the IRR of 22.43% is higher than the required return of 15%. Using goal seek, the price of gas and production levels are well above the requirements to break even. $ 194,640 $ 164,285 $ 118,497 $ 77,710 $ Project Investment Increase in Net Working Capital (NWC) Depreciation (MACRS-7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) 0 1,200,000 145,000 1 2 Solution (b - best case) Years 4 5 3 6 7 8 9 10 14.29% 8 1,200 365 4.00 0.65 24.49% 8 960 4.00 0.65 17.49% 8 768 4.00 0.65 12.49% 8 614 4.00 0.65 8.93% 8 492 4.00 0.65 8.93% 8 393 4.00 0.65 8.93% 8 315 4.00 0.65 4.45% ### 252 ### 4.00 0.65 - - 8 201 4.00 0.65 8 161 4.00 0.65 3,504,000 1,752,000 284,700 171,480 1,295,820 518,328 777,492 171,480 2,803,200 1,401,600 227,760 293,880 879,960 351,984 527,976 293,880 2,242,560 1,121,280 182,208 209,880 729,192 291,677 437,515 209,880 1,794,048 897,024 145,766 149,880 601,378 240,551 360,827 149,880 1,435,238 717,619 116,613 107,160 493,846 197,538 296,308 107,160 1,148,191 574,095 93,290 107,160 373,645 149,458 224,187 107,160 918,553 459,276 74,632 107,160 277,484 110,994 166,490 107,160 734,842 367,421 59,706 53,400 254,315 101,726 152,589 53,400 587,874 293,937 47,765 246,172 98,469 147,703 - 470,299 235,149 38,212 196,938 78,775 118,163 145,000 263,163 Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow $ (1,345,000) $ 948,972 $ 821,856 $ (1,345,000) $ 3,209,250 53.11% 825,193.04 $ 621,441.21 NPV IRR $ 647,395 $ $ 425,672.85 $ 510,707 $ 403,468 $ 291,998.13 $ 200,594.73 $ 331,347 $ 273,650 143,250.42 $ 102,875.30 $ 205,989 $ 67,338.19 $ 147,703 $ 41,986.48 $ $ 65,049.77 Project Investment Increase in Net Working Capital (NWC) Depreciation (MACRS-7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) 0 1,200,000 145,000 1 2 Solution (b - worst case) Years 4 5 3 6 7 8 9 10 14.29% 3 700 365 1.50 0.65 $ (1,345,000) NPV IRR $ (1,345,000) $ (138,432) -2.34% $ 17.49% 3 448 1.50 0.65 12.49% ### 358 ### 1.50 0.65 8.93% 3 287 1.50 0.65 8.93% 3 229 1.50 0.65 8.93% 3 184 1.50 0.65 4.45% 3 147 1.50 0.65 ### 117 ### 1.50 0.65 766,500 383,250 166,075 171,480 45,695 18,278 27,417 171,480 Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow 24.49% 3 560 1.50 0.65 613,200 306,600 132,860 293,880 (120,140) (48,056) (72,084) 293,880 490,560 245,280 106,288 209,880 (70,888) (28,355) (42,533) 209,880 392,448 196,224 85,030 149,880 (38,686) (15,475) (23,212) 149,880 313,958 156,979 68,024 107,160 (18,205) (7,282) (10,923) 107,160 251,167 125,583 54,419 107,160 (35,996) (14,398) (21,598) 107,160 200,933 100,467 43,536 107,160 (50,229) (20,092) (30,137) 107,160 160,747 80,373 34,828 53,400 (7,855) (3,142) (4,713) 53,400 128,597 64,299 27,863 36,436 14,574 21,862 - 198,897 $ 221,796 $ 167,347 172,953.91 $ 167,709.64 $ 110,033.50 $ 126,668 $ 72,422.93 $ 96,237 $ 47,846.76 $ 85,562 $ 36,990.96 $ 77,023 $ 28,955.68 $ 48,687 $ 21,862 $ 15,915.85 $ 6,214.42 3 94 1.50 0.65 $ 102,878 51,439 22,290 29,149 11,659 17,489 145,000 162,489 $ 40,164.86 Given Info - Conoco Phillips Gas Acquisition Project ConocoPhillips's Cost of Capital for project Project life 15.00% 10 years Solution Year 0 1,200,000 145,000 Project Investment Increase in Net Working Capital (NWC) Depreciation (MACRS-7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow NPV IRR 1 2 3 4 14.29% 8 1,200 365 4.00 0.65 (1,345,000) $ 17.49% 8 768 ### 4.00 0.65 12.49% 8 614 4.00 0.65 3,504,000 1,752,000 284,700 171,480 1,295,820 518,328 777,492 171,480 $ 24.49% 8 960 4.00 0.65 2,803,200 1,401,600 227,760 293,880 879,960 351,984 527,976 293,880 2,242,560 1,121,280 182,208 209,880 729,192 291,677 437,515 209,880 1,794,048 897,024 145,766 149,880 601,378 240,551 360,827 149,880 948,972 $ 821,856 $ 647,395 $ 510,707 1,440,400 53.11% Recommendation: Chris should recommend that the project be undertaken because the NPV is positive, and the IRR is greater than the required rate of However, the recommendation is contingent on volume only declining by 20%/year, and wellhead price per MCF remaining fixed at $6.00. Solution (b) Current Values Best Case Most Likely Case Worst Case Changing Cells Nat Gas Price Production Rate 8 1,200 8 1,200 1,440,400 53.11% 1,440,400 53.11% 6 900 3 700 Result Cells NPV IRR 280,051 22.43% (645,791) -2.34% Breakeven Sensitivity Analsyis (c) Students should use Goal Seek in Excel to answer this question. a.) Breakeven nautral gas price for an NPV = 0 $ 4.98 b.) Breakeven natural gas volume in Year 1 for an NPV = 0 c.) Breakeven investment for an NPV = 0 704 $ 1,573,795 Recommendation (d) I would recommend this project based on its positive NPV and its IRR of 22.43% is higher than the required return of 15%. Using goal seek, the price of gas and production levels are well above the requirements to break even. tion (a) ears 5 6 7 8 9 10 8.93% 8 492 4.00 0.65 8.93% 8 315 4.00 0.65 4.45% 8 252 4.00 0.65 - - 8 201 ### 4.00 0.65 8 161 4.00 0.65 1,435,238 717,619 116,613 107,160 493,846 197,538 296,308 107,160 $ 8.93% 8 393 4.00 0.65 1,148,191 574,095 93,290 107,160 373,645 149,458 224,187 107,160 918,553 459,276 74,632 107,160 277,484 110,994 166,490 107,160 734,842 367,421 59,706 53,400 254,315 101,726 152,589 53,400 587,874 293,937 47,765 246,172 98,469 147,703 - 273,650 $ 205,989 $ 147,703 $ 470,299 235,149 38,212 196,938 78,775 118,163 145,000 263,163 403,468 $ of return. 331,347 $ 0 1,200,000 145,000 Project Investment Increase in Net Working Capital (NWC) Depreciation (MACRS-7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow 1 2 Solution (b - best case) Years 4 5 3 6 7 8 9 10 14.29% 8 1,200 365 4.00 0.65 24.49% 8 960 4.00 0.65 17.49% 8 768 4.00 0.65 12.49% 8 614 4.00 0.65 8.93% 8 492 4.00 0.65 8.93% 8 393 4.00 0.65 8.93% 8 315 4.00 0.65 4.45% ### 252 ### 4.00 0.65 - - 8 201 4.00 0.65 8 161 4.00 0.65 3,504,000 1,752,000 284,700 171,480 1,295,820 518,328 777,492 171,480 2,803,200 1,401,600 227,760 293,880 879,960 351,984 527,976 293,880 2,242,560 1,121,280 182,208 209,880 729,192 291,677 437,515 209,880 1,794,048 897,024 145,766 149,880 601,378 240,551 360,827 149,880 1,435,238 717,619 116,613 107,160 493,846 197,538 296,308 107,160 1,148,191 574,095 93,290 107,160 373,645 149,458 224,187 107,160 918,553 459,276 74,632 107,160 277,484 110,994 166,490 107,160 734,842 367,421 59,706 53,400 254,315 101,726 152,589 53,400 587,874 293,937 47,765 246,172 98,469 147,703 - (1,345,000) $ 948,972 $ 821,856 $ 647,395 $ 510,707 $ 403,468 $ 331,347 $ 273,650 $ 205,989 $ 147,703 $ $ NPV IRR $ 470,299 235,149 38,212 196,938 78,775 118,163 145,000 263,163 (1,345,000) $ 3,209,250 53.11% 825,193.04 $ 621,441.21 $ 425,672.85 $ 291,998.13 $ 200,594.73 $ 143,250.42 $ 102,875.30 $ 67,338.19 $ 41,986.48 $ 65,049.77 $ 539,659.93 0 1,200,000 145,000 Project Investment Increase in Net Working Capital (NWC) Depreciation (MACRS-7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow 1 2 Solution (b - worst case) Years 4 5 3 7 8 9 10 14.29% 3 700 365 1.50 0.65 24.49% 3 560 1.50 0.65 17.49% 3 448 1.50 0.65 12.49% ### 358 ### 1.50 0.65 8.93% 3 287 1.50 0.65 8.93% ### 229 1.50 0.65 8.93% 3 184 1.50 0.65 4.45% ### 147 1.50 0.65 - - 3 117 ### 1.50 0.65 3 94 1.50 0.65 766,500 383,250 166,075 171,480 45,695 18,278 27,417 171,480 613,200 306,600 132,860 293,880 (120,140) (48,056) (72,084) 293,880 490,560 245,280 106,288 209,880 (70,888) (28,355) (42,533) 209,880 392,448 196,224 85,030 149,880 (38,686) (15,475) (23,212) 149,880 313,958 156,979 68,024 107,160 (18,205) (7,282) (10,923) 107,160 251,167 125,583 54,419 107,160 (35,996) (14,398) (21,598) 107,160 200,933 100,467 43,536 107,160 (50,229) (20,092) (30,137) 107,160 160,747 80,373 34,828 53,400 (7,855) (3,142) (4,713) 53,400 128,597 64,299 27,863 36,436 14,574 21,862 21,862 $ 102,878 51,439 22,290 29,149 11,659 17,489 145,000 162,489 15,915.85 $ 6,214.42 $ 40,164.86 $ (1,345,000) $ 198,897 $ 221,796 $ 167,347 $ 126,668 $ $ NPV IRR 6 (1,345,000) $ (138,432) -2.34% 172,953.91 $ 167,709.64 $ 110,033.50 $ 72,422.93 $ 96,237 $ 47,846.76 $ 85,562 $ 36,990.96 77,023 $ $ 28,955.68 $ 48,687 $ Scenario Summary Current Values: Best Case Scenario Created on 2/20/2012 Most Likely Scenario Created on 2/20/2012 Changing Cells: NGPrice 6 8 MCFDay 900 1200 Result Cells: NPV $280,051 $1,440,400 IRR 22.43% 53.11% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in Blue. Worst Case Scenario Created on 2/20/2012 6 900 3 700 $280,051 22.43% ($645,791) -2.34% PROBLEM 9-2 Solution Legend Given Discount rate Year 5 multiple Debt (0) Year 1 2 3 4 5 $ 15% 6.00 2,400,000 Cash flows $ 1,200,000 Solution a. Enterprise Value b. Equity Value = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output