Question

3. Gabriela Manufacturing must decide whether to insource (make) or outsource (buy) a new toxin-free carpet cleaner that works with its Miracle Carpet Cleaning Machine. If it decides to insource the product, the process would incur $300,000 of annual fixed costs and $1.50 per unit of variable costs. If it is outsourced, a supplier has offered to make it for an annual fixed cost of $120,000 and a variable cost of $2.25 per unit in variable costs.

(a) Construct a base-case spreadsheet model that shows both of these alternatives side-by-side. Use the same number of units (stored in a single cell) to drive the calculations.

(b) If the expected annual demand for the new cleaner is 300,000 units, what would you recommend that Gabriela Manufacturing do? Provide evidence to support your recommendation.

(c) Use Goal Seek to find the indifference point (where total costs are equal) between these two alternatives. (Hint: Goal Seek can work only with a single “Set cell.”) Create a cell in your model that computes the difference between the total costs of the two alternatives before running Goal Seek.

(d) Construct an XY chart of total cost versus quantity. Graph both alternatives on the same chart. Use a Data Table to generate the values for the chart. (Hint: The Data Table will need to track two output values: the total costs of the two alternatives. When setting up the Data Table, it will simply have an additional column, compared to the example presented in the text.)

Answer 1

1. Spreadsheet

	Insource Cost	Outsource Cost
Fixed Cost	300000	120000
Variable Cost/Unit	1.5	2.25
No of Units [Enter the value]	300000	300000
Total Cost	750000	795000

Formula Sheet: Copy Paste in Cell A1

	Insource Cost	Outsource Cost
Fixed Cost	300000	120000
Variable Cost/Unit	=1.5	=2.25
No of Units [Enter the value]	300000	300000
Total Cost	=B2+(B3*B4)	=C2+(C3*C4)

2. Indifference point: Make = Buy

      $300,000 + $1.50Q = $120,000 + $2.25Q

      Q = ($300,000 - $120,000) / ($2.25 - $1.50)

      Q = 240,000 carpet cleaners

3. Demand (Q) = 300,000 units.

      Cost to outsource: $120,000 + ($2.25 X 300,000) = $795,000

      Cost to insource: $300,000 + ($1.50 X 300,000) = $750,000

• Insourcing would cost $45,000 less.

(d) Graph: Cost Vs Quantity

No. of Units	Insource Cost	Outsource Cost
10000	315000	142500
20000	330000	165000
30000	345000	187500
40000	360000	210000
50000	375000	232500
60000	390000	255000
70000	405000	277500
80000	420000	300000
90000	435000	322500
100000	450000	345000
110000	465000	367500
120000	480000	390000
130000	495000	412500
140000	510000	435000
150000	525000	457500
160000	540000	480000
170000	555000	502500
180000	570000	525000
190000	585000	547500
200000	600000	570000
210000	615000	592500
220000	630000	615000
230000	645000	637500
240000	660000	660000
250000	675000	682500
260000	690000	705000
270000	705000	727500
280000	720000	750000
290000	735000	772500
300000	750000	795000

Excel Formula Sheet: Copy Paste in Cell A1

No. of Units	Insource Cost	Outsource Cost
10000	=300000+(1.5*A2)	=120000+(2.25*A2)
20000	=300000+(1.5*A3)	=120000+(2.25*A3)
30000	=300000+(1.5*A4)	=120000+(2.25*A4)
40000	=300000+(1.5*A5)	=120000+(2.25*A5)
50000	=300000+(1.5*A6)	=120000+(2.25*A6)
60000	=300000+(1.5*A7)	=120000+(2.25*A7)
70000	=300000+(1.5*A8)	=120000+(2.25*A8)
80000	=300000+(1.5*A9)	=120000+(2.25*A9)
90000	=300000+(1.5*A10)	=120000+(2.25*A10)
100000	=300000+(1.5*A11)	=120000+(2.25*A11)
110000	=300000+(1.5*A12)	=120000+(2.25*A12)
120000	=300000+(1.5*A13)	=120000+(2.25*A13)
130000	=300000+(1.5*A14)	=120000+(2.25*A14)
140000	=300000+(1.5*A15)	=120000+(2.25*A15)
150000	=300000+(1.5*A16)	=120000+(2.25*A16)
160000	=300000+(1.5*A17)	=120000+(2.25*A17)
170000	=300000+(1.5*A18)	=120000+(2.25*A18)
180000	=300000+(1.5*A19)	=120000+(2.25*A19)
190000	=300000+(1.5*A20)	=120000+(2.25*A20)
200000	=300000+(1.5*A21)	=120000+(2.25*A21)
210000	=300000+(1.5*A22)	=120000+(2.25*A22)
220000	=300000+(1.5*A23)	=120000+(2.25*A23)
230000	=300000+(1.5*A24)	=120000+(2.25*A24)
240000	=300000+(1.5*A25)	=120000+(2.25*A25)
250000	=300000+(1.5*A26)	=120000+(2.25*A26)
260000	=300000+(1.5*A27)	=120000+(2.25*A27)
270000	=300000+(1.5*A28)	=120000+(2.25*A28)
280000	=300000+(1.5*A29)	=120000+(2.25*A29)
290000	=300000+(1.5*A30)	=120000+(2.25*A30)
300000	=300000+(1.5*A31)	=120000+(2.25*A31)

Corresponding graph: