Spreadsheet Modeling

1
Spreadsheet Modeling Decision Analysis

• A Practical Introduction to Management Science
• 4th edition
• Cliff T. Ragsdale

2
Decision Analysis
Chapter 15
3
On Uncertainty and Decision-Making

• "Uncertainty is the most difficult thing about
  decision-making. In the face of uncertainty,
  some people react with paralysis, or they do
  exhaustive research to avoid making a decision.
  The best decision-making happens when the mental
  environment is focused. That fined-tuned focus
  doesnt leave room for fears and doubts to enter.
  Doubts knock at the door of our consciousness,
  but you don't have to have them in for tea and
  crumpets."
• -- Timothy Gallwey, author of The Inner Game of
  Tennis and The Inner Game of Work.

4
Introduction to Decision Analysis

• Models help managers gain insight and
  understanding, but they cant make decisions.
• Decision making often remains a difficult task
  due to
• Uncertainty regarding the future
• Conflicting values or objectives
• Consider the following example...

5
Deciding Between Job Offers

• Company A
• In a new industry that could boom or bust.
• Low starting salary, but could increase rapidly.
• Located near friends, family and favorite sports
  team.
• Company B
• Established firm with financial strength and
  commitment to employees.
• Higher starting salary but slower advancement
  opportunity.
• Distant location, offering few cultural or
  sporting activities.
• Which job would you take?

6
Good Decisions vs. Good Outcomes

• A structured approach to decision making can help
  us make good decisions, but cant guarantee good
  outcomes.
• Good decisions sometimes result in bad outcomes.

7
Characteristics of Decision Problems

• Alternatives - different courses of action
  intended to solve a problem.
• Work for company A
• Work for company B
• Reject both offers and keep looking
• Criteria - factors that are important to the
  decision maker and influenced by the
  alternatives.
• Salary
• Career potential
• Location
• States of Nature - future events not under the
  decision makers control.
• Company A grows
• Company A goes bust
• etc

8
An Example Magnolia Inns

• Hartsfield International Airport in Atlanta,
  Georgia, is one of the busiest airports in the
  world.
• It has expanded many times to handle increasing
  air traffic.
• Commercial development around the airport
  prevents it from building more runways to handle
  future air traffic demands.
• Plans are being made to build another airport
  outside the city limits.
• Two possible locations for the new airport have
  been identified, but a final decision will not be
  made for a year.
• Continued

9
Magnolia Inns (continued)

• The Magnolia Inns hotel chain intends to build a
  new facility near the new airport once its site
  is determined.
• Land values around the two possible sites for the
  new airport are increasing as investors speculate
  that property values will increase greatly in the
  vicinity of the new airport.
• See data in file Fig15-1.xls

10
The Decision Alternatives

• 1) Buy the parcel of land at location A.
• 2) Buy the parcel of land at location B.
• 3) Buy both parcels.
• 4) Buy nothing.

11
The Possible States of Nature

• 1) The new airport is built at location A.
• 2) The new airport is built at location B.

12
Constructing a Payoff Matrix
See file Fig15-1.xls
13
Decision Rules

• If the future state of nature (airport location)
  were known, it would be easy to make a decision.
• Failing this, a variety of nonprobabilistic
  decision rules can be applied to this problem
• Maximax
• Maximin
• Minimax regret
• No decision rule is always best and each has its
  own weaknesses.

14
The Maximax Decision Rule

• Identify the maximum payoff for each alternative.
• Choose the alternative with the largest maximum
  payoff.
• See file Fig15-1.xls

• Weakness
• Consider the following payoff matrix

15
The Maximin Decision Rule

• Identify the minimum payoff for each alternative.
• Choose the alternative with the largest minimum
  payoff.
• See file Fig15-1.xls

16
The Minimax Regret Decision Rule

• Compute the possible regret for each alternative
  under each state of nature.
• Identify the maximum possible regret for each
  alternative.
• Choose the alternative with the smallest maximum
  regret.
• See file Fig15-1.xls

17
Anomalies with the Minimax Regret Rule

• Note that we prefer A to B.
• Now lets add an alternative...

18
Adding an Alternative

• Consider the following payoff matrix

• The regret matrix is

• Now we prefer B to A???

19
Probabilistic Methods

• At times, states of nature can be assigned
  probabilities that represent their likelihood of
  occurrence.
• For decision problems that occur more than once,
  we can often estimate these probabilities from
  historical data.
• Other decision problems (such as the Magnolia
  Inns problem) represent one-time decisions where
  historical data for estimating probabilities
  dont exist.
• In these cases, subjective probabilities are
  often assigned based on interviews with one or
  more domain experts.
• Interviewing techniques exist for soliciting
  probability estimates that are reasonably
  accurate and free of the unconscious biases that
  may impact an experts opinions.
• We will focus on techniques that can be used once
  appropriate probability estimates have been
  obtained.

20
Expected Monetary Value

• Selects alternative with the largest expected
  monetary value (EMV)

• EMVi is the average payoff wed receive if we
  faced the same decision problem numerous times
  and always selected alternative i.
• See file Fig15-1.xls

21
EMV Caution

• The EMV rule should be used with caution in
  one-time decision problems.

22
Expected Regret or Opportunity Loss

• Selects alternative with the smallest expected
  regret or opportunity loss (EOL)

• The decision with the largest EMV will also have
  the smallest EOL.
• See file Fig15-1.xls

23
The Expected Value of Perfect Information

• Suppose we could hire a consultant who could
  predict the future with 100 accuracy.
• With such perfect information, Magnolia Inns
  average payoff would be
• EV with PI 0.413 0.611 11.8 (in
  millions)
• Without perfect information, the EMV was 3.4
  million.
• The expected value of perfect information is
  therefore,
• EV of PI 11.8 - 3.4 8.4 (in millions)
• In general,
• EV of PI EV with PI - maximum EMV
• It will always the the case that,
• EV of PI minimum EOL

24
A Decision Tree for Magnolia Inns
25
Rolling Back A Decision Tree
26
Alternate Decision Tree
Land Purchase Decision
Airport Location
Payoff
0.4
13
A
31
Buy A
1
EMV-2
-18
6
-12
B
0.6
0.4
A
-8
4
Buy B
2
-12
EMV3.4
23
B
11
0.6
0
0.4
5
A
35
Buy AB
EMV3.4
3
-30
EMV1.4
29
-1
B
0.6
Buy nothing
0
0
27
Using TreePlan

• TreePlan is an Excel add-in for decision trees.
• See file Fig15-14.xls

28
About TreePlan

• TreePlan is a shareware product developed by Dr.
  Mike Middleton at the Univ. of San Francisco and
  distributed with this textbook at no charge to
  you.
• If you like this software package and plan to use
  for more than 30 days, you are expected to pay a
  nominal registration fee. Details on registration
  are available near the end of the TreePlan help
  file.

29
Multi-stage Decision Problems

• Many problems involve a series of decisions
• Example
• Should you go out to dinner tonight?
• If so,
• How much will you spend?
• Where will you go?
• How will you get there?
• Multistage decisions can be analyzed using
  decision trees

30
Multi-Stage Decision Example COM-TECH

• Steve Hinton, owner of COM-TECH, is considering
  whether to apply for a 85,000 OSHA research
  grant for using wireless communications
  technology to enhance safety in the coal
  industry.
• Steve would spend approximately 5,000 preparing
  the grant proposal and estimates a 50-50 chance
  of receiving the grant.
• If awarded the grant, Steve would need to decide
  whether to use microwave, cellular, or infrared
  communications technology.

• Steve would need to acquire some new equipment
  depending on which technology is used

31
COM-TECH (continued)

• Steve needs to synthesize all the factors in this
  problem to decide whether or not to submit a
  grant proposal to OSHA.
• See file Fig15-26.xls

32
Analyzing Risk in a Decision Tree

• How sensitive is the decision in the COM-TECH
  problem to changes in the probability estimates?
• We can use Solver to determine the smallest
  probability of receiving the grant for which
  Steve should still be willing to submit the
  proposal.
• Lets go back to file Fig15-26.xls...

33
Risk Profiles

• A risk profile summarizes the make-up of an EMV

• This can also be summarized in a decision tree.
• See file Fig15-29.xls

34
Using Sample Information in Decision Making

• We can often obtain information about the
  possible outcomes of decisions before the
  decisions are made.
• This sample information allows us to refine
  probability estimates associated with various
  outcomes.

35
Example Colonial Motors

• Colonial Motors (CM) needs to determine whether
  to build a large or small plant for a new car it
  is developing.
• The cost of constructing a large plant is 25
  million and the cost of constructing a small
  plant is 15 million.
• CM believes a 70 chance exists that demand for
  the new car will be high and a 30 chance that it
  will be low.

• See decision tree in file Fig15-32.xls

36
Including Sample Information

• Before making a decision, suppose CM conducts a
  consumer attitude survey (with zero cost).
• The survey can indicate favorable or unfavorable
  attitudes toward the new car. Assume
• P(favorable response) 0.67
• P(unfavorable response) 0.33
• If the survey response is favorable, this should
  increase CMs belief that demand will be high.
  Assume
• P(high demand favorable response)0.9
• P(low demand favorable response)0.1
• If the survey response is unfavorable, this
  should increase CMs belief that demand will be
  low. Assume
• P(low demand unfavorable response)0.7
• P(high demand unfavorable response)0.3
• See decision tree in file Fig15-33.xls

37
The Expected Value of Sample Information

• How much should CM be willing to pay to conduct
  the consumer attitude survey?

• In the CM example,
• E.V. of Sample Info. 126.82 - 126 0.82
  million

38
Computing Conditional Probabilities

• Conditional probabilities (like those in the CM
  example) are often computed from joint
  probability tables.

39
Computing Conditional Probabilities (contd)
40
Bayess Theorem

• Bayess Theorem provides another definition of
  conditional probability that is sometimes helpful.

41
Utility Theory

• Sometimes the decision with the highest EMV is
  not the most desired or most preferred
  alternative.

• Decision makers have different attitudes toward
  risk
• Some might prefer decision alternative A,
• Others would prefer decision alternative B.
• Utility Theory incorporates risk preferences in
  the decision making process.

42
Common Utility Functions
43
Constructing Utility Functions

• Assign utility values of 0 to the worst payoff
  and 1 to the best.
• For the previous example,
• U(-30,000)0 and U(150,000)1
• To find the utility associated with a 70,000
  payoff identify the value p at which the decision
  maker is indifferent between
• Alternative 1 Receive 70,000 with certainty.
• Alternative 2 Receive 150,000 with probability
  p and lose 30,000 with probability (1-p).

• If decision maker is indifferent when p0.8
• U(70,000)U(150,000)0.8U(-30,000)0.210.80
  0.20.8
• When p0.8, the expected value of Alternative 2
  is
• 150,0000.8 30,0000.2 114,000
• The decision maker is risk averse. (Willing to
  accept 70,000 with certainty versus a risky
  situation with an expected value of 114,000.)

44
Constructing Utility Functions (contd)

• If we repeat this process with different values
  in Alternative 1, the decision makers utility
  function emerges (e.g., if U(40,000)0.65)

45
Comments

• Certainty Equivalent - the amount that is
  equivalent in the decision makers mind to a
  situation involving risk.
• (e.g., 70,000 was equivalent to Alternative 2
  with p 0.8)
• Risk Premium - the EMV the decision maker is
  willing to give up to avoid a risky decision.
• (e.g., Risk premium 114,000-70,000 44,000)

46
Using Utilities to Make Decisions

• Replace monetary values in payoff tables with
  utilities.

• Decision B provides the greatest utility even
  though it the payoff table indicated it had a
  smaller EMV.

47
The Exponential Utility Function

• The exponential utility function is often used to
  model classic risk averse behavior

48
Incorporating Utilities in TreePlan

• TreePlan will automatically convert monetary
  values to utilities using the exponential utility
  function.
• We must first determine a value for the risk
  tolerance parameter R.
• R is equivalent to the maximum value of Y for
  which the decision maker is willing to accept the
  following gamble
• Win Y with probability 0.5,
• Lose Y/2 with probability 0.5.
• Note that R must be expressed in the same units
  as the payoffs!
• In Excel, insert R in a cell named RT. (Note RT
  must be outside the rectangular range containing
  the decision tree!)
• On TreePlans Options dialog box select,
• Use Exponential Utility Function
• See file Fig15-38.xls

49
Multicriteria Decision Making

• Decision problem often involve two or more
  conflicting criterion or objectives
• Investing
• risk vs. return
• Choosing Among Job Offers
• salary, location, career potential, etc.
• Selecting a Camcorder
• price, warranty, zoom, weight, lighting, etc.
• Choosing Among Job Applicants
• education, experience, personality, etc.

• Well consider two techniques for these types of
  problems
• The Multicriteria Scoring Model
• The Analytic Hierarchy Process (AHP)

50
The Multicriteria Scoring Model

• Score (or rate) each alternative on each
  criterion.
• Assign weights the criterion reflecting their
  relative importance.

• See file Fig15-41.xls

51
The Analytic Hierarchy Process (AHP)

• Provides a structured approach for determining
  the scores and weights in a multicriteria scoring
  model.
• Well illustrate AHP using the following example
• A company wants to purchase a new payroll and
  personnel records information system.
• Three systems are being considered (X, Y and Z).
• Three criteria are relevant
• Price
• User support
• Ease of use

52
Pairwise Comparisons

• The first step in AHP is to create a pairwise
  comparison matrix for each alternative on each
  criterion using the following values

• Pij extent to which we prefer alternative i to
  j on a given criterion.
• We assume Pji 1/Pij
• See price comparisons in file Fig15-45.xls

53
Normalization Scoring

• To normalize a pairwise comparison matrix,
• 1) Compute the sum of each column,
• 2) Divide each entry in the matrix by its column
  sum.
• The score (sj) for each alternative is given by
  the average of each row in the normalized
  comparison matrix.
• See file Fig15-45.xls

54
Consistency

• We can check to make sure the decision maker was
  consistent in making the comparisons.

• If the decision maker was perfectly consistent
  each Ci should equal to the number of
  alternatives in the problem.

55
Consistency (contd)

• Typically, some inconsistency exists.

56
Obtaining Remaining Scores Weights

• This process is repeated to obtain scores for the
  other criterion as well as the criterion weights.
• The scores and weights are then used as inputs to
  a multicriteria scoring model in the usual way.
• See file Fig15-45.xls

57
End of Chapter 15