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
LM213	$137,000	$41,250	41,000	180	5,500	2,750
NP1215	$128,900	$43,500	18,750	75	7,250	2,900

After constructing your spreadsheet answer the following questions?

1. What is the cost per unit of each product when direct labor hours are used as the cost driver for overhead allocation?
2. How much overhead is allocated to the NJ1246 product line when machine hours are used as the cost driver for overhead allocation?
3. What is the total cost of the LM213 product line when direct labor hours are used as the cost driver for overhead allocation?
4. What is the cost per unit of the NP1215 product line when the number of batches is used as the cost driver for overhead allocation?
5. What is the allocation rate when machine hours are used as the cost driver?

Other information

• You may consult with other class members for clarification purposes, but you must build the spreadsheet and answer the questions yourself. All Excel and Word files must be built from scratch. Any violations will result in a zero on the project.
• You must use VLookup in this project.
• You must create a nicely formatted Excel spreadsheet for the calculations, you need to create either a word document or separate worksheet within your Excel file to answer the questions. This spreadsheet should be ready for professional presentation.
• You will submit your assignment through the Canvas assignment tool.
• The calculation worksheet should be set up so that it will print on one page (File Print change No scaling to Fit Sheet on One Page.
• Format the assignment so that it looks appropriate. With the number formats described in assignment