Please provide all formulas and explanition of how you did them. Thank you
Excel File Edit View Insert Format Tools Data Window Help Insert Page Layout Formulas Data Review View Cut Calibri (Body) 11 A A Copy Paste Copy Format 8 You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your 9 team is considering expanding in 2020 by building one or more new retail outlets in the Detroit 10 area. Four locations (A, B, C, and D) are being considered. Locations A, B, and C are ten year 11 investments, with income projections shown in the "Location Data" tab. Location D is an eight 12 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) 14 15 Your manager has asked you to prepare a report on how best to use the capital budget of $3.55 million. 16 This may include investing in one of the available locations, or it may not. If it is possible under the 17 $3,550,000 budget, you may recommend investing in more than one location. 18 19 In an Excel file: 20 Calculate the following for each location: 21 Cash payback period Net present value Internal rate of return Profitability index Average rate of return 23 24 25 26 Use a discount rate of 14% (Best Bean's minimum rate of return) where necessary. Show all answers to 27 two decimal places (dollars and percentages), Note some helpful formulas in the "Excel TVM formulas" 28 tab. 29 Excel File Edit View Insert Format Tools Data Windo Insert Page Layout Formulas Data Review View Home Copy Format BIU. Paste C1 2The PV) function i annuities, or even situations where you have both (a recurring cash inflow and a residual s what you use to calculate present values for both single amounts and 4 value, for instance). To calculate the present value of a single amount: 6 Future value Discount rate 1,000,000 Number of periods 10 10 Present value (463,193) 12 13 Note the negative answer. By convention, cash inflows are shown as positives and cash 14 outflows are shown as negatives. Change 15 how the answer changes. 16 17 To calculate the present value of an annuity: 18 19 20 21 some of the parameters (DS, D6, and D7) and note 1 Annuity payment Discount rate Number of periods 8.00% 10 1,342,016) Present value 23 24 Future values 25 You would use the FV) function to translate known present values and or annuity 26 into a future value. 27 28 payments Present value Discount rate Number of periods 1,000,000 8.00% 10 30 31 32 Future value (2,158,925) Annuity payment Discount rate Number of periods 200,000 8.00% 10 34 36 37 38 12,897,312) Future value
