3. 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 $48,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. You estimate that the new sound and video equipment will have zero salvage value so the full $48,000 will be depreciated. Finally, you assume a marginal tax rate of 25%. 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. Complete the NATCF table in the 'Q3a-e' worksheet. (15 pts.) (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. Complete the NPV table in the 'Q3a-c' worksheet. (10 pts.) (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). Enter your answers in the boxes provided in the 'Q3a-c' worksheet. (15 pts.) (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 8-year loan with a fixed interest rate of 4% (annual compounding and end-of-year payments) and a 50% down payment. Complete the final two columns of Table 1 below. Hint: 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 on page Complete the NATCF and NPV table in the 'Q3d-e' worksheet. (10 pts.) (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. Enter your answer in the box provided in the 'Q3d-e' worksheet. A B C D G . I M o P Q NATCF Table (part a) NPV Table (part b) Time Inflows Outflows Depreciation Taxable income Taxes NATCF PV at 5% PV at 10% PV at 15% PV at 20% 0 1 2 3 4 5 6 7 8 9 10 NPV IRR Enter your answer for partc here, and enter the IRR value in the box above: What can you say about the IRR based on the NPV values before you even calculate the exact IRR value? NATCF and NPV Table (part d) Time Inflows Outflows Depreciation Principal Interest Total Payment Taxable income Taxes NATCF PV at 10% 0 1 2 3 4) 5 6 7 8 9 10 NPV Enter your answer for parte here: Based on the NPV and a 10% discount rate, should you upgrade the sound system and, if so, should you pay cash or use a loan to finance the investment? Briefly explain why. 3. 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 $48,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. You estimate that the new sound and video equipment will have zero salvage value so the full $48,000 will be depreciated. Finally, you assume a marginal tax rate of 25%. 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. Complete the NATCF table in the 'Q3a-e' worksheet. (15 pts.) (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. Complete the NPV table in the 'Q3a-c' worksheet. (10 pts.) (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). Enter your answers in the boxes provided in the 'Q3a-c' worksheet. (15 pts.) (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 8-year loan with a fixed interest rate of 4% (annual compounding and end-of-year payments) and a 50% down payment. Complete the final two columns of Table 1 below. Hint: 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 on page Complete the NATCF and NPV table in the 'Q3d-e' worksheet. (10 pts.) (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. Enter your answer in the box provided in the 'Q3d-e' worksheet. A B C D G . I M o P Q NATCF Table (part a) NPV Table (part b) Time Inflows Outflows Depreciation Taxable income Taxes NATCF PV at 5% PV at 10% PV at 15% PV at 20% 0 1 2 3 4 5 6 7 8 9 10 NPV IRR Enter your answer for partc here, and enter the IRR value in the box above: What can you say about the IRR based on the NPV values before you even calculate the exact IRR value? NATCF and NPV Table (part d) Time Inflows Outflows Depreciation Principal Interest Total Payment Taxable income Taxes NATCF PV at 10% 0 1 2 3 4) 5 6 7 8 9 10 NPV Enter your answer for parte here: Based on the NPV and a 10% discount rate, should you upgrade the sound system and, if so, should you pay cash or use a loan to finance the investment? Briefly explain why
