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 $345,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 identifyfourpossible 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 theVLOOKUPfunction 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

NO246

$142,000

$31,500

3000

5

4,100

2,100

AR478

$138,000

$36,750

7000

10

4,000

2,450

LM213

$137,000

$41,250

4000

8

5,500

2,750

RY780

$128,900

$43,500

8750

15

7,250

2,900

After constructing your spreadsheet answer the following questions?

1. What is the cost per unit of each product when machine hours are used as the cost driver for overhead allocation?
2. How much overhead is allocated to the AR478 product line when direct labor 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 RY780 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?