Excel-based Project Data/Instructions

Sandra Martin owns and operates Accounting Solutions. The attached Excel file includes the unadjusted trial balance for 12/31/2018.

The following additional facts are collected for use in making adjusting entries prior to preparing financial statements for the year ended 12/31/2018.

1. The December 31 inventory count of supplies shows $875 still available.
2. Six months have expired since the 24-month insurance premium of $5,400 was paid in advance.
3. As of December 31, an employee has not been paid for 3 days of work at $200 per day.
4. The company's computers are expected to have a three-year life with no salvage value.
5. The office furniture is expected to have a seven-year life with no salvage value
6. The company earned $2,400 of the unearned revenue during the year.

Requirements

1. Prepare adjusting entries to reflect accrual-based account balances for December 31, 2018. Use the General Journal Adj Entries tab in Excel. Post those entries to the accounts in the ledger. Use the General Ledger tab in the Excel workbook. (6 points)
2. Prepare an adjusted trial balance as of December 31, 2018. Use the Adjusted TB-Dec 2018 tab in the Excel workbook. (5 points)
3. Prepare a worksheet for December 31, 2018. Use the Worksheet tab in the Excel workbook. (10 points)
4. Prepare an income statement for the year ended December 31, 2018. Prepare a statement of changes in owners equity and balance sheet as of December 31, 2018. Use the appropriate tabs in the Excel workbook. (15 points)
5. Record and post the necessary closing entries for Accounting Solutions. Use the General Journal-Closing Ent tab in the Excel workbook. (4 points)
6. Prepare a post-closing trial balance as of December 31, 2018. Use the Post Closing TB tab in the Excel workbook. (5 points)
7. Typed paragraph, in Word, including the following: observations on the comparing the use of a general ledger computer system and a manual Excel system, recommendations for Accounting Solutions to help them improve their financial position, recommendations to help their accounting system, and what you have learned from this project. (5 points)

Grading Criteria

• The Excel file submitted must be completed with your own efforts. Copying and sharing files or portions of files between students can result in receiving a zero for the portion(s) copied.
• Adjusted trial balance and post-closing trial balance numbers should be linked to the general ledger.
• The worksheet unadjusted trial balance columns should be linked to the unadjusted trial balance. The adjustments columns should be linked to the adjusting entries in the general journal. The numbers in the adjusted trial balance, income statement and balance sheet and statement of owners equity columns should be completed using formulas. Formulas should be used for all math functions (adding and subtracting).
• The financial statements (Income Statement, Balance Sheet and Statement of Owners Equity) should be completed using links to the worksheet and formulas.
• Word document, to be uploaded with Excel file, in the proper format.

Check Figures for the Excel Project:

(3) Adjusted trial balance totals, $107,995

(4) Total assets, $35,335

(6) Post-closing trial balance totals, $39,335

fx 214 _ A x B C D EG Name Trial Balance Debit Credit 9,870 8,540 2.725 5,400 7,000 9,000 6,825 Accounting Solutions Unadjusted Trial Balance 12/31/2018 Acct # 101 Cash 106 Accounts Receivable 126 Supplies 128 Prepaid Insurance 163 Office Furniture 164 Accumulated Depreciation - Office Furniture 167 Computer Equipment 168 Accumulated Depreciation - Computer Equipment 201 Accounts Payable 210 Wages Payable 236 Unearned revenue 301 S. Martin, Capital 302 S. Martin, Withdrawals 403 Accounting services revenue 612 Depreciation expense - Office Furniture 613 Depreciation expense - Computer Equipment 623 Wages Expense 637 Insurance Expense 640 Rent Expense 652 Supplies Expense 655 Advertising Expense 676 Mileage Expense 677 Miscellaneous Expense 4,750 15,000 2,100 76,820 34,250 14,400 3,250 2,675 2,160 2,025 Totals 103,395 103,395 GENERAL LEDGER Cash P.R. Explanation DEBIT CREDIT Date 31-Dec ACCOUNT NO. 101 Bal-Debit Bal-Credit 9,870 8 Accounts Receivable P.R. DEBIT Explanation CREDIT NO COW Date 31-Dec ACCOUNT NO. 106 Bal-Debit Bal-Credit 8,540 Supplies P.R. Explanation DEBIT CREDIT 19 Date 31-Dec ACCOUNT NO. 126 Bal-Debit Bal-Credit 2,725 Prepaid Insurance Explanation CREDIT 25 Date 31-Dec ACCOUNT NO. 128 Bal-Debit Bal-Credit 5,400 Office Furniture P.R. DEBIT Explanation CREDIT 31 Date 32 31-Dec ACCOUNT NO. 163 Bal-Debit Bal-Credit 7,000 Accumulated Depreciation - Office Furniture Explanation P.R. DEBIT CREDIT ACCOUNT NO. 164 Bal-Debit Bal-Credit Date 31-Dec Computer Equipment P.R. DEBIT Explanation CREDIT 12 13 Date 31-Dec ACCOUNT NO. 167 Bal-Debit Bal-Credit 9,000 7 Accumulated Depreciation - Computer Equipment Explanation P.R. DEBIT CREDIT ACCOUNT NO. 168 Bal-Debit Bal-Credit Date 31-Dec mm Om Accounts Payable P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 201 Bal-Debit Bal-Credit 6,825 in m m Wages Payable P.R. DEBIT ACCOUNT NO. 210 Bal-Debit Bal-Credit Date Explanation CREDIT Unearned Revenue P.R. DEBIT Date Explanation L CREDIT ACCOUNT NO. 236 Bal-Debit Bal-Credit 4,750 31-Dec m in om mom S Martin, Capital P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 301 Bal-Debit Bal-Credit 15,000 S Martin, Withdrawals P.R. DEBIT Explanation CREDIT in inn m m Onlm Date 31-Dec ACCOUNT NO. 302 Bal-Debit Bal-Credit 2,100 Accounting Services Revenue Explanation P.R. DEBIT Date CREDIT ACCOUNT NO. 403 Bal-Debit Bal-Credit 76,820 31-Dec ini mm 00 Depreciation Expense - Office Furniture Explanation P.R. DEBIT L ACCOUNT NO. 612 Bal-Debit Bal-Credit Date CREDIT 92 93 94 95 96 Depreciation Expense - Computer Equipment Explanation P. R D EBIT CREDIT ACCOUNT NO. 613 Bal-Debit Bal-Credit Date 98 Wages Expense P.R. DEBIT CREDIT ACCOUNT NO. 623 Bal-Debit Bal-Credit 34,250 99 100 101 102 Date Explanation 103 31-Dec 104 105 106 107 108 Date Explanation 109 Year end JE 110 Insurance Expense P.R. DEBIT ACCOUNT NO. 637 Bal-Debit Bal-Credit CREDIT 111 Explanation Rent Expense P.R. DEBIT CREDIT ACCOUNT NO. 640 Bal-Debit Bal-Credit 14,400 715 112 113 114 Date 31-Dec 116 117 118 119 120 Date 121 31-Dec Supplies Expense P.R. DEBIT Explanation CREDIT ACCOUNT NO. 652 Bal-Debit Bal-Credit 3,250 122 123 124 125 Advertising Expense P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 655 Bal-Debit Bal-Credit 2,675 127 129 130 131 Mileage Expense P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 676 Bal-Debit | Bal-Credit 2,160 133 134 135 136 137 138 Date Miscellaneous Expense P.R. DEBIT Explanation CREDIT ACCOUNT NO. 677 Bal-Debit | Bal-Credit 2,025 139 31-Dec 140 141 142 143 144 Date 145 31-Dec 146 Income Summary P.R. DEBIT ACCOUNT NO. 801 Bal-Debit Bal-Credit Explanation CREDIT 147 148 149 150 are Name General Journal GENERAL JOURNAL Credit Date 31-Dec Account Titles and Explanation Dr. Supplies Expense Cr. Supplies Debit 1,850 1,850 31-Dec 1,350 Insurance Expense Cr. Prepaid Insurance 1,350 Dr. Wages Expense Cr. Wages Payable 600 3,000 Dr. Depritiation Expense Cr. Accumaltion Deprtiation CE 3,000 B C D EG Name Trial Balance Debit Credit 9,870 8,540 875 Accounting Solutions Adjusted Trial Balance 12/31/2018 Acct # 101 Cash 106 Accounts Receivable 126 Supplies 128 Prepaid Insurance 163 Office Furniture 164|Accumulated Depreciation - Office Furniture 167 Computer Equipment 168 Accumulated Depreciation - Computer Equipment 201 Accounts Payable 210 Wages Payable 236 Unearned revenue 301 S. Martin, Capital 302 S. Martin, Withdrawals 403 Accounting services revenue 612 Depreciation expense - Office Equipment 613 Depreciation expense - Computer Equipment 623 Wages Expense 637 Insurance Expense 640 Rent Expense 652 Supplies Expense 655 Advertising Expense 676 Mileage Expense 677 Miscellaneous Expense Totals 19,285 Accounting Solutions Work Sheet For Month Ended December 31, 2018 Balance Sheet & Statement of Owner's Equity Dr. Cr. Adjusted Trial Balance Dr. Cr. Adjustments Dr. Cr. Income Statement Dr. cr. Account Titles Cash Accounts Receivable 1 Supplies Prepaid Insurance T Office Furniture Accumulated Depreciation - Office F Computer Equipment Accumulated Depreciation - Comput Accounts Payable Wages Payable Unearned revenue S. Martin, Capital T S. Martin, Withdrawals Accounting services revenue I Depreciation expense - Office Equipl Depreciation expense - Computer El Wages Expense Insurance Expense Rent Expense Supplies Expense Advertising Expense Mileage Expense - Miscellaneous Expense Unadjusted Trial Balance Dr. Cr. 9,870 - 8 ,540 - 2,725 2.725 - 5 ,400 7.000 . 9,000 . 6,825 - - 4.750 - 15,000 2,100 - 76,820 . TILSLLLLLL 34,250 14,400 3,250 2,675 2,160 2,025 103,395 103,395 D E F G Statement of Owner's Equity and Balance Sheet OWN Accounting Solutions Statement of Changes in Owner's Equity For Month Ended December 31, 2018 Accounting Solutions Balance Sheet December 31, 2018 Name General Journal GENERAL JOURNAL Date Account Titles and Explanation R. Debit Credit Company Name Post-Closing Trial Balance Date, Year Debit Credit It Totals mm fx 214 _ A x B C D EG Name Trial Balance Debit Credit 9,870 8,540 2.725 5,400 7,000 9,000 6,825 Accounting Solutions Unadjusted Trial Balance 12/31/2018 Acct # 101 Cash 106 Accounts Receivable 126 Supplies 128 Prepaid Insurance 163 Office Furniture 164 Accumulated Depreciation - Office Furniture 167 Computer Equipment 168 Accumulated Depreciation - Computer Equipment 201 Accounts Payable 210 Wages Payable 236 Unearned revenue 301 S. Martin, Capital 302 S. Martin, Withdrawals 403 Accounting services revenue 612 Depreciation expense - Office Furniture 613 Depreciation expense - Computer Equipment 623 Wages Expense 637 Insurance Expense 640 Rent Expense 652 Supplies Expense 655 Advertising Expense 676 Mileage Expense 677 Miscellaneous Expense 4,750 15,000 2,100 76,820 34,250 14,400 3,250 2,675 2,160 2,025 Totals 103,395 103,395 GENERAL LEDGER Cash P.R. Explanation DEBIT CREDIT Date 31-Dec ACCOUNT NO. 101 Bal-Debit Bal-Credit 9,870 8 Accounts Receivable P.R. DEBIT Explanation CREDIT NO COW Date 31-Dec ACCOUNT NO. 106 Bal-Debit Bal-Credit 8,540 Supplies P.R. Explanation DEBIT CREDIT 19 Date 31-Dec ACCOUNT NO. 126 Bal-Debit Bal-Credit 2,725 Prepaid Insurance Explanation CREDIT 25 Date 31-Dec ACCOUNT NO. 128 Bal-Debit Bal-Credit 5,400 Office Furniture P.R. DEBIT Explanation CREDIT 31 Date 32 31-Dec ACCOUNT NO. 163 Bal-Debit Bal-Credit 7,000 Accumulated Depreciation - Office Furniture Explanation P.R. DEBIT CREDIT ACCOUNT NO. 164 Bal-Debit Bal-Credit Date 31-Dec Computer Equipment P.R. DEBIT Explanation CREDIT 12 13 Date 31-Dec ACCOUNT NO. 167 Bal-Debit Bal-Credit 9,000 7 Accumulated Depreciation - Computer Equipment Explanation P.R. DEBIT CREDIT ACCOUNT NO. 168 Bal-Debit Bal-Credit Date 31-Dec mm Om Accounts Payable P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 201 Bal-Debit Bal-Credit 6,825 in m m Wages Payable P.R. DEBIT ACCOUNT NO. 210 Bal-Debit Bal-Credit Date Explanation CREDIT Unearned Revenue P.R. DEBIT Date Explanation L CREDIT ACCOUNT NO. 236 Bal-Debit Bal-Credit 4,750 31-Dec m in om mom S Martin, Capital P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 301 Bal-Debit Bal-Credit 15,000 S Martin, Withdrawals P.R. DEBIT Explanation CREDIT in inn m m Onlm Date 31-Dec ACCOUNT NO. 302 Bal-Debit Bal-Credit 2,100 Accounting Services Revenue Explanation P.R. DEBIT Date CREDIT ACCOUNT NO. 403 Bal-Debit Bal-Credit 76,820 31-Dec ini mm 00 Depreciation Expense - Office Furniture Explanation P.R. DEBIT L ACCOUNT NO. 612 Bal-Debit Bal-Credit Date CREDIT 92 93 94 95 96 Depreciation Expense - Computer Equipment Explanation P. R D EBIT CREDIT ACCOUNT NO. 613 Bal-Debit Bal-Credit Date 98 Wages Expense P.R. DEBIT CREDIT ACCOUNT NO. 623 Bal-Debit Bal-Credit 34,250 99 100 101 102 Date Explanation 103 31-Dec 104 105 106 107 108 Date Explanation 109 Year end JE 110 Insurance Expense P.R. DEBIT ACCOUNT NO. 637 Bal-Debit Bal-Credit CREDIT 111 Explanation Rent Expense P.R. DEBIT CREDIT ACCOUNT NO. 640 Bal-Debit Bal-Credit 14,400 715 112 113 114 Date 31-Dec 116 117 118 119 120 Date 121 31-Dec Supplies Expense P.R. DEBIT Explanation CREDIT ACCOUNT NO. 652 Bal-Debit Bal-Credit 3,250 122 123 124 125 Advertising Expense P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 655 Bal-Debit Bal-Credit 2,675 127 129 130 131 Mileage Expense P.R. DEBIT Explanation CREDIT Date 31-Dec ACCOUNT NO. 676 Bal-Debit | Bal-Credit 2,160 133 134 135 136 137 138 Date Miscellaneous Expense P.R. DEBIT Explanation CREDIT ACCOUNT NO. 677 Bal-Debit | Bal-Credit 2,025 139 31-Dec 140 141 142 143 144 Date 145 31-Dec 146 Income Summary P.R. DEBIT ACCOUNT NO. 801 Bal-Debit Bal-Credit Explanation CREDIT 147 148 149 150 are Name General Journal GENERAL JOURNAL Credit Date 31-Dec Account Titles and Explanation Dr. Supplies Expense Cr. Supplies Debit 1,850 1,850 31-Dec 1,350 Insurance Expense Cr. Prepaid Insurance 1,350 Dr. Wages Expense Cr. Wages Payable 600 3,000 Dr. Depritiation Expense Cr. Accumaltion Deprtiation CE 3,000 B C D EG Name Trial Balance Debit Credit 9,870 8,540 875 Accounting Solutions Adjusted Trial Balance 12/31/2018 Acct # 101 Cash 106 Accounts Receivable 126 Supplies 128 Prepaid Insurance 163 Office Furniture 164|Accumulated Depreciation - Office Furniture 167 Computer Equipment 168 Accumulated Depreciation - Computer Equipment 201 Accounts Payable 210 Wages Payable 236 Unearned revenue 301 S. Martin, Capital 302 S. Martin, Withdrawals 403 Accounting services revenue 612 Depreciation expense - Office Equipment 613 Depreciation expense - Computer Equipment 623 Wages Expense 637 Insurance Expense 640 Rent Expense 652 Supplies Expense 655 Advertising Expense 676 Mileage Expense 677 Miscellaneous Expense Totals 19,285 Accounting Solutions Work Sheet For Month Ended December 31, 2018 Balance Sheet & Statement of Owner's Equity Dr. Cr. Adjusted Trial Balance Dr. Cr. Adjustments Dr. Cr. Income Statement Dr. cr. Account Titles Cash Accounts Receivable 1 Supplies Prepaid Insurance T Office Furniture Accumulated Depreciation - Office F Computer Equipment Accumulated Depreciation - Comput Accounts Payable Wages Payable Unearned revenue S. Martin, Capital T S. Martin, Withdrawals Accounting services revenue I Depreciation expense - Office Equipl Depreciation expense - Computer El Wages Expense Insurance Expense Rent Expense Supplies Expense Advertising Expense Mileage Expense - Miscellaneous Expense Unadjusted Trial Balance Dr. Cr. 9,870 - 8 ,540 - 2,725 2.725 - 5 ,400 7.000 . 9,000 . 6,825 - - 4.750 - 15,000 2,100 - 76,820 . TILSLLLLLL 34,250 14,400 3,250 2,675 2,160 2,025 103,395 103,395 D E F G Statement of Owner's Equity and Balance Sheet OWN Accounting Solutions Statement of Changes in Owner's Equity For Month Ended December 31, 2018 Accounting Solutions Balance Sheet December 31, 2018 Name General Journal GENERAL JOURNAL Date Account Titles and Explanation R. Debit Credit Company Name Post-Closing Trial Balance Date, Year Debit Credit It Totals mm