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 morenew retail outlets in the Detroit area. Four locations (A, B, C, and D) are being considered. Locations A, B, and C are 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. Ifit 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 12% (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. Location A nitial investment Residual value Annual depreciation 2,286,000 0 228,600 2020 761,800 594,200 167,600 2027 761,800 94,200 2028 761,800 594,200 167,600 2029 761,800 594,200 167,600 2022 2023 761,800 594,200 167,600 ro 2024 2025 2026 761,800 594,200 167,600 761,800 594,200 167,600 761,800 594,200 761,800 594,200 Revenues 594,200 167,600 Net income Location B Initial investment Residual value Annual depreciation 1,670,000 200,000 147,000 2024 656,200 40400 115,800 Projected income: Revenues 2020 656,200 540,400 2021 656,200 540 400 2022 656,200 2023 656,200 2026 656,200 540400 115,800 2027 2028 656,200 540400 115,800 2029 656,200 540,400 656,200 540400 115,800 656,200 540,400 115,800 Net income Location C Initial investment Residual value Annual depreciation 1,482,000 0 148,200 2020 567,400 454,000 113,400 Projected income: 567,400 454,000 113,400 557,400 454,000 103,400 557,400 454,000 2024 547,400 454,000 454,000 454,000 454,000 83,400 454,000 Net income Initial investment Residual value Annual depreciation 2,032,000 300,000 Projected income Revenues Expenses Net income 846,200 640,800 205,400 846,200 640,800 205,400 2022 846,200 640,800 205,400 2023 846,200 640,800 205,400 846,200 640,800 205,400 846,200 640,800 205,400 2027 846,200 640,800 205,400 640,800 205,400 -Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash
