coffee & cafe supplier ACCOUNTING EXCEL

profilenearzl
excel.xls
Home>Business & Finance homework help>Accounting homework help>coffee & cafe supplier ACCOUNTING EXCEL

Title page

www.perdisco.com
spreadsheet version: PCU3e.S1

Worksheet

Moondollars - Worksheet
June 30, 20XX
Acct. No. Account Name Unadjusted Trial Balance Adjustments Adjusted Trial Balance Income Statement Balance Sheet
Debit Credit Debit Credit Debit Credit Debit Credit Debit Credit
100 Cash 103,835 103,835 103,835
102 Short-term Investments 20,000 20,000 20,000
110 ARC - Accounts Receivable Control 6,895 6,895 19,380
112 Interest Receivable 100 100 100
120 Merchandise Inventory 73,322 0 73,322 73,322
130 Office Supplies 7,083 4,037 3,046 3,046
140 Prepaid Rent 6,000 3,000 3,000 3,000
141 Prepaid Advertising 9,000 3,000 6,000 6,000
142 Prepaid Insurance 2,950 590 2,360 2,360
150 Office Furniture 9,000 9,000 9,000
151 Accum Depn: Office Furniture 2,031 156 2,187 2,187
160 Office Equipment 43,000 43,000 43,000
161 Accum Depn: Office Equipment 13,532 366 13,898 13,898
210 APC - Accounts Payable Control 155 155 155
220 Wages Payable 1,320 1,320 1,320
221 Electricity Payable 874 874 874
222 Water Payable 235 235 235
225 Interest Payable 275 275 275
250 Bank Loan Payable 55,000 55,000 55,000
300 Common Stock 80,000 80,000 80,000
301 Retained Earnings 100,401 100,401 100,401
400 Sales Revenue 46,370 46,370 46,370
401 Sales Returns and Allowances 1,198 1,198 1,198
402 Sales Discounts 284 284 284
403 Interest Revenue 234 100 334 334
500 Purchases 735 735 735
501 Purchase Returns and Allowances 1,403 1,403 1,403
502 Purchase Discounts 66 66 66
511 Advertising Expense 3,000 3,000 3,000
516 Wages Expense 10,685 1,320 12,005 12,005
540 Rent Expense 3,000 3,000 3,000
541 Electricity Expense 874 874 874
542 Water Expense 235 235 235
543 Insurance Expense 590 590 590
544 Office Supplies Expense 4,037 4,037 4,037
545 Salary Expense 5,200 5,200 5,200
560 Depn Expense: Office Furniture 156 156 156
561 Depn Expense: Office Equipment 366 366 366
571 Interest Expense 275 275 275
572 Admin. Expense - Bank Charges 5 5 5
Totals 299,192 299,192 13,953 13,953 302,518 302,518 31,960 48,173 283,043 254,345
Net income or loss 16,213 16,213
Totals 299,192 299,192 13,953 13,953 302,518 302,518 31,960 48,173 283,043 270,558
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

General journal

General journal
Date Account and Explanation Post Ref. Debit Credit
11 6 sales returns 1,198
SloZone 1,198
18 6 Abejundio Coffee 1,403
purchases returns 1,403
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

General ledger

General ledger
Account: Cash
Account No. 100
Date Description Ref. Debit Credit Balance
1 6 loan payable 55,000 55,000
2 6 East Tangiers Co-op 3,743 51,257
2 6 Buena Vista Coffee 1,385 49,872
3 6 purchases 220 49,652
4 6 sales 5,961
9 6 Central Coffee 2,560
11 6 sales 10,081
13 6 Klam Wholesalers 3,144
103,906 dr
Account: Short-term Investments
Account No. 102
Date Description Ref. Debit Credit Balance
1 6 bal b/d 20,000 20,000 dr
Account: ARC - Accounts Receivable Control
Account No. 110
Date Description Ref. Debit Credit Balance
1 6 bal b/d 12,570 12,570 dr
Account: Interest Receivable
Account No. 112
Date Description Ref. Debit Credit Balance
234 234 dr
Account: Merchandise Inventory
Account No. 120
Date Description Ref. Debit Credit Balance
1 6 bal b/d 73,322 73,322 dr
Account: Office Supplies
Account No. 130
Date Description Ref. Debit Credit Balance
1 6 bal b/d 7,083 7,083 dr
Account: Prepaid Rent
Account No. 140
Date Description Ref. Debit Credit Balance
1 6 bal b/d 6,000 6,000 dr
Account: Prepaid Advertising
Account No. 141
Date Description Ref. Debit Credit Balance
1 6 bal b/d 9,000 9,000 dr
Account: Prepaid Insurance
Account No. 142
Date Description Ref. Debit Credit Balance
1 6 bal b/d 2,950 2,950
Account: Office Furniture
Account No. 150
Date Description Ref. Debit Credit Balance
1 6 bal b/d 9,000 900
Account: Accum Depn: Office Furniture
Account No. 151
Date Description Ref. Debit Credit Balance
1 6 bal b/d 2,031 2,031 cr
depreciation expense-furniture 156 156 dr
Account: Office Equipment
Account No. 160
Date Description Ref. Debit Credit Balance
1 6 bal b/d 43,000 43,000 dr
Account: Accum Depn: Office Equipment
Account No. 161
Date Description Ref. Debit Credit Balance
1 6 bal b/d 13,532 13,532 cr
depreciation expense-equipment 366 366 dr
Account: APC - Accounts Payable Control
Account No. 210
Date Description Ref. Debit Credit Balance
1 6 bal b/d 16,438 16,438
Account: Wages Payable
Account No. 220
Date Description Ref. Debit Credit Balance
15 6 cash 5,103
15 6 wages expense 5,103
29 6 wages expense 5,582
29 6 cash 5,582 0
Account: Electricity Payable
Account No. 221
Date Description Ref. Debit Credit Balance
electrice expense 874
cash 874 0
Account: Water Payable
Account No. 222
Date Description Ref. Debit Credit Balance
water usage 235
cash 235 0
Account: Interest Payable
Account No. 225
Date Description Ref. Debit Credit Balance
Account: Bank Loan Payable
Account No. 250
Date Description Ref. Debit Credit Balance
1 6 cash 55,000 55,000
Account: Common Stock
Account No. 300
Date Description Ref. Debit Credit Balance
1 6 80,000 80,000 cr
Account: Retained Earnings
Account No. 301
Date Description Ref. Debit Credit Balance
1 6 bal b/d 100,401 100,401 cr
Account: Income Summary
Account No. 310
Date Description Ref. Debit Credit Balance
Account: Sales Revenue
Account No. 400
Date Description Ref. Debit Credit Balance
4 6 cash 5,961 5,961
7 6 SloZone 1,435
11 6 cash 10,081
12 6 Jandesh's Hot Spot 484
17 6 Revamp Coffee Carts 2,060
18 6 cash 9,658
24 6 Jandesh's Hot Spot 470
25 6 cash 9,592
30 6 cash 6,629
46,370 cr
Account: Sales Returns and Allowances
Account No. 401
Date Description Ref. Debit Credit Balance
11 6 SloZone 1,198 1,198 cr
Account: Sales Discounts
Account No. 402
Date Description Ref. Debit Credit Balance
9 6 Central Coffee 284 284 dr
Account: Interest Revenue
Account No. 403
Date Description Ref. Debit Credit Balance
234 234 cr
Account: Purchases
Account No. 500
Date Description Ref. Debit Credit Balance
1 6 Abejundio Coffee 160 160
1 6 Brazil Imports Group 200 360
3 6 cash 220
30 6 Buena Vista Coffee 155 735 dr
Account: Purchase Returns and Allowances
Account No. 501
Date Description Ref. Debit Credit Balance
18 6 Abejundio Coffee 1,403 1,403 cr
Account: Purchase Discounts
Account No. 502
Date Description Ref. Debit Credit Balance
2 6 East Tangiers Co-op 38
2 6 Buena Vista Coffee 28 66
Account: Advertising Expense
Account No. 511
Date Description Ref. Debit Credit Balance
advertising expense 3,000
cash 3,000 0
Account: Wages Expense
Account No. 516
Date Description Ref. Debit Credit Balance
15 6 wages payable 5,103
29 6 wages payable 5,582 10,685
Account: Rent Expense
Account No. 540
Date Description Ref. Debit Credit Balance
rent expense 3,000
cash 3,000 0
Account: Electricity Expense
Account No. 541
Date Description Ref. Debit Credit Balance
Account: Water Expense
Account No. 542
Date Description Ref. Debit Credit Balance
cash 235
water usage 235 0
Account: Insurance Expense
Account No. 543
Date Description Ref. Debit Credit Balance
insurance expense 590
cash 590 0
Account: Office Supplies Expense
Account No. 544
Date Description Ref. Debit Credit Balance
suppliers 4,037
cash 4,037 0
Account: Salary Expense
Account No. 545
Date Description Ref. Debit Credit Balance
30 6 cash 5,200 5,200
Account: Depn Expense: Office Furniture
Account No. 560
Date Description Ref. Debit Credit Balance
Account: Depn Expense: Office Equipment
Account No. 561
Date Description Ref. Debit Credit Balance
Account: Interest Expense
Account No. 571
Date Description Ref. Debit Credit Balance
loan 275
interst expense 275 0
Account: Admin. Expense - Bank Charges
Account No. 572
Date Description Ref. Debit Credit Balance
interest earned 100
investment 100 0
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

cash account

Date Description Ref. Debit Credit Balance
1 6 bal b/d 29477
1 6 loan payable 55,000 55,000
2 6 East Tangiers Co-op 3,743 51,257
2 6 Buena Vista Coffee 1,385 49,872
3 6 purchases 220 49,652
4 6 sales 5,961
9 6 Central Coffee 2,560
11 6 sales 10,081
13 6 Klam Wholesalers 3,144
15 6 staff wages payable 5,103
18 6 sales 9,658
19 6 Jandesh's Hot Spot 300
21 6 SloZone 2381
25 6 sales 9592
26 6 Brazil Imports Group 3632
27 6 Abejundio Coffee 3425
29 6 wages payable 5582
30 6 Quick Bolt Coffee 3701
30 6 sales 6629
30 6 salary 5200 103,906 dr balance

Bank reconciliation

Bank reconciliation statement
Moondollars Bank Reconciliation Statement June 30, 20XX
bank balance as at 30th june 103,657
add
deposit on transit 6,629
deposit on transit 2381
112,667
less:
outstanding checks
911 issued on 30th june 5,200
908 issued on 26/6 3632
103,835
add
interest 234
deposit understated 142
104,211
less
nsf check 300
service charge 5
book balance as at 30th June 103,906
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Cash payments journal

Cash payments journal
Date Account Check No. Post Ref. Debit Credit
Accounts Payable Purchases Other Accounts Cash Purchase Discounts
2 6 East Tangiers Co-op 903 3,781 3,743 38
2 6 Buena Vista Coffee 904 1,413 1,385 28
3 6 purchases 905 220 220
13 6 Klam Wholesalers 906 3,144 3,144
15 6 staff wages 907 5,103 5,103
26 6 Brazil Imports Group 908 3,632 3,632
27 6 Abejundio Coffee 909 3,425 3,425
29 6 wages 910 5,582 5,582
30 6 salary 911 5,200 5,200
15,395 5,200 10,905 31,434 66
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Accts receivable subsidiary

Accounts receivable subsidiary ledger
Account: ARC - Central Coffee
Account No. 110-1
Date Description Ref. Debit Credit Balance
1 6 bal b/d 2,844
9 6 cash 2,560
9 6 sales discount 284
Account: ARC - Con's Coffee
Account No. 110-2
Date Description Ref. Debit Credit Balance
1 6 bal b/d 3,881
Account: ARC - SloZone
Account No. 110-3
Date Description Ref. Debit Credit Balance
1 6 bal b/d 2,144
7 6 sales 1,435
11 6 sales returns 1,198
21 6 cash 2,381
Account: ARC - Quick Bolt Coffee
Account No. 110-4
Date Description Ref. Debit Credit Balance
1 6 bal b/d 3,701
30 6 cash 3,701
Account: ARC - Revamp Coffee Carts
Account No. 110-5
Date Description Ref. Debit Credit Balance
17 6 sales 2,060
Account: ARC - Jandesh's Hot Spot
Account No. 110-6
Date Description Ref. Debit Credit Balance
12 6 sales 484
19 6 cash 300
24 6 sales 480
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Sales journal

Sales journal
Date Account Invoice No. Post Ref. Amount
7 6 SloZone 201 1,435
12 6 Jandesh's Hot Spot 202 484
17 6 Revamp Coffee Carts 203 2,060
24 6 Jandesh's Hot Spot 204 470
4,449
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Schedules of accounts

Schedule of accounts receivable
Moondollars Schedule of Accounts Receivable June 30, 20XX
Account No. Name Debit ($) Credit ($)
Total
Schedule of accounts payable
Moondollars Schedule of Accounts Payable June 30, 20XX
Account No. Name Debit ($) Credit ($) Terms
Total
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Income statement

Moondollars Income Statement For the month ended June 30, 20XX
INCOME
Revenue
Sales Revenue 46,370
Net sales revenue
COST OF GOODS SOLD
GROSS PROFIT
OPERATING EXPENSES
Selling expenses
Total selling expenses
General and administrative expenses
Total general and administrative expenses
TOTAL OPERATING EXPENSES
INCOME/(LOSS) FROM OPERATIONS
Other revenue
Other expenses
NET INCOME/(LOSS)
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Stockholders' equity

Moondollars Statement of Stockholders' Equity For the month ended June 30, 20XX
COMMON STOCK
Opening balance May 31, 20XX
Issue of common stock   
Closing balance June 30, 20XX   
RETAINED EARNINGS
Opening balance May 31, 20XX   
Net income/(loss)
Dividend paid
Closing balance June 30, 20XX
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Balance sheet

Moondollars Balance Sheet June 30, 20XX
ASSETS
Current Assets
Total Current Assets
Property, Plant and Equipment
Total Property, Plant and Equipment
TOTAL ASSETS
LIABILITIES
Current Liabilities
Total Current Liabilities
Long-term Liabilities
TOTAL LIABILITIES
STOCKHOLDERS' EQUITY
TOTAL EQUITY
TOTAL LIABILITIES AND STOCKHOLDERS' EQUITY
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)

Post-closing trial balance

Moondollars Post-closing Trial Balance June 30, 20XX
Account No. Name Debit ($) Credit ($)
Total
&LCOFFEE & CAFÉ SUPPLIER - US GAAP EDITION 3 (PCU3e.S1)