You run a manufacturing outfit that produces Widget, an electronic part that is used in automotive production. A certain expensive componentlets 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 products 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: $2,000
• The Material Cost per Unit: $250
• Labor Hours to Produce the First Unit: 9 hours
• Learning Percentage: 80%
• Labor Cost (including benefits) per Hour: $80
• Overhead Cost: 20% of labor, material, and fixed cost
• Purchase cost per unit $1000

1. Create an X,Y scatter graph for the Make Option. The X-axis of the graph is Q (quantity produced). The Y-axis should only include the total setup 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 scatter 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).

3. What is the indifference point between the two (Make or Buy) options? Show a precise computation (Hint: your answer here should be consistent with your graph in (2). Explain what this means.