Selection of Projects with Monte-Carlo simulation
Eight possible projects have been identified. A net present value analysis has calculated: (1) the expected revenue for each project if it is successful, (2) the estimated probability of success for each project, and (3) the initial investment required for each project.
Using these figures, the finance manager calculated the expected return and expected profit for each project, as shown on the Model worksheet. Unfortunately, the available budget is only $2.0 million and selecting all projects would require a total initial investment of $2.8 million. Therefore, the problem is to determine which projects to select in order to maximize the total expected profit while staying within the budget constraint.
1. The assumptions represent the success rates for each project. These rates are modeled using a binomial distribution with one trial. During the simulation, the results in column E will be 0 (unsuccessful) or 1 (successful) with the probabilities initially specified. For each simulated test, the expected returns will be equal to the expected income generated in column D or zero.
2. Consequently, the benefits can be positive or negative. By using a cell reference in the probability field of each binomial guess, you can change the probability of the distribution by changing the value in the spreadsheet cell.
3. After running a simulation, you will see the forecast graph, which can be used to analyze the total win results and answer questions such as: What is the average total win? What is the certainty of making a profit of $1.95 million? What is the certainty of losing money? What happens to the forecast if you decide against certain projects?
In the following link you can download the excel sheet, to see the detail of the distribution models of each project: See EXCEL