Instructions
ACTG 381
Pre-requisite Knowledge Assessment
Winter 2021
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, Chapter 3 in the textbook for this class, or the ACTG 381 Boot Camp on your D2L page.
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 D2L Assignments by 10pm on Saturday, January 9th. Late assignments will receive a score of zero as the solution will be posted on D2L by 11pm the same evening. If you are unfamiliar with D2L, please refer to http://www.pdx.edu/oit/d2l
Background: Nguyen Service Corporation (Nguyen) was founded in 2012 by Judy and Julie Nguyen. The company repairs espresso machines and grinders for coffee shops and other types of clients. Judy and Julie decided to start the company when they realized it was the perfect way to combine their love of coffee and mechanical systems. Nguyen employs a few field technicians and sales people as well as an MBA, Darrell Johnson. To date Darrell has been doing all the accounting for the company. In January 2020, Nguyen hires you as an accounting intern.
Required:
Darrell Johnson has just provided you with the attached beginning balances for 2020. Assume this trial balance has been correctly prepared. Use the balances in the 1-1-20 Trial Balance tab as your beginning balances for all T-Accounts. This should be completed through the use of Excel formulas rather than retyping the numbers in the T-accounts. See the Beginning Balance in the Cash T-account for an example. Nguyen’s year end is December 31st.
(A) Using the Excel General Journal spreadsheet in this file, record the 2020 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 100,000 shares of common stock on January 2. The stock was sold for $200,000, which equals the par value of the stock.
2. Purchased equipment for $24,000 cash on January 3.
3. Received cash payment of $12,000 for outstanding accounts receivable on January 5.
4. Provided services on account to clients for $16,000 on February 10.
5. Received bill and paid utilities of $15,000 on Feburary 28.
6. On March 23, provided services for clients, for which $20,000 was collected in cash and $44,000 was billed to the clients.
7. Paid employee salaries of $46,500 on April 2.
8. Collected $51,200 on outstanding accounts receivable on April 15.
9. Incurred legal fees of $13,000 on May 24, but did not pay for these services.
10. Declared and paid dividends to stockholders of $22,000 on June 10.
11. Paid $45,000 for a three-year insurance policy on July 6 with coverage beginning on October 1.
12. Collected $63,000 on July 20 for services to be provided over the coming year.
13. Paid $8,000 on August 15 related to legal fees incurred on May 24.
14. Sold land with cost of $100,000 for $88,000 cash on September 25.
15. Paid $2,500 in interest costs related to the Long Term Notes Payable on November 15.
Darrell also provided you the following information that she thought may be helpful in preparing the year-end financial statements.
16. Nguyen received a information from a field technician indicating that services amounting to $5,800 had been provided that need to be billed to customers and recorded.
17. Nguyen depreciates its equipment at a rate of $6,000 per year. Depreciation expense has not been recorded as of December 31.
18. Nguyen has incurred salaries of $15,600 at the end of the year. The next payroll date is January 2 of the following year. (Ignore payroll taxes and withholdings)
19. For the service revenue collected on July 20 (#12 above), Nguyen estimated that 60% of the amount collected in advance had been earned by the end of the year.
20. As of December 31, Nguyen has not recorded any insurance expense for the year. The only insurance policy it owns is the one purchased in #11 above.
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-account automatically calculates its ending balance (see the Cash and Common Stock t-accounts for examples of both a debit and credit account formula). 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/20 and a SINGLE-STEP income statement for the year ended 12/31/20. 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/20 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 1-1-20
Unadjusted Trial Balance
General Ledger Account Name 1/1/20
Debit Credit Note: All the accounts in this trial balance
Cash 54,150 will be used. No additional accounts are needed.
Accounts Receivable 18,000
Prepaid Insurance 0
Equipment 52,000
Accum Depreciation 12,000
Land 100,000
Accounts Payable 2,150
Unearned Service Revenue 0
Salaries Payable 0
Long Term Notes Payable 25,000
Common Stock 60,000
Retained Earnings 125,000
Dividends
Service Revenue
Loss on Sale of Land
Depreciation Expense
Insurance Expense
Legal Fees Expense
Interest Expense
Salaries Expense
Utilities Expense
Total 224,150 224,150 0.00 Control totals
General Journal
Nguyen Service Corporation
General Journal
200,000 200,000 (Control totals)
# Date Transaction Debit Credit
1 1/2/20 Cash 200,000
Common Stock 200,000
To record issuance of common stock.
T-Accounts
Nguyen Services Corporation NAME:
DR total
CR total
Difference
Cash Accounts Receivable Prepaid Insurance
beg 54,150 18,000
1 200,000
end 254,150
Equipment Accum Depreciation Land
Accounts Payable Unearned Service Revenue Salaries Payable
Long Term Notes Payable Common Stock Retained Earnings
60,000 beg 125,000 beg
200,000 1
260,000 end 125,000
Dividends Service Revenue Loss on Sale of Land
Salaries Expense Legal Fees Expense Interest Expense
Insurance Expense Depreciation Expense Utilities Expense
Income Statement
Student Name:
Section:
Nguyen Service Corporation
Income Statement or Profit and Loss Statement
For the Year Ended 12/31/20
Revenues and Gains
Service Revenue $ – 0 1 pt
Total Revenues
Expenses and Losses
1 pt
1 pt
1 pt
1 pt
1 pt
1 pt
1 pt
Total Expenses
Check Figure
Net Income $ 9,250
Total 8 pts
Balance Sheet
Student Name:
Section:
Nguyen Service Corporation
Balance Sheet
December 31, 2020
Assets
Cash $ 254,150 1 pt
1 pt
1 pt
Total Current Assets
1 pt
1 pt
Total Assets
Liabilities
1 pt
1 pt
1 pt
Total Current Liabilities
1 pt
Total Liabilities
Stockholders’ Equity
1 pt
Retained Earnings 125,000 1 pt This should be referenced from the Stmt of Retained Earnings
Total Stockholders’ Equity 1 pt
Total Liabilities and Stockholders’ Equity
Total 12 pts
Note: Prepaid Insurance can be shown as all CA or split between CA and LT
Check Figure Total Assets 445,200
Statement of Retained Earnings
Student Name:
Section:
Nguyen Service Corporation
Statement of Retained Earnings
For the Year Ended December 31, 2020
Beginning Retained Earnings $ 125,000
Net Income – 0
Less: Dividends – 0
Ending Retained Earnings $ 125,000
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.