Calculations shown | Accounting homework help

For the month ended October 31

Selling Expenses 96,000

Raw Materials, beginning 7,000

Raw Materials, ending 10,900

Utilities, Factory 15,000

Direct labor cost 84,600

Depreciation, factory 50,000

Purchases of raw materials 65,000

Sales 523,000

Indirect labor 80,000

Administrative Expenses 96,400

Work in Process, beginning 110,000

Work in Process, ending 90,000

Finished Goods, beginning 130,000

Finished Goods, ending 140,000

 

 

Required:

1. Change the name of Sheet2 tab at bottom of page to Cost of Goods Manufactured.  Create a solution that looks similar to Exhibit 2-18 on Page 67 in the textbook. 

Insert the Calculation of Direct Materials Used after the line titled “Plus Manufacturing Costs Incurred”.  See Exhibit 2-17 on Page 66 in the textbook.

Indent the Calculation of Direct Materials Used amounts one column to the left.  There should be two number columns.

Add a three line heading at the top with the Company Name on line 1, the name of the schedule on line 2, and the date on line 3.  See above for an example.

The numbers on this page should be all formulas.  Do not type any numbers on this page.  They should all be linked to the Data Box or be a computation.

 

2. Change the name of Sheet3 tab at bottom of page to Income Statement.  Create a solution that looks similar to Exhibit 2-15 on Page 65 in the textbook. 

Insert the Calculation of Cost of Goods Sold in the Income Statement.  See Exhibit 2-19 on Page 67 in the textbook.

Indent the Calculation of Cost of Goods Sold amounts one column to the left.  There should be two number columns.

The numbers on this page should be all formulas.  Do not type any numbers on this page.  They should all be linked to the Data Box or be a computation.

 

3. Go through the “Checklist for Microsoft Excel” handout to make sure that you have followed all the rules.

 

4. Check figures:

Cost of Goods Manufactured           310,700

Net Operating Income            29,900

For many years, Thomson Company manufactured a single product called a LEC 40. Then three years ago, the company automated a portion of its plant and at the same time introduced

a second product called a LEC 90 that has become increasingly popular. The LEC 90 is a more complex product, requiring 0.50 hour of direct labor time per unit to manufacture and

extensive machining in the automated portion of the plant. The LEC 40 requires only 0.30 hour of direct labor time per unit and only a small amount of machining.  

Manufacturing overhead costs are currently assigned to products on the basis of direct labor-hours.      

Despite the growing popularity of the company’s new LEC 90, profits have been declining steadily. Management is beginning to believe that there may be a problem with the

company’s costing system.       

Material and labor costs per unit are as follows:        

LEC 40 LEC 90       

Direct materials $50.00  $100.00       

Direct labor (@ $10.00 per hour) $3.00  $5.00       

Direct labor time per unit   Hard Code from data above   

Management estimates the following for the current year:        

Manufacturing overhead costs $665,000        

Units produced and sold 25,000 5,000      

Required (Complete the templates by filling in the highlighted cells)       

Complete the templates for Part 1, 2 and 3 by filling in the highlighted cells.      

Do not hard code the amounts. You must link to the data given and enter formulas.      

Format all number cells as Accounting.        

Use the round function and show 2 places after the decimal where appropriate.      

Round all rates and per unit amounts to the penny.       

Use the sum function for totals.        

Part 1 Compute the predetermined manufacturing overhead rate assuming that the company continues to apply manufacturing overhead cost on the basis of direct labor-hours.

Using this rate and other data from the problem, determine the unit product cost of each product.      

Part 2 Management is considering using activity-based costing to apply manufacturing overhead cost to products for external financial reports.   

The activity-based costing system would have the following four activity cost pools:       

Activity Cost Pool Activity Allocation Base Estimated Overhead Cost      

Maintaining parts inventory Number of part types $60,000       

Processing purchase orders Number of purchase orders 145,000      

Quality control Number of tests run 285,000      

Machine related Machine-hours 175,000      

$665,000         

Expected Activity      

Activity Pool LEC 40 LEC 90 Total     

Number of part types 500 700 1,200     

Number of purchase orders 2,600 1,400 4,000     

Number of tests run 5,550 9,450 15,000     

Machine-hours 3,000 7,000 10,000     

Determine the activity rate (i.e., predetermined overhead rate) for each of the four activity cost pools.      

Part 3 Using the activity rates you computed in part (2) above, do the following:      

a. Determine the total amount of manufacturing overhead cost that would be applied to each product using the activity-based costing system.   

After these totals have been computed, determine the amount of manufacturing overhead cost per unit of each product.     

b. Compute the unit product cost of each product.       

Check figures:        

Part 1:        

LEC 40 Total unit cost 72.95       

LEC 90 Total unit cost 138.25       

Part 3:        

LEC 40 Total unit cost 64.09       

LEC 90 Total unit cost 182.56