ITOM 6202 Management Decision Analysis

1
ITOM 6202Management Decision Analysis

• Linear Programming(Chapter 3)

2
Constrained Optimization

• A constrained optimization model takes the form
  of a performance measure to be optimized over a
  range of feasible values of the decision
  variables.
• The feasible values of the decision variables are
  determined by a set of constraints (usually
  inequalities).
• Values of the decision variables must be chosen
  such that all constraints are satisfied while
  either maximizing or minimizing the desired
  objective function.

3
Linear Programs (LPs)

• In a linear program (linear optimization model),
  the objective function and all constraints are
  linear
• LPs can be solved very efficiently
• LPs can contain tens, hundreds, or thousands of
  decision variables and constraints

4
Formulating LPs

• Every LP has three important components
• Decision variables
• Those resources controlled by the decision-maker
• Objective function
• A single performance measure to be minimized or
  maximized (e.g., maximize profit, minimize cost)
• Constraints
• Constraints are restrictions or requirements on
  the choice of solutions
• Constraints stem from physical, economic or
  policy limitations or requirements

5
Formulating LPs

• The formulation of an LP should follow these
  three steps
• Identify the decision variables
• Identify the constraints
• Which resources are in scarce supply?
• Which factors limit your use of or access to
  resources?
• Specify an objective function
• What is a reasonable measure for a good decision?

6
Example Constraints

• Investment decisions are restricted by the amount
  of available capital and by government
  regulations
• Production decisions are limited by plant
  capacity and the availability of raw materials
  and components
• Staffing and flight plans of an airline are
  restricted by the maintenance needs of the planes
  and the number of employees on hand
• The use of a certain type of crude oil in
  producing gasoline is restricted by the
  characteristics of the gasoline (e.g., the octane
  rating, etc.)

7
Oak Products Example

• Consider a small furniture manufacturer, Oak
  Products, Inc.
• The company has two products
• Captain chairs
• Mate chairs
• Based on an economic forecast for the next week,
  Oak Products has determined that the company can
  sell as many chairs as it can produce at the
  current wholesale price
• Oak Products needs to decide how many Captains
  and Mates to produce next week so as to maximize
  profit

8
Oak Products Example

• The following factors must be considered
• The unit profit contribution (price minus unit
  variable cost) is 56 for each Captain sold and
  40 for each Mate
• Each chair is assembled from long dowels, short
  dowels, legs, and one of two types of seats
• Each Captain requires 8 long and 4 short dowels
• Each Mate requires 4 long and 12 short dowels
• There is a total inventory of 1280 long dowels
  and 1600 short dowels for next weeks production

9
Oak Products Example

• The total inventory of legs is 760 units and each
  chair of either type uses 4 legs
• The inventory of heavy seats (one for each
  Captain) and light seats (one for each Mate) is
  140 and 120, respectively
• Management has entered into an agreement with the
  union to manufacture a minimum of 100 chairs in
  any combination each week so as to guarantee the
  workers pay for a minimum number of hours per
  week

10
Oak Products Example

• Oak Products problem is known as an optimal
  production plan problem
• Formulate the decision problem as an LP
• See board

11
Lets summarize the information given so far in a
table
Given these considerations, now decide how many
Captains and Mates to produce next week.
This is called an optimal production mix problem
or an optimal production plan.
The first step in solving this is to identify the
constraints and the objective function.
12
For notation purposes, let C number of Captains
chairs and M number of Mates chairs to be
produced.
Start with the number of long dowels
Both types of chairs require long dowels.
So, we can mathematically describe this as
8( Captains produced) 4( Mates produced)
1280
or
8C 4M 1280
13
Once again, here is the equation for the long
dowels
8C 4M 1280
However, since only 1280 long dowels are
available and we can use at most, only that
amount, we must make this equation an inequality
in order to satisfy this restriction. In this
case
8C 4M lt 1280
We use the ? (less than or equal to) inequality
because, we can use all 1280 long dowels, we can
use less than that amount, but we will never be
able to use more than 1280.
This is called an inequality constraint.
Left hand side (LHS) is called a constraint
function.
Right hand side (RHS) specifies the limitation.

14
Now lets consider the rest of the constraints
Short Dowels We need 4 for each Captain and 12
for each mate and we cannot exceed 1600 short
dowels.
4C 12M lt 1600
Legs Each chair requires 4 legs and we have 760
in inventory.
4C 4M lt 760
15
Heavy Seat These seats are only used for
Captains chairs and only 1 per chair is needed.
140 are available.
1C 0M lt 140 or 1C lt 140
Light Seat These seats are only used for Mates
chairs and only 1 per chair is needed. 120 are
available.
1M lt 120
16
We need to consider one other constraint not
given in the table.
Remember that there is a union agreement that we
have to honor The total number of Captains and
Mates chairs cannot fall below 100.
This means that we can make exactly 100 chairs,
more than 100 chairs, but never less than 100
chairs. Therefore, the inequality that we will
use will be gt (greater than or equal to).
C M gt 100
Note that there are no coefficients (1) in front
of C or M because we can make any combination of
Captains or Mates chairs as long as the total
number does not fall below 100.
17
Nonnegativity Conditions
Since it does not make sense to produce a
negative number of Captain or Mate chairs, we
must include two additional conditions called
non-negativity constraints
C gt 0 and M gt 0
Nonnegativity means that the resulting values can
be 0 or positive, but not negative.
18
Oak Products LP Formulation

• max 56C 40M
• subject to
• 8C 4M 1280 (long dowels)
• 4C 12M 1600 (short dowels)
• 4C 4M 760 (legs)
• C 140 (heavy seats)
• M 120 (light seats)
• C M 100 (union agreement)
• C, M 0 (non-negativity)

19
Some Terminology

• max 56C 40M (objective function)
• subject to
• 8C 4M 1280 (constraint)

coefficient


LHS(left-handside)
RHS(right-handside)
variable
inequality
20
Some LP Rules

• Constraint LHS consists of as many terms as there
  are decision variables
• Each term consists of a coefficient multiplied by
  a variable
• Coefficient might be 0, in which case the term
  does not have to be displayed
• Each variable appears only once on the LHS
• Terms are separated by either or -
• Constraint RHS consists of a single constant
  (number)
• There are no variables on the RHS
• Objective function has the same structure as the
  constraint LHS

21
Mapping an LP into Excel

• Each model variable will result in a spreadsheet
  column
• Objective function and each constraint will
  result in a row of coefficients
• Set aside one cell for each decision variable to
  hold its numerical value
• Line up objective function coefficients and
  constraint coefficients in their respective
  column below the decision variable

22
Mapping an LP into Excel
23
Mapping an LP into Excel

• Excel functions used
• SUMPRODUCT (inner product in linear algebra)
• Absolute cell references (toggle with F4)

24
Evaluating an LP

• A solution is a set of choices of values for the
  decision variables
• Every solution that satisfies all of the
  constraints is called a feasible solution
• A feasible solution that maximizes (minimizes)
  the objective function is called optimal
• An optimal solution may have fractional
  (non-integer) values for some or all of the
  decision variables
• If this is unacceptable, integer constraints must
  be added to the formulation
• The interpretation of a solution may depend on
  whether or not the solution is allowed to be
  fractional

25
Using Solver in Excel

• On-screen demonstration

26
The choice of values for the pair of variables
(C,M) is called a decision (solution).
C and M are called decision variables because
they are quantities that you can control.
In this problem, a decision is a production plan.
The mix, or decision, is a combination of
Captains and Mates that will not violate one of
the constraints.
Of the infinitely many nonnegative pairs of
numbers (C,M), including fractional values, some
pairs, or decisions, will violate at least one of
the constraints, and some will satisfy all the
constraints.
27
Only non-negative decisions that satisfy all the
constraints are allowable. These decisions are
called feasible decisions.
The Objective Function
Every linear programming model has a specific
objective as well as constraints. In this case,
we would like to maximize next weeks profit.
Profit contribution comes from the sale of two
chairs Captains and Mates. In fact, we will
receive 56 for every Captain and 40 for every
Mate sold. We can mathematically state this as
Total profit contribution 56C 40M
28
An Optimal Solution
An optimal solution is one that gives the largest
total profit contribution.
An optimal solution is a decision that will
maximize the total weekly profit contribution
relative to the set of all possible feasible
decisions.
Since total profit contribution is a function of
the variables C,M, we refer to the mathematical
expression as the payoff or objective function.
The goal is to find feasible values of C and M
that optimize (i.e., maximize) the objective
function
Max 56C 40M
29
Max 56C 40M (objective function)
Subject to (s.t.)
30
Max 56C 40M (objective function)
Subject to (s.t.)
Notice that the constraints
and the objective function
are functions of the two decision variables.
The graph of a linear function of the two
variables is a straight line.
31
A linear function is one in which
Examples of Linear Equations
Examples of Nonlinear Equations
32
Remember,
1. A linear program always has an objective
function (to be either maximized or minimized)
and constraints.
2. All functions in the problem (objective and
constraints) are linear.
33
In LP models where fractional solutions are not
directly meaningful, there are four possible
recourses
1. Add an integrality constraint to the LP
model, which forces one or more decision
variables to take on only integer values.
This results in an integer program (or MIP).

2. Solve the model as an ordinary LP and then
round any decision variable for which a
fractional answer cannot be
used/implemented.
34
3. Consider the one-week Oak Product model
results to be an average weeks production
for an ongoing multiple week situation. The
whole units are produced and the fraction is
carried over to the next week as a work in
progress.
4. Consider the one-week Oak Product model
results to be for planning purposes only and
not operational decisions to be implemented
per se. Take into account other
information when making the final decision.
35
Create a verbal to translate a managerial
situation into a symbolic model
1. Express the objective and its performance
measure objective function in words.
2. Express each constraint in words.
3. Verbally identify the decision variables.
Ask yourself What decisions must be made
in order to optimize the objective function?
4. Express each decision variable in the
constraint using symbols.
5. Express each decision variable in the
objective function using symbols.
Finally, check your work for consistency of units
of measurement.
36
There are often two types of costs sunk (fixed
costs) and variable.
Only the variable costs are relevant in
optimization models.
Unit contribution margin per unit revenue per
unit variable cost
Sunk costs affect only the accounting report of
income or net profit in financial statements.
They play no part in future decisions.
37
Here is a spreadsheet version of the simplified
Oak Product model containing the previously
specified objective function and constraints.
Note that producing 110 Captains and 90 Mates
violates the Legs constraint.
38
Labels are used to clarify the meaning of other
entries.
The numbers represent the coefficients,
parameters, and decision values.
Formulas represent the objective function and
constraints.
Slack is the difference between the constraint
function and the right-hand side computed so that
it is non-negative.
39
Surplus is another name for Slack.
More on Slack
The purpose of these calculations is to give an
indication of how close any constraint is to
binding (evaluated as an equality). Zero Slack
indicates a binding constraint.
To calculate Slack
For a lt constraint, slack is the RHS side minus
the left hand side.
For a gt constraint, slack is the left-hand side
minus the right hand side.
Optional but useful, Slack tells us if any
constraints are violated, resulting in an
infeasible production plan.
40
What if? Projections
You can use the spreadsheet model to perform
What if? analyses.
Type production values for the Captain and Mate
chairs and observe the resulting weekly profit.
Also make sure that the slack is not negative.
41
Optimizing the Spreadsheet
Note that you can enter an arbitrary number of
decision values for C and M in a What if?
fashion. However, with Solver, you can transform
any spreadsheet LP model into an optimization
model with just a few mouse clicks. Here is the
optimal model
42
To produce an Excel LP model, follow these steps
1. Write out the model on paper as a symbolic
LP. Examine your written formulation and
look for errors in the logic.
2. Use the symbolic model as a guide in creating
the Excel representation. Further debug
the Excel model by changing the decision
variables and checking for obvious errors.
3. Try to optimize the model with Solver. If
there is a problem, Solver will give an
error message.
43
(No Transcript)
44
START EXCEL
Solver Flowchart
Solver Flowchart
BUILD OR RETRIEVE YOUR OPTIMIZATION MODEL
SAVE YOUR WORKBOOK!!
CHOOSE Solver IN THE Tools MENU

• SPECIFY IN SOLVER DIALOG BOX
• CELL TO BE OPTIMIZED
• CHANGING CELLS
• CONSTRAINTS

MODIFY MODEL
IN OPTIONS DIALOG, CLICK ASSUME LINEAR MODEL
CLICK THE OK BUTTON
CLICK ON SOLVE BUTTON TO BEGIN OPTIMIZATION
REVIEW SOLVER COMPLETION MESSAGE
45
SOLVER FOUND OPTIMUM SOLUTION?
NO
YES
CLICK KEEP SOLVER SOLUTION CLICK OK BUTTON
WANT TO CHANGE MODEL AND RE-OPTIMIZE?
YES
NO
SAVE FINAL MODEL AND EXIT EXCEL
46
LP Modeling Terminology
Solver Terminology
Objective function Decision variables Constraints
Constraint functions (LHS) RHS LP Model
Target Cell Changing Cells Constraints Constraint
Cell Reference Constraint Assume Linear Model
47
To optimize the Oak Products model with Solver,
first open the file in Excel and then choose
Tools Solver
48
The Solver Parameters dialog box will appear.
By default, Max is selected (for maximization)
and the cursor is in the first edit field Set
Target Cell.
Look for the Premium button. If it is not there,
then you have not installed Premium Solver for
Education (available from the CD). Please
install this version.
49
Clicking on Premium allows you to specify the
type of optimization that it will perform.
We will use the default Standard Simplex LP
optimization.
50
With your cursor in the Set Target Cell edit
field, specify the cell to be maximized (i.e.,
your models performance measure).
The easiest way to do this is to move the dialog
(drag the title bar) so that cell D4 is visible
and then click on that cell.
51
The Equal to field allows you to specify the
type of optimization. You can either maximize or
minimize the objective variable or cause the
Target Cell to be equal to a value of your
choosing (select Value of).

Specify the Oak Product models decision
variables (cells B4C4) in the By Changing Cells
edit field.
52
To specify the constraints, click on the Add
button to open the Add Constraint dialog.
For the LHS of the constraint, specify the cells
for the Total LHS of either one constraint or a
group of similar constraints (i.e., constraints
with the same inequality) in the Cell Reference
edit field of the Add Constraint dialog.
53
For the RHS of the constraint, specify the cell
ranges of either one resource limitation or a
group of similar limitations in the Constraint
edit field.
Note that when specifying many constraints at the
same time, the number of cells referenced in the
LHS must equal the number in the RHS.
Click Add to add these constraints to Solver.
54
Finally, add the Chair Production constraint.
Note that the inequality is gt for this
constraint.
55
Here is the resulting Solver dialog after adding
all of the constraints
Now, in the Solver Parameters dialog, click the
Options button to specify a linear model.
56
In the resulting Solver Options dialog, click on
the following options
Assume linear model
(Specifies an LP model, same as Standard Simplex
LP)
Assume Non-Negativity
(Apply nonnegativity constraints)
Use Automatic Scaling
(to be discussed later)
57
Click OK to return to the Solver Parameters
window and then click Solve to start the
optimization.
Remember that this is an interative technique and
may take a few seconds or a few minutes depending
on the size of the model.
When completed, the Solver Results dialog will
appear.
It is important to check to see if Solver found a
solution and if all constraints and optimality
conditions were satisfied. This information will
be displayed in the first sentence in this
dialog. ALWAYS READ THIS SENTENCE!
58
Upon the successful completion of the Solver
program, you have the following options
NOTE The Premium edition may also list an ?
Report and a Non-Linear Report if there is a
problem.
59
Now, select the Answer report, accept the default
Keep Solver Solution, and click OK.
The resulting report/sheet will be placed in the
Excel workbook as a worksheet with the tab name
Answer Report 1
Note this report has been edited to remove
unneeded blank lines.
60
Maximum Total Profit
Optimal decision values
61
Solver updated the spreadsheet to reflect the
analysis results.
Note that these cells have now changed to reflect
the inventory consequences of the optimal
production decisions. Remember, a Slack (End.
Inv.) 0 indicates the constraint is binding.
62
Three LP modeling habits you should develop for
better use of Solver
1. Make sure the numbers in your LP model are
scaled such that the difference between the
smallest and largest numbers in the
spreadsheet is no more than 6 or 7 digits of
precision (e.g., .05 and 10.00 is an
acceptable range while .05 and 1,000,000 is
not).
63
2. All RHSs in the Constraints section of the
Solver Parameters dialog should contain cell
references (to the RHS cells in the
spreadsheet model itself), and
The RHS cells on the spreadsheet model itself
should contain constraints and not formulas (or
more precisely, no formulas that involve decision
variables directly or indirectly).
64
3. Use Excels Range Naming commands to give
range names to the performance measure cell,
its decision cells, its constraint function
(Total LHS) cells, and its RHS cells.
These names will automatically be substituted for
the corresponding cell ranges in the Solver
Parameters dialog.
65
Solver is used on the more complex version of the
Oak Products model from Chapter 2. The optimal
solution is given below.