Balance sheet excel

Instructions

ACTG

38

1 Pre-requisite Accounting Mechanics Quiz Winter 2

0

18 Purpose of Quiz: This take home quiz has been designed as a review of the pre-requisite accounting knowledge necessary to be successful in ACTG 381. This quiz 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 quiz 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 or Chapter 3 in the textbook for this class. Students who are unable to successfully complete this quiz 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: A printed copy of your completed

Balance Sheet

and

Income Statement

must be turned in at the

beg

inning of class on Thursday, January 11th. Your Excel file should be posted to D2L Assignments by 10pm on Friday, January 12th. 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:

Summer Corporation

(Summer) was founded in 2008 by Erica and Phil Summer. The company designs, sells, installs, and services security systems for high-tech companies. The founders, who describe themselves as “entrepreneurial geeks,” met in a computer lab when they were teenagers and found they had common interests in working on security systems for critical industries. Summer also has one employee, Suzanne Johnson, who has an MBA. To date Suzanne has been doing all the accounting for the company. In June 2017, Summer hires you as an accounting intern. Required: Suzanne Johnson has just provided you with the attached preliminary unadjusted trial balance for 5/31/17. Assume this trial balance has been correctly prepared. Summer’s year end is June 30th. (A) Using the Excel

General Journal

spreadsheet in this file, record the June transactions listed below AND the necessary month 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. $

750,000

of product was sold on account. This product had a cost of goods sold of $

360,000

. 2. $62,000 received from customer for sales made on account in previous months. 3. The following invoices totaling $69,000 were received and recorded on account: § 

Legal and Accounting Expense

of $27,000 § 

Office Supplies Expense

of $5,500 § 

Utilities Expense

of $17,200 §  Repair & Maintenance of $19,300 4. $116,000 of inventory was purchased on account and received into the warehouse during June. The company uses a perpetual inventory system. 5. $83,750 of vendor invoices were paid during June. These invoices had already been accrued into accounts payable in May. 6. On June 1 a customer made a $72,600 deposit for product sales to be made in July 2017. 7.

Total

June wages were $97,000, of which $84,750 were paid in June and $12,250 were to be paid in July. Payroll taxes should be ignored when you record this entry. 8. On July 1, 2016, Summer sold equipment with an original cost of $12,000 and accumulated depreciation of $8,000 for $6,000. In June 2017, Summer realized this entry had not yet been recorded. Hint: Since this equipment was sold on 7/1/16, make sure not to include it in the calculation of year-end depreciation. Suzanne also provided you the following information that she thought may be helpful in preparing the year-end financial statements. 9. On January 1, 2017, ABC Corp. had paid Summer $155,000 in advance for 8 months of consulting services starting on January 1, 2017. Suzanne has been properly recording consulting revenue each month. 10. Bad debt expense has been estimated at $23,500. Bad debt expense is recorded annually at the end of the year, and has not yet been recorded. 11. Monthly interest expense on long-term liabilities is $6,845. Interest should be accrued every month but has not yet been accrued for June. 12.  The Prepaid Expense account includes a one-year insurance policy purchased and recorded on January 1, 2017 for $14,400. Suzanne has been properly recognizing insurance expense each month through the end of May. 13. Depreciation is recorded annually on the straight line basis at the end of the fiscal year (i.e., no depreciation expense has been recorded yet for 2017). The company owns one building which has a useful life of 30 years and is assumed to have a $250,000 salvage value. Furniture and equpment are assumed to have a useful life of 10 years with no salvage value. Hint: Don’t forget the impact of entry

#

8 14.  On June 1, 2017, Summer declared a dividend of $132,000, to be paid on October 20, 2017. Do not use a separate Dividends account.

Debit

the amount directly to

Retained Earnings

. (B)   “Post” the journal entries 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). 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 06/30/17 and a SINGLE-STEP income statement for the year ended 6/30/17. This should be completed through the use of Excel formulas rather than retyping the ending balances from your T-accounts in the financial statements. Note that you do not need to record closing entries to the General Journal, just use the T-account balances to create your balance sheet and income statement. You do NOT need to prepare a Statement of Shareholders’ Equity or Statement of

Cash

Flows. Income taxes should be ignored.

Trial Balance 5-31-17

Debit

Cash

0

Retained Earnings

0

0

0

Legal and Accounting Expense

Utilities Expense

Total

13,736,893

General Ledger Account Name	Unadjusted T/B 5/31/17
	Credit
325,800
		Accounts Receivable		211,543
Allowance for Doubtful Accounts	84,962
		Inventory		1,641,300
Prepaid expenses	19,500
	Building	700,000
	Furniture & Equipment	125,000
	Land	452,600
	Accum Depreciation	205,564
	Investments	185,200
Goodwill	630,000
	Other Intangible Assets	115,600
	Accounts Payable	1,056,340
	Dividends Payable
	Interest Payable	5,100
	Unearned	Revenue	158,660
	Accrued Wages	41,630
	Payroll Taxes Payable	8,850
	Long Term Debt	650,000
	Common Stock	920,000
	Paid-in Capital	105,000
	Treasury Stock	400,000
607,017
		Sales Revenue		9,880,540
		Cost of Goods Sold		6,145,876
	Advertising Expense	185,000
	Bad Debt Expense
	Depreciation Expense
	Insurance Expense	60,101
	Interest Expense	53,214
	Investment Income	13,230
Gain on sale of PPE
193,340
Office supplies Expense	187,613
	Payroll Tax Expense	156,975
	Property Tax Expense	104,570
Repair and Maintenance Expense	192,809
57,134
	Wage Expense	1,593,718
	13,736,893			0.00

T-Accounts

Summer Corporation

Cash Accounts Receivable

beg 211,543
1 750,000
Inventory

Building

beg 1,641,300 360,000 1
Furniture & Equipment Accum Depreciation Land
Investments

Other Intangible Assets

Accounts Payable Dividends Payable Interest Payable
Unearned Revenue Accrued Wages Payroll Taxes Payable
Long Term Debt Common Stock Paid-in Capital
Treasury Stock Retained Earnings Sales Revenue
9,880,540 beg
750,000 1
Investment Income

Cost of Goods Sold

beg 6,145,876
1 360,000
Advertising Expense Bad Debt Expense Depreciation Expense
Insurance Expense Interest Expense

Office Supplies Expense Payroll Tax Expense Property Tax Expense

Utilities Expense Wage Expense

NAME:
Allow for Doubtful Accts
Prepaid	Expenses
Goodwill
`
Gain on Equipment
Legal & Actg Expense
Repair & Maintenance Exp

GenJournal

Summer Corporation
General Journal

1,110,000

#

Debit Credit

1 Accounts Receivable 750,000
Revenue 750,000
Cost of Goods Sold 360,000
Inventory 360,000

	1,110,000	(Control totals)
Transaction
To record sale of product

Income Stmt

Summer Corporation

Income Statement
Sales Revenue 0.00
Expenses

	Student Name:
	Section:
For the year ended 6/30/17
Revenues
Total Revenues
Total Expenses
Net Income

Balance Sheet

Student Name:
Section:
Summer Corporation

Cash 0.00
Stockholders’ Equity

Balance Sheet
6/30/2017
Current assets
Total Current Assets
Total Assets
Liabilities and	Stockholders’ Equity
Current Liabilities
Total Current Liabilities
Total Liabilities
Total Stockholders’ Equity
Total Liabilities and Stockholders’ Equity