Decision Tree
zeusman99
LGMT-420-Excel-OM.pdf
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
