This paper describes advanced methods for finding a verified global optimum and finding all solutions of a system of linear programming, as implemented in the Premium Solver Platform, an extension of the Solver bundled with Microsoft Excel. It also describes the underlying tools that allow Excel spreadsheets to be used over linear data, with fast computation of optimization. Also it provides: a brief overview of Excel's Add-in Solver; basic theory of optimization as implemented within the Solver; advantages of the Excel Solver in linear programming, and three numerical examples outlining the steps involved in carrying out adjustment of Solver to solve the linear programming problems. The reasons to use of Excel for optimization can be considered a viable option are: (a) Excel is readily available in any Windows platform without any additional cost. (b) Excel is easy to use. (c) The data transfer to and from Excel is very flexible.

ResearchGate Logo

Discover the world's research

  • 20+ million members
  • 135+ million publications
  • 700k+ research projects

Join for free

Solving Linear Programming Problems

By Using Excel's Solver

By

Dr. Eng. Salim A. Saleh Ass. Lec. Thekra I. Latif

Engineering College College of Comp. Sci. & Math.

University of Tikrit University of Tikrit

2007- 2008 2007- 2008

Abstract:

This paper describes advanced methods for finding a verified global

optimum and finding all solutions of a system of linear programming, as

implemented in the Premium Solver Platform, an extension of the Solver

bundled with Microsoft Excel. It also describes the underlying tools that

allow Excel spreadsheets to be used over linear data, with fast

computation of optimization.

Also it provides: a brief overview of Excel's Add-in Solver; basic

theory of optimization as implemented within the Solver; advantages of

the Excel Solver in linear programming, and three numerical examples

outlining the steps involved in carrying out adjustment of Solver to solve

the linear programming problems. The reasons to use of Excel for

optimization can be considered a viable option are: (a) Excel is readily

available in any Windows platform without any additional cost. (b) Excel

is easy to use. (c) The data transfer to and from Excel is very flexible.

Keywords;

Operations Research, Linear Programming, Excel Solver, Optimization.

Introduction:

Since its introduction in February 1991, the Microsoft Excel Solver

has become the most widely distributed and almost surely the most

widely used general purpose optimization modeling system.

Bundled with every copy of Microsoft Excel and Microsoft Office

shipped during the last eight years, the Excel Solver is in the hands of 80

to 90 percent of users of office-productivity software worldwide. The

remaining 10 to 20 percent of this audience use either Lotus 1-2-3 or

Quattro Pro, both of which now include very similar spreadsheet solvers,

based on the same technology used in the Excel Solver. This widespread

availability has spawned many applications in industry and government

[1].

In review of the background and design philosophy of the Excel

Solver. It was clear up some common misunderstandings and pitfalls,

and to suggest ideas for good modeling practice when using spreadsheet

optimization. It was found many applications of the Excel Solver in

industry and education and describe how practitioners who are not

affiliated with the OR/MS ( Operations Research / Management Science )

community use it [2].

The Microsoft Excel Solver combines the functions of a graphical user

interface (GUI), an algebraic modeling language like GAMS [3] or

AMPL [4], and optimizers for linear, nonlinear, and integer programs.

Each of these functions is integrated into the host spreadsheet program as

closely as possible.

On the basis of the relevant literature and given that this can be easily

formulated as Linear Programming (LP) techniques would have been

widely used in every business or management school worldwide, they

have been, so far, hardly used in real world conditions by management.

This is because the LP formulation of, even quite simple, business

situations involves an exceptionally big number of variables and

constraints, and hence, expensive dedicated software requiring

specialized personnel needed to be used for handling the resulting

models. Thus LP, for a number of years, has been used only by very big

business, government agencies and organizations or in the frames of

academic research [5].

The extensive use of personal computers, the dramatic reduction of

their cost and the tremendous increase of their computing ability have

influenced the management culture worldwide. Senior, medium and

front-line management have now access to personal computers and

spreadsheet software such as Microsoft Excel [Microsoft Corporation,

(1985-2007)] is extensively used. The package contains Solver, an

exceptionally evolved and impressively powerful tool that is very

effective for handling linear and non linear optimization problems [6].

Each problem of LP situations are not only easily handled by Solver

but additional decision support information can also be obtained [5]. The

advantages of spreadsheets include the power and breadth of their

functions for quantitative analysis, and their intuitive grid-like user

interface with which many users are familiar and comfortable.

Spreadsheets are omnipresent in many organizations, so there is

already a large knowledge base upon which to draw. Specifically for OR,

spreadsheets offer a multitude of resources such as dynamic recalculation

and chart updating, statistical analysis, built-in optimization algorithms

(such as Excel Solver), programming languages (such as Excel's VBA),

database connectivity, rapid application development with visual

components, and the widespread availability of specialist "Add-Ins" [7].

In the following pages the paper introduces and describes the method

of using the Microsoft Excel's Solver to find the optimal solution of some

Linear Programming problems.

Steps of LP solution by Excel Solver

Step 1: User has to familiarize his/herself with the LP data set.

Step 2: Set up the optimization model (Model Construction).

Step 3: Setting up Excel Solver to solve LPs by the following sub steps:

1. Open a new Excel spreadsheet and name it to "Name of the

Problem".

2. Lay out the problem data in Excel spreadsheet as follows:

3. Type the formula ;

F4 = SUMPRODUCT ($B$2:$C$3: …:$E$2;B4:C4: … :E4) and pull-

down it to cells F6:F10.

4. Next, invoke the Excel Solver. To do this select Solver from the

Tools pull-down menu. (If Solver is not on the Tools menu, it will

be necessary to install this add-in using the Add-Ins option on the

Tools pull-down menu). In the Solver Dialog box, specify the

following ;

Target Cell: F4

Constraints: F6:F10 <=, =, or >= H6:H10

Changing Cells: $B$2:$C$3: …:$E$2

Equal To: Max or Min

5. While still in the Solver dialog box, click Options and set the

following options ;

Assume Linear Model: On

Assume Nonnegative: On

This tells Solver that your model is linear in variables and the

choice variables are all nonnegative, and then click OK.

6. While still in Solver, click Solve. This should return a dialog box

with the notice: "Solver found solution". If not, you have an error

somewhere, so go back and re-check all of the steps.

7. While still in the solver, click "Answer, Sensitivity, Limits" or "

Each you need" under "Reports" and click OK. This should

produce the following output under your worksheet entitled "

Answer Report", "Sensitivity Report "," Limits Report".

LINEAR PROGRAMMING PROBLEMS

The following linear programming problems will introduces you to the

exciting world of linear programming and describes the method of using

the Excel Solver in optimization of such problems.

Problem1: Maximizing Profit

Step 1: Familiarizing with the data set:

Stratton Co. problem states that it

• Produces two basic types of plastic pipes;

• Three resources have been identified as critical to pipe output

"Pipe extrusion hours, Packaging hours, and Special additive mix."

Stratton Company Data was summarized in the following Table:

The problem requirement to formulate an LP model to determine

how much of each type of pipe should be produced to maximize profit.

Step 2: Set up the optimization model (Model Construction):

Decision Variables:

P1= No. of pipe 1 to be produced

P2= No. of pipe 2 to be produced

Objective Function:

MAX Z= 34 P1 + 40 P2

Subject to Model Constraints:

4 P1 + 6 P2 ≤ 4 Extrusion hours

2 P1 + 2 P2 ≤18 Packaging hours

2 P1 + 1 P2 ≤16 Additive supply

P1 , P2 ≥ 0 Non-negativity

Step 3: Setting up Excel Solver to solve LPs

•Solver is an add-in to Excel

•Not automatically ready,

•To get solver ready

(In Excel points to → Tools → Add Ins then Scroll down to Solver Add

In → Check the box → Click on OK as shown figure 1)

• Only need to do this one time

To solve an LP using Excel Solver;

•Setup the spreadsheet;

TYPE data in one place (as shown in figure 2);

CREATE in D4 Cell the function ( as shown in figure 3);

Pull- Down D4 Function to The cells D5 to D7 ( as shown

in figure 4 );

OPEN Solver box with Tools → Solver ( as shown in

figures 5, 6 and 7);

CREATE Cells for decisions variables and formulas to

calculate LHS of constraints ( figure 8);

ENTER formulas to calculate Objective Function (Figs. 9

and 10);

CLICK OK then Solve to get the optimal solution ( Figs.11

and 12);

Now solver found the optimal solution, click on all reports to

keep the solution ( as in figure 13 );

Click on OK to get the optimal solution ( as in figure 14 );

All reports can be found in figures (15, 16, and 17);

Compare SOLVER solution with Graphical and SIMPLEX

solutions in figures (18 and 19);

Figure 2 The spreadsheet

Figure 4 Pull- Down fun. cell

Figure 6

Figure 8 Create D.V.& Constraints

Figure 10 Formula of Obj. Fun.

Figure 3 Create the function cell

Figure 5 Open Solver box

Figure 7

Figure 9

Figure 19 Simplex Solution

Problem2: Minimizing Problem

Consider the following problem:

Minimize Z= 0.6 X1 + 0.5 X2

S.T. 20 X1 + 50 X2 ≥ 100

25 X1 + 25 X2 ≥ 100

50 X1 + 10 X2 ≥ 100

X1 , X2 ≥ 0

By applying the solution steps by the Excel Solver that were applied

in the first problem, with changing the inequality symbols to ( >= ), it

has to be begun with the following spreadsheet of the problem:

The optimal solution, through Step 3 with changing sub step3-4

"Equal to by Min ", can be found as in figures (20, 21, 22, and 23).

Figure 23 Limits Report

Problem3: Artificial Starting Solution Problem

Consider the following problem [8]:

Minimize Z= 4 X1 + X2

S.T. 3 X1 + X2 = 3

4 X1 + 3 X2 ≥ 6

X1 + 2 X2 ≤ 4

X1 , X2 ≥ 0

By applying the solution steps by the Excel Solver that were applied

in the first problem, with adding each constraint and its inequality symbol

of (=,>=,<=) at a time individually by using Add in figure 7, it has to be

begun with the following spreadsheet of the problem:

The optimal solution, through Step 3 with changing sub step3-4

" Equal to by Min ", can be found as in figures (24, 25, 26, and 27).

Figure 27 Limits Report

Conclusions

Excel Solver provides a simple, yet effective, medium for allowing

users to explore linear programming problems. It can be used for large

problems containing hundreds of variables and constraints, and does these

relatively quickly, but as a teaching tool using small illustrative problems

it is very potent, particularly as the user must appreciate the structure of a

LP when entering it into the spreadsheet.

On the downside, one can't view the Tableau as it is generated at each

iteration and so those users who want to be proficient in the manual

methods of LP would find Solver less superior to allow this. It does,

however, produce a superior set of results and sensitivity reports when

compared to Simplex method, and, due to the spreadsheet nature, does

allow the student very quickly to observe the effects of any changes made

to constraints or the objective function.

References

1. Daniel Fylstra, Leon Lasdon, John Watson, and Allan Waren

(1998), "Design and Use of the Microsoft Excel Solver",

COMPUTERS / Computer Science Software Interfaces 28(5), Pp.

29–55.

2. Bodily, S. (1996), "Teaching MBA quantitative business analysis

with cases ", Interfaces, 26 (6), Pp. 132–149.

3. Brooke, A.; Kendrick, D.; and Meeraus, A. (1992), "GAMS, A

User's Guide ", Boyd and Fraser, Danvers, Massachusetts.

4. Fourer, R.; Gay, D. M.; and Kernighan, B. W. (1993), " AMPL: A

Modeling Language for Mathematical Programming ", Duxbury

Press, Pacific Grove, California.

5. Caine D.J., Parker B.J., (1996), "Linear Programming comes of

age: a decision support tool for every manager ", Management

Decision, 34(4), Pp. 46-53.

6. Burton, Carrol & Wall (1999), "Quantitative Methods for Business

& Economics", Longman, NY.

7. Alistair Clark (2007), " Free modeling languages for linear and

integer programming ", MSOR Connections 7 (3), Pp. 31-35.

8. Taha, H. A. (1982), " Operations Research an Introduction ", 3rd

ed., Macmillan Publishing Co., Inc., NY.

    

 

!" #"$% # " "&

' ()"& *+ ,- '. /

) - 0 "$% 1234 ) 56

- 0 5728 1234

2007 -2008 2007 -2008

:9

)









 

   

 ! " #$% &

'

()*+ , -. /

0" 1 2

34 ' 56 78 9 .: 7; ;< )=

+

* >

Solver 2?( @ A B"C D 6:E Microsoft Excel (F )=

+



$

(

GH$I /

J"<

+ 7 K

L

) 1; E / +5M N. 78 , -6( (>&L'% Excel spreadsheets 7O(?( & 1 P )J 34 Q )=R( / E

L+ 1

$L S

+ F*T< ,



GH$I -6 

!  Q! Excel's Add-in Solver U $-.( ; ;< S

+ *T< ' 7 :3 QV  U)=

+

 W8 78( #: 2

34 X=XJ

*TI

$"! Y

4

*

/

34 7 ZN 78( / :  )=

+

 Q &[ &

&W L #: Q

O

$

(F

34 \

I

:1$( )

1;C , -6 &L'% S

+ *T<  R 0 1

8 ] :;^   ?I>(_( $A [ 8

LEJ 78 9 .: 7; ;< `

< , -6

86 (> any Windows \JE(? `

I 5*' 28 VC , -6(?]> &L'% &; 2) 1;0 $(?a>( &

$

+

/

6 : BC B.

J \O

6 F

G[

/ *O( ( ((_( (

b+E 2/ *1 #E 23R )=

2&L'% F*T<

ResearchGate has not been able to resolve any citations for this publication.

ResearchGate has not been able to resolve any references for this publication.