See document "Excel Project Data - Adj Trial Balance". You are provided with an adjusted trial balance for Kelley Consulting. Use this data to complete the following: 1. In Excel, create three separate worksheets within your workbook (excel file) and rename the sheets: IS, OE and BS. Save the Excel workbook as Excel_Project_Your Name. 2. Use the data provided to create the company's three main financial statements. a. IS worksheet - Create the Income Statement b. OE worksheet - Create the Statement of Owners Equity C. BS worksheet - Create the Balance Sheet 3. See document "Excel Project Data - Fin Statement Template" for reference when completing the steps below. 4. Income Statement specific requirements a. Follow heading, underlining and column formatting as presented. b. Total Expenses must be a FORMULA I. You can add up the individual cells or use the SUM function 1. See youtube tutorials on how to create these basic formulas c. Net Income/Loss must be a FORMULA 1. You will be subtracting Total Expenses from Fees Earned 5. Statement of Owners Equity specific requirements a. Follow heading underlining and column formatting as presented. b. Net Income must "flow" from the IS worksheet 1. You must use a cell reference to "push" Net Income into the correct cell on the OE worksheet 1. See youtube tutorial on cell referencing. c. Increase in owner's equity must be a formula. d. Ending Capital must be a formula that adds beginning capital to Increase in owner's equity. 6. Balance Sheet specific requirements a. Follow heading, underlining and column formatting as presented. b. Total current assets must be a formula. c. Total property, plant and equipment must be a formula. It will be the book value of the Office Equipment d. Total Assets must be a formula e. Total liabilities must be a formula f. Capital under Owners Equity section must be a cell reference from the OE worksheet 7. Total Liabilities and Owners Equity must be a formula 4-5g Step 7. Preparing an Adjusted Trial Balance After the adjustments have been journalized and posted, an adjusted trial balance is prepared to verify the equality of the total of the debit and credit balances. This is the last step before preparing the financial statements. If the adjusted trial balance does not balance, an error has occurred and must be found and corrected. The adjusted trial balance for Kelly Consulting as of April 30, 2045, is shown in Exhibit 14. Exhibit 14 Adjusted Trial Balance, Kelly Consulting Kelly Consulting Adjusted Trial Balance April 30, 2045 Account No. Cash.. Accounts Receivable. Supplies....... Prepaid Rent... Prepaid Insurance Office Equipment... Accumulated Depreciation, Accounts Payable... Salaries Payable... Unearned Fees... Kelly Pitney, Capital.. Kelly Pitney, Drawing Fees Earned... Salary Expense.... Rent Expense... Supplies Expense.. Depreciation Expense... Insurance Expense... Miscellaneous Expense, 11 12 14 15 16 18 19 21 22 23 31 32 41 51 52 53 54 55 59 Debit Credit Balances Balances 22,100 3,400 1,350 3,200 1,500 14,500 330 800 120 2,500 30,000 6,000 23,450 1,620 1,600 850 330 300 450 57,200 57,200 4-5h Step 8. Preparing the Financial Statements The most important outcome of the accounting cycle is the financial statements. The income statement is prepared first, followed by the statement of owner's equity and then the balance sheet. The statements can be prepared directly from the adjusted trial balance, the end-of-period spreadsheet, or the ledger. The net income or net loss shown on the income statement is reported on the statement of owner's equity along with any additional investments by the owner and any withdrawals. The ending owner's capital is reported on the balance sheet and is added with total liabilities to equal total assets. The financial statements for Kelly Consulting are shown in Exhibit 15. Kelly Consulting earned net income of $18,300 for April. As of April 30, 2045, Kelly Consulting has total assets of $45,720, total liabilities of $3,420, and total owner's equity of $42,300. Exhibit 15 Financial Statements, Kelly Consulting Kelly Consulting Income Statement For the Month Ended April 30, 2045 $23,450 Fees earned..... Expenses: Salary expense...... Rent expense....... Supplies expense... Depreciation expense.... Insurance expense... Miscellaneous expense.... Total expenses..... Net Income.... $1,620 1,600 850 330 300 450 5,150 $18,300 Kelly Consulting Statement of Owner's Equity For the Month Ended April 30, 2045 Kelly Pitney, capital, April 1, 2045...... Investment during the month... Net Income for the month...... Withdrawals... Increase in owner's equity... Kelly Pitney, capital, April 30, 20Y5..... $30,000 18,300 (6,000) 42,300 $42,300 Kelly Consulting Balance Sheet April 30, 2045 *** $22,100 3400 1,350 3,200 1,500 $31,550 Assets Current assets: Cash...... Accounts receivable..... Supplies.......... Prepaid rent... Prepaid insurance... Total current assets..... Property, plant, and equipment: Office equipment...... Less accumulated depreciation...... Total property, plant, and equipment... Total assets...... Liabilities Current liabilities: Accounts payable...... Salaries payable...... Unearned fees...... Total liabilities....... $14,500 330 *** 14,170 $45,720 $ 800 120 2,500 $ 3,420 Owner's Equity Kelly Pitney, capital..... Total liabilities and owner's equity............. 42,300 $45,720