MKT 465 - Production & Operationsfour

SP 3.2

Solved Problem 3.2
Jenkins Manufacturing: Current or New Equipment
Problem Data
	Current	New
Annual Fixed Cost	$40,000	$60,000
Unit Variable Cost	$50.00	$25.00
Point of Indifference	800
Computations for a user-entered Sales Quantity
Sales Quantity	800
Annual Total Cost	$80,000	$80,000
Difference in Total Cost	$0	(positive implies "New" is cheaper)
Better Alternative	0
Data Table to Compare Alternatives
Sales Quantity	Current	New
	$80,000	$80,000
0	$40,000	$60,000
100	$45,000	$62,500
200	$50,000	$65,000
300	$55,000	$67,500
400	$60,000	$70,000
500	$65,000	$72,500
600	$70,000	$75,000
700	$75,000	$77,500
800	$80,000	$80,000
900	$85,000	$82,500
1000	$90,000	$85,000
1100	$95,000	$87,500
1200	$100,000	$90,000
1300	$105,000	$92,500
1400	$110,000	$95,000
1500	$115,000	$97,500
1600	$120,000	$100,000

SP 3.2

Goal Seek (Data, What-If Analysis…Goal Seek) can be used to quickly find the indifference point between making and buying. Shown below is an image of the correctly completed Goal Seek box for this model. Note that to find a "point of indifference," or breakeven, between Making and Buying, our spreadsheet model must explicitly compute the diffference in the costs of the two alternatives (done in cell B10). This is because Goal Seek can only operate on a single "set cell."

By setting up and computing a data table, one can quickly compute the annual costs for a range of sales quantities and generate a graph of the alternatives.

Spreadsheet is not explained in textbook. I'm geussing what formulas to enter. There is a data table in cells A45:C63. If the sheet is protected, the user can only click on and enter values for costs in B6:C7 and sales quantity in B12. The formulas are protected.