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 C 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). 13 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. 17 18 In an Excel file: 19 Calculate the following for each location: 20 Cash payback period 21 Net present value Internal rate of return 23 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. 28 29 In a Word document: | 30 Prepare a memo summarizing your findings and recommendations. 2,028,000 1 Location A 2 Initial investment 3 Residual value 4 Annual depreciation 202,800 2020 790,200 562,300 227,900 2021 790,200 562,300 227,900 2022 790,200 562,300 227,900 2023 790,200 562,300 227,900 2024 790,200 562,300 227,900 2025 790,200 562,300 227,900 2026 790,200 562,300 227,900 2027 790,200 562,300 227,900 2028 790,200 562,300 227,900 2029 790,200 562,300 227,900 6 Projected income! 7 Revenues 8 Expenses * 9 Net income 10 11 Location B 12 Initial investment 13 Residual value 14 Annual depreciation 1,626,000 200,000 142,600 15 2020 699,300 504,700 194,600 2021 699,300 504,700 194,600 2022 699,300 504,700 194,600 2023 699,300 504,700 194,600 2024 699,300 504,700 194,600 2025 699,300 504,700 194,600 2026 699,300 504,700 194,600 2027 699,300 504,700 194,600 2028 699,300 504,700 194,600 2029 699,300 504,700 194,600 1,418,000 16 Projected income: 17 Revenues 18 Expenses * 19 Net income 20 21 Location C 22 Initial investment 23 Residual value 24 Annual depreciation 25 26 Projected income: 27 Revenues 28 Expenses * 29 Net income 141,800 2020 591,700 410,400 181,300 2021 591,700 410,400 181,300 2022 581,700 410,400 171,300 2023 581,700 410,400 171,300 2024 571,700 410,400 161,300 2025 571,700 410,400 161,300 2026 561,700 410,400 151,300 2027 561,700 410,400 151,300 2028 551,700 410,400 141,300 2029 551,700 410,400 141,300 1,878,000 300,000 197,250 31 Location D 32 Initial investment 33 Residual value 34 Annual depreciation 35 36 Projected income: 37 Revenues 38 Expenses * 39 Net income 2020 885,800 640,700 245,100 2021 885,800 640,700 245,100 2022 885,800 640,700 245,100 2023 885,800 640,700 245,100 2024 885,800 640,700 245,100 2025 885,800 640,700 245,100 2026 885,800 640,700 245,100 2027 885,800 640,700 245,100