Assignment: Smokey and the Bandit produces outdoor activity clothing. The product line consists of pants, jackets, tops, accessories and boots. Data has been collected related to direct materials and direct labor for the five product lines. Smokey and the Bandit has also collected information on five possible cost drivers (units machine setups, machine hours, labor hours, and prime cost). All this information is listed below. Construct a spreadsheet that will allocate overhead for each of these alternative drivers and will calculate the total per unit cost for each product line. Use the VLOOKUP function when constructing the spreadsheet so that you can determine the effect of different cost drivers on the overhead allocated and the resulting cost per unit. Product Line Units Average Sales Price per unit Total Material Cost Total Labor Cost Pants Jackets Tops Accessories Boots 4,200 2,500 10,400 18,500 1,500 $85 $115 $40 $12 $135 $246,500 $145,000 $ 166,400 $ 37,000 $ 72,000 $18,315 $25,200 $34,500 $15,210 $ 9,270 Product Line Machine Setups Machine Hours Labor Hours Pants 38 1,465 Jackets 26 1,070 Tops 65 2,700 Accessories 95 2.250 Boots 20 724 *Remember Prime cost are DM+DL cost and are a potential cost driver for Smokey & the Bandit 1,221 1,400 2,300 845 515 Total overhead cost to be allocated: $380,000 After constructing your spreadsheet answer the following questions? 1. What is the total cost per unit of each product line when labor hours are used as the cost driver for overhead allocation? 2. How much overhead is allocated to Jackets when prime cost are used as the cost driver for overhead allocation? 3. What is the total cost of Boots when machine hours are used as the cost driver for overhead allocation? 4. What is the total cost per unit of Accessories when machine setups is used as the cost driver for overhead allocation? 5. What is the allocation rate when units are used as the cost driver? 6. If you are the manager of the Jackets product line which cost driver would you prefer? Why? 7. Currently the OH cost are being allocated based on DL hours. If the company decides to use units as the cost driver how would you react if you are the Boots product line manager