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)

Use the following Excel formulation to populate the data-table for the total cost of A, B, C for volume ranging from 0 to 30,000.

The sequence of operation in data table: Complete all the formulae on the sheet ---> select the array $G$2:$J$18 --> go to data-table from the what-if analysis menu ---> enter $C$2 in the column input cell ---> Click OK

The output of the data table

	TC-A	TC-B	TC-C
0	150,000	250,000	180,000
2,000	184,000	272,000	220,000
4,000	218,000	294,000	260,000
6,000	252,000	316,000	300,000
8,000	286,000	338,000	340,000
10,000	320,000	360,000	380,000
12,000	354,000	382,000	420,000
14,000	388,000	404,000	460,000
16,000	422,000	426,000	500,000
18,000	456,000	448,000	540,000
20,000	490,000	470,000	580,000
22,000	524,000	492,000	620,000
24,000	558,000	514,000	660,000
26,000	592,000	536,000	700,000
28,000	626,000	558,000	740,000
30,000	660,000	580,000	780,000

(b)

Generate the straight lines for TC using the scatter plot option in Excel from the above output.

(c)

Interpretation

• Process C is never a desired option because it does not give minimum cost in any situation.
• Process A is best if the volume is 16,666 or less.
• Process B is best if the volume is 16,667 or more.