Use an excel spreadsheet to calculate the profitability index, IRR and NPV for the following scenario: (show your work).
Chapter 8 Expansion at East Coast Yachts RWIJ text page: 260 Adapted for use by Robert Timmons, DBA Jan/2020) Input area: Data to use for the OUTPUT section below. Initial equipment cost Equipment in 1 year Year 1 depreciation Year 2 depreciation Year 3 depreciation Year 4 depreciation Year 5 depreciation 55,000,000 30,000,000 3.750% 7.219% 6.677% 6.177% 5.713% Year 3 27,000,000 $ Year 4 35,000,000 $ Year 5 39,000,000 $ Yoar 6 43,000,000 Year 2 18,000,000 $ 60% 2,500,000 Sales Variable cost Fixed cost NWC percentage of sales Terminal growth rate Tax rate Required return 8% 3% 40% 11.000% Output area: Enter your solutions here. Solutions MUST include calculation cell references for each solution. Entries without cell references (formulas) will be marked incorrect. Year 0 Year 1 Year 2 Year 3 Year 5 Depreciation Year O investment Year 1 investment Total depreciation Year o Year 1 Year 2 Year 3 Year 4 The preliminary analysis of the project is a sunk cost and should be Ignored. The cash flow to start the project is the SSS million and will occur immediately. The $30 million outlay will occur in one year. We need to be careful in depreciating the equipment. The $55 million expenditure will have depreciation in Year 1, while the depreciation for the Year 1 expenditure will not begin being depreciated until Year 2. Revenue Variable costs Fixed costs Depreciation EBT Tax Net income OCF Mate2 New working capital: Beginning Ending NWC cash flow Since the NWC is a percentage of sales, we need to determine the NWC cash flow each year. Calculating the beginning and ending NWC requirement for each year, we can find the NWC cash flows will be? Year 5 cash flow Value today of perpetual CF Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Operating cash flow Capital spending Networking capital Terminal value Total cash flows Note 3 Now we can calculate the DCF for each year. Since the cash flows will grow at 3 percent after five years, we only need to calculate the OCF for the next five years. Note We need to calculate the value of the perpetual cash flows. Discounted cash flow NPV The value of the perpetual cash flows beyond Year 5 at Year 5 is a growing perpetuity. So, the value of the cash flows in Year 5 will be? Profitability Index Chapter 8 Expansion at East Coast Yachts RWIJ text page: 260 Adapted for use by Robert Timmons, DBA Jan/2020) Input area: Data to use for the OUTPUT section below. Initial equipment cost Equipment in 1 year Year 1 depreciation Year 2 depreciation Year 3 depreciation Year 4 depreciation Year 5 depreciation 55,000,000 30,000,000 3.750% 7.219% 6.677% 6.177% 5.713% Year 3 27,000,000 $ Year 4 35,000,000 $ Year 5 39,000,000 $ Yoar 6 43,000,000 Year 2 18,000,000 $ 60% 2,500,000 Sales Variable cost Fixed cost NWC percentage of sales Terminal growth rate Tax rate Required return 8% 3% 40% 11.000% Output area: Enter your solutions here. Solutions MUST include calculation cell references for each solution. Entries without cell references (formulas) will be marked incorrect. Year 0 Year 1 Year 2 Year 3 Year 5 Depreciation Year O investment Year 1 investment Total depreciation Year o Year 1 Year 2 Year 3 Year 4 The preliminary analysis of the project is a sunk cost and should be Ignored. The cash flow to start the project is the SSS million and will occur immediately. The $30 million outlay will occur in one year. We need to be careful in depreciating the equipment. The $55 million expenditure will have depreciation in Year 1, while the depreciation for the Year 1 expenditure will not begin being depreciated until Year 2. Revenue Variable costs Fixed costs Depreciation EBT Tax Net income OCF Mate2 New working capital: Beginning Ending NWC cash flow Since the NWC is a percentage of sales, we need to determine the NWC cash flow each year. Calculating the beginning and ending NWC requirement for each year, we can find the NWC cash flows will be? Year 5 cash flow Value today of perpetual CF Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Operating cash flow Capital spending Networking capital Terminal value Total cash flows Note 3 Now we can calculate the DCF for each year. Since the cash flows will grow at 3 percent after five years, we only need to calculate the OCF for the next five years. Note We need to calculate the value of the perpetual cash flows. Discounted cash flow NPV The value of the perpetual cash flows beyond Year 5 at Year 5 is a growing perpetuity. So, the value of the cash flows in Year 5 will be? Profitability Index
