For six years now, SkyTec, Inc has been manufacturing electronic equipment. They have five main products and they are having difficulty in cost and pricing considerations. The company has a shared manufacturing overhead cost per quarter of $3,820,000 but is unsure of how to allocate it. They understand proper costing is necessary to determine price and profitability of each product line. The direct costs (direct material and direct labor) are easily traceable to each product and the information for the last quarter has been collected. A team at SkyTec has worked together to identify five possible cost drivers (units, machine setups, machine hours, direct labor hours, number of orders) for the shared overhead cost. This information has also been collected for the last quarter and presented in the table below. The team is ready for you help in allocating the overhead cost in order to determine the cost of each of the five electronics products that SkyTec produces.

Construct a spreadsheet that will allocate overhead and calculate unit cost for each of these alternative drivers (one results table that can be used to switch between the cost drivers). 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. Format the spreadsheet so that dollar amounts have a $. For the per unit amounts format to two decimal places, all other number should be format to zero decimal places. For numbers that are not dollar amounts, format with a comma.

Do not hard key when a formula should be used.