individual assignment for Kesha grey only

Raina9312

AD715TutorialforPreparationofA2andforWorkingwithExcelAdd-InTreePlan.pdf

Home >Business & Finance homework help >Management homework help >individual assignment for Kesha grey only

Tutorial for Preparation of Assignment 2 and for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Table of Content

Task 2-1: Payoff Table Preparation & Simplified Decision Tree

Task 2-2: EMV Calculations

Task 2-3: Sensitivity Analysis

Task 2-4: Decision Tree in Excel Add-In ‘TreePlan’

BU MET AD 715 Quantitative & Qualitative Decision-Making

Example of Decision Tree Analysis

Problem Description:

A business owner is considering whether to open a new shop in City A.

There are three decision alternatives:

(1) Open a small shop

(2) Open a medium-sized shop

(3) Do not open a shop

The amount of payoffs (profit) depends on the following market conditions:

(1) Good Market

(2) Average Market

(3) Bad Market

The probabilities for Good Market, Average Market, and Bad Market are predefined.

For the purpose of the sensitivity analysis, the probability of Bad Market condition is given.

Payoff Table: Template

Task 2-1 Payoff Table Preparation & Simplified Decision Tree

The estimated payoffs and probabilities are shown as follows:

Alternatives States of Nature

Good Market Average Market Bad Market

Small Shop $40,000 $20,000 -$15,000

Medium-sized shop $70,000 $30,000 -$50,000

No shop $0 $0 $0

Probability 0.3 0.4 0.3

Probability for Sensitivity Analysis

P 0.6 - P i = 0.4

0.6

Payoff Table: Template

Task 2-1 Payoff Table Preparation & Simplified Decision Tree

The estimated payoffs and probabilities are shown as follows:

Alternatives

States of Nature

Good Market Average Market Bad Market

Small Shop $40,000 $20,000 -$15,000

Medium-sized shop $70,000 $30,000 -$50,000

No shop $0 $0 $0

Probability 0.3 0.4 0.3

Task 2-1: Payoff Table Solution

Task 2-1 Payoff Table Preparation & Simplified Decision Tree (continued)

Based on the payoff table, we can draw the simplified decision tree (without

probabilities and EMVs), where 1 is a decision node, and 2, 3, and 4 are chance nodes:

Task 2-2: EMV Calculation

Based on the payoff table, the EMV for each decision alternative could be calculated as

below:

(1) EMV (Small Shop) =

($40,000)*0.3 + ($20,000)*0.4+(-$15,000)*0.3 =$15,500

(2) EMV (Medium-Sized Shop) =

($70,000)*0.3+($30,000)*0.4+(-$50,000)*0.3 =$18,000

(3) EMV (No Shop) =

$0*0.3+$0*0.4+$0*0.3 =$0

Task 2-2: Solution – EMV Calculation

Task 2-2: EMV Calculation (continued)

Payoff Table Results:

Alternatives

States of Nature

EMV Good Market Average Market Bad Market

Small Shop $40,000 $20,000 -$15,000 $15,500

Medium-sized shop $70,000 $30,000 -$50,000 $18,000

No shop $0 $0 $0 $0

Probability 0.3 0.4 0.3

Task 2-2: Solution

Task 2-2 Solution: The best EMV is EMV (Medium-sized shop)

Alternatives

States of Nature

EMV Node Good

Market Average Market

Bad Market

Small Shop $40,000 $20,000 -$15,000 $15,500 2

Medium-sized shop

$70,000 $30,000 -$50,000 $18,000 3

No shop $0 $0 $0 $0 4

Probability 0.3 0.4 0.3

Final Decision EMV Node

Medium-sized Shop $18,000 1

Task 2-3: Sensitivity Analysis

Task 2-3: Sensitivity Analysis:

Let’s assume that the probability of Bad Market condition is i = 0.4

Use sensitivity analysis to determine the probability range for Good Market which would

change the business owner's decision. Draw a sensitivity chart and find the probability

for the cross points.

Task 2-3: Sensitivity Analysis

The given information is that the probability of Bad Market is 0.4. Therefore, the total

probability of Good Market and Average Market is 0.6. If we set P as the probability of

Good Market, the probability of Average Market would be (0.6-P).

Alternatives

States of Nature

Good Market Average Market Bad Market

Small Shop $40,000 $20,000 -$15,000

Medium-sized shop $70,000 $30,000 -$50,000

No shop $0 $0 $0

Probability P 0.6 - P 0.4

Task 2-3: Sensitivity Analysis

EMV (No shop) = $0*P+$0*(0.6-P)+$0*0.4 = $0

EMV (Medium-sized shop)= ($70,000)*P+($30,000)*(0.6-P)+(-$50,000)*0.4

= $40,000*P-$2,000

EMV(Small Shop) = ($40,000)*P + ($20,000)*(0.6-P)+(-$15,000)*0.4

= $20,000*P+$6,000

Alternatives

States of Nature EMV

Good Market Average Market Bad Market

Small Shop $40,000 $20,000 -$15,000 $20,000*P+$6,000

Medium-sized shop

$70,000 $30,000 -$50,000 $40,000*P-$2,000

No shop $0 $0 $0 $0

Probability P 0.6-P 0.4

In summary, we have the equations for the three EMVs (in dollars), where P is the probability of Good Market:

EMV (Small Shop)=$20,000*P + $6,000

EMV (Medium-sized shop)=$40,000*P - $2,000

EMV (No shop)=0

When P = 0: EMV(Small Shop)=$6,000

EMV(Medium-sized Shop)=-$2,000

EMV(No shop)=$0

When P = 0.6: EMV(Small Shop)=$18,000

EMV(Medium-sized Shop)=$22,000

EMV(No shop)=$0

Now, we can use these points to draw the lines for EMVs to do sensitivity analysis.

Task 2-3: Sensitivity Analysis

Based on the previous equations, here are the three lines representing the EMVs for each

decision alternatives respectively:

Cross Point 2: P = 0.4

Cross Point 1: P = 0.05

EMV (Small Shop)

EMV (Medium-sized Shop)

EMV (No Shop)

($5,000)

$5,000

$10,000

$15,000

$20,000

$25,000

0 0.1 0.2 0.3 0.4 0.5 0.6

E M V

P (Good Market)

Sensitivity Analysis

$40,000*P-2000 = 0

P = 2,000/$40,000 = 0.05

$40,000*P - $2,000 = =$20,000*P + $6,000

$40,000*P - $20,000*P = = $6,000 + $2,000

$20,000*P = $8,000

P = $8,000/$20,000 = 0.4

Task 2-3: Solution

From the sensitivity chart, it’s obvious to see the cross points:

When P = 0.05 EMV (Medium-sized Shop) = EMV (No Shop) = $0;

When P = 0.4 EMV (Small Shop) = EMV (Medium-sized Shop) = $14,000

Probability ranges for Good Market:

When 0 < P(Good Market) < 0.4 EMV (Small Shop) is the highest EMV, opening a

small shop is the best choice.

When 0.4 < P (Good Market) < 0.6 EMV (Medium-sized Shop) is the highest EMV,

therefore opening a medium-sized shop is the

best choice.

Task 2-4: Decision Tree in Excel Add-In ‘TreePlan’

Use the following Tutorial for Building a Decision Tree in Excel Add-in ‘TreePlan’:

A Step-By-Step Approach

Step 1: Getting Started

Step 2: Adding Branch

Step 3: Naming Alternatives

Step 4: Adding chance node

Step 5: Naming Alternatives

Step 6: Copy Subtree

Step 7: Paste Subtree

Step 8: Inserting Values

What is ‘TreePlan’?

TreePlan = The Decision Tree Microsoft Excel Add-In

‘TreePlan’ helps you build a decision tree diagram in an Excel worksheet.

How to Get Access to Decision Tree using Excel Add-in ‘TreePlan’?

Open V-Lab: http://www.bu.edu/metit/hw-and-sw/virtual-labs/ and log in to your BU account

Click excel 2016 and open an new excel worksheet.

Choose Add-Ins  Decision Tree.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

http://www.bu.edu/metit/hw-and-sw/virtual-labs/

Step 1: Getting Started. In a new worksheet, select cell A1. From Add-Ins tab choose

Decision Tree from the Menu Commands group, then click New Tree

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 2: Adding Branch. Now the decision tree has two alternatives. Click cell B5,

from Add-Ins tab choose Decision Tree from the Menu Commands group, click

Add Branch and click OK.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 3: Naming Alternatives. Entering “Open a small shop” in cell D2, “Open a

medium-sized shop” in cell D7, and “No shop” in cell D12 instead of ‘Alternative

1’, ‘Alternative 2’ and ‘Alternative 3’.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 4: Adding chance node. Click cell F3, choose Decision Tree from the Menu

Commands group. Then select Change to event node. Select Three in the

Branches section and Click Ok.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 5: Naming Alternatives. Entering “Good Market” in cell H2, “Average

Market” in cell H6, and “Bad Market” in cell H11 instead of ‘Outcome 1’,

‘Outcome 2’ and ‘Outcome 3’.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 6: Copy Subtree. Click on cell F8, choose Decision Tree from the Menu

Commands group. Select Copy Subtree.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 7: Paste Subtree. Click on cell F18, choose Decision Tree from the Menu

Commands group, then select Paste subtree.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 7 (Continued): Click on cell F8, Copy Subtree, and click cell F33 and Paste the

Subtree, the decision tree will be shown as the following:

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 8: Inserting Values. List the values for each branch based on the given payoff

tables.

o In cell H1, H11, H16, H26, H31 and cell H41, input 0.3;

o In cell H6, H21 and cell H36, input 0.4.

o In cell K3, K8 and cell K13, input value $40,000, $20,000, -$15,000 respectively.

o In cell K18, cell K23, and cell K28, input value $70,000, $30,000, -$50,000

respectively.

The EMV of the decision tree will be calculated automatically by Excel.

The best EMV is $18,000, which is opening a medium-sized shop.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’

Step 8 (Continued): EMV Calculation by Excel

• Excel Add-in ‘TreePlan’ will automatically calculate the EMV for each chance node and give the best EMV of the each decision node.

• The best EMV is $18,000. Therefore, open a medium-sized shop is the best choice.

Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’