I am working on a school finance project and I am caught in a little problem. I am running a simple simulation to value equity where sales growth, COGS, and several other assumptions are randomized. There are three different sales growth assumptions, high, medium, and low where there is an explicit forecast for 10 years than a continuing value period. The probabilities are 20%, 60%, and 20% respectively. So far, I have 100 randomly generated data points for each case (300 total). I calculated the expected value by taking each case and adding the values up, dividing by 100, then multiplying that number by its probability, and redoing that for the other two cases. My question is how do I calculate the standard deviation for these 300 points. I thought of taking the standard deviation of each of the 3 cases and multiplying them by their respective probabilities and adding them up. That seems like an ugly way to do it though. I have a feeling there is a ridiculously easy way to do this but I am suffering from a brain fart.
You appear to be mixing Monte Carlo analysis with some weird 3-scenario (of varying probabilities) overlay. Unless there is some incredibly compelling reason to keep a scenario-based analysis in there, just drop it. Generate your 1,000 (not 100) paths. If you want to achieve a desired distribution of paths to match expected mean or std dev, search “moment matching” on Google for a straightforward technique for doing that.
Yea thats where I am confused. The teacher lead me to this direction and trying to figure out how to make it work is troubling me. I have another question then. The growth is the “driver” of this project. In the file, there is a pro forma statement that is linked to those sales growths. When you type a 1 in the cell, the High sales growth values are used to calc the pro forma. If you type a 2 the medium sales growth values are use etc . . . I was thinking of using RANDBETWEEN(1,3) to randomly generate the sales growth values when running the simulation. Is there a way to have the number 1 show up 20% of the time 2 to show up 60% of the time, and 3 to show up 20% of the time. That way instead of having 3 columns of 100 (or 1000s) of data points there is just one column. Thank you for the help.
I looked up moment matching . . . I would definitely need to spend some more time with that to understand it. Thanks for the tip though. Is what I am doing not possible unless I write a macro?
No macros should be required. We use data tables to evaluate Monte Carlo paths, and moment matching requires onl a simple transformation of the original paths. For matching mean it’s simple: your paths have a mean of Xpaths, your desired mean is Xdesired, add (Xdesired - Xpaths) to each path. Matching the second moment isn’t much harder. Actually it’s the exact same transformation as calculating a z-score (where desired mean is 0 and std dev 1.)
I think, you have to calculate by definition, second moments - first monent, then taking squre of the difference. the second moments and mean, we can get them by weight of percentage, that is your probability. make sense ?