Modeling Excel

Modeling Excel

For decision trees, you may choose to solve the problems using the PrecisionTree add-in in Excel.

Problem 1

A Tech has developed a new AI-based robot and is in the process of deciding how to monetize it. The market leader ClosedAI has offered to acquire the technology for $100,000 in cash, plus an additional bonus of $100,000 if the product is a hit. If A Tech decides not to sell the tech and manufacture the product itself, it could potentially make a profit of $500,000 if it becomes a hit, but also an estimated loss of $300,000 if it proves unpopular. To simplify the problem, A Tech’s executives are assuming that the product is either a hit, i.e., it captures a certain critical number of customers, or not a hit.

(a) Using decision tree analysis, evaluate what the company should do if:

  • Executives estimate the chance that the product will be a hit as 0.7?
  • Executives are uncertain about the product’s prospects and says the chances are “evens” (i.e.    the probability of the toy being a hit equals 0.5)?

(b) What is the ‘critical’ value for the probability of the product being a hit: that is, the probability value which makes A Tech indifferent between (i) selling the rights and (ii) making the product itself?

Problem 2

A Inc. is a manufacturer of computer servers and runs its production at its factory in North Carolina. The plant has the capacity to produce 10,000 servers per year. To produce a server, the raw materials cost $6000 and the manufacturing processes (including capital and labor) cost an additional $400. A Inc. sells the servers for $12,000 each.

Because of the rapid development of the data center sector in Northern Virginia, A Inc anticipates a growth of demand in its servers, and is considering how to acquire additional production capacity. There are two possible options for capacity acquisition. The first option is to invest in state-of-the-art manufacturing machinery at the existing plant, which costs $5 million. This would double the production capacity, and also decrease the production cost per server from $400 to $350. The second option is not to increase in-house production capacity (i.e., capacity remains at 10,000 units), but if demand exceeds in-house capacity, any shortfall will be outsourced to an alternative supplier. This alternative supplier charges $8000 per server (materials already included).

In the past year, demand for A Inc servers exactly met the production capacity of 10,000 units. For the coming year, demand is expected to either increase by 50% with 80% probability, or stay the same with 20% chance.

Develop a decision tree for this problem. Using an expected value criterion, identify the best decision for the plant.

Problem 3

G Development Corp. (GDC) is considering bidding on a contract for a new commercial building complex. If GDC chooses to bid on the contract, the pre-bidding legal preparation would cost $200,000. There is a 80% chance that the bid is successful. In that case, GDC has to pay a contribution of $2 million to become a partner in the project.

After becoming a partner, GDC would have two options. In the first option, it could build the complex on its own. In this case, GDC’s income from the project would depend on the level of demand for office space by the time the project is completed. It is estimated that the income (revenue minus construction costs) will be $6 million if the demand is high, $4 million if demand is medium, and $1 million if demand is low. In the second option, GDC may sell the rights in the contract to another developer, who offers $3.5 million in cash.

GDC has gathered the following probability estimates for the level of demand for office space: the probability of low demand is 0.3; the probability of medium demand is 0.4; and the probability of high demand is 0.3.

(a) Draw a decision tree for this problem. Using an expected value criterion, identify the best decision for GDC.

(b) Discuss how the decision taken in part (a) would change with respect to the chance of the bid becoming successful.