Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Operations Research I: Models & Applications
Using Excel to Solve Linear Programs
Michelle Chang
Department of Information Management
National Taiwan University
OR I: Using Excel to Solve LPs 1 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Road map
I The Solver add-in.
I Example 1: producing desks and tables.
I Example 2: personnel scheduling.
OR I: Using Excel to Solve LPs 2 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Excel Solver add-in
I “Solver” is a Microsoft Excel add-in program for solving linear
programs, integer programs, and nonlinear programs.
I Solver adjusts the values of the decision variables in your model.
I To satisfy all the constraints.
I Maximize or minimize the objective value.
OR I: Using Excel to Solve LPs 3 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Install Solver add-in for Windows 2019
OR I: Using Excel to Solve LPs 4 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Install Solver add-in for Mac 2019 (1)
OR I: Using Excel to Solve LPs 5 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Install Solver add-in for Mac 2019 (2)
OR I: Using Excel to Solve LPs 6 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Road map
I The Solver add-in.
I Example 1: producing desks and tables.
I Example 2: personnel scheduling.
OR I: Using Excel to Solve LPs 7 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Complete formulation
I According to the previous videos, let
x1 = number of desks produced in a day and
x2 = number of tables produced in a day.
I The formulation of this example is
max 700x1 + 900x2
s.t. 3x1 + 5x2 ≤ 3600 (wood)
x1 + 2x2 ≤ 1600 (labor)
50x1 + 20x2 ≤ 48000 (machine)
x1 ≥ 0
x2 ≥ 0
I Let’s use the Solver add-in to find an optimal solution!
OR I: Using Excel to Solve LPs 8 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Solve by the Solver add-in
I An optimal solution of this LP is (884.2, 189.5). z ∗ = 789473.7.
OR I: Using Excel to Solve LPs 9 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Road map
I The Solver add-in.
I Example 1: producing desks and tables.
I Example 2: personnel scheduling.
OR I: Using Excel to Solve LPs 10 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Complete formulation
I According to the previous videos, let xi be the number of people who
start to work on day i for five consecutive days.
I The formulation of this example is
min x1 + x2 + x3 + x4 + x5 + x6 + x7
s.t. x1 + x4 + x5 + x6 + x7 ≥ 110
x1 + x2 + x5 + x6 + x7 ≥ 80
x1 + x2 + x3 + x6 + x7 ≥ 150
x1 + x2 + x3 + x4 + x7 ≥ 30
x1 + x2 + x3 + x4 + x5 ≥ 70
x2 + x3 + x4 + x5 + x6 ≥ 160
x3 + x4 + x5 + x6 + x7 ≥ 120
xi ≥ 0 ∀i = 1, ..., 7.
I Let’s use the Solver add-in to find an optimal solution!
OR I: Using Excel to Solve LPs 11 / 12
Install Solver Add-in in Excel Example 1: producing desks and tables Example 2: personnel scheduling
Solve by the Solver add-in
I An optimal solution is (3.3, 0, 53.3, 0, 13.3, 93.3, 0). z ∗ = 163.3.
OR I: Using Excel to Solve LPs 12 / 12
