Explain in detail in Excel(Show Formulas)

Spitfire Aviation Inc. manufactures small, private aircraft. Management is evaluating a proposal to introduce a new high-performance plane. High-performance aviation is an expensive sport undertaken largely by people who are both young and wealthy. Spitfire sees its target market as affluent professionals under 35 who have made a lot of money in the stock market in recent years.

Stock prices have been rising rapidly for some time, so investment profits have been very handsome, but lately there are serious concerns about a market downturn. If the market remains strong, Spitfire estimates it will sell 50 of the new planes a year for 5 years, each of which will result in a net cash flow contribution of $200,000. If the market turns down, however, only about 20 units a year will be sold. Economists think theres about a 40% chance that the market will turn down in the near future.

There are also some concerns about the design of the new plane. Not everyone is convinced it will perform as well as the engineering department thinks. Indeed, the engineers have sometimes been too optimistic about their projects in the past. If performance is below the engineering estimate, word-of-mouth communication among fliers will erode the products reputation, and unit sales after the first year will be 50% of the preceding forecasts. Management thinks there is a 30% chance the plane will not perform as well as the engineers think it will. The cost to bring the plane through design into production is estimated at $15 million. Spitfires cost of capital is 12%.

a. Draw and fully label the decision tree for the project.

b. Calculate the NPV and probability along each path.

c. Calculate the projects expected NPV.

d. Calculate the projects standard deviation.