Refer to the original data given in the preceding problem for Rapid City Technology, Inc. In the preceding problem, Rapid City Technology, Inc. manufactures chemicals used in agricultural pest control. The controller has established the following activity cost pools and cost drivers.

An order for 1,000 boxes of a chemical product designated JLRP has the following production requirements.
Machine setups....................................................................... 6 setups
Raw material..................................................................... 9,000 pounds
Hazardous materials........................................................ 2,100 pounds
Inspections............................................................................... 8 inspections
Machine hours..................................................................... 550 machine hours

Required:
1. Calculate the unit cost of a production order for 100 specially coated containers used in transporting chemicals. In addition to direct material costing $210 per container and direct labor costing $60 per container, the order requires:
Machine setups...................................................................4
Raw material....................................................................800 pounds
Hazardous materials.......................................................400 pounds
Inspections..........................................................................4
Machine hours..................................................................60
2. Build a spreadsheet: Construct an Excel spreadsheet to solve the preceding requirement. (This will be an extension of the spreadsheet constructed for requirement (6) in the preceding problem.) Show how the solution will change if the data given in the preceding problem change as follows: the overhead associated with machine setups is $500,000, and there are 1,000 inspections budgeted.

Budgeted Overhead Cost Budgeted Level for Cost Driver Activity Cost Pool Machine setups.. Material handling Hazardous waste Cost Driver Pool Rate S1,000,000 Number of setups 250 $4,000 per setup 300,000 Weight of raw material 75,000 Ib $4 per pound Weight of hazardous control Quality control 100,000 300,000 800,000 chemicals used Number of inspections Machine hours 10,000 Ib. 2,000 40,000 $10 per pound $150 per inspection $20 per machine hour Total S2,500,000