Question

Please answer this fully. This is a major final project so it's crucial I get it right. Thank you so much!

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 Cand 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 12% (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.

Answer 1

Location A Initial Investment Depreciation Total Cash Flows DR @12% Present Value Year Income 2019(Year 0) -23,30,000 -23,30,000 -23,30,000.00 1,79,300 2,33,000 4,12,300 2020 0.89286 3,68,125.00 4,12,300 2021 1,79,300 1,79,300 2,33,000 0.79719 3,28,683.04 2,33,000 2,93,467.00 2022 4,12,300 0.71178 0.63552 2,33,000 2,33,000 2,33,000 2,62,024.10 2023 1,79,300 4,12,300 1,79,300 1,79,300 1,79,300 2024 4,12,300 0.56743 2,33,950.09 2025 4,12,300 0.50663 2,08,884.01 2026 2,33,000 4,12,300 0.45235 1,86,503.58 2027 1,79,300 2,33,000 4,12,300 0.40388 1,66,521.05 1,48,679.51 2028 1,79,300 1,79,300 Net Present Value 2,33,000 4,12,300 0.36061 1,32,749.57 2029 2,33,000 4,12,300 0.32197 -413.05

(1) Cash Payback Period((Initial Investment divided by Cash Inflows after Cash Expenses)*No. of Years) (2) Net Present Value (As Computed on left side) (3) Internal Rate of Return (Based on Excel Formula) 5.65 Years -413.05 11.99567% (Nearly 12%, if rounded to 2 Decimal Places) 0.99982 (Nearly 1, if rounded to 2 Decimal Places) (4) Profitability Index (PV of Future Cash Flows divided by Initial Investment) (5) Average Rate of Return (Net Earnings throughout the period of Investment 7.70% plus residual value divided by Years and divided by Initial Investment)

Location B Initial Investment Depreciation Total Cash Flows DR @12% Present Value Year Income 2019(Year 0) -17,42,000 -17,42,000 1 -17,42,000.00 1,54,200 2020 1,31,100 2,85,300 0.89286 2,54,732.14 2,85,300 2021 1,31,100 1,54,200 0.79719 2,27,439.41 1,54,200 2,85,300 2,03,070.90 2022 1,31,100 0.71178 2023 1,31,100 1,54,200 2,85,300 0.63552 1,81,313.31 2024 1,31,100 1,54,200 2,85,300 0.56743 1,61,886.88 1,44,541.86 2025 1,31,100 1,31,100 1,54,200 2,85,300 0.50663 2026 2,85,300 1,54,200 0.45235 1,29,055.23 2027 1,31,100 1,54,200 2,85,300 0.40388 1,15,227.88 2028 1,31,100 1,54,200 2,85,300 0.36061 1,02,882.04 2029 2,00,000 1,31,100 1,54,200 4,85,300 0.32197 1,56,253.61 Net Present Value -65,596.72

(1) Cash Payback Period (Kindly ignore Salvage Value in calculating Cash Payback Period) 6.33 Years (2) Net Present Value (3) Internal Rate of Return (4) Profitability Index (5) Average Rate of Return -65,596.72 11.11% 0.96 8.67%

Location C Initial Investment Depreciation Total Cash Flows DR @12% Present Value Year Income 2019(Year 0) -15,46,000 -15,46,000 1 -15,46,000.00 2,46,250.00 2,19,866.07 1,21,200 1,54,600 2020 2,75,800 0.89286 0.79719 2021 1,21,200 1,54,600 1,54,600 2,75,800 1,21,200 1,96,308.99 2022 2,75,800 0.71178 0.63552 1,75,275.89 2023 1,21,200 1,54,600 2,75,800 2024 1,21,200 1,54,600 2,75,800 0.56743 1,56,496.33 2025 1,21,200 1,54,600 2,75,800 0.50663 1,39,728.86 2026 1,54,600 1,21,200 2,75,800 0.45235 1,24,757.91 2027 1,21,200 1,54,600 2,75,800 0.40388 1,11,390.99 2028 1,54,600 1,54,600 Net Present Value 1,21,200 2,75,800 0.36061 99,456.24 2029 1,21,200 2,75,800 0.32197 88,800.22 12,331.51

(1) Cash Payback Period (2) Net Present Value (3) Internal Rate of Return (4) Profitability Index (5) Average Rate of Return 5.61 Years 12,331.51 12.19% 1.01 7.84%

Location D Depreciation Total Cash Flows DR @12% Present Value Initial Investment Year Income 2019(Year 0) -19,06,000 -19,06,000 -19,06,000.00 0.89286 2020 1,40,700 2,00,750 3,41,450 3,04,866.07 2,00,750 2021 1,40,700 1,40,700 3,41,450 0.79719 2,72,201.85 2022 2,00,750 3,41,450 0.71178 2,43,037.37 2023 1,40,700 2,00,750 3,41,450 0.63552 2,16,997.65 1,93,747.90 1,40,700 2,00,750 2024 3,41,450 3,41,450 0.56743 2025 1,40,700 2,00,750 0.50663 1,72,989.20 1,54,454.64 3,41,450 2026 1,40,700 1,40,700 2,00,750 0.45235 0.40388 2,59,070.90 2027 3,00,000 2,00,750 6,41,450 Net Present Value -88,634.43

(1) Cash Payback Period (Kindly ignore Salvage Value in calculating Cash Payback Period) 5.58 Years (2) Net Present Value (3) Internal Rate of Return (4) Profitability Index (5) Average Rate of Return -88,634.43 10.74% 0.95 9.35%

The Management would like to Invest in Location C only as it is giving positive Net Present Value. Apart from Location C, other locations such as Location A,B, and D are not worthwhile to Invest