Required information i Excel Analytics 11-1 (Static) Return on Investment (ROI) [LO11-1] [The following information applies to the questions displayed below.] Edman Company is a merchandiser that has provided the following balance sheet and income statement for this year. Assets Cash Accounts receivable Inventory Property, plant & equipment (net) Other assets Total assets Liabilities & Stockholders' Equity Accounts payable Bonds payable Common stock Retained earnings Total liabilities & stockholders' equity Beginning Balance Ending Balance 62,800. $ 150,000 160,000. 180,000 230,000 240,000 833,000 793,000 37,000 37,000 $1,322,800 $1,400,000 $ 70,000 550,000 $ 80,000 550,000 410,000 410,000 292,800 360,000 $1,322,800 $1,400,000 Sales Variable expenses: Cost of goods sold Variable selling expense Total variable expenses This Year $2,100,000 1,600,000 240,000 1,840,000 Contribution margin 660,000 Fixed expenses: Fixed selling expenses 220,000 Fixed administrative expenses 300,000 Total fixed expenses 520,000 Net operating income 140,000 Interest expense (88) 44,000 Net income before tax 96,000 Tax expense (30%) 28,800 Net income $ 67,200 Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a a brief tutorial on Charts in Excel. Excel Analytics 11-1 (Static) Part 3 3. To evaluate alternative 1, refer to the "Requirement 3 Financials" tab within your template. Assume the company streamlines it working capital management practices with the following estimated impacts: Next year's ending balance in accounts receivable decreases by $80,000 compared to its beginning balance. Next year's ending balance in inventory decreases by $120,000 compared to its beginning balance. Next year's ending balance in property, plant, and equipment (net) decreases by $40,000 compared to its beginning balance to reflect next year's depreciation expense. Next year's ending balance in accounts payable decreases by $40,000 compared to its beginning balance. Next year's ending balance in bonds payable decreases by $300,000 compared to its beginning balance to reflect a retirement of bonds payable. Next year's ending balances in other assets and common stock are the same as their beginning balances. Next year's total sales, variables expenses, fixed expenses, and net operating income are the same as this year. a. Based on the above estimated impacts, use Excel formulas to calculate ending balances as needed in column C. What is the ending balance in the following accounts? b. Create formulas within column D that calculate next year's average balances for all balance sheet accounts (except Cash which will automatically be computed for you). What is the average balance in the following accounts? c. What is the company's estimated average total liabilities and stockholders' equity for next year? Complete this question by entering your answers in the tabs below. Req 3A Req 38 Req 3C Based on the above estimated impacts, use Excel formulas to calculate ending balances as needed in column C. What is the ending balance in the following accounts? Inventory $ 120,000 Accounts payable $ 40,000 Retained earnings $ 427,200 Complete this question by entering your answers in the tabs below. Req 3A Reg 38 Req 3C Create formulas within column D that calculate next year's average balances for all balance sheet accounts (except Cash which will automatically be computed for you). What is the average balance in the following accounts? Inventory $ 180,000 Accounts payable $ 60,000 Retained earnings $ 393,600 of 10 Complete this question by entering your answers in the tabs below. Req 3A Req 38 What is the company's estimated average total liabilities and stockholders' equity for next year? Average total abilities and stockholden equity $ 1.263.600 10 Required information Excel Analytics 11-1 (Static) Return on Investment (ROI) [LO11-1] [The following information applies to the questions displayed below.) Edman Company is a merchandiser that has provided the following balance sheet and income statement for this year. Ending Balance ok Assets Cash nt Accounts receivable. Inventory ences Property, plant & equipment (net) Other assets Total assets Liabilities & Stockholders' Equity Accounts payable Bonds payable Common stock Retained earnings Total liabilities & stockholders' equity Beginning Balance $ 62,800 160,000 $ 150,000 180,000 230,000 833,000 37,000 240,000 793,000 37,000 $1,322,800 $1,400,000 $ 70,000 $ 80,000 550,000 550,000 410,000 410,000 292,800 360,000 $1,322,800 $1,400,000 10 Sales Variable expenses: Cost of goods sold Variable selling expense. Total variable expenses. Contribution margin nces Fixed expenses: Fixed selling expenses Fixed administrative expenses Total fixed expenses Net operating income Interest expense (8) Net income before tax Tax expense (30%) Net income This Year $2,500,000 1,600,000 240,000 1,840,000 660,000 220,000 300,000 520,000 140,000 44,000 96,000 28,800 67,200 Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a a brief tutorial on Charts in Excel. I Excel Analytics 11-1 (Static) Part 4 4. Refer to the "Requirement 4 DuPont Diagram" tab within your template. For alternative 1, complete the diagram by using appropriate formulas and reference cells. (In some instances your formulas and reference cells will refer to the "Requirement 3 Financials" tab.) a. Which choice shows the formulas used to compute the net operating income and average operating assets? b. What is this alternative's margin, turnover, and return on investment (ROI)? c. If the company pursues this alternative, would it cause next year's ROI to be greater than, less than, or equal to this year's ROI (as computed in Requirement 2b)? Required Information Complete this question by entering your answers in the tabs below. Req 4A Req 48 Req 4C Which choice shows the formulas used to compute the net operating income and average operating assets? Net operating Average operating assets income E21+E29 E3-E8 E3-E8 E21 E29 -E21/E29 =E3+EB -E3/E8 -E21-E29 Required information Complete this question by entering your answers in the tabs below. Req 4A RQ 48 Req 4C What is this alternative's margin, turnover, and return on investment (ROI)? (Round your answers to 2 decimal places.) Margin Turnover Return on investment (ROI) < Req 4A Req 4C > 0 Required information Complete this question by entering your answers in the tabs below. Req 4A Req 48 Req 4C If the company pursues this alternative, would it cause next year's ROI to be greater than, less than, or equal to this year's ROI (as computed in Requirement 2b)? Greater than Less than t ces Equal < Req 4B Req 4C>