Question: 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
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 | ||||||||||
| Years Ending December 31 | ||||||||||
| First Year | |------------------------------------------------------------------------------------ Forecast ----------------------------------------------------------------------------------| | |||||||||
| 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | |
| Total revenue | $14,500,000 | |||||||||
| Cost of Goods Sold | 7,250,000 | |||||||||
| Gross profit | 7,250,000 | |||||||||
| Selling, general and administrative expenses | 2,900,000 | |||||||||
| Earnings before interest, taxes, depr. & amort. (EBITDA) | 4,350,000 | |||||||||
| Depreciation and amortization (MACRS) | 1,000,000 | |||||||||
| Earnings before Interest and taxes (EBIT) | 3,350,000 | |||||||||
| Federal and State Income Taxes | 1,172,500 | |||||||||
| Net Operating Profit After-Tax (NOPAT) | 2,177,500 | |||||||||
| Equipment Salvage Value in 2024, column K | Equipment Salvage Value | 1,000,000 | ||||||||
| Capital Gain/Loss in column K | Capital Gain/Loss | |||||||||
| Federal Capital Gain/Loss Tax Rate 20% in column K | Federal Capital Gain/Loss Tax Rate 20% | |||||||||
| Terminal value, 2024 in column K | Add back Net Working Capital | |||||||||
| Terminal value, 2024 | ||||||||||
| Present Value of Free Cash Flows (K Expenditures informational) | (5,000,000) | |||||||||
| Initital Increase in Net Working Capital (informational) | (500,000) | |||||||||
| Add back depreciation and amortization | 1,000,000 | |||||||||
| Subtract sum of Capital Expenditures AND | (5,000,000) | |||||||||
| New Net Working Capital | (500,000) | |||||||||
| Free Cash Flow 2015 (See Ch 3-15 on PowerPoint) | ($2,322,500) | |||||||||
| PV of Discounted Free Cash Flow | ||||||||||
| Cumulative Discounted Free Cash Flow | ||||||||||
| NPV | ||||||||||
| IRR | ||||||||||
| MIRR @ Reinvestment Rate of 10% | ||||||||||
| Discounted FCF Payback | ||||||||||
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
Study Help