Using the accompanying Superior Office Supply trial balance, prepare an Income Statement, Statement of Retained Earnings, and Balance Sheet with the following requirements: 1. The one adjusting entry that has not yet been recorded is income tax expense for the month of September. Determine this entry and add it to the trial balance. Assume the company has an effective tax rate of 35%. Prepare each financial statement, in good form, using Excel. Good form means you should have an appropriate three-part heading, no abbreviations, and dollar signs and underscores in appropriate places. You should be proud to hand these financial statements to your banker. Use the financial statements in the Accounting Cycle Review PowerPoint file from the first day of class as a guide with the following modifications: a. Balance Sheet: Be aware that the balance sheet of the example company T-Shirts "R" Us (from the handout example in class) does not have any long-term assets or liabilities. You should prepare a classified balance sheet with appropriate subtotals for current assets and current liabilities. You can group like assets and liabilities (i.e. all cash accounts can be totaled in one line item called "Cash and equivalents" and all payroll tax liabilities including FUTA and SUTA can be totaled in one line item called "Payroll tax liabilities"). Note that the retained earnings balance on the trial balance is the beginning of the year retained earnings. b. Income Statement: The T-Shirts R Us example company reviewed in class has a very simple multi-step income statement. Your income statement will be a bit more detailed and should include appropriate subtotals for Net Sales, Gross Margin, Operating Income (separate operating expenses from non-operating expenses), Income before Tax, and Net Income. List out all expenses individually. 3. Articulate the financial statements by using cell references (i.e. there should be no hard-coded numbers in your financial statements - everything should cell reference from the trial balance or be a formula). You should print your financial statements in both standard view and formula view. (Ctrl will change the statements to formula view). All subtotals on your statements should be calculated using Excel formulas. 4. You should hand in six statements on the due date: the three financial statements and the three statements in formula view. If you wish, you can print more than one statement per page. Dr. $ 70,852.50 25,684.65 10,000.00 212,561.58 10 $ 14,500.00 179,854.65 6,200.00 160,000.00 511,000.00 1,050,657.00 14 18 329,359.00 232,421.24 19,685.32 16,834.56 8,880.76 2,225.64 79 5 Account # 1010 1020 8 1030 1100 1150 1200 1400 1500 1550 1600 1900 2000 2310 2330 20 2340 2360 2380 2500 2550 2600 2700 3910 3930 3980 30 4000 4050 5000 6000 6100 6200 6300 7000 38 8000 39 9000 Description Savings Account Checking Account Certificate of Deposit Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Expenses Land Building Equipment Accumulated Depreciation - Building & Equipment Accounts Payable Sales Tax Payable Federal Payroll Taxes Payable FUTA Payable SUTA Payable Income Taxes Payable Dividends Payable Notes Payable Current Portion of Long Term Debt Long Term Debt Common Stock Retained Earnings Dividends Declared Gross Sales Revenue Sales Discounts Cost of Goods Sold Salaries and Wage Expense Payroll Tax Expense Advertising Expense Bad Debt Expense Interest Expense Gain (loss) on Sale of Equipment Income Tax Expense 25 45,000.00 15,667.34 58,695.00 338,654.52 150,000.00 278,527.86 26 27 28 45,000.00 2,369,320.73 265,889.43 910,978.54 245,853.13 20,813.35 134,186.32 21,148.71 24,695.84 36 37 15,603.73 Using the accompanying Superior Office Supply trial balance, prepare an Income Statement, Statement of Retained Earnings, and Balance Sheet with the following requirements: 1. The one adjusting entry that has not yet been recorded is income tax expense for the month of September. Determine this entry and add it to the trial balance. Assume the company has an effective tax rate of 35%. Prepare each financial statement, in good form, using Excel. Good form means you should have an appropriate three-part heading, no abbreviations, and dollar signs and underscores in appropriate places. You should be proud to hand these financial statements to your banker. Use the financial statements in the Accounting Cycle Review PowerPoint file from the first day of class as a guide with the following modifications: a. Balance Sheet: Be aware that the balance sheet of the example company T-Shirts "R" Us (from the handout example in class) does not have any long-term assets or liabilities. You should prepare a classified balance sheet with appropriate subtotals for current assets and current liabilities. You can group like assets and liabilities (i.e. all cash accounts can be totaled in one line item called "Cash and equivalents" and all payroll tax liabilities including FUTA and SUTA can be totaled in one line item called "Payroll tax liabilities"). Note that the retained earnings balance on the trial balance is the beginning of the year retained earnings. b. Income Statement: The T-Shirts R Us example company reviewed in class has a very simple multi-step income statement. Your income statement will be a bit more detailed and should include appropriate subtotals for Net Sales, Gross Margin, Operating Income (separate operating expenses from non-operating expenses), Income before Tax, and Net Income. List out all expenses individually. 3. Articulate the financial statements by using cell references (i.e. there should be no hard-coded numbers in your financial statements - everything should cell reference from the trial balance or be a formula). You should print your financial statements in both standard view and formula view. (Ctrl will change the statements to formula view). All subtotals on your statements should be calculated using Excel formulas. 4. You should hand in six statements on the due date: the three financial statements and the three statements in formula view. If you wish, you can print more than one statement per page. Dr. $ 70,852.50 25,684.65 10,000.00 212,561.58 10 $ 14,500.00 179,854.65 6,200.00 160,000.00 511,000.00 1,050,657.00 14 18 329,359.00 232,421.24 19,685.32 16,834.56 8,880.76 2,225.64 79 5 Account # 1010 1020 8 1030 1100 1150 1200 1400 1500 1550 1600 1900 2000 2310 2330 20 2340 2360 2380 2500 2550 2600 2700 3910 3930 3980 30 4000 4050 5000 6000 6100 6200 6300 7000 38 8000 39 9000 Description Savings Account Checking Account Certificate of Deposit Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Expenses Land Building Equipment Accumulated Depreciation - Building & Equipment Accounts Payable Sales Tax Payable Federal Payroll Taxes Payable FUTA Payable SUTA Payable Income Taxes Payable Dividends Payable Notes Payable Current Portion of Long Term Debt Long Term Debt Common Stock Retained Earnings Dividends Declared Gross Sales Revenue Sales Discounts Cost of Goods Sold Salaries and Wage Expense Payroll Tax Expense Advertising Expense Bad Debt Expense Interest Expense Gain (loss) on Sale of Equipment Income Tax Expense 25 45,000.00 15,667.34 58,695.00 338,654.52 150,000.00 278,527.86 26 27 28 45,000.00 2,369,320.73 265,889.43 910,978.54 245,853.13 20,813.35 134,186.32 21,148.71 24,695.84 36 37 15,603.73