Share
Explore BrainMass

Creating a Decision Tree in Excel

Management at Canron, a switch maker, is confronting a problem of determining whether or not to develop a new ultra power optical switch. The research and development costs of developing such a switch is estimated to be $25 million. If the company goes ahead with the R&D and develops the switch, a crucial issue is whether or not the switch will be viewed by customers as superior to existing optical switches available to the market (and therefore worth paying additional for). The company assesses a profitability of 0.6 that the switch will be viewed as superior, and 0.4 probability that the switch will be viewed as inferior.

After the initial R&D is made, and dependent upon the product's image, (e.g. once Canron knows whether it is viewed as superior or inferior), the company needs to decide whether to produce and market the switch. If they decide to produce and market the switch, its success depends on whether Canron's main competitor, Norbtel, reacts and develops a competing product. Canron assesses a probability of 0.8 that there will be a competing optical switch by Norbtel if Canron's switch is viewed as a superior product. They also assess a probability of 0.3 that there will be a competitive switch by Norbtel if Canron's switch is viewed as an inferior product.

If Canron's switch is viewed as a superior product, then Canron estimates that it will gross $150 million in profits (if produced and marketed) if there is a competitive product, and $350 million in profits (if produced and marketed) if there is no competitive product. If Canron's switch is viewed as an inferior product then Canron estimates that it will lose $150 million (if produced and marketed) if there is a competitive product and will make $30 million in profits (if produced and marketed) if there is no competitive product. All of these numbers do not include the R&D costs.

1. Set up/structure Canron's management problem as a decision tree using Decision Tree (Treeplan) tool in MS Excel. Show practice work/diagram there.

2. Using the EMV criteria (maximize expected profit in this case is goal), determine what course of action that Canron should take. Be certain to state the best strategy in words for this practice and why.

Solution Summary

This Solution contains calculations and a decision tree for the given problem.

$2.19