PREPARING AN INCOME STATEMENT Prepare an income statement and a common-sized income statement from the following information. a Complete the steps below using cell references to given data or previous calculations, In some cases, a simple cell reference is all you need. To copy/paste a formula across a + row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. DATA Sales Cost of goods sold General and administrative expenses Depreciation expenses Interest expense Income taxes $525,000 200,000 62,000 8,000 12,000 97,200 SOLUTION Income Statement and Common-Sized Income Statement: Sales Cost of goods sold Gross profits General and administrative expenses Depreciation expense Total operating expense 2 Operating income (EBIT) 3 Interest expense Earnings before taxes 5 Taxes (40%) 5 Net income 7 3 Requirements: Important note: All calculations must be shown using cell references. Do NOT enter numerical values in the cells 2. In cell C16, input the sales amount (1 point) 3. In cell C17, input the cost of goods sold amount Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 point) 24. In cell C18, calculate the gross profits. (1 point) S. In cell B19, input the general and administrative expenses amount Note: The output of the expression or function you typed in this cell is expected as a positive number. (L point) 6. In cell B20, input the depreciation expense amount. Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 point) 7. In cell C21, calculate the total operating expense. Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 point) 8. In cell C22, calculate the operating income (EBIT). (1 point) 9. In cell C23, input the interest expense amount Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 point) 10. In cell C24, calculate the earnings before taxes. (I point) 11. In cell C25, input the taxes amount. Note: The output of the expression or function you typed in this cell is expected as a positive number. (I point) 12. In cell C26, calculate the net income. (1 point) 13. To prepare the common-sized income statement, find the ratio between each account in the income statement and the sales amount In cell D 16, divide the sales amount by the sales datum in cell Cand make an absolute cell reference to the sales datum for copying and pasting purposes. (1 polnt) 14. Copy cell D16 and paste onto the cell ranges D17D18 and D21:026. (2 points) 15. In cell D19, divide the general and administrative expenses amount by the sales datum and make an absolute cell reference to the sales datum for copying and pasting purposes. I polne) 16. Copy cell D 19 and paste onto cell D20. ( polnt) 17. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directs E . B D reference to me cen i Winch me IU IS JUURU. WURE your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest 5 appearance of the data in your formulas, usually the Given Data section. 6 DATA 7 Sales $525,000 8 Cost of goods sold 200,000 9 General and administrative expenses 62,000 10 Depreciation expenses 8,000 11 Interest expense 12,000 12 Income taxes 97,200 13 14 SOLUTION 15 Income Statement and Common-Sized Income Statement: 16 Sales 17 Cost of goods sold 18 Gross profits 19 General and administrative expenses 20 Depreciation expense 21 Total operating expense 22 Operating income (EBIT) 23 Interest expense 24 Earnings before taxes 25 Taxes (40%) 26 Net income 27 28 Requirements