Project #66824 - Accounting Excel Project

From the information given below, prepare the required schedules and financial statements using an excel spreadsheet being careful to use formulas whenever possible. Do not type in numbers that could otherwise be calculated or transferred from another schedule column or line item. When complete your file should then be e-mailed to my QC address: snussbaum@qc.cuny.edu. The spreadsheets will only be returned to you if there are problems if sent to me no later than April 20th.

           

 

You are thinking of investing in one of two corporations, both in the same industry, the Craig Corporation or the Lori Corporation. Selected data follows:

 

Sales data for the year ended 12/31/15:

Craig: Gross revenue $40,785,000; Returns $1,210,000

Lori: Gross revenue $50,245,000; Returns $2,530,000

 

Selling expenses:

Craig: Payroll $3,342,000; benefits $1,050,000, travel $350,200; supplies $450,150, commissions $520,300, marketing $810,000, postage $210,300, misc $41,500.

Lori: Payroll $4,431,000; benefits $1,650,000, travel $420,200; supplies $560,350, commissions $622,300, marketing $790,000, postage $292,100, misc $182,300.

 

Administrative expenses:

Craig: Distribution $1,428,000, warehouse $959,000, IT $625,200, finance $529,400, human resources $413,250; administrative $310,000, depreciation $122,000

Lori: Distribution $1,712,310, warehouse $1,020,220, IT $516,420, finance $615,270, human resources $285,450; administrative $530,600, depreciation $156,100.

 

Other Gains and Losses

Craig: Sale of machine with a book value of $27,500 for $4,000 cash.

Lori: Sold a marketable security at a loss of $11,000.

 

 

Irregular Items:\

Craig: Extraordinary loss due to warehouse fire of $422,000 before tax effect.

Lori: Loss on sale of discontinued division of $875,000 before tax effect.

 

Tax rates: 35% for both companies:

 

Outstanding shares of common stock:

Craig: 2,000,000 $1PV shares authorized, 800,000 shares issued and outstanding.

Lori:  3,100,000 $1PV shares authorized, 950,000 shares issued and outstanding.

 

 

Selected Balance Sheet Information:

Craig:

Cash $1,822,000;

marketable securities (at cost $410,300, at fair value $495,400);

Account receivable (gross $1,725,400 less allowance for doubtful accounts of $112,100); Finished goods inventory, opening balance $189,400,

 purchases $12,443,400,

ending balance $175,610,

Prepaid expenses $40,210.

Plant assets at cost $21,400,000,

accumulated depreciation $7,312,000.

Accounts payable, $613,410,

accrued expense payable $191,260,

bonds payable $4,814,693,

capital stock $1,450,000,

retained earnings beginning balance $1,883,000.

 

Lori:

Cash $2,310,000;

marketable securities (at cost $652,430, at fair value $699,400);

Account receivable (gross $2,935,400 less allowance for doubtful accounts of $172,300); Finished goods inventory, opening balance $322,640,

purchases $14,892,400,

ending balance $285,420,

Prepaid expenses $61,310.

Plant assets at cost $19,240,000,

 accumulated depreciation $8,245,000.

Accounts payable, $693,450,

accrued expense payable $201,220,

bonds payable $1,809,466,

Capital stock $2,210,000,

retained earnings beginning balance $1,375,500.

 

Both companies declared and paid cash dividends of $1 per share during the year.

 

Required:

 

1)    Prepare a separate schedule comparing the selling and administrative expenses of the two companies. Separate subtotals are required for both selling and administrative expenses.

2)    Prepare a separate schedule of cost of goods sold for each of the companies.

3)    Prepare a comparative income statement in good form for the two companies. Be sure the selling, administrative and cost of goods number comes from your first two schedules by way of formula. Do not simply type in numbers. Percentage of sale data should be presented for cost of goods sold, gross margin, selling expense and administrative expense and net income.

4)    Prepare an EPS summary following your income statement.

5)    Prepare a Statement of Retained Earnings as shown in chapter 5 of the text.

6)    Prepare a Classified Balance Sheet as shown in Chapter 5 of text.

7)    Properly formatted numbers, the appropriate use of $ signs, underlines and double underlines are imperative. Style counts!

 

This assignment is due no later than April 26th. Please work on it independently of your fellow classmates.

 

 

 

               
       

Craig Co

 

Lori Co.

 
       

 

 

 

 

Total Assets

   

$18,234,520

 

$17,114,230

 
       

 

 

 

 

Net Income

   

$10,082,157

 

$11,774,594

 
               
               
               
               
               

 

Subject Business
Due By (Pacific Time) 04/16/2015 11:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy