Excel Solver
The Excel Solver is used to solve a mathematical model which has been entered
into an Excel spreadsheet
This mathematical model can be either
o Linear Programming problem = there is a linear relationship among all
constraints and the objective function
o Integer Programming problem = decision variables can only take integer
values in a given range (these integer values can also be boolean = 0 or 1
only)
The Mathematical Model
Decision Variables = variables assigned to a quantity or response that must be
determined in the problem
Objective Function = equation which states the goal of the model
Maximize
Minimize
Constraints = equations which state limitations of the problem
To solve the model, each constraint must be considered simultaneously in
conjunction with the objective function
The Solver Steps
Step 1: Read and Interpret the Problem
o Step 1.1: determine the decision variables
o Step 1.2: state the objective function
o Step 1.3: state any constraints
Step 2: Prepare the Spreadsheet
o Step 2.1: Enter the decision variables
o Step 2.2: Enter the constraints
o Step 2. 3: Enter the objective function
Step 3: Solve the model with the Solver
o Step 3.1: Set the Target Cell and choose Min or Max
o Step 3.2: Select Changing Cells
o Step 3.3: Add Constraints
o Step 3.4: Set Solver Options
o Step 3.5: Solve and review Results
Example
The problem:
Texo Part Manufacturers are trying to ship a portion of its latest products using a
freight line. The total area available on the train is 20,000 sq ft. and the train can
hold up to 40 tons of material. There are three types of items to be shipped.
There is 24 tons of Item 1 and each ton takes 525 sq ft. There is 20 tons of Item 2
which fills 680 sq ft per ton. The company has 30 tons of Item 3 which takes 600
sq ft per ton to hold. At least eight tons of each item must be shipped. How
much of each product should Texo ship if Item 1 earns $325 a ton, Item 2 $400
per ton, and Item 3 $350 per ton?
[email protected]
Step 1
Decision Variables
o Quantity (in tons) of Item 1 = x1
o Quantity of Item 2 = x2
o Quantity of Item 3 = x3
Objective Function
o Maximize Profit = $325x1 + $400x2 + $350x3
Constraints
o Quota : x1, x2, x3 >= 8
o Limit : x1 <= 24, x2 <= 20, x3 <=30
o Weight = x1 + x2 + x3 <= 40
o Space = 525x1 + 680x2 + 600x3 <= 20,000
Step 2
The spreadsheet should have each part of the model clearly entered
Step 3
The Solver parameters can now be set according to the cell references with the
appropriate model parts
[email protected]
Solver Options should also now be set
Results
The Results of the Solver are shown All constraints are met
Reports
Answer Report: lists the target cell and the changing cells with their original and
final values, constraints, and information about the constraints.
Sensitivity Report: shows how sensitive the solution is to small changes in the
objective function formula or the constraints. For linear models, the report includes
reduced costs, shadow prices, objective coefficient (with allowable increase and
decrease), and constraint right-hand side ranges.
Limits Report: lists the target cell and the changing cells with their respective values,
lower and upper limits, and target values. The lower limit is the smallest value that
the changing cell can take while holding all other changing cells fixed and still
satisfying the constraints. The upper limit is the greatest value.
Applications of the Solver
Transportation models Dynamic inventories
Assignment scenarios Production planning
[email protected]
[email protected]
