Problem: Alexandra Patel, the manager of the food and Lodging division at Winter Wonderland, has hired you as a consultant to help her examine her division’s performance under several different circumstances.
Q1. Type the following format into a spread sheet to compute the Food and Lodging Divisions’s actual return on investment and residual income. Match your data entry to the rows and column shown below. Review Problem. (Hint: When entering the formulas, type = in front of the formula in the cell. Then the spreadsheet will know to compute the answer. Remember to format each cell for the type of numbers it holds, such as percentage, currency, or general.)
1 Investment Center Food and Lodging Division Actual Results
2 Actual results
3 Sales $40,000,000
4 Operating income $6,450,000
5 Average assets invested $10,000,000
6 Desired ROI 30%
7
8 Return on Investment D5/D6
9
10 Profit Margin Asset D5/D4
11
12 Turnover Residual D4/D6
13
14 Income D5-(D7*D6)
15
16
Q2. Patel would like to know how the figures would change if Food and Lodging desired ROI of 40 percent and average assets invested of $10,000,000. Revise your spreadsheet from 1 to compute the division's return on investment and residual income under those conditions.
Q3. Patel also wants to know how the figures would change if Food and Lodging had a desired ROI of 30 percent and average assets invested of $12,000,000. Revise your spreadsheet from 1 to compute the division's return on investment and residual income under those conditions.
Q4. Does the use of formatted spreadsheets simplify the computation of ROI and residual income? Do such spreadsheets make it easier to do "what-if" analyses?