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. In a Word document: Prepare a memo summarizing your findings and recommendations. 1,994,000 Location A Initial investment Residual value Annual depreciation 199,400 Proiected income: Revenues Expenses Net income 2020 773,700 577,000 196,700 2021 773,700 577,000 196,700 2022 773,700 577,000 196,700 2023 773,700 577,000 196,700 2024 773,700 577,000 196,700 2025 773,700 577,000 196,700 2026 773,700 577,000 196,700 2027 773,700 577,000 196,700 2028 773,700 577,000 196,700 2029 773,700 577,000 196,700 Location B Initial investment Residual value Annual depreciation 1,594,000 200,000 139,400 Proiected income: Revenues Expenses * Net income 2020 681,000 517,600 163,400 2021 681,000 517,600 163,400 2022 681,000 517,600 163,400 2023 681,000 517,600 163,400 2024 681,000 517,600 163,400 2025 681,000 517,600 163,400 2026 681,000 517,600 163,400 2027 681,000 517,600 163,400 2028 681,000 517,600 163,400 2029 681,000 517,600 163,400 1,408,000 Location Initial investment Residual value Annual depreciation 140,800 Proiected income: Revenues Expenses * Net income 2020 571,600 423,600 148,000 2021 571,600 423,600 148,000 2022 561,600 423,600 138,000 2023 561,600 423,600 138,000 2024 551,600 423,600 128,000 2025 551,600 423,600 128,000 2026 541,600 423,600 118,000 2027 541,600 423,600 118,000 2028 531,600 423,600 108,000 2029 531,600 423,600 108,000 Location D Initial investment Residual value Annual depreciation 2,000,000 300,000 212,500 Proiected income: Revenues Expenses * Net income 2020 887,300 662,000 225,300 2021 887,300 662,000 225,300 2022 887,300 662,000 225,300 2023 887,300 662,000 225,300 2024 887,300 662,000 225,300 2025 887,300 662,000 225,300 2026 887,300 662,000 225,300 2027 887,300 662,000 225,300 * - Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. 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. In a Word document: Prepare a memo summarizing your findings and recommendations. 1,994,000 Location A Initial investment Residual value Annual depreciation 199,400 Proiected income: Revenues Expenses Net income 2020 773,700 577,000 196,700 2021 773,700 577,000 196,700 2022 773,700 577,000 196,700 2023 773,700 577,000 196,700 2024 773,700 577,000 196,700 2025 773,700 577,000 196,700 2026 773,700 577,000 196,700 2027 773,700 577,000 196,700 2028 773,700 577,000 196,700 2029 773,700 577,000 196,700 Location B Initial investment Residual value Annual depreciation 1,594,000 200,000 139,400 Proiected income: Revenues Expenses * Net income 2020 681,000 517,600 163,400 2021 681,000 517,600 163,400 2022 681,000 517,600 163,400 2023 681,000 517,600 163,400 2024 681,000 517,600 163,400 2025 681,000 517,600 163,400 2026 681,000 517,600 163,400 2027 681,000 517,600 163,400 2028 681,000 517,600 163,400 2029 681,000 517,600 163,400 1,408,000 Location Initial investment Residual value Annual depreciation 140,800 Proiected income: Revenues Expenses * Net income 2020 571,600 423,600 148,000 2021 571,600 423,600 148,000 2022 561,600 423,600 138,000 2023 561,600 423,600 138,000 2024 551,600 423,600 128,000 2025 551,600 423,600 128,000 2026 541,600 423,600 118,000 2027 541,600 423,600 118,000 2028 531,600 423,600 108,000 2029 531,600 423,600 108,000 Location D Initial investment Residual value Annual depreciation 2,000,000 300,000 212,500 Proiected income: Revenues Expenses * Net income 2020 887,300 662,000 225,300 2021 887,300 662,000 225,300 2022 887,300 662,000 225,300 2023 887,300 662,000 225,300 2024 887,300 662,000 225,300 2025 887,300 662,000 225,300 2026 887,300 662,000 225,300 2027 887,300 662,000 225,300 * - Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash
