Transcript Document

Building systems using models,
decision analysis and statistical
techniques
Using systems to “hide” complex
algorithms from management while
still providing powerful results
DSS
Humans:
Decision
Making
Process
Tools:
Computers and
IT. VB, VBA,
Excel, InterDev,
Etc.
DSS
Data:
Facts pertinent
to the decision
at hand.
Algorithms:
Math/Flow Chart
stuff that helps the
tools help the humans
make decisions.
A Modeling Approach to
Decision Making
• mental model - picturing in your “minds
eye”
• visual model - blueprints, schematics, maps
• physical models - scale models and
prototypes
A Modeling Approach to
Decision Making
• mathematical models - mathematical symbols are
used to represent decision variables which are then
related by the appropriate math functions to
describe a real system or decision problem
• spreadsheet models - cells are used to represent
decision variables which are then related by
functions to describe the decision problem
Benefits of Models
• less costly to analyze than real system or
problem
• can be analyzed more rapidly
• facilitate “what if” and “what’s best”
analysis
• provide insight about real problem or
system
The Modeling Process
• The process of building a model begins with
an understanding of the real world system.
Management must be able to answer
questions such as:
The Modeling Process
What drives the system under consideration?
What are the key factors which predict the
behavior of the system?
What problems are relevant to management?
What are the appropriate measures of
success?
The Modeling Process
• The next step in the modeling process
involves simplifying and abstracting from
the real world system. This model
formulation step is exceptionally important.
The Modeling Process
Should the model be too complex it may be
difficult if not impossible to analyze.
If the model is too simple management may
not be willing to trust results obtained with
the model fearing that the model is
unrealistic.
The Modeling Process
• Once a model is formulated an appropriate
method of analysis must be identified.
The Modeling Process
• That results are available at this point does
not mean that the managerial questions have
been answered. These results must be
interpreted. Interpretation may be thought
of as a reversal of the formulation step.
The Modeling Process
Formulation required that the broad
description of the real world system be
narrowed and abstracted.
Interpretation requires that the narrow
meaning of the results be broadened and
applied to the actual situation.
The Modeling Process
• This set of steps; understanding,
formulation, analysis and interpretation
should provide new insights on the real
world system. This insight may well
provide new understanding of the problem
and restart the modeling process. Thus, the
modeling process should be an iterative one.
Start with Excel, build to VBA, and
beyond…
Let’s build a sheet from scratch:
Thom Pearman has a new house and a new
child. He feels he needs more insurance.
His current policy has a $40,000 death
benefit and is paid in full for life. Thom is
trying to decide whether or not to keep this
policy, or pay premiums on a new policy
which has a death benefit of $350,000.
Insurance Decision Problem
Thom’s current policy can be cashed in for $6,000. Premiums
for the next ten years for the new policy are:
Year
1
2
3
4
5
6
7
8
9
10
Pr emi um
Due
$423
$457
$489
$516
$530
$558
$595
$618
$660
$716
Insurance Decision Problem
Thom would like to cash in his old policy, and
use after-tax interest income from the
$6,000 to pay the premiums for the new
policy. Thom’s marginal tax rate is 28%.
Is this realistic? In particular, what is the
smallest interest rate Thom’s investments
would have to earn to accomplish this?
Let’s build a more complex
system:
Optimization (more later, but just
watch this for now).
VBA.
Constructing the Insurance
System.
• Build the spreadsheet first.
• Build the VBA “front-end”.
– What inputs/outputs are necessary?
– Flexibility.
– Graphical capability.
How much should/can we make?
• Tiger’s Golf Factory makes three products: clubs, bags, and
balls. Clubs sell for $50 each, bags sell for $105 each, and
balls sell for $2 each. Variable costs for making each product
are $24, $20, and $1, respectively. Fixed costs are $1,000.
• TGF has limited resources. In particular, each club takes .4
worker hours and .3 machine hours to make. Each bag takes
1.5 worker hours and .8 machine hours, and each ball take
.001 worker and .005 machine hours to produce.
• Problem is, TGF only has workers for 150 hours per week,
and capacity of 200 machine hours per week. TGF wants to
make as much profit as possible. Assuming there is demand
for anything made, how much of each product should TGF
make to get the largest profit?
Linear Programming: finding the
best (or “optimal”) value for a certain
linear objective given a set of linear
constraints.
Linear: a mathematical expression that
can be written with no variables multiplied
together or raised to any power. A “flat”
equation.
Linear Programming Models
• Elements:
–
–
–
–
Objective
Decision Variables
Constraints
Parameters
Simple Illustrative Example:
• The Leghorn Toy Company makes a profit of $4 on each
stuffed bear they sell, and $6 on each stuffed pig. 2 hours of
labor and 3 square yards of fabric are required to make a pig;
the same numbers for a bear are 1.5 hours and 2.5 square yards.
Daily labor available is 16 hours (two workers), and daily
material available is 25 square yards. How many bears and
how many pigs should Leghorn make each day to maximize
profit?
• Decision Variables: ??
• Objective Function: ??
• Constraints: ??
When Can I Use LP? What assumptions
are made?
• All equations must be linear.
• The answers to the decision variables don’t
have to be integers.
• The parameters are known, and they stay the
same.
Important!!!
• If you satisfy and are comfortable with
the assumptions, AND CAN
CORRECTLY FORMULATE THE
PROBLEM, the computer will find the
answer for you.
• Complex DSS/MSS/Intelligent systems
which are “manager-friendly” can be
built on top of these algorithms!
LP Constraint Examples:
• “We must produce 5 times as many Bears as
Pigs.”
• “The ratio of Pigs to Bears must be at least
2:1.”
• “Forty percent of production should be
Bears.”
• Etc.
General Form of an LP Model
Maximize
( or Minimize
)
c 1 x 1  c 2 x 2    c n x n
Subject to
a 11 x 1  a 12 x 2     a 1 n x n (  ,  ,  ) b 1
a
21
x1  a
22
x 2    a
2n
x n(  ,  ,  )b 2
a m 1 x 1  a m 2 x 2     a mn x n (  ,  ,  ) b m
x 1 , x 2 ,   , x n  0
General Form of an LP Model
where the c’s, a’s and b’s are constants
determined from the problem
and the x’s are the decision variables
In Solver:
• Decision Variables <==> “Changing cells”
Constructing the Tiger System.
• Build the spreadsheet first.
• What questions and answers does
management want/need?
• Build the VBA “front-end”.
Sensitivity Analysis
The Blue Blocker Company produces two types of
sunglasses: Blueblockers and Blueblocker Imitations.
Blueblockers yield a profit of $10, while the Imitations
yield only $1.
Blueblockers require 3 units of plastic per week,
Imitations require 1, and there are 12 total units of
plastic available per week.
Each product requires two labor hours to produce, and
there are two workers who each work 8 hours per
week for Blue Blocker.
Finally company policy states that Blue Blockers
produced be at most 2 more than the number of
Imitations produced.
After you get the “answer” (values of the
Decision Variables)...
• THREE VERY IMPORTANT LP
CONCEPTS:
• Range of Optimality: The range for
each DV coefficient in the Objective
Function for which the DV values will
not change.
• Shadow Price: The amount the objective
function value will increase (decrease) for
each unit increase in the right-hand-side of
the associated constraint (as long as that r-hs remains within its “Range of Feasibility”).
• Range of Feasibility: The range for the r-h-s
of a constraint where the Shadow Price is
applicable (where you can use it). Also:
range where the “binding” status stays the
same.
Range of Optimality
• Examples of Managerial Questions
(Blue Blocker example):
• "How much would the profit made on
Imitations have to increase to change
the production mix?"
• "What would the maximum profit be if a
profit of only $8 were achieved on the
Blueblockers?"
Shadow Price
• Examples of Managerial Questions (Blue
Blocker example):
• "If Blue Blocker changed company policy and
allowed the number of Blueblockers produced
to be at most 4 more than the number of
Imitations produced, what effect would this
action have on maximum profit?”
• "If Blue Blocker hired 3 more workers, how
much would their profit increase?”
Range of Feasibility
• Examples of Managerial Question
(Blue Blocker example):
• “Vendor 1 will give us 4 extra units of
plastic per week for $14, and Vendor 2
will give us 6 extra units of plastic per
week for $17. Which vendor should we
use?”
Make Vs Buy Decisions
“Outsourcing”
The Human Resources department of ABC Company
is responsible for responding to employee inquiries
on three functions: payroll, 401(k), and vacation
accrual. HR has a $750,000 annual budget for
these functions. Each inquiry for a function
requires a certain amount of time from an ABC
technical specialist and lawyer. The following table
summarizes the relevant data:
Make Vs Buy Decisions
Payroll
401(k)
Vacation
Accrual
Estimated #
of EE inq.
(per yr.)
3,000
2,000
900
Hrs. per inq.
– technical
Hrs. per inq.
-- legal
2
1.5
3
1
2
1
Make Vs Buy Decisions
ABC does not have enough technical specialists and
lawyers to respond to all these inquiries. The company
has 5 full time technical specialists (10,000 hrs per yr.)
and 2 full time lawyers and one part time lawyer (5,000
hours per yr.). ABC is considering outsourcing some
of the work to a consulting firm, and has determined
that Firm XYZ has the best reputation and prices in this
area. The unit-cost of handling inquiries in house vs.
having XYZ take care of them are given in the
following table:
Make Vs Buy Decisions
401(k)
Vacation
Accrual
Payroll
Cost to
handle "in
house"
$50
$83
$130
Cost to
contract
to XYZ
$61
$97
$145
Outsourcing LP example
Let
I1 = Number of Employee Payroll Inquiries to handle "in house".
I2 = Number of Employee 401(k) Inquiries to handle "in house".
I3 = Number of Employee Vacation Accrual Inquiries to handle "in house".
O1 = Number of Employee Payroll Inquiries to outsource.
O2 = Number of Employee 401(k) Inquiries to outsource.
O3 = Number of Employee Vacation Accrual Inquiries to outsource.
Min 50I1 + 83I2 +130I3 + 61O1 + 97O2 + 145O3
Subject to:
I1+O1 = 3000
I2 + O2 = 2000
I3 + O3 = 900
2I1 + 1.5I2 + 3I3  10000
I1 + 2I2 + I3  5000
I1-3, O1-3  0
Examples of Managerial Questions:
For $4,000 more in salary, ABC's part time attorney will become full time (move from
1000 hrs./yr. To 2000 hrs./yr.). Do you authorize this decision? Why or why not?
Through hard work and excellent planning, ABC is able to reduce the cost of handling a
401(k) inquiry from $83/inquiry to $80/inquiry. Will this new lower cost reduce the
number of 401(k) inquiries that ABC outsources? Will it affect ABC's overall cost?
Through extra hard work and most excellent planning, ABC is able to reduce the cost of
handling a 401(k) inquiry from $83/inquiry to $80/inquiry and reduce the cost of
handling of vacation accrual inquiry from $130/inquiry to $100/inquiry. Will this new
lower cost reduce the overall number of inquiries that ABC outsources? Will it affect
ABC's overall cost?
Constructing the Outsourcing
System.
• Build the spreadsheet first.
• What questions and answers does
management want/need?
• Build the VBA “front-end”.
– What inputs/outputs?
– Flexibility
– What if and goal seeking
American Auto Rental
American Auto Rental rents one-way to any major
US. city. They presently have an imbalance in the
number of cars available and the number required
in various cities. There are 12 extra cars in Los
Angeles, 6 in Miami, and 5 in New York. There
are too few cars in Atlanta (3), Boston (8), Chicago
(3) and Detroit (8). Using the following mileage
chart, they wish to determine the lowest mileage
arrangement to remedy their imbalance.
American Auto Rental
Atlanta Boston Chicago Detroit
Los
Angeles
Miami
23
31
22
24
7
16
14
14
New
York
9
2
8
6
American Auto Rental LP example
Let
Xij = Number of cars to ship from location i to location j,
Where i=1 to 3 and j=1 to 4,
and
for i, LA=1, Miami=2, and NY=3
for j, Atlanta=1, Boston=2, Chicago=3, and Detroit=4
Min 23X11 + 31X12 + 22X13 + 24X14 + 7X21 + 16X22 + 14X23 + 14X24 + 9X31 +
2X32 + 8X33 + 6X34
Subject to:
X11 + X21 + X31 = 3
X12 + X22 + X32 = 8
X13 + X23 + X33 = 3
X14 + X24 + X34 = 8
X11 + X12 + X13 + X14  12
X21 + X22 + X23 + X24  6
X31 + X32 + X33 + X34  5
Xij  0
Examples of Managerial Questions:
What happens if one fewer car is available from New York?
What happens to the objective function if one more car is requested in
Boston? What about if one less car is requested in Boston?
How much will profit change if one more car is needed in Chicago?
Constructing the Auto Rental
System.
• Build the spreadsheet first.
• What questions and answers does
management want/need?
• Build the VBA “front-end”.
– What inputs/outputs?
– Flexibility
– What if and goal seeking