29 Jan Decision trees can be used to determine the best possible alternatives and potential payoff for a new product or solving other management problems where uncertainty is
Decision trees can be used to determine the best possible alternatives and potential payoff for a new product or solving other management problems where uncertainty is present.
Your task is to build a decision tree based on the following scenario.
OM, Inc., a manufacturer of widgets, is considering the possibility of producing a new super-duper widget. However, they cannot decide between using an automatic 3D printing manufacturing technique or producing the widgets by traditional methods. This new project will require OM, Inc. to either purchase a high-end 3D printer or hire and train four additional employees. The market for the new widget could be either favorable or unfavorable. Ultimately, OM, Inc. can also decide not to develop the new widget.
Sales for favorable customer acceptance would be 20,000 widgets selling for $1,900 each. With unfavorable acceptance, sales of the widgets would only be 4,000 widgets at a selling price of $1,900 each. The initial setup cost of the 3D printing system is $2,000,000; however, the hiring and training of the four new employees would cost only $400,000. In the end, manufacturing costs are $1,700 for each widget when manufacturing without 3D printing and $1,500 each when 3D printed.
The probability of favorable acceptance of the new widgets is .30; the probability of unfavorable acceptance is .70.
Before you start building your decision tree, review the How to Build a Decision Tree in Excel OM example presentation narrated by the course developer, Dr. Bob Walton. Make sure to also review the videos in 3.1 Readings, Presentations & Videos. There is also more information in your textbook on Decision trees and the use of the OM Software in Module A.
Assignment
Use Excel OM to build a decision tree. Make sure you carefully review the "Instructions & Example" provided in the OM plugin, and the example in your book. Submit your Excel decision tree along with a short explanation of what decision should be made by the company and how you arrived at your answer.
Save your assignment using a naming convention that includes your first and last name and the activity number (or description). Do not add punctuation or special characters.
Review the Problems Rubric for detailed grading information.
How to Build a How to Build a Decision Tree Decision Tree in Excel OMin Excel OM
All rights are reserved. The material contained herein is the copyright property of Embry-Riddle Aeronautical University, Daytona Beach, Florida, 32114. No part of this material may be reproduced, stored in a retrieval system or transmitted in any form, electronic, mechanical, photocopying,
recording or otherwise without the prior written consent of the University.
ProblemProblem
Company considering the production of a new product. They can either:
Buy a new robot for $300,000 Hire two new employees at $200,000
Price for new product is $60. Sells projections up to 10,000, or low of 5,000. Favorable acceptance is .30 and unfavorable .70. Cost $10 each for robot production, $15 each for human.
RobotRobot
Cost Factor
$600,000 Revenue ($60 x 10,000)
-$100,000 Mfg. Cost ($10 x 10,000)
$500,000 Profit
-$300,000 Robot Cost
$200,000 Net
FavorableFavorable (High Sales){
Cost Factor
$300,000 Revenue ($60 x 5,000)
-$50,000 Mfg. Cost ($10 x 5,000)
$250,000 Profit
-$300,000 Robot Cost
-$50,000 Net
UnfavorableUnfavorable (Low Sales){
HumansHumans
CostCost FactorFactor
$600,000 Revenue ($60 x 10,000)
-$150,000 Mfg. Cost ($15 x 10,000)
$450,000 Profit
-$200,000 Hire and Training Cost
$250,000 Net
FavorableFavorable (High Sales){
Cost Factor
$300,000 Revenue ($60 x 5,000)
-$75,000 Mfg. Cost ($15 x 5,000)
$225,000 Profit
-$200,000 Hire and Training Cost
$25,000 Net
UnfavorableUnfavorable (Low Sales){
Figuring Out the LogicFiguring Out the Logic
0
1
Robot
0
Hire 0
Do Nothing 0
2
Fav 0
Unfav 0
5
6
3
Fav 0
Unfav
0
4
7
82
0
5
0
7
0
0
0
0
0
Filling in the NumbersFilling in the Numbers
92,500
1
Robot
-300,000
Hire -200,000
Do Nothing 0
2
0.3 Fav
500,000
0.7 Unfav
250,000
5
6
3
0.3 Fav
450,000
0.7 Unfav
225,000
4
7
83
325,000
292,500
0
25,000
250,000
-50,000
200,000
The AnswerThe Answer
92,500
1
Robot
-300,000
Hire -200,000
Do Nothing 0
2
0.3 Fav
500,000
0.7 Unfav
250,000
5
6
3
0.3 Fav
450,000
0.7 Unfav
225,000
4
7
83
325,000
292,500
0
25,000
250,000
-50,000
200,000
ConclusionConclusion
,
LGMT 420 Decision Tree
Slide 1
One of the assignments that many students seem to have problems with is building decision trees. Decision trees can be used to determine the best possible alternatives and potential payoff for a new product or solving other management problems where uncertainty is present. So, I would like to take you through the process using the following example and using the Excel OM software.
Slide 2
So, let’s assume that you work for a company that is getting ready to produce a new product. The firm can either buy a new robot to do the production or hire two new employees. The problem is that the firm does not know if the product will actually sale or not, so in the end, they may decide not to produce the product at all.
So, the company has three choices — to buy a robot, to hire two new employees or not to make the product at all. The question is which is best —i.e. which has the best potential payoff?
The price for the new product is $60 and the sales department thinks that they can sell as many as 10,000 but may only be able to sale 5,000. So, let’ call 10,000 the favorable option and 5,000 as the unfavorable. Now the sales department has also predicted that the probability of a favorable acceptance of the new produce is .30 and the probability of unfavorable acceptance is .70, so pretty high if we get it wrong.
The cost to buy the robot is $300,000 and the cost to hire and train the two new employees is $200,000. You can see that the robot cost more to purchase, but it can produce the new product for $10 each, whereas the product produced by the humans would cost $15 each to produce.
So how do we solve this problem? First, we need to calculate our net cost for each option, remember we had three of them, robot, human or abandon the plan.
Slide 3
So, let’s work on the robot option first:
Robot
Favorable (high sales)
|
$600,000 |
Revenue ($60 x 10,000) |
|
$-100,000 |
Mfg. Cost ($10 x 10,000) |
|
$500,000 |
Profit |
|
$-300,000 |
Robot cost |
|
$200,000 |
Net |
Unfavorable (low sales)
|
$300,000 |
Revenue ($60 x 5,000) |
|
$-50,000 |
Mfg. Cost ($10 x 5,000) |
|
$250,000 |
Profit |
|
$-300,000 |
Robot cost |
|
$-50,000 |
Net |
Now that we have the cost for our two options for the robot, let’s do the same if we hire two new employees.
Slide 4
Hire and Train new employees
Favorable (high sales)
|
$600,00 |
Revenue ($60 x 10,000) |
|
$-150,000 |
Mfg. Cost ($15 x 10,000) |
|
$450,000 |
Profit |
|
$-200,000 |
Hire and training cost |
|
$250,000 |
Net |
Unfavorable (low sales)
|
$300,000 |
Revenue ($60 x 5,000) |
|
$-75,000 |
Mfg. Cost ($15 x 5,000) |
|
$225,000 |
Profit |
|
$-200,000 |
Hire and training cost |
|
$25,000 |
Net |
And of course, the last option is to do nothing.
Slide 5
If we open Excel OM and find the decision tree, we can build the skeleton tree as shown here. As you can see have the three options, Robot, hire, and do nothing in the first column. Next, we have to add decisions for favorable and unfavorable sales conditions for both the robot and hire option.
Slide 6
So, if we put the numbers into our Excel OM software, it looks like this. The cost of the robot is $300,000 so we enter this under the robot box as a negative and the cost to hire and train two new people is $200,000 so this is entered, again as a negative under the hire box. And of course, there is no cost for the do-nothing option. We then enter the probabilities of .3 and .7 over the favorable and unfavorable options for both the robot and hire options and the total cost for each option that we calculated a few minutes ago.
Slide 7
So, how do we get the answer? If you look inside the red box it shows the highest expected monetary value as $92,500 and it is associated with branch 3 (in the red circle) so the firm’s best choice is to hire the two new employees.
Slide 8
Well I hope that this example has helped to clarify the process of building a decision tree. Good luck with your assignment.
College of Arts & Sciences | worldwide.erau.edu
All rights are reserved. The material contained herein is the copyright property of Embry-Riddle Aeronautical University, Daytona Beach, Florida, 32114. No part of this material may be reproduced, stored in a retrieval system or transmitted in any form, electronic, mechanical, photocopying, recording or otherwise without the prior written consent of the University
image1.emf
image2.png
image3.png
