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. 2,330,000 Location A Initial investment Residual value Annual depreciation 233,000 Projected income: Revenues Expenses Net income 2020 780,400 601,100 179,300 2021 780,400 601,100 179,300 2022 780,400 601,100 179,300 2023 780,400 601,100 179,300 2024 780,400 601,100 179,300 2025 780,400 601,100 179,300 2026 780,400 601,100 179,300 2027 780,400 601,100 179,300 2028 780,400 601,100 179,300 2029 780,400 601,100 179,300 Location B Initial investment Residual value Annual depreciation 1,742,000 200,000 154,200 2025 675,100 Projected income: Revenues Expenses Net income 2020 675,100 544,000 131,100 2021 675,100 544,000 131,100 2022 675,100 544,000 131,100 2023 675, 100 544,000 131,100 2024 675,100 544,000 131,100 2026 675,100 544,000 131,100 2027 675,100 544,000 131,100 2028 675,100 544,000 131,100 2029 675,100 544,000 131,100 131,100 1,546,000 Location C Initial investment Residual value Annual depreciation 154,600 Projected income: Revenues Expenses Net income 2020 583,000 461,800 121,200 2021 583,000 461,800 121,200 2022 573,000 461,800 111,200 2023 573,000 461,800 111,200 2024 563,000 461,800 101,200 2025 563,000 461,800 101,200 2026 553,000 461,800 91,200 2027 553,000 461,800 91,200 2028 543,000 461,800 81,200 2029 543,000 461,800 81,200 ,200 Location D Initial investment Residual value Annual depreciation 1,906,000 300,000 200,750 Projected income: Revenues Expenses Net income 2020 836,600 695,900 140,700 2021 836,600 695,900 140,700 2022 836,600 695,900 140,700 2023 836,600 695,900 140.700 2024 836,600 695,900 140,700 2025 836,600 695,900 140,700 2026 836,600 695,900 140,700 2027 836,600 695,900 140,700 * - Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. 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) d cash Note the negative answer. By convention, cash inflows are shown as positives and cash outflows are shown as negatives. Change some of the parameters (D5, 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) Future values You would use the =FV() function to translate known present values and or annuity payments 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 Future value (2,897,312) Unknown discount rates You can use the =RATE() function when you know the present and future values, but don't know what the discount rate is. Make sure you put in cash inflows and outflows with 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% The IRR() function is also very helpful. You can enter a range of values representing an initial investment (as a negative number) followed by annual cash inflows and get an 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%