Question

You run a manufacturing outfit that produces Widget, an electronic part that is used in automotive production. A certain expensive component—let’s call it Product X-- is one of the materials required in assembling each Widget.

Currently, you are purchasing Product X from a supplier at a cost of $1,000 per unit. You are contemplating to produce Product X in-house (instead of buying or outsourcing) for a variety of reasons, including more control of the product’s quality, lead time, and inventory. Manufacturing Product X in-house is subject to economies of scale. In particular, the learning curve applies to the labor content in manufacturing the product. As such, the average cost to manufacture Product X decreases as the number of units produced (Q) increases.

In order to assist you in making a choice between buying Product X from a supplier or making the part in house, develop a template using Excel for evaluating the total costs, as well as average cost, for the Buy and Produce options under varying levels of annual demand (Q) for Product X.

Additional information (inputs to your comparative analysis) for the Produce option is as follows:

• Setup Cost (FC):                                                                  $1,000
• The Material Cost per Unit (TMC):                                          $250
• Labor Hours to Produce the First Unit (T1):                          8 hours
• Learning Percentage (L%):                                                         90%
• Labor Cost (including benefits) per Hour (LCpH): $75
• Overhead Cost (OHC):                                                                20% of labor, material, and fixed cost
• Purchase cost per unit (PuC)                                                     $1000

1. Create an X,Y line graph for the Make Option. The X-axis of the graph is Q (quantity produced). The Y-axis should only include the total fixed cost, total labor cost, total material cost, total overhead cost, and total cost. Name and label the graph completely and clearly. Be sure that anyone reading this graph will know what each of the graph components means (i.e. use appropriate graph labels and titles).

2. Create and X,Y graph comparing the average cost per unit of making widget in-house and the average cost per unit of the widget if purchased from the supplier. Name and label the graph clearly and completely. Be sure that anyone reading this graph will know what each of the graph components means (i.e. use appropriate graph labels and titles). What is the indifference point between the two options? Explain what this means.

Answer 1

Question 1

Summary of cost for 1 unit production

Fixed cost	1000
Overhead1	50
Overhead2	520
Overhead3 ( fixed as 20 %FC)	200
Gross Fixed cost	1770	(Total)
Material cost	250
purchase cost	1000
Labour hourly rate	75
Hours need to produce	8
Learning percentage	0.9
Labour cost Per unit	600
Gross Variable cost	2600	( Total)
Total cost	4370
Units produced	1

Quantity	Fixed cost	Material cost	Labour cost	Overhead cost	Total Cost
1	1000	250	600	770	4370
2	1000	500	1200	1680	5880
3	1000	750	1800	2930	7730
4	1000	1000	2400	4520	9920
5	1000	1250	3000	6450	12450
6	1000	1500	3600	8720	15320
7	1000	1750	4200	11330	18530
8	1000	2000	4800	14280	22080
9	1000	2250	5400	17570	25970
10	1000	2500	6000	21200	30200

2: Inhouse vs production