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 o investments, with income projections shown in the "Location Data" tab. Location D is an eight 1 year investment. Locations Cand D are also very near each other in Allen Park, and so should be 2 considered mutually exclusive (that is, Best Bean might invest in one or the other, but not both). 3 4 Your manager has asked you to prepare a report on how best to use the capital budget of $3.55 million. 5 This may include investing in one of the available locations, or it may not. If it is possible under the 6 $3,550,000 budget, you may recommend investing in more than one location. 3 In an Excel file: 9 Calculate the following for each location: Cash payback period Net present value Internal rate of return Profitability index Average rate of return 5 Use a discount rate of 16% (Best Bean's minimum rate of return) where necessary. Show all answers to 6 two decimal places (dollars and percentages). Note some helpful formulas in the "Excel TVM formulas" 7 tab. 9 in a Word document: O Prepare a memo summarizing your findings and recommendations. 2 Submit both documents through Canvas by 11:59 PM April 24th B H 1 Location A 2 Initial investment 3 Residual value 4 Annual depreciation 2,118,000 211,800 2021 2023 2024 6 Proiected income: 7 Revenues 8 Expenses * 9 Net income 2020 782,100 572,800 209,300 782,100 572,800 209,300 2022 782,100 572,800 209,300 782,100 572,800 209,300 782,100 572,800 209,300 2025 782,100 572,800 209,300 2026 782,100 572,800 209,300 2027 782,100 572,800 209,300 2028 782,100 572,800 209,300 2029 782,100 572,800 209,300 10 1,720,000 200,000 152,000 11 Location B 12 Initial investment 13 Residual value 14 Annual depreciation 15 16 Proiected income: 17 Revenues 18 Expenses * 19 Net income 20 21 Location 22 Initial investment 23 Residual value 24 Annual depreciation 2020 691,800 512,200 179,600 2021 691,800 512,200 179,600 2022 691,800 512,200 179,600 2023 691,800 512,200 179,600 2024 691,800 512,200 179,600 2025 691,800 512,200 179,600 2026 691,800 512,200 179,600 2027 691,800 512,200 179,600 2028 691,800 512,200 179,600 2029 691,800 512,200 179,600 1,524,000 152,400 26 Proiected income: 27 Revenues 28 Expenses * 29 Net income 2020 581,200 418,500 162,700 2021 581,200 418,500 162,700 2022 571,200 418,500 152,700 2023 571,200 418,500 152,700 2024 561,200 418,500 142,700 2025 561,200 418,500 142,700 2026 551,200 418,500 132,700 2027 551,200 418,500 132,700 2028 541,200 418,500 122,700 2029 541,200 418,500 122,700 31 Location D 32 Initial investment 33 Residual value 34 Annual depreciation 1,928,000 300,000 203,500 36 Proiected income: 37 Revenues 38 Expenses * 39 Net income 2020 869,900 654,200 215,700 2021 869,900 654,200 215,700 2022 869,900 654,200 215,700 2023 869,900 654,200 215,700 2024 869,900 654,200 215,700 2025 869,900 654,200 215,700 2026 869,900 654,200 215,700 2027 869,900 654,200 215,700 1 * - Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. H 1 2 3 4 5 6 ALB G Present values The PV() function is what you use to calculate present values for both single amounts and annuities, or even situations where you have both (a recurring cash inflow and a residual value, for instance). To calculate the present value of a single amount: Future value 1,000,000 Discount rate 8.00% Number of periods 10 Present value (463,193) 13 Note the negative answer. By convention,cash inflows are shown as positives and cash 14 outflows are shown as negatives. Change some of the parameters (D5,D6, and D7) and note 15 how the answer changes. 16 17 To calculate the present value of an annuity: Annuity payment 200,000 Discount rate 8.00% Number of periods 10 Present value (1,342,016) 24 Future values 25 You would use the FV() function to translate known present values and or annuity payments 26 into a future value. Present value 1,000,000 Discount rate 8.00% Number of periods 10 Future value (2,158,925) Annuity payment Discount rate Number of periods 200,000 8.00% 10 36 37 Future value (2,897,312) 39 Unknown discount rates 40 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 42 opposite signs. Present value Future value Number of periods (1,000,000) 2,000,000 10 Discount rate 7.18% Annuity payments Future value Number of periods (150,000) 2,000,000 10 Discount rate 6.24% 55 56 The IRR() function is also very helpful. You can enter a range of values representing an 57 initial investment (as a negative number followed by annualcash inflows and get an 58 internal rate of return value. Initial investment Year 1 Year 2 Year 3 Year 4 (5,000,000) 1,800,000 1,800,000 1,700,000 1,600,000 Internal rate of return 14.58% 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 o investments, with income projections shown in the "Location Data" tab. Location D is an eight 1 year investment. Locations Cand D are also very near each other in Allen Park, and so should be 2 considered mutually exclusive (that is, Best Bean might invest in one or the other, but not both). 3 4 Your manager has asked you to prepare a report on how best to use the capital budget of $3.55 million. 5 This may include investing in one of the available locations, or it may not. If it is possible under the 6 $3,550,000 budget, you may recommend investing in more than one location. 3 In an Excel file: 9 Calculate the following for each location: Cash payback period Net present value Internal rate of return Profitability index Average rate of return 5 Use a discount rate of 16% (Best Bean's minimum rate of return) where necessary. Show all answers to 6 two decimal places (dollars and percentages). Note some helpful formulas in the "Excel TVM formulas" 7 tab. 9 in a Word document: O Prepare a memo summarizing your findings and recommendations. 2 Submit both documents through Canvas by 11:59 PM April 24th B H 1 Location A 2 Initial investment 3 Residual value 4 Annual depreciation 2,118,000 211,800 2021 2023 2024 6 Proiected income: 7 Revenues 8 Expenses * 9 Net income 2020 782,100 572,800 209,300 782,100 572,800 209,300 2022 782,100 572,800 209,300 782,100 572,800 209,300 782,100 572,800 209,300 2025 782,100 572,800 209,300 2026 782,100 572,800 209,300 2027 782,100 572,800 209,300 2028 782,100 572,800 209,300 2029 782,100 572,800 209,300 10 1,720,000 200,000 152,000 11 Location B 12 Initial investment 13 Residual value 14 Annual depreciation 15 16 Proiected income: 17 Revenues 18 Expenses * 19 Net income 20 21 Location 22 Initial investment 23 Residual value 24 Annual depreciation 2020 691,800 512,200 179,600 2021 691,800 512,200 179,600 2022 691,800 512,200 179,600 2023 691,800 512,200 179,600 2024 691,800 512,200 179,600 2025 691,800 512,200 179,600 2026 691,800 512,200 179,600 2027 691,800 512,200 179,600 2028 691,800 512,200 179,600 2029 691,800 512,200 179,600 1,524,000 152,400 26 Proiected income: 27 Revenues 28 Expenses * 29 Net income 2020 581,200 418,500 162,700 2021 581,200 418,500 162,700 2022 571,200 418,500 152,700 2023 571,200 418,500 152,700 2024 561,200 418,500 142,700 2025 561,200 418,500 142,700 2026 551,200 418,500 132,700 2027 551,200 418,500 132,700 2028 541,200 418,500 122,700 2029 541,200 418,500 122,700 31 Location D 32 Initial investment 33 Residual value 34 Annual depreciation 1,928,000 300,000 203,500 36 Proiected income: 37 Revenues 38 Expenses * 39 Net income 2020 869,900 654,200 215,700 2021 869,900 654,200 215,700 2022 869,900 654,200 215,700 2023 869,900 654,200 215,700 2024 869,900 654,200 215,700 2025 869,900 654,200 215,700 2026 869,900 654,200 215,700 2027 869,900 654,200 215,700 1 * - Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. H 1 2 3 4 5 6 ALB G Present values The PV() function is what you use to calculate present values for both single amounts and annuities, or even situations where you have both (a recurring cash inflow and a residual value, for instance). To calculate the present value of a single amount: Future value 1,000,000 Discount rate 8.00% Number of periods 10 Present value (463,193) 13 Note the negative answer. By convention,cash inflows are shown as positives and cash 14 outflows are shown as negatives. Change some of the parameters (D5,D6, and D7) and note 15 how the answer changes. 16 17 To calculate the present value of an annuity: Annuity payment 200,000 Discount rate 8.00% Number of periods 10 Present value (1,342,016) 24 Future values 25 You would use the FV() function to translate known present values and or annuity payments 26 into a future value. Present value 1,000,000 Discount rate 8.00% Number of periods 10 Future value (2,158,925) Annuity payment Discount rate Number of periods 200,000 8.00% 10 36 37 Future value (2,897,312) 39 Unknown discount rates 40 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 42 opposite signs. Present value Future value Number of periods (1,000,000) 2,000,000 10 Discount rate 7.18% Annuity payments Future value Number of periods (150,000) 2,000,000 10 Discount rate 6.24% 55 56 The IRR() function is also very helpful. You can enter a range of values representing an 57 initial investment (as a negative number followed by annualcash inflows and get an 58 internal rate of return value. Initial investment Year 1 Year 2 Year 3 Year 4 (5,000,000) 1,800,000 1,800,000 1,700,000 1,600,000 Internal rate of return 14.58%