DATA

Sales $$525,000$

Cost of goods sold $200,000$

General and administrative expenses $62,000$

Depreciation expenses $8,000$

Interest expense $12,000$

Income taxes $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

Operating income $($EBIT$)$

Interest expense

Earnings before taxes

Taxes $(40\%)$

Net income

Requirements:

$"1.$ Start Excel. Download and open the workbook named:

Keown$\_$Martin$\_$Petty$\_$Problem$\_3-3\_$Start.

Important note: All calculations must be shown using cell references. Do NOT enter numerical values in the cells.

$"$

$2.$ In cell C$16,$ input the sales amount. $(1$ point$)$

$"3.$ In cell C$17,$ 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$)"$

$4.$ In cell C$18,$ calculate the gross profits. $(1$ point$)$

$"5.$ In cell B$19,$ 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. $(1$ point$)"$

$"6.$ In cell B$20,$ 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 C$21,$ 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 C$22,$ calculate the operating income $($EBIT$).(1$ point$)$

$"9.$ In cell C$23,$ 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 C$24,$ calculate the earnings before taxes. $(1$ point$)$

$"11.$ In cell C$25,$ input the taxes amount.

Note: The output of the expression or function you typed in this cell is expected as a positive number. $(1$ point$)"$

$12.$ In cell C$26,$ 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 C$7$ and make an absolute cell reference to the sales datum for copying and pasting purposes. $(1$ point$)"$

$14.$ Copy cell D$16$ and paste onto the cell ranges D$17$:D$18$ and D$21$:D$26.(2$ points$)$

$15.$ In cell D$19,$ 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. $(1$ point$)$

$16.$ Copy cell D$19$ and paste onto cell D$20.(1$ point$)$

$($#$4,3-3)$