Can you please provide me with a solution for all the questions? It needed to be solved in an Excel file

RESEARCH AND DEVELOPMENT AT ICI: ANTHRAQUINONE Anthraquinone (AQ) was an agent used to reduce pollution in the effluent of a pulp mill. The addition of AQ to the waste stream precipitated out certain paper and pulp waste products allowing them to be filtered out of the mill effluent. AQ was produced by distilling coal tar, a by-product of coke production at a steel works, with the result that the amount of AQ produced was highly dependent on the demand for steel. AQ had uses in dyestuffs and pulp mill waste control, but developing new uses for AQ was contingent on the development of a new process to manufacture AQ synthetically. BASF, one of ICI's competitors, was known to have developed such a process. ICI's research division had discovered a new but unpatentable use for AQ. Without its own process to synthesize AQ, this discovery was unexploitable and would only benefit BASF, which would have an uncatchable head start. ICI had to decide whether to accelerate research on a process to synthesize AQ and develop the commercial application or to terminate the ongoing modest R\&D effort. A decision to go forward would have to be approved by the Board. THE COSTS AND BENEFITS OF THE R\&D The costs and benefits of R\&D were subject to considerable uncertainty. If successful, the benefit of the new venture into AQ was estimated to have a net present value of $25 million, plus or minus 50%. If the venture was unsuccessful, even after successful process development and best efforts at commercial development, a break-even outcome was expected. The research expenses to find a new process were estimated at $0.8 million 25%; if successful, this research would be followed by process development expenses of $3.0 million 25%. Research on the new production process would need to be accompanied by some preliminary market development at a cost of $200,00025%. If this initial market development suggested significant market potential, preliminary commercial development would have to be undertaken at an estimated cost of $0.5 million 25%. Additional commercial development expenses of $1.0 million 25% would be necessary after the Board approved the full-scale commercialization of the product. Assume all the costs and benefits provided above follow symmetric triangular distributions. QUANTIFYING THE UNCERTAINTIES Any R\&D project is subject to a high degree of uncertainty. The project team assessed the conditional probabilities for the AQ R\&D project as follows: Probability that research would find a new AQ synthesis process: 0.60.15 Probability that initial market development would indicate a significant market: 0.60.15 Probability that the Board would approve the final commercial development (if the market and initial commercial development were successful): 0.80.2 Probability of final commercial success: 0.80.2 Assume all the probabilities provided above follow uniform distributions. Questions: 1- Construct the decision tree. (5 points) 2- Analyze the decision tree under the optimistic scenario (all revenue items and probabilities at their high estimate and all cost items at their low estimate). What are the optimal decision and expected monetary value (i.e., expected net present value)? You do not need a simulation for this question. ( 10 points) 3- Analyze the decision tree under the pessimistic scenario (all revenue items and probabilities at their low estimate and all cost items at their high estimate). What are the optimal decision and expected monetary value (i.e., expected net present value)? You do not need a simulation for this question. (10 points) 4- Analyze the decision tree under the most likely scenario (all revenue and cost items and probabilities at their expected value). What are the optimal decision and expected monetary value (i.e., expected net present value)? You do not need a simulation for this question. ( 10 points) 5- Analyze the decision tree under the stochastic scenario, i.e., simulate the decision tree using the distributions provided for 2000 replications. Report the following after freezing the values of your simulation table: (20 points ) - Construct the histogram of the expected net present values with six bins. - Report the 95% confidence interval for the expected net present value. - Report the probability that the expected net present value is negative. - In how many replications the optimal decision is to initiate R\&D? What is the average net present value for the replications in which the optimal decision is to initiate R&D ? 6- What would be your recommendation based on the different scenarios (optimistic, pessimistic, most likely, and stochastic)? Justify your recommendation. ( 5 points)