Calculate the Net Present Value for each location

You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your team is considering expanding in 2019 by building one or more new retail outlets in the Detroit area. Five locations (A, B, C, D, and E) are being considered. Locations A, B, and C are ten year investments, with income projections shown in the "Location Data" tab. Locations D and E are eight year investments. Locations D and E are also very near each other, and so should be considered mutually exclusive (that is, Best Bean might invest in one or the other, but not both). Your manager has asked you to prepare a report on how best to use the capital budget of $6 million. This may include investing in one of the available locations, or it may not. If it is possible under the $6 million budget, you may recommend investing in more than one location. In an Excel file: Calculate the following for each location: Cash payback period Net present value Internal rate of return Profitability index Average rate of return Use a discount rate of 15% where necessary. Show all answers to two decimal places. Initial investment 3,000,000 Annual depreciation 300,000 2024 1,216,500 1,216,5001,216,500 1,216,500 1,216,5001,216,500 1,216,500 1216,5001,216,5001,216,500 950,000 266,500 950,000 266,500 950,000 266,500 00 266,500 950,000 266,500 Net income 266,500 266,500 266,500 266,500 266,500 Initial investment 3,000,000 400,000 260,000 Annual depreciation 2023 296,500 1,296,500 1,000,000 296,500 2024 1,296,500 1,000,000 296,500 1,296,500 1,000,000 296,500 1,296,500 1,000,000 296,500 1,296,500 1.296,500 1,000,000 296,500 1,296,500 1,296,500 1,000,000 296,500 1,296,500 1,000,000 296,500 Net income 296,500 296,500 296,500 Initial investment 4,500,000 Annual depreciation 450,000 2024 1,549,500 2026 1,549,500 2028 549,500,59,500 549,5001, 1,549,500 1,140,000 1,140,000,140,000 1,140,000 1,140,0001,140,000 1,140,000 1,140,0001,140,000 1,140,000 409,500 1,549,500 1,549,500 1,549,500 1,549,500 Net income 409,500 409,500 409,500 409,500 409,500 409,500 409,500 409,500 Initial investment 2,000,000 320,000 eciation 2024 2025 842,400 842,400 842,400 842,400 740,000 842,400 740,000 842,400 740,000 842,400 842,400 Net income Initial investment 2,000,000 250,000 2024 853,200 700,000 858,200 700,000 868,200 700,000 868,200 700,000 168,200 700,000 700,000 700,000 700,000 Net income *- Expense fiaures shown include deoreciation. With the exceotion of depreciation. all expenses are assumed paid in cash