A small, private company is contemplating an initial public offering (IPO) in which they will sell 40,000 shares of stock. The price of the stock at the IPO is uncertain, with the following distribution

In each of the next five years there is a 30 percent chance the company will fail. If it does not fail, its stock value will increase by an amount given by a lognormal distribution with a mean of 1.5 percent and a standard deviation of 0.5 percent.

Please first gather all the raw information and build a deterministic base Excel spreadsheet model. (Hint: the stock price over time and the companys survival process can be constructed separately.)

(b) Then define the distribution assumptions and output accordingly.

Run the simulation and document your answers to the two questions (9a and 9b )in a text box on your spreadsheet.

a. What is the mean value of the stock at the end of five years, assuming the company does not fail in the interim? b. What is the probability the company will still be in existence after 5 years?

Price Probability 10 11 12 13 14 15 0.10 0.20 0.30 0.20 0.10 0.10