AC322 Homework Excel File

ayywinston

AC322COMPREHENSIVEPROBLEMSPRING2021StudentCopy.xlsx

Home >Business & Finance homework help >Accounting homework help >AC322 Homework Excel File

Instructions

Read ALL instructions before getting started!

ABC Corporation is a new company that buys and sells office supplies. Business began on January 1, 2021.

Given on the first two tabs are ABC's 12/31/21 Unadjusted Trial Balance and a list of needed adjustments.

1. Make all 14 adjustments on the "Adjusting Journal Entries" tab. Remember to include a description under each journal entry.

2. Post the adjustments to the general ledger on the "12-31-21 T-Accounts" tab. You may have to add T-Accounts for new accounts.

Link your T-Account entries to your Journal Entries. PLEASE NOTE THAT THE "BB" (BEGINNING BALANCES) FOR THE

T-ACCOUNTS REPRESENT THE BALANCES AS OF 12/31/21.

3. Once the 12/31/21 T-Accounts are complete, prepare the Adjusted Trial Balance. There may be some accounts with zero dollars, and you

may have to insert lines for new accounts. Link the Adjusted Trial Balance to your T-Accounts.

4. Use the Adjusted Trial Balance numbers to complete the Income Statement, Statement of Shareholders' Equity, and Balance Sheet.

For purposes of the Income Statement, prepare using the multiple step format and assume that Rent Revenue, any Unrealized Holding Gains/Losses,

Interest Expense, Interest Revenue, and any other Gains/Losses are NOT part of the major central ongoing operations of the company. For purposes

of the Balance Sheet, be sure to prepare a classifed Balance Sheet. Link your financial statements to your Adjusted Trial Balance.

If necessary, review financial statement preparation in Chapters 3 and 4 of your Intermediate Accounting textbook for a quick refresher.

5. When the Financial Statements are complete, make the closing entries on the "Closing Entries" tab.

6. When closing entries have been made, post the entries to the general ledger on the "Post-Close T-Accounts" tab. Make sure your adjusting

journal entries are also on your Post-Close T-Accounts. They will not automatically flow from tab-to-tab. (Helpful hint: After you have completed

and posted all of your adjusting entries, make a duplicate copy of your "12-31-21 T-Accounts" tab to replace the existing blank

"Post-Close T Accounts" tab by right clicking on the completed "12-31-21 T-Accounts" tab, select Move or Copy,

then click on "Create a Copy" and then place at the desired location. You can then delete the original "Post-Close T-Accounts" tab and rename the

newly duplicated tab as your "Post-Close T-Accounts" tab).

7. The final step is the Post-Closing Trial Balance, which will use the ending balances from the Post-Close T-Accounts.

8. Double-check your work. Here are a few things to check for:

-Adjusted Trial Balance: Make sure debit column and credit column total to the same figure at the bottom.

-Net income from the income statement will flow through to the Statement of Retained Earnings.

-Ending Shareholders' Equity balances will flow through to the Stockholders' Equity section of the Balance Sheet.

-The Post-Closing Trial Balance should not have any revenue, expense, gain, or loss (temporary) accounts.

-Check figure 1: Income from operations = $305,446.

-Check figure 2: Income before income taxes = $244,119.

-Check figure 3: Total Current Assets at 12/31/21 = $1,138,625.

-Check figure 4: Retained Earnings at 12/31/21 = $167,295.

-Check figure 5: Total Stockholders' Equity at 12/31/21 = $612,295.

-Check figure 6: Total Liabilities at 12/31/21= $1,408,940.

-Check figure 7: Total Other Income/Gains and (Expenses)/(Losses) for 2021= ($61,327).

-Remember: Neatness matters in Financial Statements. Print or Print Preview before submitting to make sure your statements are neat.

Otherwise, management may send back to you for revision!

-Include your work at the bottom of each tab as needed.

-Ask questions prior to the day/night before the due date. The due date is clearly indicated on the course schedule.

-Utilize formulas and worksheet linkings in your financial statements to improve accuracy and save time in completing the assignment.

-Please take advantage of Excel by using formulas to calculate groups of numbers (i.e. "Total Liabilities and Stockholders' Equity").

-DO NOT force any cells to match check figures given. Any adjustments in the T-Accounts or financial statements not supported by

legitimate adjusting or closing entries will be considered financial statement misrepresentation sufficient to result in a failing grade.

Final comments: This project is intended to make sure that you understand the accounting cycle as well as several key financial accounting transactions that you have

studied during your Intermediate Accounting series. It is very important to take the necessary time on this project to master these concepts. The concepts mastered in this

comprehensive problem will serve you well in the rest of your accounting curriculum.

Please review the grading rubric tab as you start work on the assignment to make sure that you understand how your work will be evaluated. Please note that 50 points

of the total grade on this assignment (25%) is based on your overall presentation of work and your use of Microsoft Excel features (cell links, formulas, etc.)

Unadjusted Trial Balance

ABC Corporation
Unadjusted Trial Balance
December 31, 2021
	Debit	Credit
Cash	393,125
Accounts receivable	300,000
Allowance for doubtful accounts		- 0
Inventory	- 0
Allowance to Reduce Inventory to NRV		- 0
Purchases	350,000
Prepaid insurance	4,167
Land	88,000
Building	500,000
Accumulated depreciation: building		16,570
Equipment	260,000
Accumulated depreciation: equipment		108,330
Delivery Trucks	- 0
Accumulated depreciation: delivery trucks		- 0
Investment in XYZ Company Stock	100,000
Patent	200,000
Accounts payable		116,184
Notes payable		100,000
Income taxes payable		47,667
Unearned rent revenue		15,000
Bonds Payable		1,000,000
Premium on Bonds Payable		81,105
Common stock		125,000
PIC In Excess of Par-Common Stock		40,000
Retained earnings
Treasury stock	50,000
Dividends	28,000
Sales Revenue		1,000,880
Unrealized Holding Losses/Gains on Trading Securities-NI
Advertising expense	9,240
Wages expense	62,150
Office expense	54,083
Depreciation expense	124,900
Utilities expense	33,571
Insurance expense	45,833
Income taxes expense	47,667
	$ 2,650,736	$ 2,650,736

Adjustments Needed

1	On January 1, 2021, ABC purchased a one-year liability insurance policy for $50,000
	Upon purchase, the following journal entry was made:
		Dr Prepaid insurance		50,000
			Cr Cash		50,000
	The expired portion of insurance must be recorded as of 12/31/21.
	Notice that the expired portion from January through November has been recorded already.
	Make sure that the Prepaid Insurance balance after the adjusting entry is correct.
2	Depreciation expense must be recorded for the month of December.
	The building was purchased on February 1, 2021 for $500,000 with a remaining useful life of 25 years and a salvage value of $3,000.
		The method of depreciation for the building is straight-line.
	The equipment was purchased on February 1, 2021 for $260,000 with a remaining useful life of 4 years and a salvage value of $1,800.
		The method of depreciation for the equipment is double-declining balance.
	Depreciation has been recorded for the building and equipment for months February through November.
3	On December 1, 2021, XYZ Co. agreed to rent space in ABC's building for $5,000 per month,
	and XYZ paid ABC on December 1 in advance for the first three months' rent.
	The entry made on December 1 was as follows:
		Dr Cash		15,000
			Cr Unearned rent revenue		15,000
	The unearned revenue account must be adjusted to reflect the amount earned as of 12/31/21.
4	Per timecards, from the last payroll date through December 31, 2021, ABC's employees have worked a total of 300 hours.
	Including payroll taxes, ABC's wage expense averages about $30 per hour. The next payroll date is January 5, 2022.
	The liability for wages payable must be recorded as of 12/31/21.
5	On November 30, 2021, ABC borrowed $100,000 from American National Bank by issuing an interest-bearing note payable.
	This loan is to be repaid in three months (on February 28, 2022), along with interest computed at an annual rate of 7%.
	The entry made on November 30 to record the borrowing was:
		Dr Cash		100,000
			Cr Notes payable		100,000
	On February 28, 2022 ABC must pay the bank the amount borrowed plus interest.
	Assume the beginning balance for Notes Payable is correct.
	Interest through 12/31/21 must be accrued on the $100,000 note.
6	ABC uses a periodic inventory system, and the ending inventory for each year is determined by taking a complete
	physical inventory at year-end. A physical count was taken on December 31, 2021, and the inventory on-hand at
	that time totaled $50,000, which reflects historical cost. Record the adjusting entry for properly recognizing
	2021 Cost of Goods Sold. Hint: This was the first year of operations, so beginning inventory balance is zero.
	Additionally, ABC adheres to GAAP by recording ending inventory at the lower of cost and net realizable value at a total inventory level.
	A review of inventory data further indicated that the current retail sales value of the ending inventory is $45,000 and estimated costs of
	completion and shipping is 10% of retail. Be sure to make an additional adjustment, if necessary, to properly value ending inventory
	using the Loss and Allowance methodology. For Income Statement presentation purposes, be sure to use the Loss Method for accounting
	for adjustments of inventory to market value.
7	It would be unusual for a company to have an asset impairment in Year 1, but for the sake of this example, ABC determined
	that their intangible asset might be impaired on December 31, 2021. Record the impairment adjustment, if any.
	The expected future undiscounted net cash flows for this intangible asset totals $175,000, and the fair value of the asset is $165,000.
8	On 7/1/21, ABC purchased 5,000 shares of its own stock from existing stockholders as treasury stock. The cost of the treasury
	stock was $10 per share, or $50,000 in total. The effects of this transaction are already shown in the unadjusted trial balance. On 12/31/21,
	ABC reissued 2,000 shares of the treasury stock at $15 per share. Record the journal entry required for the reissuance of the treasury stock.
	To refresh your memory, treasury stock is usually accounted for at cost. When treasury stock is reissued for more than its cost, a separate
	Paid-in Capital-Treasury Stock account should be used to account for the excess proceeds over cost. (See your Principles of Accounting textbook
	or Chapter 18 of your Intermediate Accounting textbook for a review.)
9	On 12/31/21, ABC issued 20,000 shares of $1 par value common stock at the closing market price of $15 per share. Prepare ABC's journal entry
	to reflect the issuance of the stock on 12/31/21. To refresh your memory, a Paid-in Capital in Excess of Par account should be used to account for
	excess proceeds over par value in a stock issuance transaction. (See your Principles of Accounting textbook or Chapter 18 of your Intermediate
	Accounting textbook for a review.)
10	On 7/1/21, ABC sold 10% bonds having a maturity value of $1,000,000 for $1,081,105, resulting in an effective yield of 8%. The bonds are
	dated 7/1/21, and mature 7/1/26. Interest is payable semiannually on July 1 and January 1. ABC uses the effective interest method of
	amortization for bond premium or discount. Record the adjusting entry for the accrual of interest and the related amortization on 12/31/21.
	Hint: Develop an abbreviated amortization schedule to accurately determine the interest expense.
11	ABC Corporation prepares an aging schedule on 12/31/21 that estimates total uncollectible accounts at $40,000. Assuming that the allowance
	method is used, prepare the entry to record bad debt expense for the calendar year.
12	ABC Corporation purchased 5,000 shares of XYZ Company common stock for $20.00 per share on 11/30/21. The investment represents
	a 5% voting interest and is classified as a trading security. At 12/31/21, the stock is trading at $25.00 per share. Prepare the appropriate
	adjusting journal entry for end-of-year valuation purposes.
13	On 12/31/21, ABC Corporation exchanged equipment for two pickup trucks. The book value and fair value of the equipment given
	up were $20,000 (original cost of $65,000 less accumulated depreciation of $45,000) and $17,000, respectively. Assume ABC paid $10,000
	in cash and the exchange has commercial substance. Prepare the approriate journal entry to reflect the nonmonetary exchange.
14	Do this final adjusting entry after preparing the Income Statement through the line "Income Before Income Taxes":
	Corporate taxes are due in four estimated quarterly payments on April 15, June 15, September 15, and December 15.
	However, for the purposes of this ABC illustration, we will assume that estimates are not paid, and that the tax is paid in full
	on the return's March 15, 2022 due date.
	ABC's income tax rate is 20%. The entire year's income tax expense was estimated at the beginning of 2021 to be $52,000,
	so January through November income tax expense recognized amounts to $47,667 (11/12 months).
	Since we are assuming estimates are not paid during the year, the balance in Income taxes payable represents
	income tax accrued for January through November. Assume no deferred tax assets or deferred tax liabilities.
	Based on the income before income taxes figure from the income statement, calculate and record December's income tax expense adjustment
	so that the entire year's tax expense is correct (i.e. the difference between total income tax expense and the amount already accrued through November).

Amortization Table

	5.0%	4.0%	premium	carrying
date	cash	interest	amort	value
7/1/21				1,081,105.00
1/1/22
7/1/22
1/1/23
7/1/23
1/1/24
6/30/24
12/31/24
6/30/25
12/31/25
7/1/26

Adjusting Journal Entries

12/31/21	Adjusting Journal Entries
JE #	Account Titles	Debit	Credit
1
	To record expired insurance for December
2
	To record monthly depreciation for building and equipment
3
	To record month month rent revenue earned
4
	To record accrued wages payable
5
	To accrue interest for 1 month on note payable
6
	To recognize COGS and adjustment of inventory balance
	To record year-end market value adjustment to reflect LCM
7
	To record impairment on patent
8
	To record reissuance of treasury stock
9
	To record proceeds from issuance of common stock
10
	To record the accrual of interest and discount amortization
11
	To record the estimate for bad debts expense
12
	To record end of year adjusting entry for stock investment
13
	To record accounting entry for nonmonetary exchange
14
	To record adjusting entry for annual income tax expense

12-31-21 T-Accounts

Beginning balances (bb) on these T-accounts are the 12/31/21 unadjusted balances.
Hint: If there is no beginning balance (bb), it is a new account.
	Cash				Accounts receivable				Inventory				Purchases				Prepaid insurance				Land
bb	393,125			bb	300,000			bb	-			bb	350,000			bb	4,167			bb	88,000
	393,125				300,000				-				350,000				4,167				88,000
	Delivery Trucks				Investment in XYZ Company Stock				Allowance for doubtful accounts				Allowance to Reduce Inventory to NRV				Bonds Payable				Premium on Bonds Payable				Unrealized Holding Losses/Gains on Trading Securities-NI
bb	-			bb	100,000					-	bb			-	bb			1,000,000	bb			81,105	bb			-	bb
																		1,000,000
	-				100,000					-				-								81,105
																										-
	Building				Accumulated depreciation: building				Equipment				Accumulated depreciation: equipment				Patent				Accounts payable				Accumulated depreciation: delivery trucks
bb	500,000					16,570	bb	bb	260,000					108,330	bb	bb	200,000					116,184	bb			-	bb
														-
	500,000					16,570			260,000					108,330			200,000					116,184				-
	Notes payable				Income taxes payable				Unearned rent revenue				Common stock				Retained earnings				Dividends				PIC In Excess of Par-Common Stock
		100,000	bb			47,667	bb			15,000	bb			125,000	bb			-	bb	bb	28,000					40,000	bb
		100,000
						47,667				15,000				125,000				-			28,000					40,000
	Sales Revenue				Advertising expense				Wages expense				Office expense				Depreciation expense				Utilities expense				Treasury stock
		1,000,880	bb	bb	9,240			bb	62,150			bb	54,083			bb	124,900			bb	33,571			bb	50,000
		1,000,880			9,240				62,150				54,083				124,900				33,571				50,000
								These additional T-acccounts are provided for your use as necessary when making the adjusting entries. Be sure to insert sum formulas as needed depending on nature of account. You can use the sum formulas as supplied on existing T-accounts above.
	Insurance expense				Income taxes expense				Rent revenue earned				Wages payable				Interest expense				Interest payable				PIC-Treasury Stock
bb	45,833			bb	47,667
	45,833				47,667					-				-			-					-				-
	Cost of goods sold				Loss on Impairment				Loss Due to Decline of Inventory				Bad debt expense				Fair Value Adjustment in XYZ Stock								Loss on Exchange of Assets
	-				-				-				-				-								-

Adjusted Trial Balance

ABC Corporation
Adjusted Trial Balance
December 31, 2021
	Debit	Credit
Total	$ - 0	$ - 0

Income Statement

ABC Corporation

Income Statement (Multiple Step Format)

For the Year Ended December 31, 2021

Statement of Shareholder Equity

ABC Corporation

Statement of Shareholders' Equity

For the Year Ended December 31, 2021

Common Stock

PIC Common Stock

Treasury Stock

PIC Treasury Stock

Retained Earnings

Total Shareholders' Equity

Balance as of January 1, 2021

1/1, sold 125,000 shares of Common Stock, par $1

7/1, repurchased 5,000 shares at $10 each for Treasury Stock

12/31, 2,000 shares of Treasury stock resold for $15 each

12/31 issued 20,000 shares of Commn Stock par $1, for $15 each

Net Income

Less: Dividends

Balance as of December 31, 2021

Balance Sheet

ABC Corporation

Balance Sheet

For the Year Ended December 31, 2021

Closing Entries

12/31/21	Closing Entries
JE #	Account Titles	Debit	Credit
13
	To close revenue and other temporary credit balance accounts to income summary
14
	To close expense and other temporary debit balance accounts to income summary
15
	To close income summary to retained earnings
16
	To close dividends to retained earnings

Post-close T-Accounts

Beginning balances (bb) on these T-accounts are the 12/31/21 unadjusted balances.						See instructions #6 as you initially work on posting your closing entries here to this tab
Hint: If there is no beginning balance (bb), it is a new account.
	Cash				Accounts receivable				Inventory				Purchases				Prepaid insurance				Land
bb	393,125			bb	300,000			bb	-			bb	350,000			bb	4,167			bb	88,000
	393,125				300,000				-				350,000				4,167				88,000
	Delivery Trucks				Investment in XYZ Company Stock				Allowance for doubtful accounts				Allowance to Reduce Inventory to NRV				Bonds Payable				Premium on Bonds Payable				Unrealized Holding Losses/Gains on Trading Securities-NI
bb	-			bb	100,000					-	bb			-	bb			1,000,000	bb			81,105	bb			-	bb
																		1,000,000
	-				100,000					-				-								81,105
																										-
	Building				Accumulated depreciation: building				Equipment				Accumulated depreciation: equipment				Patent				Accounts payable				Accumulated depreciation: delivery trucks
bb	500,000					16,570	bb	bb	260,000					108,330	bb	bb	200,000					116,184	bb			-	bb
														-
	500,000					16,570			260,000					108,330			200,000					116,184				-
	Notes payable				Income taxes payable				Unearned rent revenue				Common stock				Retained earnings				Dividends				PIC In Excess of Par-Common Stock
		100,000	bb			47,667	bb			15,000	bb			125,000	bb			-	bb	bb	28,000					40,000	bb
		100,000
						47,667				15,000				125,000				-			28,000					40,000
	Sales Revenue				Advertising expense				Wages expense				Office expense				Depreciation expense				Utilities expense				Treasury stock
		1,000,880	bb	bb	9,240			bb	62,150			bb	54,083			bb	124,900			bb	33,571			bb	50,000
		1,000,880			9,240				62,150				54,083				124,900				33,571				50,000
								These additional T-acccounts are provided for your use as necessary when making the adjusting entries. Be sure to insert sum formulas as needed depending on nature of account. You can use the sum formulas as supplied on existing T-accounts above.
	Insurance expense				Income taxes expense				Rent revenue earned				Wages payable				Interest expense				Interest payable				PIC-Treasury Stock
bb	45,833			bb	47,667
	45,833				47,667					-				-			-					-				-
	Cost of goods sold				Loss on Impairment				Loss Due to Decline of Inventory				Bad debt expense				Fair Value Adjustment in XYZ Stock								Loss on Exchange of Assets
	-				-				-				-				-								-

Post-Closing Trial Balance

ABC Corporation
Post-Closing Trial Balance
December 31, 2021
	Debit	Credit
Total	$ - 0	$ - 0

Grading Rubric

Grading Rubric for AC322 Comprehensive Problem
Adjusting Entries	Points Possible	Points Earned	Performance-Based Assessment Grading Rubric				Points Earned		Points Possible
1	5	0		Exemplary	Competent	Needs Work			5
2	5	0	Analyzing, Journalizing, and Posting of Adjusting Entries 60 points possible	60 - 54	53 - 42	41 - 0	0		5
3	4	0		90% or more of adjusting entries are analyzed, journalized, and posted correctly.	70-89% of adjusting entries are analyzed, journalized, and posted correctly.	Less than 70% of adjusting entries are analyzed, journalized, and posted correctly.			5
4	4	0
5	4	0
6	5	0							5
7	4	0							5
8	4	0							5
9	4	0	Preparation of Adjusted Trial Balance 8 points possible	8 -7	6 -5	4 - 0	0		5
10	4	0		Adjusted Trial Balance is 90% or more correct.	Adjusted Trial Balance is 70-89% correct.	Adjusted Trial Balance is less than 70% correct.			5
11	4	0							5
12	4	0							5
13	5	0	Compilation of Basic Financial Statements 54 points possible	54 - 48	47 - 37	36 - 0	0		5
14	4	0		Financial statements are 90% or more correct.	Financial statements are between 70% and 89% correct.	Financial statements are less than 70% correct.			5
	60	0							60
Adjusted Trial Balance	Points Possible	Points Possible	Analyzing, Journalizing, and Posting of Closing Entries 20 points possible	20 - 18	17 - 14	13 - 0	0		Points Possible
Correct totals (based on formula)	4	0		90% or more of closing entries are analyzed, journalized, and posted correctly.	70-89% of closing entries are analyzed, journalized, and posted correctly.	Less than 70% of closing entries are analyzed, journalized, and posted correctly.			4
Totals balance	4	0							4
	8	0							8
Income Statement	Points Possible	Points Possible	Preparation of Post-Closing Trial Balance 8 points possible	8 -7	6 -5	4 - 0	0		Points Possible
Correct Format	5	0		Post-Closing Trial Balance is 90% or more correct.	Post-Closing Trial Balance is 70-89% correct.	Post-Closing Trial Balance is less than 70% correct.			5
Correct Income Before Income Taxes (based on formula)	5	0							5
Correct Gross Profit (based on formula)	5	0							5
Correct Income Tax Expense	5	0	Overall Professional Preparation of Project 20 points possible	20 - 18	17 - 14	13 - 0	0		5
Components Sum to Net Income	5	0		Project is very professionally prepared with no spelling errors.	Project is mostly professional and contains 1-2 spelling errors.	Project is not professionally prepared and contains more than 2 spelling errors.			5
	25	0							25
Balance Sheet	Points Possible	Points Possible							Points Possible
Correct Form	5	0	Application of Electronic Spreadsheet Technology (Microsoft Excel) 30 points possible	30 - 27	26 - 21	20 - 0	0		5
Assets=Liabilities+Stockholders Equity	5	0		Highly skilled application of Excel, including the prevalent use of formulas and cell links.	Moderately skilled application of Excel, including moderate use of formulas and cell links.	Novice application of Excel, including little to no use of formulas and cell links.			5
Correct Totals for Assets, Liab, & Equity	5	0							5
Components Sum Correctly	4	0							4
	19	0							19
Statement of Shareholders' Equity	Points Possible	Points Possible							Points Possible
Correct Form	5	0							5
Correct Ending Shareholder's Equity	5	0				TOTAL POINTS EARNED	0	0.0%	5
	10	0				TOTAL POINTS POSSIBLE	200		10
Closing Entries	Points Possible	Points Possible							Points Possible
1	5	0	Instructor Comments:						5
2	5	0							5
3	5	0							5
4	5	0							5
	20	0							20
Post-Closing Trial Balance	Points Possible	Points Possible							Points Possible
Correct totals (based on formula)	4	0							4
Totals balance	4	0							4
	8	0							8
Technical Points (See items 1-5 in grading rubric)	150	0							150