ava DO Paste FAx BI U H & fx 5 Accessid: gm0229 7 You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your 8 team is considering expanding in 2020 by building one or more new retail outlets in the Detroit 9 area. Four locations (A, B, C, and D) are being considered. Locations A, B, and Care ten year 10 investments, with income projections shown in the "Location Data" tab. Location D is an eight 11 year investment. Locations and D are also very near each other in Allen Park, and so should be 12 considered mutually exclusive (that is, Best Bean might invest in one or the other, but not both). 14 Your manager has asked you to prepare a report on how best to use the capital budget of $3.55 million. 15 This may include investing in one of the available locations, or it may not. If it is possible under the 16 $3,550,000 budget, you may recommend investing in more than one location. 18 In an Excel file: 19 Calculate the following for each location: Cash payback period Net present value Internal rate of return Profitability index Average rate of return 25 Use a discount rate of 16% (Best Bean's minimum rate of return) where necessary. Show all answers to 26 two decimal places (dollars and percentages). Note some helpful formulas in the "Excel TVM formulas 27 tab. 29 in a Word document: 30 Prepare a memo summarizing your findings and recommendations. 32 Submit both documents through Canvas by 11:59 PM April 24th Instructions Location Data Your Solutions Excel TVM formu 16 + Instructions Location Data Your Solutions Excel TVM formulas Location 1.1.000 depreciation 571 oll ha Ech ti Cla: Md MacBook Pro 4 5 6 7 Residual value 2020 2029 2021 72,700 771,700 2022 771,700 577.000 196,700 2023 773,700 577,000 196,700 2024 7 3,700 577,000 196,700 2025 771.700 577.000 2026 773,700 577,000 196,700 2027 771,700 577,000 196,700 2028 773,700 577,000 196,700 577.000 196,700 196,700 57.000 196,700 9 Net Income 1926.700 11 Location 12 Initial investment 13 Residual value 14 Annual depreciation 1,512.000 200,000 139,400 16 rocedim 2027 2020 681,000 517600 163,400 681,000 681,000 $17.699 163,400 081.000 $17.600 161.400 681.000 517600 1681.000 517,600 163.400 2026 6 81.000 517,600 163,400 681,000 517,600 163,400 2028 681,000 517,600 163,400 $17.600 163,400 19 Net Income 162.400 21 Location 22 Initial investment 23 Residual value 24 Annual depredation 140,800 2024 2025 26 Brolesed income 27 Revenues 2027 541,600 2020 571,600 423.600 148.000 571,600 423,600 148,000 2022 561,600 421,600 138,000 2023 561,600 423,600 138,000 551,600 423,600 128,000 $$1,600 4 23.600 128,000 2026 541,600 4 23,600 118,000 2028 531,600 423,600 100.000 2029 531,600 423,600 108,000 423 600 29 Net Income 118,000 31 Leation 32 Initial investment 33 Residual value 34 Annual depreciation 2.000.000 300.000 212.500 36 Pre deti 37 Revenues 2023 887,300 2024 87.300 2020 867-300 662,000 225,300 2021 887,300 662,000 225,300 2022 5 87,300 662,000 225,00 2025 887,300 662.000 225,300 2026 8 87,300 662.000 225,300 2027 8 87,300 662,000 225,300 225,300 225,300 41 - Expense ngures shown include depredation. With the exception of depredation, allepenses are assumed paid in cash. Instructions Location Data Your Solutions Excel TVM formulas ava DO Paste FAx BI U H & fx 5 Accessid: gm0229 7 You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your 8 team is considering expanding in 2020 by building one or more new retail outlets in the Detroit 9 area. Four locations (A, B, C, and D) are being considered. Locations A, B, and Care ten year 10 investments, with income projections shown in the "Location Data" tab. Location D is an eight 11 year investment. Locations and D are also very near each other in Allen Park, and so should be 12 considered mutually exclusive (that is, Best Bean might invest in one or the other, but not both). 14 Your manager has asked you to prepare a report on how best to use the capital budget of $3.55 million. 15 This may include investing in one of the available locations, or it may not. If it is possible under the 16 $3,550,000 budget, you may recommend investing in more than one location. 18 In an Excel file: 19 Calculate the following for each location: Cash payback period Net present value Internal rate of return Profitability index Average rate of return 25 Use a discount rate of 16% (Best Bean's minimum rate of return) where necessary. Show all answers to 26 two decimal places (dollars and percentages). Note some helpful formulas in the "Excel TVM formulas 27 tab. 29 in a Word document: 30 Prepare a memo summarizing your findings and recommendations. 32 Submit both documents through Canvas by 11:59 PM April 24th Instructions Location Data Your Solutions Excel TVM formu 16 + Instructions Location Data Your Solutions Excel TVM formulas Location 1.1.000 depreciation 571 oll ha Ech ti Cla: Md MacBook Pro 4 5 6 7 Residual value 2020 2029 2021 72,700 771,700 2022 771,700 577.000 196,700 2023 773,700 577,000 196,700 2024 7 3,700 577,000 196,700 2025 771.700 577.000 2026 773,700 577,000 196,700 2027 771,700 577,000 196,700 2028 773,700 577,000 196,700 577.000 196,700 196,700 57.000 196,700 9 Net Income 1926.700 11 Location 12 Initial investment 13 Residual value 14 Annual depreciation 1,512.000 200,000 139,400 16 rocedim 2027 2020 681,000 517600 163,400 681,000 681,000 $17.699 163,400 081.000 $17.600 161.400 681.000 517600 1681.000 517,600 163.400 2026 6 81.000 517,600 163,400 681,000 517,600 163,400 2028 681,000 517,600 163,400 $17.600 163,400 19 Net Income 162.400 21 Location 22 Initial investment 23 Residual value 24 Annual depredation 140,800 2024 2025 26 Brolesed income 27 Revenues 2027 541,600 2020 571,600 423.600 148.000 571,600 423,600 148,000 2022 561,600 421,600 138,000 2023 561,600 423,600 138,000 551,600 423,600 128,000 $$1,600 4 23.600 128,000 2026 541,600 4 23,600 118,000 2028 531,600 423,600 100.000 2029 531,600 423,600 108,000 423 600 29 Net Income 118,000 31 Leation 32 Initial investment 33 Residual value 34 Annual depreciation 2.000.000 300.000 212.500 36 Pre deti 37 Revenues 2023 887,300 2024 87.300 2020 867-300 662,000 225,300 2021 887,300 662,000 225,300 2022 5 87,300 662,000 225,00 2025 887,300 662.000 225,300 2026 8 87,300 662.000 225,300 2027 8 87,300 662,000 225,300 225,300 225,300 41 - Expense ngures shown include depredation. With the exception of depredation, allepenses are assumed paid in cash. Instructions Location Data Your Solutions Excel TVM formulas