Smokey and the Bandit produces outdoor activity clothing. Theproduct line consists of pants, jackets, tops, and accessories.Data has been collected related to direct materials and directlabor for the four product lines. Smokey and the Bandit has alsocollected information on four possible costdrivers (units, batches, machine hours, labor hours). All thisinformation is listed below. Construct a spreadsheet that willallocate overhead for each of these alternative drivers and willcalculate the total per unit cost for each product line.Use the VLOOKUP function (show them) whenconstructing the spreadsheet so that you can determine the effectof different cost drivers on the overhead allocated and theresulting cost per unit.
Product Line | Units | | Average Sales Price per unit | Total Material Cost | Total Labor Cost |
Pants | | 4,600 | $73 | $234,600 | $20,115 |
Jackets | | 2,500 | $98 | $145,000 | $25,200 |
Tops | | 9,800 | $36 | $156,800 | $32,970 |
Accessories | | 18,500 | $12 | $ 37,000 | $15,210 |
Product Line | Batches | | | Machine Hours | | Labor Hours |
Pants | | | 42 | | 1,640 | 1,341 |
Jackets | | | 26 | | 1,730 | 1,400 |
Tops | | | 78 | | 2,600 | 2,198 |
Accessories | | | 95 | | 2,250 | 845 |
Total overhead cost to be allocated: $321,560
After constructing your spreadsheet answer the followingquestions?
- What is the total cost per unit of each product line whenmachine hours are used as the cost driver for overheadallocation?
- How much overhead is allocated to Jackets when direct laborhours are used as the cost driver for overhead allocation?
- What is the total cost of Tops when direct labor hours are usedas the cost driver for overhead allocation?
- What is the total cost per unit of Accessories when the numberof batches is used as the cost driver for overhead allocation?
- What is the allocation rate when units are used as the costdriver?
- If you are the manager of the Jackets product line which costdriver would you prefer? Why?
- Currently the OH cost are being allocated based on DL hours. Ifthe company decides to use units as the cost driver how would youreact if you are the Accessories product line manager?
