You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your team is considering expanding in 2022 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 Cand 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 14% (Best Bean's minimum rate of return) where necessary. Show all answers to two decimal places (dollars and percentages). You may submit this document, and if you do so, there is a tab for "Your Solutions" where you can do your work. Location A Initial investment Residual value Annual depreciation 2,090,000 0 209,000 2024 2026 2028 2029 2030 Projected income: Revenues Expenses * Net income 2022 788,700 603,500 185,200 2023 788,700 603,500 185,200 788,700 603,500 185,200 2025 788,700 603,500 185,200 788,700 603,500 185,200 2027 788,700 603,500 185,200 788,700 603,500 185,200 788,700 603,500 185,200 788,700 603,500 185,200 2031 788,700 603,500 185,200 Location B Initial investment Residual value Annual depreciation 1,704,000 200,000 150,400 2025 Projected income: Revenues Expenses * Net income 2022 697,500 547,000 150,500 2023 697,500 547,000 150,500 2024 697,500 547,000 150,500 697,500 547,000 150,500 2026 697,500 547,000 150,500 2027 697,500 547,000 150,500 2028 697,500 547,000 150,500 2029 697,500 547,000 150,500 2030 697,500 547,000 150,500 2031 697,500 547,000 150,500 Location C Initial investment Residual value Annual depreciation 1,508,000 0 150,800 2025 Projected income: Revenues Expenses * Net income 2022 586,000 447,100 138,900 2023 586,000 447,100 138,900 2024 576,000 447,100 128,900 576,000 447,100 128,900 2026 566,000 447,100 118,900 2027 566,000 447,100 118,900 2028 556,000 447,100 108,900 2029 556,000 447,100 108,900 2030 546,000 447,100 98,900 2031 546,000 447,100 98,900 Location D Initial investment Residual value Annual depreciation 1,890,000 300,000 198,750 2025 2027 2028 2023 872,300 Projected income: Revenues Expenses * Net income 2022 872,300 696,500 175,800 2024 872,300 696,500 175,800 872,300 696,500 175,800 2026 872,300 696,500 175,800 696,500 175,800 872,300 696,500 175,800 2029 872,300 696,500 175,800 872,300 696,500 175,800
