[122]

Travel Trailers is a business owned and operated by Charles Fowler. This business earns it income from both the selling and servicing of mobile homes and trailers.

On May 31, 2010, after the books had been closed for May, the ledger contained the balances shown in the trial balance below:

Travel Trailers
(post closing) Trial Balance
May 31, 2010
Account: No: Debit: Credit:
Cash 101 11,751
Accounts Receivable 110 2,719
Merchandise Inventory 115 125,423
Supplies 120 1,151
Prepaid Insurance 125 2,650
Equipment 130 34,472
Accumulated Depreciation – equipment 131 6,000
Truck 140 38,000
Accumulated Depreciation – truck 141 16,000
Accounts Payable 201 21,386
Bank Loan 205 120,000
C. Fowler, Capital 300 52,780
216,166 216,166
The company also typically uses the accounts listed below:
C. Fowler, Drawings - #305
Income Summary - #310
Fees Earned - #400
Sales - #405
Sales Discounts - #410
Sales Returns - #415
Purchases - #500
Purchase Discounts - #502
Purchase Returns - #505
Transportation-in - #510
Salary & Wage Expense - #600
Utilities Expense - #605
Supplies Expense - #610
Rent Expense - #615
Miscellaneous Expense - #620
Insurance Expense - #625
Depreciation Expense - #630
Bank Charges & Interest Expense - #635
Delivery Expense - #640
Truck Maintenance Expense - #645
Instructions:
1. Prepare an opening entry in the journal using May’s post-closing trial balance. This month the journal will open on page 88.
2. Journalize the transactions below in the journal.
3. Open the ledger. Post the opening entry and the journal entries to the ledger.
4. Prepare a Trial Balance.
5. Journalize the adjusting journal entries using the following information:
a. Supplies on hand on June 30 total $1,300
b. Insurance expires at a rate of $400 per month.
c. Depreciation on the truck is calculated using the straight-line method. Its estimated useful life is 9 years and its estimated salvage value is $2,000.
d. Depreciation on the equipment is calculated using the declining balance method using a depreciation rate of 15% per year.
e. Merchandise inventory on hand on June 30 totals $136,120.
6. Post the adjusting entries to the ledger.
7. Prepare a Schedule of Cost of Goods Sold.
8. Prepare an Income Statement.
9. Prepare a Balance Sheet.
10. Journalize the required closing entries.
11. Post the closing entries to the ledger.
12. Prepare a post-closing trial balance.

Source Documents:

Date: Document: Details:
June 1 Sales invoice # 636 to A. Newman, for repairs to trailer, $590, terms 2/20, n/30
1 Cheque copy #755, to General Real Estate, for the rent for June, $1,250
2 Sales invoice #637 to L. Walker, for trailer parts, $900, terms 2/20, n/30
2 Cheque copy #756 to Double G Industries, on account, $5,000
3 Purchase invoice #40, from Parker Manufacturing, for supplies, $236, terms 2/10, n/30
3 Purchase invoice #472, from Double G Industries, for trailer parts, $1,475, terms n/30
4 Cash receipts list From W. Hoyle, on account, $86
4 Cash sales slip #7042 to Federated Finance Company for the sale of a trailer, $12,700
4 Bank debit memo From Central Bank, for interest on bank loan for May, $1,200.
5 Cheque copy #757, to C. Fowler, for personal use, $1,000
5 Cheque copy #758, for the week’s wages, $2,180
5 Sales invoice #638 to N. Thompson, for trailer repairs, $1,370, 2/20, n/30
8 Cheque Copy #759 to JC Pat Supply, $244 for supplies and $135 for miscellaneous expense items

June 8 Accounting memo Correction required: $56 of Miscellaneous Expense had been incorrectly debited to Transportation-in.
9 Cash receipt list From A. Newman, on account, $288
9 Purchase invoice #452 from Windsor Manufacturing, for trailer parts, $1,452, terms n/30.
9 Purchase invoice #64 from Maynard’s Cartage, for transportation charges on incoming merchandise, $218, terms 2/10, n/30.
10 Sales invoice #639, to B. Fraser, for trailer parts, $450, terms 2/20, n/30
10 Cash receipt list From Schell Brothers, on account, $1162
From B. Fraser, on account, $402
From N. Thompson, on account, $684
10 Cash sales slip #7043, to Federated Finance Company, for the sale of a trailer, $14,500
10 Cheque copy #760, to Modern Mobile Homes, payment in full of account, $2,247
10 Cheque copy #761, to Double G Industries, on account, $5,000.
10 Purchase invoice #481, from Windsor Manufacturing, for a new trailer, $14,500, terms n/30
11 Sales invoice #640, to Schell brothers, for service on trailers, $1,700, 2/20, n/30
11 Cheque copy #762 to C. Fowler, for personal use, $500
11 Cheque copy #763, for the week’s wages, $2,150
11 Cheque copy #764, to Parker Manufacturing, paying Invoice #40 of $236 less the 2% discount
11 Cheque copy #765, to Craighurst Garage, payment for repairs to delivery truck, $420
15 Cash sales slip #7044, to Federated Finance Company for the sale of a trailer, $15,000
15 Cheque copy #766, to Windsor Manufacturing, on account, $15,000
16 Purchase invoice #82, from Maynard Cartage, for transportation charges on incoming goods, $197, terms 2/10, n/30
16 Purchase invoice #2412, from National Hardware, for trailer parts, $280, terms n/30.
16 Purchase invoice #515, from Double G Industries, for a new trailer, $19,097, terms n/30.
16 Purchase invoice #499, from Windsor Manufacturing, for trailer parts, $284, terms n/30
17 Sales invoice #641, to W. Hoyle, for trailer servicing, $1,100, terms 2/20, n/30
17 Cheque copy #767, to Emerald Store, for the cash purchase of miscellaneous expense items, $145
18 Cheque copy #768, to National Hardware, on account, $2,982
18 Cheque copy #769, to Maynard Cartage, paying Invoice #64 from June 9 less the 2% discount.
June 18 Credit invoice issued #69, to L. Walker, to cancel Invoice #631, $85
19 Credit invoice received #600, from Double G industries, for a 10% price adjustment on invoice #515, $1,910.
19 Purchase invoice #2480, from National Hardware, for trailer parts, $409, terms n/30
19 Cheque copy #770 for the week’s wages, $2,060
22 Sales invoice #642 to L. Walker, for trailer services, $290, terms 2/20, n/30.
22 Purchase invoice #140, from Parker Manufacturing, for trailer parts, $367, terms 2/10, n/30
24 Cash sales slip #7045 to Federated Finance Company, for the sale of a trailer, $18,500
25 Cheque copy #771, to C. Fowler, for personal use, $350
25 Cheque copy #772 to Maynard Cargage, paying Invoice #82 from June 16, less the 2% discount.
25 Cheque copy #773 to Humber Fuels, cash payment for the fuel and oil for the delivery truck, $140
26 Sales invoice #643, to A. Newman, for trailer repairs, $236, terms 2/20, n/30
26 Cash receipts list From N. Thompson, in payment of Invoice #638 from June 5, less the allowed discount.
26 Cheque copy #774, to Windsor Manufacturing, on account, $2,214
26 Cheque copy #775, for the week’s wages, $2,450
26 Cheque copy #776, to City Hydro, for the hydro for the month, $495
26 Cheque copy #777, to Bell Canada, for telephone for the month, $202
29 Cash receipts list From Schell Brothers, in payment of Invoice #640 from June 11, less the allowed discount.
29 Purchase invoice #2561, from National Hardware, for supplies, $930, terms n/30
30 Sales invoice #644, to W. Hoyle, for trailer servicing, $230, terms 2/20, n/30
30 Cash receipts list From B. Fraser, in payment of Invoice #639 from June 10 less the allowed discount.

[122]

Let me know if u can do it in excel and then write a comment below, and go from there

[paraclete]

Excel can be used for many purposes as it is both a Spreadsheet and a database. You have been given a chart of accounts and opening balances so there are many things you can do and various ways to do it depending upon how many individual sheets you want to construct and how much programming you want to do.

But the real question is not whether I can do it but what your effort looks like. I think a program like quicken or quickbooks would be useful in doing this assignment