AB K L M 2,122,000 1 Location A 2 Initial investment 3 Residual value 4 Annual depreciation 212,200 2021 Projected income: Revenues Expenses 9 Net income 2020 788,100 597,800 190,300 788,100 597,800 190,300 2022 788,100 597,800 190,300 2023 788,100 597,800 2024 788,100 597,800 190,300 2025 788,100 597,800 190,300 2026 788,100 597,800 190,300 2027 788,100 597,800 190,300 2028 788,100 597,800 190,300 2029 788,100 597,800 190,300 190,300 10 11 Location B 12 Initial investment 13 Residual value 14 Annual depreciation 1,724,000 200,000 152,400 15 16 Projected income: 17 Revenues 18 Expenses 19 Net income 2020 697,800 542,800 155,000 2021 697,800 542,800 155,000 2022 697,800 542,800 155,000 2023 697,800 542,800 155,000 155,000 2024 697,800 542,800 155,000 2025 697,800 542,800 155,000 2026 697,800 542,800 155,000 2027 697,800 542,800 155,000 2028 697,800 542,800 155,000 2029 697,800 542,800 155,000 20 2027 2026 557,800 441,100 116,700 2028 547,800 441,100 106,700 2029 547,800 441,100 106,700 441,100 116,700 21 Location C 22 Initial investment 1,524,000 23 Residual value 24 Annual depreciation 152,400 25 26 Projected income: 2020 2021 2022 2023 2024 2025 27 Revenues 587,800 587,800 577,800 577,800 567,800 567,800 28 Expenses 441,100 441,100 441,100 441,100 441,100 441,100 29 Net income 146,700 146,700 136,700 136,700 126,700 126,700 30 31 Location D 32 Initial investment 1,888,000 33 Residual value 300,000 34 Annual depreciation 198,500 35 36 Projected income: 2020 2021 2022 2023 2024 2025 37 Revenues 869,900 869,900 869,900 869,900 869,900 869,900 38 Expenses 678,200 678,200 678,200 678,200 678,200 678,200 39 Net income 191,700 191,700 191,700 191,700 191,700 191,700 40 41 Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. 2026 869,900 678,200 191,700 2027 869,900 678,200 191,700 42 Home View Share Insert X Cut LS Copy - Format Draw Page Layout Calibri (Body) 11 BI U Formulas An Av Data = = Review = dli AutoSum sto Wrap Text Merge & Center F A General $ % D D - -* * Paste ) Conditional Format Formatting as Table Cell Styles Insert Delete Format Clear Sort & Filter Check for Updates B4 Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates x fx LM AG AH AI AJ AMAN 1 ACC 3020 - Fall 2019 2 Excel Case 2 - Capital Investments 4 Name: 5 AccessID: as7833 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 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). 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: Cash payback period Net present value Internal rate of return Profitability index Average rate of return 25 Use a discount rate of 12% (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. 32 Submit both documents through Canvas by 5:00 PM December 11
