AutoSave OFF na na... ActualFinalSp22 Q a go Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Arial 12 v ~ General Insert v LG FO 28- O O Conditional Formatting Format as Table Cell Styles AC 2. v Delete v Paste BI Av V $ % ) V Sensitivity .00 Sort & Filter Format Find & Select Analyze Data Create and Share Adobe PDF V A1 X fx A A C D E F G H 1 2 3 Student instructions: Use the forecasting variables 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 incremental cash flows would require an initial $9,000,000 equipment investment. Terminal Cash flow would be an inflow of $1,000,000 (don't forget to add this in the end AFTER TAXES). Discount rate is highest Marginal Cost of Capital from the previous tab, but reinvestment rate is 12%. Find NPV, IRR, MIRR, and Discounted Payback. 4 5 6 The Discounted Free Cash Flow Model for Total Equity Barking Dog Corporation 7 8 9 9 10 11 12 13 14 15 16 17 Forecasting Variables: 18 Initial Capital Invesment 9,000,000 Salvage (Terminal) value at the end of 2025 1,000,000 Income tax rate 30% Assumed long-term sustainable growth rate 5% Capital Gains Tax Rate 20% Discount rate Use the most expensive WACC Reinvestment rate 12% 19 20 21 Sales units growth factor Price growth Factor Expected Cost of Goods Sold (COGS) of Revenues S, G, & A expense % of revenues Depr. & Amort.% of Gross Capital Investment 2022 2023 2024 5% 10% 15% 7% 7% 7% 60% 50% 45% 10% 10% 10% MACRS 3 Year Depreciation (spread over 4 years) 33.33% 44.45% 14.81% 2025 20% 7% 40% 10% 7.41% 22 23 24 25 Actual Problem: 26 27 28 29 Initial Capital Invesment Salvage (Terminal) value at the end of 2025 Increase in inventory and AR (for A NOWC) Increase in accruals and AP (for A NOWC) Income tax rate Assumed long-term sustainable growth rate Discount rate 9,000,000 1,000,000 1,500,000 1,000,000 30% 6% 30 31 Use the most expensive WACC from previous tab 32 33 34 Discounted Free Cash Flow Model 35 36 Barking Dog Corporation Chapter 11 WACC Instructions Discounted FCF MACRS + Ready 3 110% AutoSave OFF na na... ActualFinalSp22 a go Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Arial 12 v General Insert v LG V TILL 15 | | Conditional Formatting Format as Table Cell Styles 28. o. O o 2. v Delete v Paste BI A V $ %) V V Sensitivity OS .00 Sort & Filter Format Find & Select Analyze Data Create and Share Adobe PDF V A1 . x fx A B B C C D E F G H 33 34 Discounted Free Cash Flow Model 35 Barking Dog Corporation 36 37 38 39 Operating Cash Flow Actual Base Yr 2021 Years Ending December 31 ---Forecast 2022 2023 2024 2025 40 41 42 Total unit sales Price Total Revenues 1,000,000 $6 $6,000,000 43 44 45 Cost of Goods Sold 46 Gross profit 47 48 Selling, general and administrative expenses 10% Revenues 49 Earnings before interest, taxes, depr. & amort. (EBITDA) 50 51 Depreciation and amortization usine MACRS 52 Earnings before Interest and taxes (EBIT) 53 No Interest payments now 54 Federal and State Income Taxes (30%) 55 Net Operating Profit After-Tax (NOPAT = EBIT - Tax) 56 Add Back Depreciation 57 (EBIT - Tax) + Depreciation (Free Cash flows for the years) 58 59 At termination of the project: 60 61 Sale of Fixed Asset, 2025 62 Capital Gain/Loss 63 Tax Liability (Capital Gains tax of 20%) 64 FCF from termination of project 65 Recover A NOWC 66 Annual Free Cash Flow from above 67 68 Discount rate from WACC sheet 69 NPV Instructions Chapter 11 WACC Discounted FCF Ready 1,000,000.00 8 & 9 SS SS SS MACRS + 3 110% AutoSave OFF na na... ActualFinalSp22 a go Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share O Comments Arial 12 v ~ General Insert v X LG ab FO Conditional Formatting Format as Table Cell Styles 28- O 2. v Delete v Paste BI A V $ % ) V Sensitivity V .00 Sort & Filter Format v Find & Select Analyze Data Create and Share Adobe PDF V A1 . x fx B B C C D E F G H 1,000,000.00 A 52 Earnings before interest and taxes (EBIT) 53 No Interest payments now 54 Federal and State Income Taxes (30%) 55 Net Operating Profit After-Tax (NOPAT = EBIT - Tax) 56 Add Back Depreciation 57 (EBIT - Tax) + Depreciation (Free Cash flows for the years) 58 59 At termination of the project: 60 61 Sale of Fixed Asset, 2025 62 Capital Gain/Loss 63 Tax Liability (Capital Gains tax of 20%) 64 FCF from termination of project 65 Recover A NOWC 66 Annual Free Cash Flow from above 67 68 Discount rate from WACC sheet 69 NPV 70 IRR 71 MIRR At Reinvestment Rate of 12% 72 Discounted Payback 73 74 Present Value of Free Cash Flows + ANOWC @ WACC 75 Net Cumulative Discounted Free Cash Flow 76 Discounted Payback 77 78 79 80 81 82 83 84 85 86 87 AR Instructions Chapter 11 WACC Discounted FCF MACRS + Ready 3 a TIUTO AutoSave OFF na na... ActualFinalSp22 Q a go Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Arial 12 v ~ General Insert v LG FO 28- O O Conditional Formatting Format as Table Cell Styles AC 2. v Delete v Paste BI Av V $ % ) V Sensitivity .00 Sort & Filter Format Find & Select Analyze Data Create and Share Adobe PDF V A1 X fx A A C D E F G H 1 2 3 Student instructions: Use the forecasting variables 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 incremental cash flows would require an initial $9,000,000 equipment investment. Terminal Cash flow would be an inflow of $1,000,000 (don't forget to add this in the end AFTER TAXES). Discount rate is highest Marginal Cost of Capital from the previous tab, but reinvestment rate is 12%. Find NPV, IRR, MIRR, and Discounted Payback. 4 5 6 The Discounted Free Cash Flow Model for Total Equity Barking Dog Corporation 7 8 9 9 10 11 12 13 14 15 16 17 Forecasting Variables: 18 Initial Capital Invesment 9,000,000 Salvage (Terminal) value at the end of 2025 1,000,000 Income tax rate 30% Assumed long-term sustainable growth rate 5% Capital Gains Tax Rate 20% Discount rate Use the most expensive WACC Reinvestment rate 12% 19 20 21 Sales units growth factor Price growth Factor Expected Cost of Goods Sold (COGS) of Revenues S, G, & A expense % of revenues Depr. & Amort.% of Gross Capital Investment 2022 2023 2024 5% 10% 15% 7% 7% 7% 60% 50% 45% 10% 10% 10% MACRS 3 Year Depreciation (spread over 4 years) 33.33% 44.45% 14.81% 2025 20% 7% 40% 10% 7.41% 22 23 24 25 Actual Problem: 26 27 28 29 Initial Capital Invesment Salvage (Terminal) value at the end of 2025 Increase in inventory and AR (for A NOWC) Increase in accruals and AP (for A NOWC) Income tax rate Assumed long-term sustainable growth rate Discount rate 9,000,000 1,000,000 1,500,000 1,000,000 30% 6% 30 31 Use the most expensive WACC from previous tab 32 33 34 Discounted Free Cash Flow Model 35 36 Barking Dog Corporation Chapter 11 WACC Instructions Discounted FCF MACRS + Ready 3 110% AutoSave OFF na na... ActualFinalSp22 a go Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Arial 12 v General Insert v LG V TILL 15 | | Conditional Formatting Format as Table Cell Styles 28. o. O o 2. v Delete v Paste BI A V $ %) V V Sensitivity OS .00 Sort & Filter Format Find & Select Analyze Data Create and Share Adobe PDF V A1 . x fx A B B C C D E F G H 33 34 Discounted Free Cash Flow Model 35 Barking Dog Corporation 36 37 38 39 Operating Cash Flow Actual Base Yr 2021 Years Ending December 31 ---Forecast 2022 2023 2024 2025 40 41 42 Total unit sales Price Total Revenues 1,000,000 $6 $6,000,000 43 44 45 Cost of Goods Sold 46 Gross profit 47 48 Selling, general and administrative expenses 10% Revenues 49 Earnings before interest, taxes, depr. & amort. (EBITDA) 50 51 Depreciation and amortization usine MACRS 52 Earnings before Interest and taxes (EBIT) 53 No Interest payments now 54 Federal and State Income Taxes (30%) 55 Net Operating Profit After-Tax (NOPAT = EBIT - Tax) 56 Add Back Depreciation 57 (EBIT - Tax) + Depreciation (Free Cash flows for the years) 58 59 At termination of the project: 60 61 Sale of Fixed Asset, 2025 62 Capital Gain/Loss 63 Tax Liability (Capital Gains tax of 20%) 64 FCF from termination of project 65 Recover A NOWC 66 Annual Free Cash Flow from above 67 68 Discount rate from WACC sheet 69 NPV Instructions Chapter 11 WACC Discounted FCF Ready 1,000,000.00 8 & 9 SS SS SS MACRS + 3 110% AutoSave OFF na na... ActualFinalSp22 a go Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share O Comments Arial 12 v ~ General Insert v X LG ab FO Conditional Formatting Format as Table Cell Styles 28- O 2. v Delete v Paste BI A V $ % ) V Sensitivity V .00 Sort & Filter Format v Find & Select Analyze Data Create and Share Adobe PDF V A1 . x fx B B C C D E F G H 1,000,000.00 A 52 Earnings before interest and taxes (EBIT) 53 No Interest payments now 54 Federal and State Income Taxes (30%) 55 Net Operating Profit After-Tax (NOPAT = EBIT - Tax) 56 Add Back Depreciation 57 (EBIT - Tax) + Depreciation (Free Cash flows for the years) 58 59 At termination of the project: 60 61 Sale of Fixed Asset, 2025 62 Capital Gain/Loss 63 Tax Liability (Capital Gains tax of 20%) 64 FCF from termination of project 65 Recover A NOWC 66 Annual Free Cash Flow from above 67 68 Discount rate from WACC sheet 69 NPV 70 IRR 71 MIRR At Reinvestment Rate of 12% 72 Discounted Payback 73 74 Present Value of Free Cash Flows + ANOWC @ WACC 75 Net Cumulative Discounted Free Cash Flow 76 Discounted Payback 77 78 79 80 81 82 83 84 85 86 87 AR Instructions Chapter 11 WACC Discounted FCF MACRS + Ready 3 a TIUTO
