= = = 5 E Merge & Center Conditional Forma Formatting as Table Styles C1 H - G#: X Vfx A B C D E F 1 Chapter 11: Applying Excel Name: 2 3 Data 4 Sales $25,000,000 5 Net operating income $3,000,000 6 Average operating assets $10,000,000 7 Minimum required rate of retum 25% 8 9 Enter a formula into each of the green cells marked with a ? below. Once complete use the "Copy Formula" button. 10 Review Problem: Return on Investment (ROI) and Residual Income 11 12 Compute the ROI 13 Margin ? Copy Formulas 14 Turnover ? 15 ROI ? 16 17 Compute the residual income 18 Average operating assets ? 19 Net operating income ? 20 Minimum required return ? 21 Residual income ? 22 23 24 25 26 27 28 29 30 A Formulas A Req 1 Ch11 Req 2 Ch11 + PASTE Insert Forn = = = DIB 1BRS 5. Marga & Center Canon anal Format L. Formatting as Table Styles Y SOT Filter Find Select B6 A B C D M E F H T J K L Req. 1: Enter $8,000,000 for the average operating assets in the green cell to the left. If your formulas are correct, the ROI should now be 38% and the residual income $1,000,000. If not, got back to the previous sheet, correct your formulas, and use the copy formulas button until your answers are correct. 1 Chapter 11: Applying Excel Requirement 2 3 Data 4 Sales 5 Net operating income 6 Average operating assets Minimum required rate of return $25,000,000 $3,000,000 Why did the ROI increase when the average operating assets decreased? 25% Why did the residual income increase when the average operating assets decreased? ? 9 10 Review Problem: Retum on Investment (ROI) and Residual Income 11 12 Compute the ROI 13 Margin ? 14 Turnover ? 15 ROI ? 16 17 Compute the residual income 18 Average oporating assets 19 Net operating Income ? 20 Minimum required return ? 21 Residual income ? 22 23 24 25 26 26 27 28 29 30 31 Formulas Req 1 Ch11 Req 2 Ch11 + + 140 PASIR 5. Merge & Center Insert Forn = = = BIRTS 1BRS Canon anal Format L. Formatting as Table Styles SOT Filter Find a Select B4 D E F G L M Req. 2: Enter the data for requirement 2 on page 526 of the textbook in the green cells to the left. What is the ROI? What is the residual income? A B C 1 Chapter 11: Applying Excel Requirement 2 2 3 Data 4 Sales ? 5 Net operating income 7 6 Average operating assets ? 7 Minimum required rate of return ? 8 9 10 Review Problem: Retum on Investment (ROI) and Residual Income 11 12 Compute the ROI 13 Margin ? 14 Turnover ? 15 ROI ? 16 17 Compute the residual income 18 Average operating assets 7 19 Net operating income ? 20 Minimum required return ? 21 Residual income 7 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Formulas Reg 1 Ch11 Reg 2 Ch11 + Explain the relationship between ROI and the residual Income. 14C Page 256 Required: 1. Prepare a scattergraph plot that puts labor-hours on the X-axis and overhead expenses on the Y-axis. What insights are revealed by your scattergraph? 2. Use the least-squares regression method to estimate the fixed and variable components of overhead expenses. Express these estimates in the form Y = a + bX. 3. If Chavez charges her usual price of $31 per guest for the 180-guest cocktail party, how much contribution margin will she earn by serving this event? 4. How low could Chavez bid for the charity event in terms of a price per guest and still break even on the event itself? 5. The individual who is organizing the charity's fundraising event has indicated that he has already received a bid under $30 from another catering company. Do you think Chavez should bid below her normal $31 per guest price for the charity event? Why or why not? (CMA, adapted) = = = 5 E Merge & Center Conditional Forma Formatting as Table Styles C1 H - G#: X Vfx A B C D E F 1 Chapter 11: Applying Excel Name: 2 3 Data 4 Sales $25,000,000 5 Net operating income $3,000,000 6 Average operating assets $10,000,000 7 Minimum required rate of retum 25% 8 9 Enter a formula into each of the green cells marked with a ? below. Once complete use the "Copy Formula" button. 10 Review Problem: Return on Investment (ROI) and Residual Income 11 12 Compute the ROI 13 Margin ? Copy Formulas 14 Turnover ? 15 ROI ? 16 17 Compute the residual income 18 Average operating assets ? 19 Net operating income ? 20 Minimum required return ? 21 Residual income ? 22 23 24 25 26 27 28 29 30 A Formulas A Req 1 Ch11 Req 2 Ch11 + PASTE Insert Forn = = = DIB 1BRS 5. Marga & Center Canon anal Format L. Formatting as Table Styles Y SOT Filter Find Select B6 A B C D M E F H T J K L Req. 1: Enter $8,000,000 for the average operating assets in the green cell to the left. If your formulas are correct, the ROI should now be 38% and the residual income $1,000,000. If not, got back to the previous sheet, correct your formulas, and use the copy formulas button until your answers are correct. 1 Chapter 11: Applying Excel Requirement 2 3 Data 4 Sales 5 Net operating income 6 Average operating assets Minimum required rate of return $25,000,000 $3,000,000 Why did the ROI increase when the average operating assets decreased? 25% Why did the residual income increase when the average operating assets decreased? ? 9 10 Review Problem: Retum on Investment (ROI) and Residual Income 11 12 Compute the ROI 13 Margin ? 14 Turnover ? 15 ROI ? 16 17 Compute the residual income 18 Average oporating assets 19 Net operating Income ? 20 Minimum required return ? 21 Residual income ? 22 23 24 25 26 26 27 28 29 30 31 Formulas Req 1 Ch11 Req 2 Ch11 + + 140 PASIR 5. Merge & Center Insert Forn = = = BIRTS 1BRS Canon anal Format L. Formatting as Table Styles SOT Filter Find a Select B4 D E F G L M Req. 2: Enter the data for requirement 2 on page 526 of the textbook in the green cells to the left. What is the ROI? What is the residual income? A B C 1 Chapter 11: Applying Excel Requirement 2 2 3 Data 4 Sales ? 5 Net operating income 7 6 Average operating assets ? 7 Minimum required rate of return ? 8 9 10 Review Problem: Retum on Investment (ROI) and Residual Income 11 12 Compute the ROI 13 Margin ? 14 Turnover ? 15 ROI ? 16 17 Compute the residual income 18 Average operating assets 7 19 Net operating income ? 20 Minimum required return ? 21 Residual income 7 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Formulas Reg 1 Ch11 Reg 2 Ch11 + Explain the relationship between ROI and the residual Income. 14C Page 256 Required: 1. Prepare a scattergraph plot that puts labor-hours on the X-axis and overhead expenses on the Y-axis. What insights are revealed by your scattergraph? 2. Use the least-squares regression method to estimate the fixed and variable components of overhead expenses. Express these estimates in the form Y = a + bX. 3. If Chavez charges her usual price of $31 per guest for the 180-guest cocktail party, how much contribution margin will she earn by serving this event? 4. How low could Chavez bid for the charity event in terms of a price per guest and still break even on the event itself? 5. The individual who is organizing the charity's fundraising event has indicated that he has already received a bid under $30 from another catering company. Do you think Chavez should bid below her normal $31 per guest price for the charity event? Why or why not? (CMA, adapted)
