![]() This may seem like a strange way to implement Monte Carlo simulation, but think about what is going on behind the scenes every time the Worksheet recalculates: (1) 5000 sets of random inputs are generated (2) The model is evaluated for all 5000 sets. So, to re-run the entire simulation all we have to do is recalculate the worksheet ( F9 is the shortcut). We have used the volatile RAND() function. Re-run the Simulation: F9Īlthough we still need to analyze the data, we have essentially completed a Monte Carlo simulation. ![]() ![]() Each row represents a single evaluation of the model, with columns A-E as inputs and the Profit as the output. We simply copy the formula for profit down 5000 rows, making sure that we use relative references in the formula (no $ signs). To iteratively evaluate our model, we don't need to write a fancy macro for this example. =A2*C2*D2-(E2+A2*B2) Step 4: Running the Simulation Our model is very simple, so to evaluate the output of our model (the Profit) for each run of the simulation, we just put the equation in another column next to the inputs, as shown in Figure 2. The process for the other variables (except for H, which is constant). To generate 5000 random numbers for L, you simply copy the formula down 5000 rows. (Hopefully you have downloaded the example Note that the reference Model!$F$14 refers to the corresponding Min value for the variable L on the Model worksheet, as shown in Figure 1. Figure 2: Screen capture from the example sales forecast spreadsheet.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |