O Search Home Insert Draw Page Layout Formulas Data Review View Help OTECTED VIEW Be careful-files from the Intermet car contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editin C F G roblem 3-13 Proe alance sheet and income statement for the Wamer Company from the following scrambled list of items. below using cell references to given data or previous calculations. In some cases, a simple cell reference Compicic th Is all you necd. 10 copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. Ifa specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data inte a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Given Data: Depreciation Expense Cash $66,000.00 $225,000,00 Long-Term Debt $334,000,00 Sales S573.000.00 Accounts Payable General & Administrative Expense Buildings & Equipment Notes Payable $102,000,00 $79,000.00 $895,000,00 $75,000,00 Accounts Receivable S167.500.00 Interest Expense Accrued Expenses $4.750,00 $7,900.00 Common Stock $289,000,00 Cost of Goods Sold $297,000.00 Inventory 599 300.00 Taxes S50,500.00 $263,000.00 21 Accur Depreciation 22 Taxes Payable Retained Eamings 553,000,00 23 $262.900.00 241 a. Prepare an income statement for the Wamer Company. 25 26 28 Wamer Company 28 Income Statement Sales 3-13 F1 F2 F3 F4 ES FE AutoSave lynne Titman Problem 3-13 Start Protected View Exc File Home Insert Draw Page Layout Formulas Data Review O Search PROTECTED VIEW Be careful-files from the Intrnet car contain vinuses Unless you need to edit, it's safer to stay in Protected View View Help Enable Editing fi G 26 27 Wamer Company 28 Income Statement Sales 29 Cost of Goods Sold Gross Profit 30 31 xpenses General & Administrative Expense Depreciation Expense Total Operating Expenses Net Operating Income eterest Expense 32 33 34 31 Earnings Before Taxes es Net Income AT 41 b. Prepare a balance sheet for the Wamer Company 42 43 Warner Company 44 45 Balance Sheet Assets Current Assets Cash Inventory Accounts Receivable 45 Total Current Assets 53 Fixed Assets Buildings & Equipment Less: Accumalated Depreciation Buildings & Equipment, set 54 55 56 Tatal Assets Liabilities Cerrent Liabilities. Accounts Pavable Taxes Payable Accrued Expeuses Notes Payable 3-13 Esc F5 E1 F2 F3 F4 F6 e Insert Draw Page Layout Formulas Data Review View O Search Help D VIEW Be careful-files from the Internet ce contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing fr C D F G In cells E29 and E30, by using cell references, determine the sales and cost of goods sold. 2 Note: The output of the expression or function you typed in cell E30 for the cost of goods sold is expected as a positive mber. (1 pt.) 11 E31, by using cell references, calculate the gross profit. (1 pt.) In cell E33 and E34, by using cell references, determine the general and administrative expense and depreciation expense. 4 Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. (1 pt.) 5 1n cell E35, by using cell references, calculate the total operating expenses. (1 pt.) 6 In cell E36, by using cell references, calculate the net operating income. (1 pt.) In cell E37, by using a cell reference, determine the interest expense. Note: The output of the expression or fanction you typed in this cell is expected as a negative number. (I pt.) 8 In cell E38, by using cell references, calculate the earnings before taxes. (1 pt.) In cell E39, by using a cell reference, determine the taxes. 9 Note: The output of the expression or function you typed in this cell is expected as a negative number. (I pt.) 10 In cell E40, by using cell references, calculate the net income. (1 pt.) Balance Sheet 11 In cells E48, E49, and E50, by using cell references, determine the cash inventory, and accounts receivable. (I pt.) 12 In cell E51, by using cell references, calculate the total current assets. (1 pt.) In cells E53 and E54, by using cell references, determine the buildings and equipment asset and accumutated depreciation 13 Note: The outpat of the expression or function you typed in cell E54 for the accummlated depreciation is expected as a ve number. (1 14 In cell E55. by using cell references, calculate the net buildings and equipment asset. Use cels ES3 and E54. (1 pt.) 15 In cell E56, by using cell references, calculate the total assets. (I pt.) In cells E59, E60o, E61, and E62, by using cell references, enter the accounts payable, taes payable, accrocd expenses, and 16 notes payable. (1 pt.) 17 In cell E63, by using cell references, calculate the total current liabilitics. (1 pt.) 18 In cell E64, by using a cell refcrence, determine the long-term debt. (1 pt.) 19 In cell E65, by using cell references, calculate the total liabilities (1 pt.) 20 In cells FE67 and E68, by using cell references, determine the commoe stock and retained carninas (1 pt.) 3-13 F7 F6 FS F4 F1 F2 C E F 8 In cell E38, by using cell references, calculate the earmings before taxes. (1 pt.) In cell E39, by using a cell reference, determine the taxes. 9 Note: The output of the expression or function you typed in this cell is expected cell E40, by using cell references, calculate the net income. (1 pt.) negative number. ( as a Balance Sheet 11 In cells E48, E49, and E50, by using cell references, determine the cash, inventory, and accounts receivabl 12 In cell E51, by using cell references, calculate the total current assets. (1 pt.) In cells E53 and E54, by using cell references, determine the buildingss and equipment asset and accumulatee 13 Note: The output of the expression negative number. (1 pt.) or function you typed in cell E54 for the accumulated depreciation is exp 14 In cell E55, by using cell references, calculate the net buildings and equipment asset. Use cells E53 and E54 15 In cell E56, by using cell references, calculate the total assets. (1 pt.) In cells E59, E60, E61, and E62, by using cell references, enter the accounts payable, taxes payable, accrued e 16 Totes payable. (1 pt.) 17 In cell E63, by using cell references, calculate the total current liabilities. (1 pt.) 18 In cell E64, by using a cell reference, determine the long-term debt. (1 pt.) 19 In cell E65, by using cell references, calculate the total liabilities. (1 pt.) 20 In cells E67 and E68, by using cell references, determine the common stock and retained earnings. (1 pt.) 21 In cell E69, by using cell references, calculate the total shareholders' equity. (1 pt.) 22 In cell E70, by using cell references, calculate the total liabilities and shareholders' equity. (1 pt.) 23 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed 3-13 C D E Net Income o. e a balance sheet for the Warner Company Wamer Company Balance Sheet Assets Current Assets Cash Inventory Accounts Receivable Total Current Assets Fixed Assets Buildings& Equipment Less: Accumulated Depreciation Buildings & Equipment, net Total Assets Liabilities Current Liabilities Accounts Payable Taxes Payable Accrued Expenses Notes Payable Total Current Liabilities Long-Term debt Total Liabilities Shareholders' Equity Common Stock Retained Eamings Total Shareholders' Equity Total Liabilities & Shareholders' Equity Requirements 1 Start Excel