In order to determine the effect of different cost drivers on the overhead allocated and the resulting cost per unitthis problem used the vlookup function.

View Accessible Table

Cell C15 is = VLOOKUP (B15, B4:K7,2).  This function operates by using the one (1) in cell B15 to look up a value in the table (the table is cells B4:K7).  The function then matches the 1 and returns the value in the second (2) column, which is unit.

Cell E15 is = VLOOKUP(B15, B4:K7,10).  In this case, the function returns the value in the 10^th column (must count all columns even empty ones).

Cell E20 is =VLOOKUP(B15, B4:K7, 4)*$G$15.  In this case, the function returns the value in the 4^th column and then multiplies it by the value in cell G15. 

Instructions

For twelve years now, Marginby, 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 $4,120,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 Marginby 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 Marginby 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. 

 

Product Name	Direct Material	Direct Labor	Units	# of Machine Setups	Machine Hours	Direct Labor Hours	# of Orders
VWPB678	$242,000	$398,475	25,300	15	379,500	18,975	250
VJDX357	$238,000	$913,500	58,000	20	870,000	43,500	610
YQLU899	$237,000	$645,750	41,000	18	615,000	30,750	430
KVGN590	$228,900	$450,450	28,750	8	331,250	21,450	180
EKFT567	$176,900	$264,600	6,125	3	131,250	12,600	54

Requirements

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? (should be 5 answers)
2. How much overhead is allocated to the VWPB678 product line when machine hours are used as the cost driver for overhead allocation?
3. What is the total cost of the YQLU899 product line when # of orders are used as the cost driver for overhead allocation?
4. What is the cost per unit of the KVGN590 product line when the # of machine setups is used as the cost driver for overhead allocation?
5. The cost per unit for the EKFT567 product is highest when using which 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".

ABC A36 fx B C D 1 2 3 Pos Standard 4 1 Unit 10000 5 2 Setup 100 6 3 Machine Hour 7 4 labor Hour 20000 4000 8 9 10 11 12 Ov 13 14 Overhead cost 500000 15 1 VLOOKUP(B15,B4:K7,2) eply ges... End Review... E F G Letitia Pleis Deluxe 15000 500 =VLOOKUP(B15,84:K7,10) 16 17 Cost -E3 18 Direct Materials 12000 19 Direct Labor 800 20 Allocated Overhead 21 Total =VLOOKUP(B15,B4:K7,4)* $G$15 -SUM(E18:E20) 22 23 Cost per unit -E21/E4 24 25 45000 9000 =E14/E15 =G3 36000 4000 90% =VLOOKUP($B$15,$B$4: -SUM(G18:G20) =G21/G4 H Grand 20000 400 60000 16000 I ] K per =VLOOKUP(B15,B4:K7,2) =13 80000 3200 =VLOOKUP($B$15,$B$4: -SUM(118:120) -121/14 Total =SUM(E4:14) -SUM(E5:15) |=SUM(E6:16) -SUM(E7:17) -K3 |=SUM(E18:118) -SUM(E19:119) =SUM(E20:120) -SUM(K18:K20)