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. Beginning Balance Ending Balance Assets Cash $ Accounts receivable 62,800 160,000 $ 150,000 180,000 Inventory 230,000 240,000 Property, plant & equipment (net) Other assets 833,000 793,000 37,000 37,000 Total assets $1,322,800 $1,400,000 Liabilities & Stockholders' Equity Accounts payable 70,000 80,000 Bonds payable 550,000 550,000 Common stock 410,000 410,000 Retained earnings 292,800 360,000 Total liabilities & stockholders' equity $1,322,800 $1,400,000 Sales Variable expenses: Cost of goods sold Variable selling expense. This Year $2,500,000 1,600,000 240,000 Total variable expenses 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 (8) 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. Ok 10 inces Excel Analytics 11-1 (Static) Part 5 5. To evaluate alternative 2, refer to the "Requirement 5 Financials" tab within your template. Assume the company purchases new equipment in an effort to grow sales with the following estimated impacts: Next year's sales and variable expenses increase by 5%. Next year's fixed expenses are the same as this year. Next year's ending balances in accounts receivable, inventory, and accounts payable each increase by 5% compared to their respective beginning balances. Next year's ending balance in property, plant, and equipment (net) increases by $110,000 compared to its beginning balance. This reflects the purchase of a $150,000 piece of equipment minus next year's depreciation expense of $40,000. Next year's ending balance in bonds payable decreases by $50,000 compared to its beginning balance. This reflects a bond issuance of $150,000 to purchase the equipment and a bond retirement of $200,000. Next year's ending balances in other assets and common stock are the same as their beginning balances. a. Based on the above estimated impacts, use Excel formulas to calculate the revised sales and variable expenses as needed in column B. (Hint: Your formulas should refer to information contained in the Requirement 1 Financials tab.) What are the revised amounts of sales and the variable expenses? b. 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? c. 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? d. What is the company's estimated average total liabilities and stockholders' equity for next year? d. What is the company's estimated avera Complete this question by entering your answers in the tabs below. Req 5A Req 58 Req SC Req 5D Based on the above estimated impacts, use Excel formulas to calculate the revised sales and variable expenses as needed in column B. (Hint: Your formulas should refer to information contained in the Requirement 1 Financials tab.) What are the revised amounts of sales and the variable expenses? Sales $ 2,625,000 Cost of goods sold $ 1,680,000 Variable selling expense $ 252,000 Req SA Req 58 > d. 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 5A Req 5 Req 5C Req 50 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? Accounts receivable $ 189,000 Accounts payable $ 84,000 Retained earnings S 425,100 Complete this question by entering your answers in the tabs below. Req SA Req 58 Req sc Req 5D 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? Accounts receivable $ 84,000 $ 500,000 Accounts payable Retained earnings $ 425,100 Complete this question by entering your answers in the tabs below. Req SA Req 5B Req SC Req 5D What is the company's estimated average total liabilities and stockholders' equity for next year? Average total liabilities and stockholders' equity $1,419,100