Monte carlo in excel average and standard deviation

LetвЂ™s suppose we want to simulate 400 trials, or iterations, for a normal random variable with a mean of 40,000 and a standard deviation of 10,000. This procedure is illustrated in the file Normalsim.xlsx, shown in Figure 60-3. If you type in any cell the formula NORMINV(rand(),mu,sigma), you will generate a simulated value of a normal random variable having a mean mu and standard deviation sigma. When we press F9 to recalculate the random numbers, the simulated probabilities are close to our assumed demand probabilities. In the cell range F8:F11, use the COUNTIF function to determine the fraction of our 400 iterations yielding each demand. This formula ensures that any random number less than 0.10 generates a demand of 10,000, any random number between 0.10 and 0.45 generates a demand of 20,000, and so on. You then generate 400 trials, or iterations, of calendar demand by copying from B3 to B4:B402 the formula VLOOKUP(C3,lookup,2). You generate 400 random numbers by copying from C3 to C4:C402 the formula RAND(). Random numbers greater than or equal to 0 and less than 0.10 will yield a demand of 10,000 random numbers greater than or equal to 0.10 and less than 0.45 will yield a demand of 20,000 random numbers greater than or equal to 0.45 and less than 0.75 will yield a demand of 40,000 and random numbers greater than or equal to 0.75 will yield a demand of 60,000. The key to our simulation is to use a random number to initiate a lookup from the table range F2:G5 (named lookup). To demonstrate the simulation of demand, look at the file Discretesim.xlsx, shown in Figure 60-2 on the next page. Greater than or equal to 0.45, and less than 0.75

Greater than or equal to 0.10, and less than 0.45 The following assignment ensures that a demand of 10,000 will occur 10 percent of the time, and so on. How can we have Excel play out, or simulate, this demand for calendars many times? The trick is to associate each possible value of the RAND function with a possible demand for calendars.

Suppose the demand for a calendar is governed by the following discrete random variable: For example, if the random number generated in cell C3 is a large number (for example, 0.99), it tells us nothing about the values of the other random numbers generated. Also note that the values generated by RAND in different cells are independent. These results are consistent with the definition of a random number. Notice that the average of the 400 numbers is always approximately 0.5, and that around 25 percent of the results are in intervals of 0.25. When you press the F9 key, the random numbers are recalculated. Then, in column F, you can track the average of the 400 random numbers (cell F2) and use the COUNTIF function to determine the fractions that are between 0 and 0.25, 0.25 and 0.50, 0.50 and 0.75, and 0.75 and 1. The RAND function always automatically recalculates the numbers it generates when a worksheet is opened or when new information is entered into the worksheet.ДЇirst, copy from cell C3 to C4:C402 the formula =RAND(). Note: When you open the file Randdemo.xlsx, you will not see the same random numbers shown in Figure 60-1. Oil and drug companies use simulation to value 'real options,' such as the value of an option to expand, contract, or postpone a project.ДЇinancial planners use Monte Carlo simulation to determine optimal investment strategies for their clientsвЂ™ retirement. Sears uses simulation to determine how many units of each product line should be ordered from suppliers-for example, the number of pairs of Dockers trousers that should be ordered this year. Proctor and Gamble uses simulation to model and optimally hedge foreign exchange risk. Lilly uses simulation to determine the optimal plant capacity for each drug. GM uses simulation for activities such as forecasting net income for the corporation, predicting structural and purchasing costs, and determining its susceptibility to different kinds of risk (such as interest rate changes and exchange rate fluctuations). At GM, this information is used by the CEO to determine which products come to market. General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb, and Eli Lilly use simulation to estimate both the average return and the risk factor of new products.

Many companies use Monte Carlo simulation as an important part of their decision-making process.

Monte carlo in excel average and standard deviation