Solution Library

Preparation of Balance Sheet and an Income Statement

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

 

Download Full Solution

Comments

  • HWA
    Rasha

    this is a very good website

  • HWA
    maani

    I have 50 questions for the same test your page is showing only 28

  • HWA
    joeanne

    hi can you please help or guide me to answer my assignments. thanks

  • HWA
    joeanne

    hi can anyone help or guide me to my assignments. thanks

  • HWA
    Monik


  • HWA
    Cristina

    This solution is perfect ...thanks

  • HWA
    Janete

    Hello Allison,I love the 2nd image that you did! I also, had never heard of SumoPaint, is something that I will have to exolpre a bit! I understand completely the 52 (or so) youtube videos that you probably watched. Sometimes they have what you want, sometimes they don't! However, it is always satisfying when you are able to produce something that you have taught yourself. Great job!Debra 0 likes

  • HWA
    Sandeep

    Perfect bank of solution. 

  • HWA
    Oxana

    great !

  • HWA
    Paul Brandon-Fritzius

    thanks for the quick response. the solution looks good. :)

  • HWA
    tina Johnson

    thnx for the answer. it was perfect. just the way i wanted it. 

  • HWA
    Giuseppe

    works fine.