ms excel and vba - module 2: solver tool -...

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool

Optimization:Overview

LinearOptimization

NonlinearOptimization

System ofLinearEquations

System ofNonlinearEquations

MS Excel and VBAModule 2: Solver Tool

Bruno Abreu Calfa

Last Update: March 28, 2012

1 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool

2 Optimization: Overview

3 Linear Optimization

4 Nonlinear Optimization

5 System of Linear Equations

6 System of Nonlinear Equations

2 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool






3 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




What is Solver?• The Solver is an add-in for MS Excel, which is used for the

optimization and simulation of business and engineering models

• It solves complex linear and nonlinear problems and can also be usedin conjunction with VBA to automate tasks

• To enable the Solver add-in, go to File→ Options→ Add-Ins→Go... and make sure the option “Solver Add-in” is selected

4 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization





optimization and simulation of business and engineering models• It solves complex linear and nonlinear problems and can also be used

in conjunction with VBA to automate tasks

• To enable the Solver add-in, go to File→ Options→ Add-Ins→Go... and make sure the option “Solver Add-in” is selected

4 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization





optimization and simulation of business and engineering models• It solves complex linear and nonlinear problems and can also be used

in conjunction with VBA to automate tasks• To enable the Solver add-in, go to File→ Options→ Add-Ins→Go... and make sure the option “Solver Add-in” is selected

4 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Solver Parameters

• Objective Cell: cell which will represent the objective orgoal

• Changing Cells: cells that can change or adjust tooptimize the target cell

• Constraints: restrictions/limitations that you apply on thechanging cells

5 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool






6 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Motivating Example

• The goal of optimization is to maximize or minimize anobjective by systematically changing variables

• For example: find the minimum of the function f (x) = x2

over all real values of x

• Solution: x∗ = 0 (minimizer) with f (x∗) = 0

7 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Classes of Problems I

• Unconstrained Optimization• General formulation:

minx

f (x)

where:x Decision variable

f (x) Objective function

• Example:min

xx2 − 4x + 3

• Feasible region: all real values of x• Remark: min f (x) is equivalent to −[max−f (x)] (think

of the parabola f (x) = x2 − 1)

8 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Classes of Problems II

• Plot of f (x) = x2 − 4x + 3

• Solution: x∗ = 2 with f (x∗) = −1

9 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Classes of Problems III• Constrained Optimization

• General formulation:

minx

f (x)

s.t. h(x) = 0g(x) ≤ 0


f (x) Objective functionh(x) Equality constraintsg(x) Inequality constraints

• Example:min

xx2 − 4x + 3

s.t. x − 1 ≤ 0

• Feasible region: x ∈ (−∞, 1]

10 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Classes of Problems IV

• Plot of f (x) = x2 − 4x + 3 and constraint x ≤ 1

• Solution: x∗ = 1 with f (x∗) = 0

11 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool






12 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Definition

minx

cT x

s.t. Ax ≤ bx ≥ 0

where:

c Cost coefficientsx Decision variablesA Matrix of coefficients of xb Vector of right-hand side elements

13 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem I

• A refinery has available two crude oils that have the yieldsshown in the following table. Because of equipment andstorage limitations, production of gasoline, kerosene, and fueloil must be limited as also shown in this table. There are noplant limitations on the production of other products such asgas oils. The profit on processing crude #1 is $1.00/bbl and oncrude #2 it is $0.70/bbl. Find the optimum daily feed rates ofthe two crudes to this plant.

Maximum allowableVolume percent yields product rateCrude #1 Crude #2 bbl/day

Gasoline 70 31 6,000Kerosene 6 9 5,400Fuel oil 24 60 5,000

14 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem II

• Formulation:

maxx

x1 + 0.7x2

s.t. 70x1 + 31x2 ≤ 6000 (Gasoline)6x1 + 9x2 ≤ 5400 (Kerosene)24x1 + 60x2 ≤ 5000 (Fuel oil)x1, x2 ≥ 0

15 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Using Solver• Enter the coefficients and formulas

• Open Solver and set its parameters as follows:

• Hit “Solve”• See file Solver_Examples.xlsx, worksheet “LO

Example”

16 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Using Solver• Enter the coefficients and formulas• Open Solver and set its parameters as follows:


Example”

16 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization





• Hit “Solve”

• See file Solver_Examples.xlsx, worksheet “LOExample”

16 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization






Example”16 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool






17 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Definition

minx

f (x)

s.t. h(x) = 0g(x) ≤ 0


f (x) Objective functionh(x) Equality constraintsg(x) Inequality constraints

• At least one of the functions f (·), g(·), or h(·) is nonlinear

18 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem

• Cost minimization of a distillation column

• See file NLO_Example.pdf for problem description• See file Solver_Examples.xlsx, worksheet “NLO

Example”

19 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem

• Cost minimization of a distillation column• See file NLO_Example.pdf for problem description

• See file Solver_Examples.xlsx, worksheet “NLOExample”

19 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem

• Cost minimization of a distillation column• See file NLO_Example.pdf for problem description• See file Solver_Examples.xlsx, worksheet “NLO

Example”

19 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool






20 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Definition• We want to solve the following:

Ax − b = 0

wherex Vector of variablesA Matrix of coefficientsb Vector of right-hand-side elements

• Idea: solve the following optimization problem

minx

z

s.t. Aix − bi = 0 i = 1, . . . , n

z −n∑

i=1

n∑j=1

(aijxj − bi) = 0

that is, set the sum of the residues to zero (in Solver, this is equivalentto making the objective cell as the sum of the residues and checkingthe option “Value Of:” with the value 0)

21 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem: Definition

• Linear Material Balances in a Process Flowsheet forethanol production

• See file LE_Example.pdf for problem description• See file Solver_Examples.xlsx, worksheet “LE

Example”

22 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization






• See file LE_Example.pdf for problem description

• See file Solver_Examples.xlsx, worksheet “LEExample”

22 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization






• See file LE_Example.pdf for problem description• See file Solver_Examples.xlsx, worksheet “LE

Example”

22 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Outline

1 The Solver Tool






23 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Definition

• We want to solve the following:

f (x) = 0

where

x Vector of variablesf (x) Vector of functions (at least one is nonlinear)

• Idea: solve the following optimization problem

minx

n∑i=1

f 2i (x)

s.t. fi(x) = 0 i = 1, . . . , n

that is, minimize the sum of the squares of the residues

24 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization





• Chemical equilibrium of the combustion of propane(C3H8) and air (O2 and N2) to form ten products

C3H8 +R2 (O2 + 4 N2) −−→ Products

• There are 10 variables and 10 nonlinear equations• All parameters are given• Taken from: Meintjes, K., Morgan, A. P. (1990) Chemical

Equilibrium Systems as Numerical Test Problems. ACMTransactions on Mathematical Software. 16(2): 143-151.

25 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization






C3H8 +R2 (O2 + 4 N2) −−→ Products

• There are 10 variables and 10 nonlinear equations

• All parameters are given• Taken from: Meintjes, K., Morgan, A. P. (1990) Chemical


25 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization






C3H8 +R2 (O2 + 4 N2) −−→ Products

• There are 10 variables and 10 nonlinear equations• All parameters are given

• Taken from: Meintjes, K., Morgan, A. P. (1990) ChemicalEquilibrium Systems as Numerical Test Problems. ACMTransactions on Mathematical Software. 16(2): 143-151.

25 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization






C3H8 +R2 (O2 + 4 N2) −−→ Products

• There are 10 variables and 10 nonlinear equations• All parameters are given• Taken from: Meintjes, K., Morgan, A. P. (1990) Chemical


25 / 26

MS Excel andVBA

Front Matter

Table ofContents

The SolverTool


LinearOptimization




Example Problem: Equations

f1(n) = n1 + n4 − 3 = 0

f2(n) = 2n1 + n2 + n4 + n7 + n8 + n9 + 2n10 − R = 0

f3(n) = 2n2 + 2n5 + n6 + n7 − 8 = 0

f4(n) = 2n3 + n9 − 4R = 0

f5(n) = K5n2n4 − n1n5 = 0

f6(n) = K6n0.52 n0.5

4 − n0.51 n6

(p

nT

)0.5= 0

f7(n) = K7n0.51 n0.5

2 − n0.54 n7

(p

nT

)0.5= 0

f8(n) = K8n1 − n4n8

(p

nT

)= 0

f9(n) = K9n1n0.53 − n4n9

(p

nT

)0.5= 0

f10(n) = K10n21 − n2

4n10

(p

nT

)= 0

where nT =∑10

i=1 ni

• See file Solver_Examples.xlsx, worksheet “NLE Example”26 / 26

ms excel and vba - module 2: solver tool -...

Documents