Question

Novell Networks manufactures five different models of network interface cards for desktop and laptop computers. As summarized in the following table, each of these network devices require differing amounts of printed circuit (PC) board, resistors, memory chips, and assembly time. Per Unit Requirements HyperLink FastLink SpeedLink MicroLink EtherLink PC Board, sq. in. 20 15 10 8 5 Resistors 28 24 18 12 16 Memory Chips 8 8 4 4 6 Labor (in hours) .75 .60 .50 .65 1.0 The unit wholesale price and manufacturing cost for each model are as follows: Per Unit Revenue and Costs HyperLink FastLink SpeedLink MicroLink EtherLink Wholesale Price $189 $149 $129 $169 $139 Manufacture Cost $136 $101 $96 $137 $101 In the next production period, Novell Networks has 80,000 square inches of PC board, 100,000 resistors, 30,000 memory chips, and 5,000 hours of assembly time available. The company can sell all the product it can manufacture, but the marketing department wants to be sure that it produces at least 500 units of each network interface card. Determine the product mix that maximizes profit.

Please use Excel

Answer 1

The Excel solution is:

The Excel formulas are:

The Solver parameters are:

The answer is:

	HyperLink	FastLink	SpeedLink	MicroLink	EtherLink	Used		Available
Printed Circuit Boards (sq in)	20	15	10	8	5	61000	<=	80000
Resistors	28	24	18	12	16	100000	<=	100000
Memory Chips	8	8	4	4	6	30000	<=	30000
Assembly Labor (hrs)	0.75	0.6	0.5	0.65	1	4037.5	<=	5000
Available	500	500	500	500	500
Used	500	500	1500	3250	500
	HyperLink	FastLink	SpeedLink	MicroLInk	EtherLink
WholesalePrice	189	149	129	169	139
Manufacturing Cost	136	101	96	137	101
Profit	53	48	33	32	38
Dec. Var.	500	500	1500	3250	500
Z	223000

Z = $223000