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 C are 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.

D E F G H 2,092,000 1 Location A 2 Initial investment 3 Residual value 4 Annual depreciation 209,200 2020 780,600 557,500 223,100 2021 780,600 557,500 223,100 2022 780,600 557,500 223,100 2023 780,600 557,500 223,100 2024 780,600 557,500 223,100 2025 780,600 557,500 223,100 2026 780,600 557,500 223,100 2027 780,600 557,500 223,100 2028 780,600 557,500 223,100 2029 780,600 557,500 223,100 6 Projected income: 7 Revenues 8 Expenses * 9 Net income 10 11 Location B 12 Initial investment 13 Residual value 14 Annual depreciation 1,700,000 200,000 150,000 15 2020 691,500 496,300 195,200 2021 691,500 496,300 195,200 2022 691,500 496,300 195,200 2023 691,500 496,300 195,200 2024 691,500 496,300 195,200 2025 691,500 496,300 195,200 2026 691,500 496,300 195,200 2027 691,500 496,300 195,200 2028 691,500 496,300 195,200 2029 691,500 496,300 1 95,200 16 Projected income: 17 Revenues 18 Expenses * 19 Net income 20 21 Location C 22 Initial investment 23 Residual value 24 Annual depreciation 1,494,000 149,400 2025 26 Projected income: Revenues Expenses * 29 Net income 2020 580,300 403,500 176,800 2021 580,300 403,500 176,800 28 2022 570,300 403,500 166,800 2023 570,300 403,500 166,800 2024 560,300 403,500 156,800 560,3001 403,500 156,800 2026 550,300 403,500 146,800 2027 550,300 403,500 146,800 2028 540,300 403,500 136,800 2029 540,300 403,500 136,800 30 1,930,000 300,000 203,750 31 Location D 32 Initial investment 33 Residual value 34 Annual depreciation 35 36 Projected income: 37 Revenues 38 Expenses * 39 Net income 2020 874,400 663,500 210,900 2021 874,400 663,500 210,900 2022 874,400 663,500 210,900 2023 874,400 663,500 210,900 2024 874,400 663,500 210,900 2025 874,400 663,500 210,900 2026 874,400 663,500 210,900 2027 874,400 663,500 210,900 40 41 *. Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. 42