Question
Refer to the data sheet DCF. Complete the calculations in the yellow shaded areas Student instructions: Use the forecasting variables on the previous tab to
Refer to the data sheet DCF. Complete the calculations in the yellow shaded areas
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 WACC in previous worksheet, but reinvestment rate is 10%. Find NPV, IRR, MIRR, and Discounted Payback.
Studenthstrictions: Use the forecasting varlabes on the previous tab toomplete the discounted free cash To Decastandvallaton Show below. Entertom ulas In the bhiks where Indtatd to compet tie cabulabis needed. These tal cremental cash Tows won tl requ te an Initial $5,000,000 equipment hestent and care of $500,000 Netoperating Wokingcaptal Savage Value the end work te 1,000,000 (don't forget to add hi: In the end and make tax adjustment:). Discount rate is WACC 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 ANOWC = $500,000 Barking Dog Corporation Years Ending December 31 First Year - Forecast 2020 20 21 2022 Initial year outlay Capital Expenditure ($5,000,000) ANOWC ($ 500,000) 2023 10% Growth in Revenues per year $14,500,000 Total revenue Cost of Goods Sold 60% of revenues Gross profit Selling, general and administrative expenses constant each year Eamings before interest, taxes, depr. & amort. (EBITDA) 2,900,000 5,000,000 Depreciation and amortizaton 100% bonus irst year 0 all others Eamings before Interest and taxes (EBIT) 1.250,000 (3,750,000) (4,250,000) Federal and State Income Taxes on new capital EBIT*1-T) = Net Operating Profit After-Tax (NOPAT) **FCF 2 Year of Purchase Equipment Salvage Value in 2023, column Capital Gain Loss in column E Tax Rate 25% in column E Net salvage value, 2023 in column E 1,000,000 Add back depreciation and amortization Add back ANOWC 5,000,000 500.000 **FCF23 PV of Discounted Free Cash Flow Cumulative Discounted Free Cash Flow NPV IRRI MIRR @ Reinvestment Rate of 10% Discounted FCF Payback **FCF = EBIT (1-T) + Dep - (CapExpenditure + ANOWC) Studenthstrictions: Use the forecasting varlabes on the previous tab toomplete the discounted free cash To Decastandvallaton Show below. Entertom ulas In the bhiks where Indtatd to compet tie cabulabis needed. These tal cremental cash Tows won tl requ te an Initial $5,000,000 equipment hestent and care of $500,000 Netoperating Wokingcaptal Savage Value the end work te 1,000,000 (don't forget to add hi: In the end and make tax adjustment:). Discount rate is WACC 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 ANOWC = $500,000 Barking Dog Corporation Years Ending December 31 First Year - Forecast 2020 20 21 2022 Initial year outlay Capital Expenditure ($5,000,000) ANOWC ($ 500,000) 2023 10% Growth in Revenues per year $14,500,000 Total revenue Cost of Goods Sold 60% of revenues Gross profit Selling, general and administrative expenses constant each year Eamings before interest, taxes, depr. & amort. (EBITDA) 2,900,000 5,000,000 Depreciation and amortizaton 100% bonus irst year 0 all others Eamings before Interest and taxes (EBIT) 1.250,000 (3,750,000) (4,250,000) Federal and State Income Taxes on new capital EBIT*1-T) = Net Operating Profit After-Tax (NOPAT) **FCF 2 Year of Purchase Equipment Salvage Value in 2023, column Capital Gain Loss in column E Tax Rate 25% in column E Net salvage value, 2023 in column E 1,000,000 Add back depreciation and amortization Add back ANOWC 5,000,000 500.000 **FCF23 PV of Discounted Free Cash Flow Cumulative Discounted Free Cash Flow NPV IRRI MIRR @ Reinvestment Rate of 10% Discounted FCF Payback **FCF = EBIT (1-T) + Dep - (CapExpenditure + ANOWC)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started