Question

The HVAC products group manufactures some of their own HVAC parts, but can also outsource the parts to two outsourcing facilities. The group needs to determine which parts are to be made internally and which parts are to be outsourced. The following table describes the internal resources required and the overall demand for the coming week.

Resources required	Part 1	Part 2	Part 3	Part 4	Part 5
Machine time (minutes per part)	12	16	22	10	25
Labor (minutes per part)	30	40	45	20	40
Materials (cost per part)	$19	$20	$17	$11	$20
Demand	241	241	182	224	163

This week they have 36 hours of machine time available at a rate of $45 per hour and 84 hours of labor available at a rate of $23 per hour. Due to quality control issues part 1 and part 2 must be produced in the same facility. The cost per part for outsourcing is listed in the table below.

	Outsource cost per part
	Part 1	Part 2	Part 3	Part 4	Part 5
Outsource 1	$43	$54	$58	$58
Outsource 2	$46	$57		$54	$45

Create an Excel model to determine the optimum sourcing strategy to minimize the total cost.

Answer 1

Excel formulations:

Solver inputs:

Solution:

	Part-1	Part-2	Part-3	Part-4	Part-5
Produce	0	0	0	216	0	0	0
Outsource-1	241	241	182	0	0	0	0
Outsource-2	0	0	0	8	163	0	0
If produced	0	0	0
If outsourced-1	1	1	0
If outsourced-2	0	0	0
Total	241	241	182	224	163
Demand	241	241	182	224	163
						Consumed	Available
Machine time	12	16	22	10	25	2160	2160
Labor	30	40	45	20	40	4320	5040
Machine time	$9.00	$12.00	$16.50	$7.50	$18.75	$1,620
Labor	$11.50	$15.33	$17.25	$7.67	$15.33	$1,656
Materials	$19	$20	$17	$11	$20	$2,376
Outsource 1	$43	$54	$58	$58	$9,999	$33,933
Outsource 2	$46	$57	$9,999	$54	$45	$7,767
					Total cost	$47,352