Please answer use excel and show excel formulas
8:09 AM Tue Nov 10 43%O PB10-1 Calculating Return on Investment, Residual Income, Determining Effect of Changes in Sales, Expenses, Invested Assets, Hurdle Rate on Each QLO 10-4, 10-5 Escuda Company has the following information available for the past year: Fence Division Brick Division Segment sales $2,200,000 $1,300,000 Segment cost of goods sold and operating expenses 1,700,000 1,000,000 Segment income $ 500,000 $ 300,000 Average invested assets $ 4,100,000 $3,000,000 The company's hurdle rate is 12 percent. 1. Determine Escuda's return on investment (ROI) and residual income for each division for last year. 2. Recalculate Escuda's ROI and residual income for both divisions for each independent situation that follows: a. Net operating income increases by 10 percent. b. Net operating income decreases by 10 percent. c. The company invests $400,000 in each division, an amount that generates $80,000 additional income per division. d. Escuda changes its hurdle rate to 9 percent. Page 478 PB10-2 Calculating Unknowns, Predicting Relationship among Return on B C E F G H J 1 Chapter 10 Lab - PB 10-1, page 477 Enter formulas or descriptions in these fields 2 3 4 5 Given data: Fence Brick 7 Fence $2,200,000 $1,700,000 Sales Cost of Goods Sold and Op. Exp. Income Brick $1,300,000 $1,000,000 $300,000 1 Margin Turnover $500,000 ROI, Margin - Turnover Margin Turnover Average Assets $4,100,000 3,000,000 ROI, NOI/AOA Operating Income/Avg. Operating Assets Residual Income Current Hurtle Rate New Hurtle Rate 12% 9% 12% 9% 2.a. Operating income increase ROI Residual income 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 a. Operating income increase b. Operating income decrease c. Additional investment d. Additional income 10% 10% $400,000 $80,000 10% 10% $400,000 $80,000 2.b. Operating income decrease ROI Residual Income 0.00% RI = Operating Incom-(Hurdgle rate * avg. op. assests) 2.c. Add'l. Invest & Income ROI Residual Income To make the formulas simpler, calculate intermediate values here: Operating Income Increase 28 29 30 31 Operating Income Decrease 2.d. New rate, old data ROI Residual Income 32 op inc (hurdle rate AOA) 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 Sheet1 + 8:09 AM Tue Nov 10 43%O PB10-1 Calculating Return on Investment, Residual Income, Determining Effect of Changes in Sales, Expenses, Invested Assets, Hurdle Rate on Each QLO 10-4, 10-5 Escuda Company has the following information available for the past year: Fence Division Brick Division Segment sales $2,200,000 $1,300,000 Segment cost of goods sold and operating expenses 1,700,000 1,000,000 Segment income $ 500,000 $ 300,000 Average invested assets $ 4,100,000 $3,000,000 The company's hurdle rate is 12 percent. 1. Determine Escuda's return on investment (ROI) and residual income for each division for last year. 2. Recalculate Escuda's ROI and residual income for both divisions for each independent situation that follows: a. Net operating income increases by 10 percent. b. Net operating income decreases by 10 percent. c. The company invests $400,000 in each division, an amount that generates $80,000 additional income per division. d. Escuda changes its hurdle rate to 9 percent. Page 478 PB10-2 Calculating Unknowns, Predicting Relationship among Return on B C E F G H J 1 Chapter 10 Lab - PB 10-1, page 477 Enter formulas or descriptions in these fields 2 3 4 5 Given data: Fence Brick 7 Fence $2,200,000 $1,700,000 Sales Cost of Goods Sold and Op. Exp. Income Brick $1,300,000 $1,000,000 $300,000 1 Margin Turnover $500,000 ROI, Margin - Turnover Margin Turnover Average Assets $4,100,000 3,000,000 ROI, NOI/AOA Operating Income/Avg. Operating Assets Residual Income Current Hurtle Rate New Hurtle Rate 12% 9% 12% 9% 2.a. Operating income increase ROI Residual income 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 a. Operating income increase b. Operating income decrease c. Additional investment d. Additional income 10% 10% $400,000 $80,000 10% 10% $400,000 $80,000 2.b. Operating income decrease ROI Residual Income 0.00% RI = Operating Incom-(Hurdgle rate * avg. op. assests) 2.c. Add'l. Invest & Income ROI Residual Income To make the formulas simpler, calculate intermediate values here: Operating Income Increase 28 29 30 31 Operating Income Decrease 2.d. New rate, old data ROI Residual Income 32 op inc (hurdle rate AOA) 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 Sheet1 +
