Spreadsheet 3 You have been hired as a financial consultant by BUBBA corporation. BUBBA is considering investing in a new machine to produce dog biscuits. INVESTMENTS SCHOOR "A DOG BUCUIT COMPANY SOUNDS LIKE A REALLY WISE CHOICE to ME." BUBBA has provided you with the following information: Full price of machine is $150,000. There is no increase in net working capital. BUBBA has a 30% marginal tax rate. The machine falls into the MACRS 3-year class (33%, 45%, 15%, and 7% depreciation rates) BBA will use the machine for 6 years and then plans to sell it for $10,000 at the end of year 6. The machine is expected to increase earnings before depreciation by $40,000 a year for the life of the machine. BUBBA has a WACC of 12%. Create a MS-Excel spreadsheet to calculate the NPV similar to the Cash Flow Estimation spreadsheet (40 points). This sheet should include cash flows in years 4, 5 and 6. The spreadsheet will be used by the BUBBA managers to assist them in making the investment decision. The spreadsheet should be set up to allow for a sensitivity analysis to be conducted. The cells to input the full price, increased earnings, sale price in year 6, and WACC should be easily identified (e.g. yellow) and allow the BUBBA managers to change their values. Your sheet should be set up so that if the assumptions change your sheet updates appropriately. Your sheet should be set up so that if the assumptions change your sheet updates appropriately. NPVA L Full price of machine Increase in net working capital Marginal tax rate Sale price of machine in year 6 Incremental Revenues or Cost Savings WACC Depreciation year 1 Depreciation year 2 Depreciation year 3 Depreciation year 4 150,000.00 0.00 30.00% 10,000.00 40,000.00 12.00% 33.00% 45.00% 15.00% 7.00% 49,500.00 67,500.00 22,500.00 10,500.00 End of year BV 100,500.00 33.000.00 10,500.00 0.00 Year Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 0 -150,000.00 Full Price of Machine Increase in net working capital 0.00 Incremental Revenues or Cost Savings Depreciation Operating Profit before taxes Taxes Operating Profit after taxes Add back depreciation Operating Cash Flow 40,000.00 49,500.00 | -9,500.00 -2,850.00 -6,650.00 49,500.00 42,850.00 40,000.00 67,500.00 -27,500.00 -8,250.00 -19,250.00 67,500.00 48,250.00 40,000.00 22,500.00 17,500.00 5,250.00 12,250.00 22,500.00 34,750.00 Sale of Machine Taxes Net Salvage Return of net working capital Total Cash Flow -150,000.00 42.850.00 48.250.00 34,750.00 Spreadsheet 3 You have been hired as a financial consultant by BUBBA corporation. BUBBA is considering investing in a new machine to produce dog biscuits. INVESTMENTS SCHOOR "A DOG BUCUIT COMPANY SOUNDS LIKE A REALLY WISE CHOICE to ME." BUBBA has provided you with the following information: Full price of machine is $150,000. There is no increase in net working capital. BUBBA has a 30% marginal tax rate. The machine falls into the MACRS 3-year class (33%, 45%, 15%, and 7% depreciation rates) BBA will use the machine for 6 years and then plans to sell it for $10,000 at the end of year 6. The machine is expected to increase earnings before depreciation by $40,000 a year for the life of the machine. BUBBA has a WACC of 12%. Create a MS-Excel spreadsheet to calculate the NPV similar to the Cash Flow Estimation spreadsheet (40 points). This sheet should include cash flows in years 4, 5 and 6. The spreadsheet will be used by the BUBBA managers to assist them in making the investment decision. The spreadsheet should be set up to allow for a sensitivity analysis to be conducted. The cells to input the full price, increased earnings, sale price in year 6, and WACC should be easily identified (e.g. yellow) and allow the BUBBA managers to change their values. Your sheet should be set up so that if the assumptions change your sheet updates appropriately. Your sheet should be set up so that if the assumptions change your sheet updates appropriately. NPVA L Full price of machine Increase in net working capital Marginal tax rate Sale price of machine in year 6 Incremental Revenues or Cost Savings WACC Depreciation year 1 Depreciation year 2 Depreciation year 3 Depreciation year 4 150,000.00 0.00 30.00% 10,000.00 40,000.00 12.00% 33.00% 45.00% 15.00% 7.00% 49,500.00 67,500.00 22,500.00 10,500.00 End of year BV 100,500.00 33.000.00 10,500.00 0.00 Year Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 0 -150,000.00 Full Price of Machine Increase in net working capital 0.00 Incremental Revenues or Cost Savings Depreciation Operating Profit before taxes Taxes Operating Profit after taxes Add back depreciation Operating Cash Flow 40,000.00 49,500.00 | -9,500.00 -2,850.00 -6,650.00 49,500.00 42,850.00 40,000.00 67,500.00 -27,500.00 -8,250.00 -19,250.00 67,500.00 48,250.00 40,000.00 22,500.00 17,500.00 5,250.00 12,250.00 22,500.00 34,750.00 Sale of Machine Taxes Net Salvage Return of net working capital Total Cash Flow -150,000.00 42.850.00 48.250.00 34,750.00