Chapter 11: Performance Measurement in Decentralized Organizations

To evaluate more comprehensively material from chapters 7 & 11, complete theIntegration Exercise #5at the end of the textbook. For this assignment, I want to see each and every step worked out in an Excel spreadsheet. Doing this should eliminate everyone reaching a bunch of different answers from rounding.

INTEGRATION EXERCISE 5 Capital Budgeting, Return on Investment, Residual IncomeLO7-2,LO11-1,LO11-2

Simmons Company is a merchandiser with multiple store locations. One of its store managers is considering a shift in her store's product mix in anticipation of a strengthening economy. Her store would invest $800,000 in more expensive merchandise (an increase in its working capital) with the expectation that it would increase annual sales and variable expenses by $400,000 and $250,000, respectively, for three years. At the end of the three-year period, the store manager believes that the economic surge will subside; therefore, she will release the additional investment in working capital. The store manager's pay raises are largely determined by her store's return on investment (ROI), which has exceeded 22% each of the last three years.

Required:

1. Assuming the company's discount rate is 16%, calculate the net present value of the store manager's investment opportunity.
2. Calculate the annual margin, turnover, and return on investment (ROI) provided by the store manager's investment opportunity.
3. Assuming that the company's minimum required rate of return is 16%, calculate the residual income earned by the store manager's investment opportunity for each of years 1 through 3.
4. Do you think the store manager would choose to pursue this investment opportunity? Do you think the company would want the store manager to pursue it? Why?
5. Using a discount rate of 16%, calculate the present value of your residual incomes for years 1 through 3. Is your answer greater than, less than, or equal to the net present value that you computed in (1) above? Why? Support your explanation with computations.