Question

SOLVE THE FOLLOWING 2 LINEAR PROGRAMMING PROBLEMS USING EXCEL AND THE SOLVER ADD-IN. PLEASE SHOW ME ALL THE EXCEL STEPS.

PROBLEM #1:   Maximize Z = $60X + $90Y

                            Subject to:   60X + 30Y >= 1,500

                                                    100X + 100Y <= 6,000

                                                                             Y >= 30

                                                                          X, Y >= 0

PROBLEM #2: Minimize Z = $3,000X + $1,000Y

                             Subject to:   60X + 20Y >= 1,200

                                                        10X + 10Y >= 400

                                                     40X + 160Y >= 2,400

                                                                         X, Y >= 0

Answer 1

Problem #1:

Step 1: Fill in the data and provide formula as follows in the excel sheet:

Step 2: Go to Data->Solver-> fill in the following links wherever provided:

Step 3: Click on Solve. We get the following results:

Objective	X	Y
Units	0	60.00
Z	60	90	5400.00
	X	Y		Maximum
Constraint 1	60	30	1800.00	1500
Constraint 2	100	100	6000.00	6000
Constraint 3		1	60.00	30

Hence, only when Y =60 and X=0, Z is maximum.

Problem #2:

Step 1: Fill in the data and provide formula as follows in the excel sheet:

Step 2: Go to Data->Solver-> fill in the following links wherever provided:

Step 3:

Objective	X	Y
Units	10	30.00
Z	3000	1000	60000.00
	X	Y		Maximum
Constraint 1	60	20	1200.00	1200
Constraint 2	10	10	400.00	400
Constraint 3	40	160	5200.00	2400

Hence Z is minimum when x=10 and y=30.