Applying Excel
Solver for
Optimization Case
DA9
Presented By:
Thao Vy - 23005696
Agenda
1. Background
2. What is Excel Solver?
3. How to add Solver in Excel?
4. Formulating LP Model
5. Using Excel Solver
Maximize profit
Business
Linear Minimize costs
Programming-
Optimization Maximize ROI
Quy hoạch tuyế n tính
Optimization is
Linear
an interesting
Programming
aspect in many
industries
What is Excel Solver
Belongs to a special set of commands often
referred to as What-if Analysis Tools
It is primarily purposed for simulation and
optimization of various business and
engineering models
Especially useful for solving linear programming
problems, aka linear optimization problems, and
therefore is sometimes called a linear programming
solver
How to add Solver in Excel?
For Excel 2010, Excel 2013, Excel 2016
Let’s figure out some
components of Excel Solver
Objective
Variable Cells
Cells Constraints
Cells that contain The cell containing a Restrictions or limits of
variable data that can be formula that represents the possible solutions to
changed to achieve the the objective, or goal, of the problem
objective the problem
e.g. No. of products sold e.g. Maximized Profit, e.g. Constraint on
to achieve highest profit Lowest Cost materials stock
Formulating LP Model
01 Understand problem
02 Identify the decision variables
03 Identify the objective function
04 Identify the constraints
Identify any upper or lower bounds on the
05 decision variables
Example 1:
Product Mix Problem
Blue Ridge Hot Tubs makes two hot tubs the Aqua-Spa
and the Hydro-Lux (say, A and B)
Each unit of A requires 9 hours of labor and 12 feet of
tubing, 1 pump.
Each unit of B requires 6 hours of labor and 16 feet of
tubing, 1 pump.
A company only have 1,566 production labor hours and
2,880 feet of tubing, 200 pumps available.
The profit per unit of A and B are $350 and $300
respectively.
Q: How many A and B should produce to
maximize profits during the next production
cycle?
Formulating LP Model
01 Understand problem
How many product A
and B should produce to maximize
profit, while using no more than
200 pumps, 1,566 labor hours, and
2,880 feet of tubing?
Formulating LP Model
02 Identify Variable Cells
Let X1 represent the number
of A to produce and X2
represent the number of B to
produce.
Formulating LP Model
03 Identify Objective Function
Earns a profit of $350 on each A (X1) and $300 on
each B (X2) they sells. Thus, Company’s objective
of maximizing the profit he earns is stated
mathematically as:
MAX:
Formulating LP Model
04 Identify The Constraints
The constraint on the number of pumps:
The constraint on the number of labor hours:
The constraint on the number of feet of tubing:
Formulating LP Model
05 Identify upper or lower bounds on the decision variables
It is impossible to produce a negative number
of hot tubs. Therefore, the following two
constraints also apply to this problem:
Formulating LP Model
The complete LP model can be stated as:
Formulating LP Model
Plot the constraints for the problem and identify its
feasible region
Using Excel Solver
Reserve separate cells in the spreadsheet to
01 represent each decision variable.
Create a formula in a cell in the spreadsheet that
02 corresponds to the objective function.
For each constraint, create a formula in a separate
03 cell in the spreadsheet that corresponds to the left-
hand-side (LHS) of the constraint.
Using Excel Solver
A spreadsheet model for the product mix problem
Using Excel Solver
Using Excel Solver
Solving Method
Generalized Reduced Gradient (GRG) Nonlinear - Default
Best For: Nonlinear problems.
Simplex LP:
Best For: Linear problems.
Evolutionary:
Best For: Complex and non-smooth nonlinear problems.
Using Excel Solver
Using Excel Solver
Type of Report
Answer
Type of Report
Sensitivity
Type of Report
Limits
Thank
You!
