Create t accounts in excel

Accounting Excel Assignment

Instructions
ACTG 381
Pre-requisite Knowledge Assessment
Spring 2020
Purpose of the Assessment: This take home assessment has been designed as a review of the pre-requisite accounting knowledge necessary to be successful in ACTG 381. This assessment focuses on recording basic accounting transactions/journal entries, understanding T accounts, and creating a set of financial statements. It is also intended as an opportunity to practice using basic Excel functions. The accounting issues included in this assessment are assumed to have been covered in your prerequisite financial accounting course. If you need a reference, you could refer to the textbook from your pre-requisite accounting course, WileyPlus Chapter 0 Reading Assignment and Problems, or Chapter 3 in the textbook for this class. Students who are unable to successfully complete this assessment should consider taking ACTG 281 prior to ACTG 381.
Possible Points: 25 points; 20 for accounting accuracy and 5 for appropriate Excel technique (i.e., internal referencing among spreadsheets, auto-sum, dr/cr control total, formatting).
Due Date: Your Excel file should be turned in to the D2L Assignments by 10pm on Friday April 3rd. Late assignments will receive a score of zero as the solution will be posted on D2L the next day. If you are unfamiliar with D2L, please refer to http://www.pdx.edu/oit/d2l
Background: Green Thumb Corporation was founded in 2014 by Erica Green. The company provides plant maintenance services to businesses such as medical offices, professional services firms and corporations. In the last couple of years the business has really taken off as decor trends shifted to real rather than artificial plants, and individuals wanted the health benefits of plants in enclosed buildings. Green Thumb has part time employees, mostly college students, who go into the buildings to water, fertilize and replace plants as necessary. To date Erica has been doing all the accounting for the company, but since she really prefers working with plants to computers, she hired you as an accounting intern in December 2019 to assist with the month's accounting, close the books and produce the year-end accrual basis financial statements that conform to GAAP.
Required:
Erica has just provided you with the trial balance at 11/30/19. Assume this trial balance has been correctly prepared. Use the balances in the 11/30/19 Trial Balance tab as your beginning balances for all T-Accounts. Green Thumb's year end is December 31st.
(A) Using the Excel General Journal spreadsheet in this file, record the December transactions listed below AND the necessary year end adjusting journal entries. Label the transactions in numeric sequence corresponding to the numbers below. Each of the transactions below requires a journal entry. Note that there is a debit and credit control total at the top of the general journal so that you can check after each entry to see if you are in balance.
1. Issued an additional 50,000 shares of common stock on December 2. The stock was sold to Erica's parents for $100,000, which equals the par value of the stock.
2. Received cash payment of $14,000 for outstanding accounts receivable on December 3.
3. Purchased a new truck for $26,000 cash on December 3.
4. Provided services for cash of $12,000 on December 3.
5. Provided services on credit for $16,000 on December 10.
6. Received bill and paid utilities of $900 on December 11.
7. Paid employee salaries of $8,700 on December 11.
8. Declared and paid dividends to stockholders of $500 on December 11.
9. Incurred legal fees of $2,000 on December 13, but did not pay for these services.
10. Collected $14,000 from customers on December 15.
11. Collected $3000 on December 15th for plant maintenance services to be provided 12/15/19 to 1/15/20.
12. Sold land with cost of $100,000 for $106,000 cash on December 15.
13. Paid $18,000 for a three-year insurance policy on December 15 with coverage beginning on 12/15/19
14. Paid $1,000 on December 20 related to legal legal fees incurred on December 13.
15. Paid $900 for miscelaneous supplies on December 20. Supplies are expensed upon purchase.
Erica also provided you the following information that she thought may be helpful in preparing the year-end financial statements.
16. As of December 31, Green Thumb has not recorded any insurance expense for the new policy purchased in #13 above.
17. Green Thumb depreciates its equipment at a rate of $7,000 per year. No depreciation expense has been recorded yet for 2019.
18. For the service revenue collected on December 15th (#11 above), record the appropriate amount of revenue for 2019.
19. Erica let you know that she provided $2,900 of services to a customer in December that had not yet been billed.
20. Green Thumb has incurred salaries of $3,000 at the end of the year. The next payroll date is 1/2/20. (Ignore payroll taxes and withholdings)
Note: Do not prepare the closing journal entries at this point! Closing journal entries should be prepared as part of step C below.
(B) "Post" journal entries 1-20 from the General Journal to the Excel spreadsheet of T-accounts in this file. All necessary T-accounts have been provided. This should be completed through the use of Excel formulas rather than retyping the numbers in your T-accounts. Please also place the number of each transaction next to each journal entry (see transaction ‘1’ in the Excel T-Account sheet for an example) and be sure to create formulas so that each T accounts automatically calculates its ending balance (see the Cash t-account for an example). TIP: Set up your spreadsheet to have debit and credit control totals so that you can check after each entry to see if you are in balance.
(C) In Excel, prepare a balance sheet as of 12/31/19 and a SINGLE-STEP income statement for the year ended 12/31/19. This should be completed through the use of Excel referencing formulas rather than retyping the ending balances from your T-accounts in the financial statements. (Note that at this point your balance sheet will be out of balance because Retained Earnings hasn't yet been updated!) After you have prepared your balance sheet and income statement, prepare closing entries in the General Journal but do not post them to the T accounts so that the pre-closing ending balances continue to flow to the Income Statement using the Excel referencing formulas. Then the final Net Income amount from your Income Statement should be referenced using Excel formulas to the Statement of Retained Earnings. Once reconciled, your ending Retained Earnings balance should be referenced using Excel formulas back to your 12/31/19 Balance Sheet. You do NOT need to prepare a Statement of Shareholders’ Equity or Statement of Cash Flows. Income taxes should be ignored.
Trial Balance 11-30-19
Unadjusted Trial Balance
General Ledger Account Name 11/30/19
Debit Credit
Cash 37,000
Accounts Receivable 24,500
Prepaid Insurance 0
Equipment 88,000
Accum Depreciation
Land 100,000
Accounts Payable 4,500
Unearned Service Revenue 0
Salaries Payable 0
Long Term Notes Payable 16,000
Common Stock 60,000
Retained Earnings 97,200
Dividends
Service Revenue 165,000
Gain on Sale of Land
Depreciation Expense
Insurance Expense 4,500
Legal Fees Expense
Supplies Expense 1,400
Salaries Expense 82,000
Utilities Expense 5,300
Total 342,700 342,700 0.00 Control totals
Note: All the accounts in this trial balance
will be used. No additional accounts are needed.
GeneralJournal
Green Thumb Corporation
General Journal
100,000 100,000 (Control totals)
# Date Transaction Debit Credit
1 12/2/19 Cash 100,000
Common Stock 100,000
To record issuance of common stock.
T-Accounts
Green Thumb Corporation NAME:
Cash Accounts Receivable Prepaid Insurance
beg 37,000
1 100,000
end 137,000
Equipment Accum Depreciation Land
Accounts Payable Unearned Services Rev Salaries Payable
Long Term Notes Payable Common Stock Retained Earnings
60,000 beg
100,000 1
160,000 end
Dividends Service Revenue Gain on Sale of Land
Salaries Expense Legal Fees Expense Rental Expense
Insurance Expense Depreciation Expense Utilities Expense
Income Stmt
Student Name:
Section:
Revenues
Service Revenue 0.00
Total Revenues 0.00
Expenses
Total Expenses 0.00
Check Figure
Net Income $ 87,450
Balance Sheet
Student Name:
Section:
Assets
Cash 137,000.00
Total Current Assets
Total Assets
Liabilities and Stockholders' Equity
Liabilities
Total Current Liabilities
Total Liabilities
Stockholders' Equity
Common Stock
Retained Earnings 0.00
Total Stockholders' Equity
Total Liabilities and Stockholders' Equity
Check Figure Total Assets $ 370,150
Stmt Retained Earnings
Student Name:
Section:
Green Thumb Corporation
Statement of Retained Earnings
For the year ended 12/31/19
Beginning Retained Earnings $ - 0
Net Income - 0
Less: Dividends - 0
Ending Retained Earnings $ - 0
Note: Per instruction C, to calculate Ending Retained Earnings you should
use Excel formulas to pull values in from the appropriate T accounts.
Ending Retained Earnings calculated here should flow into the 12/31
Balance Sheet.