"Student instructions: Use the forecasting variables on the previous tab to complete the discounted free cash flow forecast and valuation shown below. Enter formulas in the blanks where indicated to complete the calculations needed.

1) You have been hired by Drs. Dewey, Cheetham and Howe to help with NPV analysis for a replacement project. These three New Haven radiologists need to replace their existing, aging X-Ray equipment with new imaging equipment. They have calculated all the necessary figures but are unsure about how to account for the sale of their old machine. The original depreciation basis of the old machine is $200,000 and the accumulated depreciation follows the 5 year MACRS. They sold the old machine after the 4th year for $85,000 cash. Assume a tax rate for the company is 40 percent. a) What is the book value of the old X-Ray machine? $34,560 b) What is the taxable gain (loss) on the sale of the old equipment? $50,440 c) Calculate the tax on the gain (loss). $20,176 d) What is the net cash flow from the sale of the old equipment? Is this an inflow or an outflow? $64,824 e) Assume the new imaging equipment costs $400,000 and they do not expect a change in net working capital. Calculate the incremental cash flow for t0. f) Assume they could only sell the old equipment for $5,000. Recalculate parts b-d. g) Like risk investments have a return of 10%. What is the NPV in total if they sold the old machine for $5,000 and operated the new machine for 6 years, using 5 year MACRS depreciation, and had 6 years of $100,000 incremental cash flows with no change in net working capital, no salvage value and no additional expenses? The Discounted Free Cash Flow Model for Total Equity Barking Dog Corporation Forecasting Variables: First Year 2015 --50% 20% Revenue growth factor Expected gross profit margin S, G, & A expense % of revenue Depr. & Amort. % using MACRS 2016 10% 50% 20% 2017 2018 2019 15% 20% 25% 50% 50% 50% 20% 20% 20% MACRS 10 Year Depreciation 2020 25% 50% 20% 2021 20% 50% 20% 2022 15% 50% 20% 2023 10% 50% 20% 2024 5% 50% 20% Salvage value at the end of 2024 1,000,000 Income tax rate 40% Assumed long-term sustainable growth rate 4% per year after 2024 Capital Gains rate for Salvage value 20% Discount rate Use the most expensive WACC WACC facts: Barking Dog Corp Cost of Capital Given: Optimal Capital Structure: 20% Debt 10% Preferred Equity 70% Common Equity Net income for the coming year: $4,000,000 Use Retained Earnings for common equity until all but $1.5 M are exhausted. Dividends policy is to distribute 50% of NI as dividends. Current RE are 1,500,000. Borrowing Limits and Interest Rates: Amount Borrowed 0 to $1,500,000 over $1,5000,000 Interest Rate 6% 9% Use the average of CAPM and Dividends growth model for rRE Common Stock price: D1: g: Float %: Tax rate: For CAPM: rM rRF $56 $6 for the coming year 5% 10% of the market price 40% Preferred Stock price: DP0: $20 $2 for this year 8.5% 3.5% 1.05 a. Component costs of capital: After-tax cost of debt, AT rd (1) After-tax cost of debt, AT rd (2) per AT kd = BT kd(1-T) up to $1,500,000 borrowed per AT kd = BT kd(1-T) if over $1,500,000 borrowed Cost of Preferred Stock, rP Using DCF Cost of existing equity (RE), r RE per the dividend growth model Using CAPM Cost of existing equity (RE), r RE per the dividend growth model per CAPM Composite rRE Cost of new equity, rn per the dividend growth model b. MCC break points: Debt break point: = Equity Break point = based on 20% debt financing Net Income Dividends RE available c. MCC figures, using higher rates for equity and debt when past break point: WACC up to 1st break point: MCC between 1st & 2nd break points: MCC after 2nd break point: 4,000,000.00 based on 70% common equity financing Student instructions: Use the forecasting variables on the previous tab to complete the discounted free cash flow forecast and valuation shown below. Enter formulas in the blanks where indicated to complete the calculations needed. These initial incremental cash flows would require an initial $5,000,000 equipment investment and increase of $500,000 in Net Operating Working Capital. Salvage Value in the end would be $1,000,000 (don't forget to add this in the end and make tax adjustments). Discount rate is highest MCC in previous worksheet, but reinvestment rate is 10%. Find NPV, IRR, MIRR, and Discounted Payback. The Discounted Free Cash Flow Model for Total Equity Initial capital investment = $5,000,000 and NWC Investment = $500,000 Barking Dog Corporation First Year 2015 Total revenue Years Ending December 31 |------------------------------------------------------------------------------------ Forecast ----------------------------------------------------------------------------------| 2016 2017 2018 2019 2020 2021 2022 2023 2024 $14,500,000 $14,500,000 $14,500,000 $14,500,000 $14,500,000 $14,500,000 $14,500,000 $14,500,000 $14,500,000 $14,500,000 Cost of Goods Sold Gross profit 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 7,250,000 Selling, general and administrative expenses Earnings before interest, taxes, depr. & amort. (EBITDA) 2,900,000 4,350,000 Depreciation and amortization (MACRS) Earnings before Interest and taxes (EBIT) 1,000,000 3,350,000 Federal and State Income Taxes Net Operating Profit After-Tax (NOPAT) 1,172,500 2,177,500 Equipment Salvage Value Capital Gain/Loss Federal Capital Gain/Loss Tax Rate 20% Add back Net Working Capital Terminal value, 2024 1,000,000 Equipment Salvage Value in 2024, column K Capital Gain/Loss in column K Federal Capital Gain/Loss Tax Rate 20% in column K Terminal value, 2024 in column K Present Value of Free Cash Flows (K Expenditures informational) Initital Increase in Net Working Capital (informational) Add back depreciation and amortization Subtract sum of Capital Expenditures AND New Net Working Capital Free Cash Flow 2015 (See Ch 3-15 on PowerPoint) PV of Discounted Free Cash Flow Cumulative Discounted Free Cash Flow NPV IRR MIRR @ Reinvestment Rate of 10% Discounted FCF Payback (5,000,000) (500,000) 1,000,000 (5,000,000) (500,000) ($2,322,500) MACRS Depreciation Schedule