Spreadsheet Modeling Decision Analysis Introduction to Decision Analysis

Spreadsheet Modeling & Decision Analysis

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

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?

Good Decisions vs. Good Outcomes • A structured approach to decision making can help us make good decisions, but can’t guarantee good outcomes. • Good decisions sometimes result in bad outcomes.

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

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. • 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. • The Magnolia Inns hotel chain intends to build a new facility near the new airport once its site is determined. • Land values around both 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 Fig 15 -1. xls

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.

The Possible States of Nature 1) The new airport is built at location A. 2) The new airport is built at location B.

Constructing a Payoff Matrix See file Fig 15 -1. xls

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.

The Maximax Decision Rule • Identify the maximum payoff for each alternative. • Choose the alternative with the largest maximum payoff. • See file Fig 15 -1. xls § Weakness – Consider the following payoff matrix Decision A B State of Nature 1 2 30 -10000 29 29 MAX 30 29 <--maximum

The Maximin Decision Rule • Identify the minimum payoff for each alternative. • Choose the alternative with the largest minimum payoff. • See file Fig 15 -1. xls § Weakness – Consider the following payoff matrix Decision A B State of Nature 1 1000 29 2 28 29 MIN 28 29 <--maximum

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 don’t 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 expert’s opinions. • We will focus on techniques that can be used once appropriate probability estimates have been obtained.

EMV Caution • The EMV rule should be used with caution in one-time decision problems. § Weakness – Consider the following payoff matrix Decision A B Probability State of Nature 1 2 15, 000 -5, 000 4, 000 0. 5 EMV 5, 000 4, 500 <--maximum

A Decision Tree for Magnolia Inns Land Purchase Decision Buy A -18 Buy B -12 Airport Location 1 2 0 Buy A&B -30 Buy nothing 0 3 4 Payoff A 31 13 B 6 -12 A 4 -8 B 23 11 A 35 5 B 29 -1 A 0 0 B 0 0

Rolling Back A Decision Tree Land Purchase Decision Airport Location 0. 4 Buy A -18 EMV=-2 1 A 31 13 6 B 0. 6 -12 A 4 -8 23 B 0. 6 11 0. 4 Buy B -12 EMV=3. 4 2 0 EMV=3. 4 0. 4 Buy A&B -30 EMV=1. 4 3 A 35 5 B 29 0. 6 -1 A 0 0 B 0 0 0. 4 Buy nothing 0 EMV= 0 4 Payoff 0. 6

Using Tree. Plan

About Tree. Plan • Tree. Plan is a shareware product developed by Dr. Mike Middleton at the Univ. of San Diego • Tree. Plan is an Excel add-in for decision trees. • 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 Tree. Plan help file. See file Fig 15 -14. xls

Anatomy of Tree. Plan Decision Tree

Problem: Marketing Cellular Phones The design and product-testing phase has just been completed for Sonorola’s new line of cellular phones. Three alternatives are being considered for a marketing/production strategy for this product: 1. Aggressive (A) • Major commitment from the firm • Major capital expenditure • Large inventories of all models • Major global marketing campaign

2. Basic (B) • Move current production to Osaka • Modify current line in Tokyo • Inventories for only most popular items • Only local or regional advertising 3. Cautious (C) • Use excess capacity on existing phone lines to produce new products • Minimum of new tooling • Production satisfies demand • Advertising at local dealer discretion Management decides to categorize the level of demand as either strong (S) or weak (W).

Managements best estimate of the probability of a strong or weak market. Net profits measured in millions of dollars. The optimal decision if you are risk-indifferent is to select B which yields the highest expected payoff.

Tree. Plan

Lab Problem: Drive. Tek Problem • Drive. Tek Research discovers that a computer company wants a new storage device for a proposed computer system • The company will subcontract the development to a research firm • The company has offered a fee of $250, 000 for the best proposal • Drive. Tek wants to enter the competition • Management estimates a cost of $50, 000 to prepare a proposal with a 50 -50 chance of winning the contract

Drive. Tek Problem continued • Drive. Tek’s engineers are not sure about how they will develop the storage device • Three alternative approaches can be tried: 1. A mechanical method with a cost of $120, 000 and the engineers are certain they can develop a successful model with this approach 2. An electronic approach, with an estimated cost of $50, 000 to develop a model. Engineers estimate only a 50% chance of a satisfactory approach 3. A magnetic component approach which will cost $80, 000 with a 70% chance of success

Drive. Tek Problem continued • Drive. Tek’s can only work on one approach at a time and has time to try only two approaches. • If it tries either the magnetic or electronic method and the attempt fails, the second choice must be the mechanical method to guarantee a successful model • Drive. Tek needs help in incorporating this information into a decision to proceed or not.

Class Exercise in Creating a Decision Tree: A Glass Factory • Complete the following exercise using Tree. Plan. • Vary the inputs to determine when the optimal decision will change.

Class Exercise: A Glass Factory A glass factory specializing in crystal is experiencing a substantial backlog, and the firm's management is considering three courses of action: A) Arrange for subcontracting – cost is 12 (thousand) B) Construct new facilities – cost is 18 (thousand) C) Do nothing (no change) – cost is 0 The correct choice depends largely upon demand, which may be low, medium, or high. By consensus, management estimates the respective demand probabilities as 0. 1, 0. 5, and 0. 4. Given the payoffs on the next page, create and solve this problem using a decision tree.

A Glass Factory: The Payoff Table The management estimates the profits when choosing from the three alternatives (A, B, and C) under the differing probable levels of demand. These profits, in thousands of dollars are presented in the table below: