Excel AIS Assigment Help.

profileNrjr1231
New_Week_5_Excel_General_ledger_Project5.xlsm
Home>Business & Finance homework help>Accounting homework help>Excel AIS Assigment Help.

Sheet1

Name
Create a new sheet and add the headings as shown (the date is a variable)
In the General Ledger normal balances for accounts are shown in black.
Create a macro, attached to two buttons, one on the top of the General Journal and the other on top of the Adjusting Journal, that:
1 Clears the General Ledger sheet below the headings
Save the file everytime the report is started, Last name date and time
2 Imports the General Journal and the Adjusting Journal entries
3 Sorts by account number and then by transaction number
4 Skips lines between each account
5 Add "Balance" and holding places
6 Loop for all Accounts
7 Calculate and dispay a running balance and the ending balance of each account
8 Display the account balance of each account in "I"
9 If the balance ever goes negitive change the font to red
10 If the account’s balance is not its normal balance, see chart of accounts, add an error message.
A Byte of Accounting
General Ledger
For the Period Ending June 30
Date Transaction Account Name Description Debit Credit Balance
6/1 1 1110 Cash Jeremy's Investment 22,260.00 22,260.00
6/1 2 1110 Cash Courtney's Investment 40,950.00 63,210.00
6/1 3 1110 Cash Mark's Investment 63,000.00 126,210.00
6/2 4 1110 Cash Computer Equip. 31,000.00 95,210.00
6/10 7 1110 Cash Computer Equip. 23,750.00 71,460.00
6/14 8 1110 Cash Insurance 6,312.00 65,148.00
6/16 9 1110 Cash Revenue 7,000.00 72,148.00
6/16 10 1110 Cash Housing Facility 13,700.00 58,448.00
6/17 11 1110 Cash Rent 4,800.00 53,648.00
6/21 14 1110 Cash Fax Machine 650.00 52,998.00
6/21 15 1110 Cash A/P 320.00 52,678.00
6/22 16 1110 Cash Newspaper Ad 275.00 52,403.00
6/22 18 1110 Cash Wages 1,010.00 51,393.00
6/23 19 1110 Cash Revenue 3,605.00 54,998.00
6/29 22 1110 Cash Revenue 5,400.00 60,398.00
6/29 23 1110 Cash Repairs 1,165.00 59,233.00
6/29 24 1110 Cash Wages 1,010.00 58,223.00
6/30 26 1110 Cash Dividends 1,535.39 56,687.61
Balance 1110 Cash 56,687.61
6/21 13 1120 Accounts Receivable Revenue 4,500.00 4,500.00
6/23 19 1120 Accounts Receivable Collection 4,750.00 (250.00)
6/28 21 1120 Accounts Receivable Revenue 5,700.00 5,450.00
6/29 22 1120 Accounts Receivable Collection 5,650.00 (200.00)
Balance 1120 Accounts Receivable (205.00) NOT A NORMAL BALANCE
6/4 5 2101 Accounts Payable Office Equip. 400.00 400.00
6/8 6 2101 Accounts Payable Office Equip. 80.00 320.00
6/17 12 2101 Accounts Payable Newspaper Ad 275.00 595.00
6/21 15 2101 Accounts Payable A/P 320.00 275.00
6/22 16 2101 Accounts Payable Newspaper Ad 375.00 (100.00)
Balance 2101 Accounts Payable (100.00) NOT A NORMAL BALANCE

Chart of Accounts

Number Name Normal Balance Error # Message
1110 Cash Debit 1 Debit And Credit On Same Line
1120 Accounts Receivable Debit 2 Debit Before Credit
1130 Prepaid Insurance Debit 4 Need An Account Number
1140 Prepaid Rent Debit 8 Round To Two Decimal Places
1150 Office Supplies Debit 16 Need a Description
1211 Office Equip. Debit 32 Need a Date
1212 Accum. Depr.-Office Equip. Credit 64
1311 Computer Equip. Debit 128
1312 Accum. Depr.-Computer Equip. Credit 256
1411 Building Cost Debit 512
1412 Accum. Depr.-Building Credit 1024
1510 Land Debit
2101 Accounts Payable Credit
2102 Advanced Payments Credit
2103 Interest Payable Credit
2105 Salaries Payable Credit
2106 Income Taxes Payable Credit
2201 Mortgage Payable Credit
2202 Notes Payable Credit
3100 Capital Stock Credit
3200 Retained Earnings Credit
3300 Dividends Debit
3400 Income Summary Credit
4100 Computer & Consulting Revenue Credit
5010 Rent Expense Debit
5020 Salary Expense Debit
5030 Advertising Expense Debit
5040 Repairs & Maint. Expense Debit
5050 Oil & Gas Expense Debit
5080 Supplies Expense Debit
5090 Interest Expense Debit
5100 Insurance Expense Debit
5110 Depreciation Expense Debit
5120 Income Tax Expense Debit

General Journal

A Byte of Accounting, Inc.
General Journal
Transaction Date Account Name Description Debit Credit Total Balance
01 Jun 01 1110 Cash Lauren's Investment 71,400.00 71400 0
01 Jun 01 3100 Capital Stock Lauren's Investment 71,400.00 (71400) 0
0 0
0 0
02 Jun 01 1110 Cash Sasha Irshad's Common Stock 23,100.00 23100 0
02 Jun 01 1311 Computer Equip. Sasha Irshad's Common Stock 31,080.00 31080 0
02 Jun 01 3100 Capital Stock Sasha Irshad's Common Stock 54,180.00 (54180) 0
0 0
03 Jun 01 1110 Cash Courtney's Investment 45,150.00 45150 0
03 Jun 01 1311 Computer Equip. Courtney's Investment 12,180.00 12180 0
03 Jun 01 1211 Office Equip. Courtney's Investment 1,050.00 1050 0
03 Jun 01 3100 Capital Stock Courtney's Investment 58,380.00 (58380) 0
04 Jun 02 1311 Computer Equip. Computer Down Payment 175,000.00 175000 0
04 Jun 02 2202 Notes Payable Computer Down Payment 140,000.00 (140000) 0
04 Jun 02 1110 Cash Computer Down Payment 35,000.00 (35000) 0
0 0
05 Jun 04 1211 Office Equip. Office Equipment on Credit 500.00 500 0
05 Jun 04 2101 Accounts Payable Office Equipment on Credit 500.00 (500) 0
0 0
0 0
06 Jun 08 2101 Accounts Payable Return Office Equipment 100.00 100 0
06 Jun 08 1211 Office Equip. Return Office Equipment 100.00 (100) 0
0 0
0 0
07 Jun 10 2202 Notes Payable Computer Equipment 26,750.00 26750 0
07 Jun 10 1110 Cash Computer Equipment 26,750.00 (26750) 0
0 0
0 0
08 Jun 14 1130 Prepaid Insurance Pay for Insurance 6,480.00 6480 0
08 Jun 14 1110 Cash Pay for Insurance 6,480.00 (6480) 0
0 0
0 0
09 Jun 16 1110 Cash Consulting Revenue 6,500.00 6500 0
09 Jun 16 4100 Computer & Consulting Revenue Consulting Revenue 6,500.00 (6500) 0
0 0
0 0
10 Jun 16 1411 Building Cost Purchased Building and Land 95,000.00 95000 0
10 Jun 16 1510 Land Purchased Building and Land 18,000.00 18000 0
10 Jun 16 1110 Cash Purchased Building and Land 11,300.00 (11300) 0
10 Jun 16 2201 Mortgage Payable Purchased Building and Land 101,700.00 (101700) 0
11 Jun 17 1140 Prepaid Rent Pay for Rent 3,400.00 3400 0
11 Jun 17 1110 Cash Pay for Rent 3,400.00 (3400) 0
0 0
0 0
12 Jun 17 5030 Advertising Expense Newspaper Advertising Expense 250.00 250 0
12 Jun 17 2101 Accounts Payable Newspaper Advertising Expense 250.00 (250) 0
0 0
0 0
13 Jun 21 2101 Accounts Payable Paid for Accounts Payable 400.00 400 0
13 Jun 21 1110 Cash Paid for Accounts Payable 400.00 (400) 0
0 0
0 0
14 Jun 21 1211 Office Equip. Purchased Fax Machine 675.00 675 0
14 Jun 21 1110 Cash Purchased Fax Machine 675.00 (675) 0
0 0
0 0
15 Jun 21 1120 Accounts Receivable Consulting Services 4,200.00 4200 0
15 Jun 21 4100 Computer & Consulting Revenue Consulting Services 4,200.00 (4200) 0
0 0
0 0
16 Jun 22 5020 Salary Expense Equipment Operators Salaries 860.00 860 0
16 Jun 22 1110 Cash Equipment Operators Salaries 860.00 (860) 0
0 0
0 0
17 Jun 22 5040 Repairs & Maint. Expense Computer Equipment Repair Service 1,165.00 1165 0
17 Jun 22 2101 Accounts Payable Computer Equipment Repair Service 1,165.00 (1165) 0
0 0
0 0
18 Jun 22 2101 Accounts Payable Newspaper Advertising Expense 250.00 250 0
18 Jun 22 1110 Cash Newspaper Advertising Expense 250.00 (250) 0
0 0
0 0
19 Jun 23 1150 Office Supplies Purchased Office Supplies 555.00 555 0
19 Jun 23 2101 Accounts Payable Purchased Office Supplies 555.00 (555) 0
0 0
0 0
20 Jun 23 1110 Cash Billings 3,365.00 3365 0
20 Jun 23 1120 Accounts Receivable Billings 3,365.00 (3365) 0
0 0
0 0
21 Jun 28 1120 Accounts Receivable Bill for services performed 5,385.00 5385 0
21 Jun 28 4100 Computer & Consulting Revenue Bill for services performed 5,385.00 (5385) 0
0 0
0 0
22 Jun 29 2101 Accounts Payable Pay for Repairs to Computer Equipment 1,165.00 1165 0
22 Jun 29 1110 Cash Pay for Repairs to Computer Equipment 1,165.00 (1165) 0
0 0
0 0
23 Jun 29 1110 Cash Billings 5,101.00 5101 0
23 Jun 29 1120 Accounts Receivable Billings 5,101.00 (5101) 0
0 0
0 0
24 Jun 29 5020 Salary Expense Equipment Operators Salaries 860.00 860 0
24 Jun 29 1110 Cash Equipment Operators Salaries 860.00 (860) 0
0 0
0 0
25 Jun 30 5050 Oil & Gas Expense Bill from Oil and Gas Company 790.00 790 0
25 Jun 30 2101 Accounts Payable Bill from Oil and Gas Company 790.00 (790) 0
0 0
0 0
26 Jun 30 1110 Cash Test 5,000.00 5000 0
26 Jun 30 5020 Salary Expense Test 5,000.00 (5000) 0
0 0
0 0
27 0 0
0 0
0 0
0 0
28 0 0
0 0
0 0
0 0
29 0 0
0 0
0 0
0 0
30 0 0
0 0
0 0
0 0
31 0 0
0 0
0 0
0 0
32 0 0
0 0
0 0
0 0
33 0 0
0 0
0 0
0 0
34 0 0
0 0
0 0
0 0
35 0 0
0 0
0 0
0 0
36 0 0
0 0
0 0
0 0
37 0 0
0 0
0 0
0 0
0 0
38 0 0
0 0
0 0
0 0
0 0
39 0 0
0 0
0 0
0 0
40 0 0
0 0
0 0

Adjusting Journal

A Byte of Accounting, Inc.
Adjusting Journal
Transaction Date Account Name Description Debit Credit
101 Jun 30 3300 Dividends Paid Cash Dividend to Shareholders 1,401.60
101 Jun 30 1110 Cash Paid Cash Dividend to Shareholders 1,401.60
102 Jun 30 5010 Rent Expense Rent for June 1,700.00
102 Jun 30 1140 Prepaid Rent Rent for June 1,700.00
103 Jun 30 5080 Supplies Expense Office Supplies Remaining 330.00
103 Jun 30 1150 Office Supplies Office Supplies Remaining 330.00
104 Jun 30 5090 Interest Expense Interest on Mortgage 339.00
104 Jun 30 2103 Interest Payable Interest on Mortgage 339.00
105 Jun 30 5100 Insurance Expense Half of June's Insurance 270.00
105 Jun 30 1130 Prepaid Insurance Half of June's Insurance 270.00
106 Jun 30 1120 Accounts Receivable Unbilled Revenues 5,000.00
106 Jun 30 4100 Computer & Consulting Revenue Unbilled Revenues 5,000.00
107 Jun 30 5110 Depreciation Expense Depreciation for June 3,888.50
107 Jun 30 1412 Accum. Depr.-Building Depreciation for June 231.48
107 Jun 30 1312 Accum. Depr.-Computer Equip. Depreciation for June 3,637.67
107 Jun 30 1212 Accum. Depr.-Office Equip. Depreciation for June 19.35
108 Jun 30 5020 Salary Expense Unpaid Salaries Owed 516.00
108 Jun 30 2105 Salaries Payable Unpaid Salaries Owed 516.00
109 Jun 30 5090 Interest Expense Interest on Five Year Note 1,128.33
109 Jun 30 2103 Interest Payable Interest on Five Year Note 1,128.33
110 Jun 30 5120 Income Tax Expense Income Taxes 2,247.04
110 Jun 30 2106 Income Taxes Payable Income Taxes 2,247.04