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
