Management Science

1
Management Science
QM 6433 -- Spring 2007

• Decision Analysis Overview

Instructor John Seydel, Ph.D.
2
This Weeks Student Objectives

• Upon completion of this weeks course
  activities, you should be able to
• Formulate problems involving single decision
  variables with finite sets of alternatives as
  decision analysis problems
• Model decision analysis problems using payoff
  matrices
• Use Excel to support the modeling and solution of
  payoff matrices
• Incorporate the following into spreadsheet models
  where applicable
• The SUMPRODUCT() function
• Mixed absolute cell referencing in formulae

3
Refer to Our Course Outline

• Spreadsheet modeling techniques
• Reinforce/strengthen this week
• Basic decision modeling concepts
• Reinforce/strengthen this week
• Specific modeling/solution techniques
• Decision analysis
• Introduce this week
• Linear programming
• Simulation modeling analysis
• Multicriteria decision making
• Project management concepts tools

4
Introducing Decision AnalysisConsider
Essentially Any Decision

• Two problem aspects involved
• Courses of action
• What choices we have
• Examples which job, how many papers, . . .
• States of nature
• Events out of our control (hence, uncontrollable
  inputs)
• Examples whos elected, weather, court
  decisions, economy
• Example vending machine problem
• Typically states of nature (e.g., daily demand)
  are described by probability distributions
• We can use decision analysis approaches to assist
  us with many problems we encounter
• Structuring problems with finite sets of
  alternatives
• Evaluating and comparing complex alternatives

5
The Payoff Matrix Approach

• For a single decision (hence one decision
  variable) develop a table
• Create a row for each decision alternative (i.e.,
  choice)
• Create a column for each state of nature
• The vending machine problem
• Decision alternatives are the possible number of
  papers to stock
• The state of nature refers to the daily demand
  that occurs
• Each cell in the table represents the criterion
  outcome (i.e., payoff) for the corresponding
  decision alternative and state of nature e.g.,
  the vending machine problem
• It should be obvious here that the criterion of
  interest here is profit, and the objective is to
  maximize profit
• For example, consider what happens if 11 papers
  are stocked, and demand that day is 10
• The value to go into that table cell is a
  positive profit of 4.50
• Revenue 10 _at_ 1.00 10.00
• Cost 11 _at_ 0.50 5.50

6
Payoff Matrix for the Vending Machine Problem
7
Now, What to Do With the Payoff Matrix

• At first glance, it might seem to be an obvious
  choice to stock the machine with 13 papers and to
  sell all 13
• However, thats not an option
• That is, the number sold is subject to demand, an
  uncontrollable input
• Look again the table is telling us that, for
  example, if we stock 13 papers, the outcome could
  be a profit of 3.50, 4.50, 5.50, or 6.50,
  depending upon demand
• Heres where the historical demand data enter in
  we treat the relative frequencies for each of the
  demand levels as their respective probabilities
• We then use the probabilities to discount (i.e.,
  weight) the outcomes and calculate a weighted
  average outcome for each stock level
• This is known as using mathematical expectation
  or EMV, which is just a weighted average outcome
• Alternatives are compared by means of their EMVs
  . . .

8
Comparing Alternatives (via EMV)

• This is really what decision analysis is all
  about
• That is, we need to compare to two or more
  alternatives, where each alternative has multiple
  possible outcomes, each associated with a
  probability of occurrence
• We do this by converting the multiple outcomes to
  a single value, its mathematical expectation
  (EMV), which we treat as a certainty equivalent
  for a given alternative
• Once certainty equivalence values are calculated
  for each alternative, that with the best value is
  chosen
• For example, the EMV for the decision to stock 12
  papers is
• (4.00 x 0.05) (5.00 x 0.40) (6.00 x 0.30)
  (6.00 x 0.25) 5.50

9
Decision Analysis A Summary of the Procedures

• Determine alternatives
• For each alternative
• Determine outcomes (e.g., monetary values)
  possible
• Determine probabilities for those outcomes
• Create model matrix or tree (stay tuned)
• Determine EMV for each alternative
• Make choice (alternative with best EMV)
• Note the application of
• The scientific problem-solving framework
• The modeling process
• Lends itself to Excel support . . .

10
Modeling with Excel

• Lets make Excel do what its good at doing
  saving us from lots of work two opportunities
  here
• Calculating payoffs
• Determining EMV
• Consider the logic for the payoffs in the vending
  machine problem calculate a few cells manually
  first
• Payoff is dependent upon
• Stock, S (the row indicator on the payoff
  matrix), the controllable input
• Demand, D (the column indicator on the payoff
  matrix)
• Sale revenue/unit, p, non-sale revenue/unit, q,
  and cost/unit, c
• Note that S is the only controllable input
  (i.e., decision variable)
• A relationship can be stated such that payoff
  (profit) is either
• S x (p c ) ? if stock lt demand
• D x p (S D ) x q S x c ? if stock gt demand
• Implement this on a worksheet (see example) . . .

11
The Excel Worksheet Payoffs

• This requires a fairly complex formula involving
• Named cells
• Cost per unit cost
• Sale revenue/unit sale
• Nonsale revenue/unit nonsale
• An IF() function
• Mixed absolute references (the challenging part)
• Assume the top left payoff cell is B9
  (corresponding to stocking 10 units and a demand
  of 10 units)
• The formula for B9 would then be
• IF(A9ltB8,A9(sale-cost),B8sale(A9-B8)n
  onsale-A9cost)
• This formula can then simply be copied down the
  column and then across the columns
• It may be tempting just to calculate each payoff
  manually and enter the result, but you lose the
  functionality and flexibility that makes Excel
  such a valuable tool if any of the inputs
  change, you would need to go back through and
  recalculate everything.

12
The Excel Worksheet EMV

• Calculating EMV is the easy part
• We use the SUMPRODUCT() function
• This multiplies corresponding elements of two
  arrays and adds the results
• Recall that EMV involves multiplying the payoffs
  in a given row of a matrix by their corresponding
  probabilities
• Assume the top right payoff cell is E9
  (corresponding to stocking 10 units and a demand
  of 13 units) and that probability values are in
  cells B14E14
• The formula for G14 (EMV for a choice to stock 10
  papers) would then be
• SUMPRODUCT(B9E9,B14E14)
• This could then be copied down the column

13
Finally, the Solution

• Refer again to the example worksheet
• First, the solutions not 5.50
• Thats just the criterion outcome if we implement
  the best course of action
• The solution is thus the course of action we
  recommend
• The real solution
• We recommend that 12 papers be stocked daily in
  the vending machine at this location
• If that is done, well see a long-term average
  profit of 5.50 per day from this location
• Note how this is (and should be) stated in
  sentence form its a managerial problem
• Recommendation is stated
• Consequences should be stated
• Qualifications are also of concern
• This last item is not to be taken lightly . . .

14
Looking a Bit Further into Decision Analysis

• Postoptimality (e.g., sensitivity) analysis?
• Once a decision has been made, we analyze the
  quality of the decision
• Final stage in the Scientific Problem-Solving
  Process
• Allows us to test how robust our decision is
• If the uncontrollable inputs change, how much
  does that affect the decision (i.e., the
  controllable input values) involved?
• and/or
• Whats Plan B (i.e., and alternative set of
  controllable input values) if the uncontrollable
  inputs change?
• Note sensitivity analysis does not tell us the
  best values for the uncontrollable inputs
• Consider risk (another criterion)
• Think about how this might be done
• Separately or in conjunction with profit
• Well address these concerns next week, along
  with an interesting practical application
  involving pricing decisions

15
One Final Item Decision Trees as an Alternative
to Payoff Matrices

• Decision tree approach
• Accomplishes the same as payoff matrices
• Can provide more intuitive maps of the decision
  process
• Must be used in complex and/or multi-stage
  decision problems
• Can get messy very quickly
• Doesnt lend itself to native Excel capabilities
  we need add-ons (e.g., TreePlan)
• We wont be doing much with decision trees but
  you should understand the basic concepts
• Note that the symbols are not arbitrary
• Squares (decision nodes) represent decision
  alternatives (choices)
• Circles (event nodes) represent possible outcomes
• Refer to the example spreadsheet (from Handouts
  page)
• Starts with basic tree model
• Progresses to complete model
• Solution shows EMV calculated for each event node

16
Summary of Objectives

• Formulate problems involving single decision
  variables with finite sets of alternatives as
  decision analysis problems
• Model decision analysis problems using payoff
  matrices
• Use Excel to support the modeling and solution of
  payoff matrices
• Incorporate the following into spreadsheet models
  where applicable
• The SUMPRODUCT() function
• Mixed absolute cell referencing in formulae

17
Appendix
18
Spreadsheet Design Guidelines

• Organize the data, then build the model around
  the data.
• Do not embed numeric constants in formulas.
• Things which are logically related should be
  physically related.
• Use formulas that can be copied.
• Column/rows totals should be close to the
  columns/rows being totalled.
• The English-reading eye scans left to right, top
  to bottom
• Use color, shading, borders and protection to
  distinguish changeable parameters from other
  model elements.
• Use text boxes and cell notes to document various
  elements of the model.

19
Newspaper Vending Machine Case

• Scenario
• For a given location, you need to determine how
  many newspapers to stock weekdays in a
  coin-operated vending machine
• Monetary information
• Cost per paper 0.50
• Revenue per paper
• 1.00 if sold
• Nothing otherwise disposal fees are negligible
• Decision determine best value for S, how many
  papers to stock for any given weekday
• Alternatives (i.e., choices)?
• Criteria (objectives)?
• Model
• Payoff matrix
• Note Not necessarily all papers will be sold
• Whats missing . . . ?

20
Consider the Historical Demand Data
21
The Probability Distribution
22
Recall the Scientific Problem-Solving Framework
(SPSF)

• Define the problem
• Define decision variables
• One number of papers to stock
• Determine criteria of importance
• One profit
• Specify whether criteria are associated with
  goals or with objectives
• Objective maximize profit
• Identify constraints (no explicit constraints)
• Consider alternatives
• Identify them
• Stock 10, 11, 12, or 13 papers (since demand
  varies from 10 to 13)
• Evaluate them
• Calculate EMV for each alternative
• Select best one
• Implement solution
• Monitor and revise solution re-solve if
  appropriate

23
Recall Components of Decision Models

• Model components
• Inputs
• Uncontrollable (e.g., costs) also known as data
• Cost value
• Sales price
• Demand distribution
• Possible demand amounts
• Corresponding probabilities
• Controllable also known as decision variables
• Solution procedure
• Use payoff matrix approach with EMV
• Outputs i.e., the results
• Array of payoffs associated with each decision
  alternative
• EMV for each decision alternative
• The solution (not just an answer) applying the
  results
• The course of action to be taken
• That is, the values chosen for the decision
  variables

24
An Excel Worksheet for the Example