Chapter 2 Section 2

Excel Companion Chapter 2 --- LP Maximization
Return to Table of Contents	Return to Chapter 2 Index
Linear Programming	*Machines.xls*

Open the file Machines.xls and click the tab for Graph 1.

SITUATION: Three machines, A, B, and C, are needed to manufacture two types of nails: type X and type Y. To make one pound of type X requires 5 minutes on machine A, 18 minutes on machine B, and 11 minutes on machine C. To make one pound of type Y requires 10 minutes on machine A, 18 minutes on machine B and 4 minutes on machine C. On one day, machine A will be available for at most 150 minutes, machine B for at most 324 minutes and machine C for at most 132 minutes. The manufacturer can sell all the nails that it can produce and makes a profit of $3.00 per pound for type X and $5.00 per pound for type Y. The goal is to determine the optimal mixture of type X and type Y nails to manufacture in order to maximize daily profit.

In the figure on the right, the feasible region is in white and the corners are at the origin and at points P, Q, R, and S whose coordinates are: (0, 15),

(6, 12), (8.57, 9.43), and (12, 0) respectively.

As the Excel graph confirms in sheet 2_2_2, the intersection of the boundary lines for machines A and C is not one of the corner points since that point is above the boundary line for the machine B constraint. The feasible solution set is made up of only those points in the first quadrant that are on or below the boundary lines. The objective function is of the form

Z = ax+by, where a is the unit profit for type X nails and b is the unit profit for type Y nails. The current values of the parameters a and b are 3.00 and 5.00 respectively. By changing the value of these coefficients in cells C4 and E4, we obtain profit functions with different slopes.

Machines.xls - Graph 1

All points on the $50.00 iso-profit line, such as (0, 10), (6, 6.4), and (10, 4), satisfy the equation 3x + 5y = 50. All points on the dotted line with the double-headed arrows in sheet 2_2_2 give a $30.00 profit. Observe that iso-profit lines are parallel to each other and that the $30.00 iso-profit line is closer to the origin (0,0) than the $50.00 iso-profit line. As iso-profit lines move further and further away from the origin, the corresponding profit increases. If you click on the middle part of the line with arrows and slowly translate it away from the origin, you will observe that the last iso-profit line in contact with the feasible region contains the point (6, 12). This point corresponds to a profit of 3* 6+5* 12=78. Verify that this is indeed the maximum profit by checking each of the other corner points: (8.57, 9.43), (12, 0), (0, 0), and (0, 15) in the objective function.

QUESTIONS:

Name three points on the $30.00 profit line. The table to the right of the graph in Graph 1 may be useful for this purpose.

x	y

The point (5,13) would give a profit of $80.00. Explain why this point is not feasible.

a) Suppose the profit function is changed to Z = 3.00x + 6.00y. Is it possible to make a profit of $80.00? If it is possible, name the coordinates.

b) What is the maximum profit?

c) How can this maximum profit be obtained?

x	y

The Machines problem with the original parameters can be formulated for the Solver Add-in as shown below.

The left-hand sides, LHS, for the machine A, B, and C constraints are in the three cells directly below the target cell. The inequality for all three constraints is <=. The right hand sides, RHS, of each constraint are entered in the column furthest to the right. The direction column is for easy reference only. The Target Cell and Changing Cells should be set in the Solver Parameters dialog box. Click the radio button for Max since the objective function for profit is to be maximized. Don’t forget to make the non-negativity constraint explicit. Assume Linear Model should be checked under Options before clicking the Solve button in the Solver Parameters box. As in the Vitamins problem, you have the options of keeping any solutions on the spreadsheet and/or generating an Answer Report similar to that shown below.

Machines.xls - Answer Report 1

Explore Machines 2 and Graph 2 of Machines.xls. Pressing the <F9> key randomly generates coefficients of the constraints and coefficients of the objective function.

Two other stored LP models for exploration are Plywood.xls and Disketts.xls. The diskettes problem is stated in the first sheet of Disketts.xls. The formulation for Disketts.xls differs from the LP problems Vitamins and Machines in that the constraints for Disketts.xls are mixed. Some constraints are represented by greater than or equal to inequalities and others are represented by less than or equal to inequalities.

QUESTIONS:

Questions 4 - 8 that follow: apply to the Diskettes problem which is formulated below.

Maximize the profit function Z = 1.25x +0.50 y
Subject to non-negativity and

x + y ³ 4
x + y £ 10
-x + 3y ³ 0
x ³ 1

Name five points that satisfy all of the above constraints.

x	y

Name the point that maximizes Z.

x	y

In questions 6 - 8 that follow: change the objective to Z = 0.50x + 1.25y and leave the constraints unchanged.

What is the slope of all iso-value line?

What values of x and y produce the maximum value of Z?

x	y

Prepare a blank worksheet for Solver. Find the solution, generate an Answer Report. What is the otimum solution according to Solver?

Enter your name here:

first name last name

Return to Table of Contents

Return to Chapter 2 Index