You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your team is considering expanding in 2020 by building one or more new retail outlets in the Detroit area. Four locations (A, B, C, and D) are being considered. Locations A, B, and Care ten year investments, with income projections shown in the "Location Data" tab. Location D is an eight year investment. Locations C and D are also very near each other in Allen Park, 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 $3.55 million. This may include investing in one of the available locations, or it may not. If it is possible under the $3,550,000 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 16% (Best Bean's minimum rate of return) where necessary. Show all answers to two decimal places (dollars and percentages). Note some helpful formulas in the "Excel TVM formulas" tab. 2,086,000 Location A Initial investment Residual value Annual depreciation 208,600 2021 2022 Projected income: Revenues Expenses * Net income 2020 790,800 569,200 221,600 790,800 569,200 221,600 790,800 569,200 221,600 2023 790,800 569,200 221,600 2024 790,800 569,200 221,600 2025 790,800 569,200 221,600 2026 790,800 569,200 221,600 2027 790,800 569,200 221,600 2028 790,800 569,200 221,600 2029 790,800 569,200 221,600 Location B Initial investment Residual value Annual depreciation 1,686,000 200,000 148,600 2020 2025 2029 2028 700,200 Projected income: Revenues Expenses * Net income 700,200 510,400 189,800 2021 700,200 510,400 189,800 2022 700,200 510,400 189,800 2023 700,200 510,400 189,800 2024 700,200 510,400 189,800 700,200 510,400 189,800 2026 700,200 510,400 189,800 2027 700,200 510,400 189,800 700,200 510,400 189,800 189,800 1,482,000 Location C Initial investment Residual value Annual depreciation 148,200 2026 2027 Projected income: Revenues Expenses * Net income 2020 591,400 416,400 175,000 2021 591,400 416,400 175,000 2022 581,400 416,400 165,000 2023 581,400 416,400 165,000 2024 571,400 416,400 155,000 2025 571,400 416,400 155,000 561,400 416,400 145,000 561,400 416,400 145,000 2028 551,400 416,400 135,000 2029 551,400 416,400 135,000 Location D Initial investment Residual value Annual depreciation 1,872,000 300,000 196,500 2021 Projected income: Revenues Expenses * Net income 2020 876,200 642,800 233,400 876,200 642,800 233,400 2022 876,200 642,800 233,400 2023 876,200 642,800 233,400 2024 876,200 642,800 233,400 2025 876,200 642,800 233,400 2026 876,200 642,800 233,400 2027 876,200 642,800 233,400 * - Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash