Preparation of Balance Sheet and an Income Statement | Solution Library

Question

Balance Sheet and Income Statement in Excel

REQUIRED: Given the data and information, use Excel to prepare a balance sheet as of December 31, 2015 and an income statement using multi-step for the year ended December 31, 2015 for ABC Corporation. Use the SUM function to total columns or rows of data when appropriate. Otherwise use =, + or – functions. Format the statements with leading $ signs, underscore and double-underscore as appropriate.

You are also required to propose adjusting journal entries as needed, as well as earning per share.

Shown below is a trial balance ABC Corporation for the period ending December 31, 2015 (with the exception of Accumulated Deficit, which shows the balance at December 31, 2014):

ACCOUNT	BALANCE	ACCOUNT	BALANCE

COGS	$ 436,843	Investments (*)	53,608
Loss on Sale of Equipment	1,036	Note Receivable (*)	102,099
Accumulated Deficit (*)	1,509	General & Administrative Expenses	110,358
Extraordinary Gain (net)	45,364	Unearned Revenue (*)	23,000
Accumulated Depreciation	23,583	Accounts Payable	78,359
Inventory	103,597	Prepaid Insurance (*)	7,500
Interest Income	7,638	Intangible Assets (net)	36,783
Property and Equipment (gross)	170,368	Common Stock	10,000
Sales Revenue	762,118	Amortization of Trademarks	2,348
Goodwill	65,642	Cash and Cash Equivalents	36,813
Accounts Receivable (gross)	98,237	Loss on Discontinued Operations (net)	50,368
Additional Paid-in-Capital	120,000	Allowance for Doubtful Accounts	1,250
Accumulated Other Comprehensive Income	4,006	Selling Expenses	135,683
Interest Expense	25,368	Accrued Expenses	22,368
Loans from Bank (*)	368,316

(*) Additional information:

ABC experienced significant losses during its initial years, leading to the Accumulated Deficit balance at 12/31/14.
Of the overall outstanding balance, $12,361 of loans will be due in 2016.
ABC’s investments consist of the following:
- Bonds they plan to hold to maturity of $12,364. The bonds mature in 2023.
- Stocks and bonds they plan to short in the current period of $7,632.
- ABC intends to hold on to the remainder of their portfolio on a long-term basis, but it open to disposing of the securities as well.
The note receivable is structured such that ABC will receive an installment of 10,683 by the end of 2016, and the remainder beyond 12/31/16.
A pool of customers paid ABC $23,000 in advance for goods in early December 2015. As of 12/31/15, 30% of the goods had left ABC warehouses and received by the customers.
ABC paid $7,500 up front for a one-year insurance policy on 8/1/2015.
The company had 26,000 common shares outstanding throughout 2015.
ABC has a 25% income tax rate
The company paid out $12,368 in dividends to its shareholders in 2015.

Summary: This question belongs to accounting and discusses about preparation of a company’s balance sheet and income statement.

Answer is in Excel format