Question

1. A company manufactures two products: large fans and medium fans. Each large fan requires 3 hours of wiring and 2 hours of drilling. Each medium fan requires 2 hours of wiring and 1 hour of drilling. There are 480 hours of wiring time available and 280 hours of drilling time available. Each large fan yields a profit of $25. Each medium fan yields a profit of $18. The company wants to manufacture at least 20 large fans. The objective is to maximize total profit.

(a) Formulate a linear optimization model for this problem by defining the decision variables, objective function and all the constraints. What do they represent?

(b) Find the optimal solution of this model by hand using the Corner Points graphical method.

Please show with steps and formulas on excel as to how to solve these problems; :)

Answer 1

(a)

Let 'L' and 'M' represent the number of large and medium fans.

Max Z = 25 L + 18 M
s.t.
3 L + 2 M <= 480 (Wiring)
2 L + 1 M <= 280 (Drilling)
L >= 20 (min L)
L, S >= 0

(b)

Formulate this table in the following way:

A B E L L>= 20 0 50 3L + 2M<= 480 =( 480-3*B3)/2 =(480-3*B4)/2 =(480-3*B5)/2 =(480-3*B6)/2 =(480-3*B7)/2 2L+1M <= 280 (280-2*B3) = (280-2*B4) =(280-2*B5) =(280-2*B6) =(280-2*B7) 100 6 7 150 300

Then go to the scatter plot option to develop the graph. For the first two constraints, keep the 'L' column in X-axis and the 'C' and 'D' column in the Y-axis. For the third column, do the reverse i.e. keep the 'L' column in Y-axis and 'E' column in X-axis.

Select Data Source ? x ? X Chart data range: The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the Series panel. Switch Row/Column Edit Series Series name: Legend Entries Series) Horizontal Category) Axis Labels =Sheet13!$C$21 Add Edit Remove EEdit Series X values: 3L +2M<= 480 =Sheet13!$B$3:$B$7 2L+1M <= 280 Series Y values: L>= 20 =Sheet13!$C$3:$C$7 * = 3L +2M<= 4... 6 = 0, 50, 100, 15... 6 OK = 240, 165, 90, ... Cancel Hidden and Empty Cells OK Cancel

Select Data Source ? x Chart data range: The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the Series panel. Switch Row/Column Edit Series Series name: Legend Entries Series) Horizontal Category) Axis Labels =Sheet13!$D$21 Add Edit X Remove EL Edit Series X values: 3L + 2M<= 480 =Sheet13!$B$3:$B$7 2L+1M <= 280 Series Y values: L>= 20 =Sheet13!$D$3:$D$ 7 * = 2L+1M<= 2... * = 0, 50, 100, 15... = 280, 180, 80, ... Cancel OK Hidden and Empty Cells OK Cancel

Select Data Source ? x Chart data range: The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the Series panel. Edit Series Switch Row/Column Series name: Legend Entries Series) Horizontal Category) Axis Labels =Sheet13! $E$21 Add Edit X Remove - E Edit Series X values: 3L +2M<= 480 =Sheet13! $E$3:$E$7 2L+1M <= 280 Series Y values: L>= 20 =Sheet13!$B$3:$B$7 * =L >= 20 * = 20, 20, 20, 20... * OK = 0, 50, 100, 15... Cancel Hidden and Empty Cells OK Cancel

(20, 210) 3 L+2 M <= 480 (80, 120) 2L+1 M <= 280 L>= 20 (20,0) 50 100 (140, 150 200

The evaluation of the corner points are as follows:

Corner points	L	M	Z = 25 L + 18 M
(20, 0)	20	0	500
(140, 0)	140	0	3500
(80, 120)	80	120	4160
(20, 210)	20	210	4280 (max)

Optimal solution: L = 20, M = 210, Max Z = $4,280