As owner of the Illini Tap you are considering an investment in upgrading the bar's entertainment systems. If you decide to invest in these upgrades you have estimated it will require an initial investment of $45,000 which will be used to buy some TVs, a digital jukebox, and a new stereo system for the bar. You estimate that this will boost revenues by $15,000 during the first year because of the additional customers this will attract to the bar. These additional revenues are expected to increase by 3% each year. However, the upgrade will also create additional costs of $6,000 in the first year due to additional satellite service and music and sports package fees. These additional costs are also expected to increase over time, but only by 2% each year. Your accountant says you can depreciate the initial investment using the straight-line method over 6 years, and your marginal tax rate is 25%. You estimate that the new sound and video equipment will have zero salvage value so the full $45,000 will be depreciated. Using the information above, answer the following questions assuming a 10-year planning horizon. Provide complete answers in your homework file, including all values in suggested tables to show how you did the calculations. (20 pts.) (a) Calculate the annual net after tax cash flows for this investment during the planning horizon. Hint: The table below would be a good way to lay out the individual cash flows and do the necessary calculations to get the NATCF values in Excel. You could then copy and paste the table and values into a word document. a (c) Based on your calculations for part (b), what can you say about the Internal Rate of Return for this investment? Calculate the IRR for this investment to confirm (your calculator may have an IRR function; you can also use trial-and-error or the Solver in Excel, or Excel's IRR function). (d) Calculate the series of NATCFs and the NPV for this project at a 10% discount rate assuming that you finance the investment using a 6-year loan with a fixed interest rate of 6% (annual compounding and end-of-year payments) and a 50% down payment. This will require you to adjust the NATCF calculations that you made for part a. You will need to account for the loan when calculating the NATCF series. You can assume the bar is able to deduct the interest portion of the loan payments as an expense for income tax purposes. This should be done using a similar table as you created for part a, adding columns for the loan payment information. Clearly label your tables to help with grading. An example layout for the NATCF table with the loan is provided below. a (e) Finally, using NPV as your decision criteria and a 10% discount rate, should you upgrade the sound system in the bar and, if so, should you pay cash or use a loan to finance the investment? Very briefly justify/explain your answer. Suggested table layout for question 3, parts d and e. Principal Portion of Year Inflows Outflows Depreciation Loan Payment 0 Interest Portion of Loan Payment Total Loan Payment (Principal + Interest) Taxable Income Taxes NATCF PV @ 10% 1 2 3 4 5 6 7 8 9 10 Total (b) Calculate the NPV for this investment using 5%, 10%, 15%, and 20% discount rates. Provide the present values of the NATCF's and the NPV for each discount rate. Use the table layout below as a guide. Suggested table layout for question 3, part b. NATCF PV PV Year (No loan) @ 5% @ 10% 0 PV @ 15% PV @ 20% 1 2 3 4 5 6 7 8 9 10 Total As owner of the Illini Tap you are considering an investment in upgrading the bar's entertainment systems. If you decide to invest in these upgrades you have estimated it will require an initial investment of $45,000 which will be used to buy some TVs, a digital jukebox, and a new stereo system for the bar. You estimate that this will boost revenues by $15,000 during the first year because of the additional customers this will attract to the bar. These additional revenues are expected to increase by 3% each year. However, the upgrade will also create additional costs of $6,000 in the first year due to additional satellite service and music and sports package fees. These additional costs are also expected to increase over time, but only by 2% each year. Your accountant says you can depreciate the initial investment using the straight-line method over 6 years, and your marginal tax rate is 25%. You estimate that the new sound and video equipment will have zero salvage value so the full $45,000 will be depreciated. Using the information above, answer the following questions assuming a 10-year planning horizon. Provide complete answers in your homework file, including all values in suggested tables to show how you did the calculations. (20 pts.) (a) Calculate the annual net after tax cash flows for this investment during the planning horizon. Hint: The table below would be a good way to lay out the individual cash flows and do the necessary calculations to get the NATCF values in Excel. You could then copy and paste the table and values into a word document. a (c) Based on your calculations for part (b), what can you say about the Internal Rate of Return for this investment? Calculate the IRR for this investment to confirm (your calculator may have an IRR function; you can also use trial-and-error or the Solver in Excel, or Excel's IRR function). (d) Calculate the series of NATCFs and the NPV for this project at a 10% discount rate assuming that you finance the investment using a 6-year loan with a fixed interest rate of 6% (annual compounding and end-of-year payments) and a 50% down payment. This will require you to adjust the NATCF calculations that you made for part a. You will need to account for the loan when calculating the NATCF series. You can assume the bar is able to deduct the interest portion of the loan payments as an expense for income tax purposes. This should be done using a similar table as you created for part a, adding columns for the loan payment information. Clearly label your tables to help with grading. An example layout for the NATCF table with the loan is provided below. a (e) Finally, using NPV as your decision criteria and a 10% discount rate, should you upgrade the sound system in the bar and, if so, should you pay cash or use a loan to finance the investment? Very briefly justify/explain your answer. Suggested table layout for question 3, parts d and e. Principal Portion of Year Inflows Outflows Depreciation Loan Payment 0 Interest Portion of Loan Payment Total Loan Payment (Principal + Interest) Taxable Income Taxes NATCF PV @ 10% 1 2 3 4 5 6 7 8 9 10 Total (b) Calculate the NPV for this investment using 5%, 10%, 15%, and 20% discount rates. Provide the present values of the NATCF's and the NPV for each discount rate. Use the table layout below as a guide. Suggested table layout for question 3, part b. NATCF PV PV Year (No loan) @ 5% @ 10% 0 PV @ 15% PV @ 20% 1 2 3 4 5 6 7 8 9 10 Total