Chegg1 Chegg2 Q Search O Search You are working in a strategic management role at Best Bean, Inc., a chain of coffee shops. Your B C D E F G team is considering expanding in 2021 by building one or more new retail outlets in the Detroit area. Four locations are being considered, and details about each are described in an email from need your help on our capital expenditures planning for next year. We didn't get as big a 8 piece of the corporate budget as we might have liked, but $2 million is better than nothing your manager, which can be found on the next tab. Read the email, and follow the instructions and it's enough to do something with it. Weve scoped out four locations and put together 10 some estimates for their future profitability. In an Excel file: 11 12 There's a very large site in Allen Park that comes with an appropriately large price tag of Calculate the following for each location: 13 $1,880,000 initial investment. We're estimating a ten year useful life with no salvage value Cash payback period 14 It's expected to generate revenues of $1,085,000 per year over those ten years, with Net present value 15 $749,000 of annual cash costs (not including depreciation), Internal rate of return 16 17 Alocation in Bloomfield Hills is available for an initial investment of $1,350,000. It also has Profitability index 18 a ten year useful life, and we're estimatinga residual value of $199,000. Over ten years, Annual rate of return 19 we're expecting annual revenues of $794,000 and annual cash costs of $550,000. You should always leverage the power of Excel, using formulas and cell references where 20 possible, rather than keyed-in numerical values. Show all answers to two decimal places 21 The Commerce Township site has an initial cost of $1,270,000, with annual revenues of 22 $903,000 and cash costs in year 1 of $566,000. Because we won't be able to get into a long (dollars and percentages). Note some helpful formulas in the "Excel TVM formulas" tab. 23 term lease to our liking there, we're only going to estimate it with an eight year useful life, 24 and we're forecasting costs to increase by $24,000 per year each successive year into the In a Word document: 25 investment because of rising occupancy costs. We're also estimating a residual value of 26 $100,000 there. Prepare a memo summarizing your findings and recommendations. 27 28 Finally, there's a small site in Dearborn Heights we can get our hands on for an initial Submit both documents through Canvas by 10:00 AM June 28. A 10% early submission bonus 29 investment of $505,000. It has the same lease problems as Commerce, so we're estimating 30 an eight year useful life and rising occupancy costs. It has annual estimated revenues of will be given to submissions received by 9:00 AM June 24. 31 $537,000 and year 1 cash costs of $400,000, expected to rise by $11,000 in each ensuing 32 year. It has no estimated salvage value 33 34 So here's what I need from you. In an Excel file, calculate each project's cash payback period, 35 net present value, internal rate of return, profitability index, and annual rate of return. 36 Assume straight line depreciation where necessary, and this year's discount rate for all 37 present value calculations is 12%. 38 39 Also put together a memo summarizing your findings and making a recommendation on 40 which location we should invest in. Your recommendation needs to clearly indicate where 41 we should invest, and reference your analysis as a basis for your conclusion. Remember that 42 we have the whole $2 million budget, so if we can afford to invest in more than one location, 43 we can do it if it makes sense. Also, remember corporate hates throwing money away. If all 44 of these locations are losers, 'none of the above'is a perfectly acceptable answer, 45 46 Thanks in advance for your attention. 47 in