Question

A manager is trying to determine which of three production processes to implement to produce a component for a new product line. Process A would entail a variable cost of $17 per unit and an annual fixed cost of $150,000. Process B would entail a variable cost of $11 per unit and an annual fixed cost of $250,000. Process C would entail a variable cost of $20 per unit and an annual fixed cost of $180,000.

a. Develop three separate models in your spreadsheet to calculate Total cost for each process.

                  The models must be flexible and able to calculate Total cost for any Quantity produced.

b. Create a Cost-Volume graph that shows total cost lines for all three options.

                  (Volume should range from 0 to 30,000)

c. Write an interpretation of your graph

Answer 1

(a)

(b)

How to create the graph

(c)

Interpretation is that Process C is never an optimal choice. As long as the volume is less than or equal to 16,666, Process A is the optimal choice (i.e. gives minimum total cost). For volume = 16,667 onwards, Process B becomes optimal.