BevArt has been in operations for three years now and is having difficulty understanding the cost of the four beverage related products that they make. The company has a shared manufacturing overhead cost per quarter of $535,000 but is unsure of how to allocate it. 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 BevArt has worked together to identify four possible cost drivers (units, batches, machine hours, direct labor hours) 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 four products that BevArt produces. Construct a spreadsheet that will allocate overhead and calculate unit cost for each of these alternative 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. Product Name Direct Material Direct Labor Units # of Batches Machine Hours Direct Labor Hours NJ1246 $142.000 $31.500 35.000 150 4.100 2,100 ZX876 $138.000 $36.750 78,000 200 4.000 2.450