A manufacturer must decide whether to build a small or a large plant at a new location. Demand at the location can be either small or large, with probabilities estimated to be 0.4 and 0.6 respectively. If a small plant is built, and demand is large, the production manager may choose to maintain the current size or to expand. The net present value of profits is $223,000 if the firm chooses not to expand. However, if the firm chooses to expand, there is a 50% chance that the net present value of the returns will be 330,000 and 50% chance the estimated net present value of profits will be $210,000. If a small facility is built and demand is small, there is no reason to expand and the net present value of the profits is $200,000. However, if a large facility is built and the demand turns out to be small, the choice is to do nothing with a net present value of $40,000 or to stimulate demand through local advertising. The response to advertising can be either modest with a probability of .3 or favorable with a probability of .7. If the response to advertising is modest the net present value of the profits is $200,000. However, if the response to advertising is favorable, then the net present value of the profits is $220,000. Finally, when large plant is built and the demand happens to be high, the net present value of the profits $800,000.
Draw a decision tree using Excel and recommend a course of action for this company. (Provide your answer in the space provided, in addition an excel document needs to be submitted show your work)