CHAPTER 2
1
After finishing this chapter, Students could
understand:
Meaning and composition of linear
optimization problem
Solve problem by using Solver
Analyze cases relate to optimal solution
Apply optimization problems in financial
situation
2
Model of linear planning has two important
features: objective (maximum or minimum)
and constraints
Model presents a problem about the
distribution of limited resource so that the
goal is optimization objective
Example:
3
Objective function
The parameter measures the result which
managers want
◦ Maximize: profit, rate of return, effective
◦ Minimize: cost or time
4
Constraints
The constraints could be seen as all of limitation which decision
variables must follow.
Two kinds of constraints: from limitations and requirements
Or distinguish as: natural, economic, or policies constraints
Capital limitation about capital, regulation of securities
commission
constraint of produce (natural limitation) and available
Production
resource (limitation about economy and natural)
Pay dividend Have profit (limitation of natural) or rate of profit could
not pass minimum number (limitation of policies)
5
Step 1: Transfering the management situation to
model
-Write the objective and constraint
-Confirm decision variable
Step 2: Transfering to excel
Step 3: Using Solver
6
Notice
◦ Every decision variables should be presented in difference cells
◦ Every constraints should be presented in difference row or column
◦ Variable or constraints should be presented nearly together
◦ Every factors (complement of fixed cost, cost by unit) are set in
difference row but near decision variable to reflect the effective and
the objective function will be presented near these factors.
◦ The cells of decision variable, objective are highlight by difference
colors.
◦ For constraints, the right of function is constant or formula does not
contain decision variables
7
Case study
Company C produce SP1- SP2. Objective: volume of SP1 và
SP2 to maximize profit
◦ Contribution margin: SP1: 56$; SP2: 40$
◦ Spare part of g1 = 1.280; g2=1.600;
◦ Sp1: use 8 g1 and 4 g2. SP2: 4 g1 and 12 g2.
◦ Spare part of chair leg: 760; SP1, SP2 need 4 chair legs.
◦ Spare part of g3 and g4: 140 and 120. Sp1, Sp2 only use
1 g3 or 1 g4.
◦ According the contract, weekly volume is higher than 100
units
◦ G1, g2, g3, g4 is limit
8
Material Sp1 Sp2 Total
g1 8 4 1280
g2 4 12 1600
leg 4 4 760
g3 1 0 140
g4 0 1 120
9
Decision variable
X1 and x2 represent for quantity of SP1 and SP2
The value of (x1,x2) are belong to the collection of feasibility
decisions
Objective function
56x1 + 40x2: max
Constraints
◦ (2.1) (constraint of g1) 8x1 + 4x2 ≤ 1280
◦ (2.2) (constraint of g2) 4x1 + 12x2 ≤ 1600
◦ (2.3) (constraint of contract)
x1 +x2 ≥ 100
◦ (2.4) (constraint of leg) 4x1+ 4x2 ≤ 760
◦ (2.5) (constraint of g3,g4) x1 ≤ 140 và x2≤ 120
◦ (2.6) (natural constraint) x1 ≥ 0 và x2 ≥ 0
11
Step 2
12
Step 3
Term of linear planning Term of Solver
Objective Set target cell
Decision variable By changing cells
Constraints Subject to the constraints/add
Constraint functions (the left of function) Cell reference
Limitation of constraint (the right of function) Constraint
Linear programing LP
13
Install Solver:
◦ Solver is available in Excel: Excel option Add In
Solver
◦ Solver is not available: copy OpenSolver into addins:
C:\User\appdata\roaming\microsoft\addins
14
15
Answer of Solver
16
Result of Company C
17
Sensitivity report
18
Notice
◦ The variance between smallest and biggest in table
could not be more than 6 or 7 numbers. (5% and
10.000)
◦ The right of table must be constant or formula does
not include decision variables
19
Case study 2
Dự án Năm 0 Năm 1 Năm 2 Năm 3 NPV (r =
10%)
W – 70 – 20 60 60 6,44
X ––– – 90 60 50 5,30
Y – 80 10 60 30 1,18
Z ––– – 50 30 30 1,86
Capital in current year and 1st year is not over
100 bil VND
20
Step 1: Objective
◦ NPV = 6,44w + 5,30 x + 1,18y + 1,86z –> max
◦ Contraint
70w + 80y 100
20w + 90 x – 10y + 50z 100
0 w 1 ; 0 x 1 ; 0 y 1 ; 0 z 1
21
Step 2
22
Step 3
23
