12 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 shculd 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 to 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. Submit both documents through Canvas by 5:00 PM December 11. 2021 2025 2020 771.000 551200 219,800 771.000 551.200 219 800 271,000 551,200 219 800 2023 771,000 551,200 2 19 BOO 771,000 551,200 219.800 771,000 551,200 219,800 2026 771,000 551,200 219.800 2027 2028 2029 771,000771000771000 551,200 5 51,200 551,200 219,800 219,000 213,800 1.674.000 200.000 147,400 2025 2020 682,200 490 900 191.300 2020 682,200 490 900 191300 2022 682,200 490.900 191,300 2023 682,200 490,000 191,300 2024 682,200 490.000 1 91,300 682,200 490.000 191.100 2026 682,200 490.900 191 300 2027 682,200 490,900 191,300 2728 682,200 4 90,900 1 91,300 2029 6 82,200 90.900 191,300 1,470,000 147,000 2020 571,900 396.600 175,300 2021 571,900 396,600 175,300 2022 561,900 396,600 2023 5 61,900 3 96,600 165.300 2025 551.900 396,600 135.100 2026 541.900 396,600 165.300 2027 5 41.900 196,600 145.300 2028 531,900 396,000 125.300 2029 531.900 395 500 1.992.000 300,000 211,500 2020 2021 878,000 657800 220,200 657.800 220,200 2022 878.000 657.800 220,200 2023 878,000 657,800 220,200 2024 878,000 657,800 220,200 2025 878,000 657.890 2 20,200 2026 378,000 657.800 2 20,200 2027 878,000 657 800 220,200 in include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. Present n guni - T PVO fundon is what you use to call n ations where you have both farece value, for instan nstance) ulate the present value of a single amount Future value 1,000,000 Discount rate 8.00% Number of periods 20 Present value (463,193) Note the negative answer. By convention, cash inflows are shown as positives and cash outflows are shown as negatives. Change some of the parameters (DS, D6, and D7) and note how the answer changes. To calculate the present value of an annuity: Annuity payment Discount rate Number of periods 200.000 8.00% 10 Present value (1,342,016) Eviture values You would use the FV) function to translate known present values and or annuity payments into a future value.. Present valus 1,000,000 Discount rate 8.00% Number of periods Future value (2,158,925) Annuity payment Discount rate Number of periods 200,000 8.00% 10 Future value 12,897, 322) Laknown discount rates 2025 2027 1,000 551,200 771,00 551,200 219,800 771.000 551,200 219,800 551,200 219,800 800 551,200 219,800 1,674,000 200,000 147,400 2021 682,200 682,200 662,200 490 900 191,300 490,900 191,300 682,200 490,900 191,300 682,200 490.900 191.300 682,200 490.900 191,300 191,300 1921.300 191,00 1,470,000 147,000 2023 2024 2025 541,900 571,900 396,600 175,300 571,900 396,600 175,300 561.900 396.600 165,300 561,900 395,600 165,300 551.900 396,600 155,300 $51.900 396.600 155,300 531,900 396 600 135,300 1992.000 300.000 211,500 2020 878,000 657.800 220,200 2000 000 657.800 220.200 800 2025 878,000 657 800 220,200 2026 878,000 657.800 220,200 2027 878.000 657.800 220,200 220,200 - Expense figures shown include depreciation with the exception of depreciation, all expenses assumed paid in cash you use to calculate present values for both single and its and en situations where you have both (a recurring cash inflow and a dual To calculate the present value of a single amount Future value 1,000,000 Discount rate 8.00% Number of periods 10 (463,193) 13 Note the negative answer to wention, cash inflows are shown as positives and cash 14 outflows are shown as negates. Change some of the parameters (DS, D6, and D7) and note 15 how the answer changes. 17 To calculate the present value of an annuity: Annuity payment Discount rate Number of periods 200,000 8.00% Present value (1,342,016) 24 Buture values 25 You would use the function to translate known present values and or annu 26 into a future value Present value 1,000,000 Discount rate 8.00% Number of periods Future value 12,158,925) Annuity payment Discount rate Number of periods 200.000 8.00 Future value 12,897,312) 29 Unknown count.cat 20 You can use the RAL function when you know the present and future values, but don't 41now what the discount rates Make sure you put in cash inflows and outfiows with 42 opposite signs Future value Number of beds (1.000.000) 2.000.000 D etrate 7.18 the function to translate known pres 1000.000 BLOOM Discount rate Number of periods Future value 12.150 9251 Annuity payment Discount rate Number of pe 5 200.000 8.00% 10 Future value 12,897,312) Wiscounts You can use the RATE) function when you know the present and future values, but don't 41 know what the discount rate is. Make sure you put in cash inflows and outflows with Present value Future value Number of periods (1,000,000 2,000,000 Discount rate SER 7.18% Annuity payments Future value Number of periods (150.000) 2.000.000 Discount rate 56 The RR function is also very helpful You can enter a range of values represe intal investment as a negative number followed by annual cash Internal rate of return value Initial investment 15,000,000 1,700.000 1.000.000 rate of return 12 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 shculd 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 to 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. Submit both documents through Canvas by 5:00 PM December 11. 2021 2025 2020 771.000 551200 219,800 771.000 551.200 219 800 271,000 551,200 219 800 2023 771,000 551,200 2 19 BOO 771,000 551,200 219.800 771,000 551,200 219,800 2026 771,000 551,200 219.800 2027 2028 2029 771,000771000771000 551,200 5 51,200 551,200 219,800 219,000 213,800 1.674.000 200.000 147,400 2025 2020 682,200 490 900 191.300 2020 682,200 490 900 191300 2022 682,200 490.900 191,300 2023 682,200 490,000 191,300 2024 682,200 490.000 1 91,300 682,200 490.000 191.100 2026 682,200 490.900 191 300 2027 682,200 490,900 191,300 2728 682,200 4 90,900 1 91,300 2029 6 82,200 90.900 191,300 1,470,000 147,000 2020 571,900 396.600 175,300 2021 571,900 396,600 175,300 2022 561,900 396,600 2023 5 61,900 3 96,600 165.300 2025 551.900 396,600 135.100 2026 541.900 396,600 165.300 2027 5 41.900 196,600 145.300 2028 531,900 396,000 125.300 2029 531.900 395 500 1.992.000 300,000 211,500 2020 2021 878,000 657800 220,200 657.800 220,200 2022 878.000 657.800 220,200 2023 878,000 657,800 220,200 2024 878,000 657,800 220,200 2025 878,000 657.890 2 20,200 2026 378,000 657.800 2 20,200 2027 878,000 657 800 220,200 in include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. Present n guni - T PVO fundon is what you use to call n ations where you have both farece value, for instan nstance) ulate the present value of a single amount Future value 1,000,000 Discount rate 8.00% Number of periods 20 Present value (463,193) Note the negative answer. By convention, cash inflows are shown as positives and cash outflows are shown as negatives. Change some of the parameters (DS, D6, and D7) and note how the answer changes. To calculate the present value of an annuity: Annuity payment Discount rate Number of periods 200.000 8.00% 10 Present value (1,342,016) Eviture values You would use the FV) function to translate known present values and or annuity payments into a future value.. Present valus 1,000,000 Discount rate 8.00% Number of periods Future value (2,158,925) Annuity payment Discount rate Number of periods 200,000 8.00% 10 Future value 12,897, 322) Laknown discount rates 2025 2027 1,000 551,200 771,00 551,200 219,800 771.000 551,200 219,800 551,200 219,800 800 551,200 219,800 1,674,000 200,000 147,400 2021 682,200 682,200 662,200 490 900 191,300 490,900 191,300 682,200 490,900 191,300 682,200 490.900 191.300 682,200 490.900 191,300 191,300 1921.300 191,00 1,470,000 147,000 2023 2024 2025 541,900 571,900 396,600 175,300 571,900 396,600 175,300 561.900 396.600 165,300 561,900 395,600 165,300 551.900 396,600 155,300 $51.900 396.600 155,300 531,900 396 600 135,300 1992.000 300.000 211,500 2020 878,000 657.800 220,200 2000 000 657.800 220.200 800 2025 878,000 657 800 220,200 2026 878,000 657.800 220,200 2027 878.000 657.800 220,200 220,200 - Expense figures shown include depreciation with the exception of depreciation, all expenses assumed paid in cash you use to calculate present values for both single and its and en situations where you have both (a recurring cash inflow and a dual To calculate the present value of a single amount Future value 1,000,000 Discount rate 8.00% Number of periods 10 (463,193) 13 Note the negative answer to wention, cash inflows are shown as positives and cash 14 outflows are shown as negates. Change some of the parameters (DS, D6, and D7) and note 15 how the answer changes. 17 To calculate the present value of an annuity: Annuity payment Discount rate Number of periods 200,000 8.00% Present value (1,342,016) 24 Buture values 25 You would use the function to translate known present values and or annu 26 into a future value Present value 1,000,000 Discount rate 8.00% Number of periods Future value 12,158,925) Annuity payment Discount rate Number of periods 200.000 8.00 Future value 12,897,312) 29 Unknown count.cat 20 You can use the RAL function when you know the present and future values, but don't 41now what the discount rates Make sure you put in cash inflows and outfiows with 42 opposite signs Future value Number of beds (1.000.000) 2.000.000 D etrate 7.18 the function to translate known pres 1000.000 BLOOM Discount rate Number of periods Future value 12.150 9251 Annuity payment Discount rate Number of pe 5 200.000 8.00% 10 Future value 12,897,312) Wiscounts You can use the RATE) function when you know the present and future values, but don't 41 know what the discount rate is. Make sure you put in cash inflows and outflows with Present value Future value Number of periods (1,000,000 2,000,000 Discount rate SER 7.18% Annuity payments Future value Number of periods (150.000) 2.000.000 Discount rate 56 The RR function is also very helpful You can enter a range of values represe intal investment as a negative number followed by annual cash Internal rate of return value Initial investment 15,000,000 1,700.000 1.000.000 rate of return
