Michael515
Board Regular
- Joined
- Jul 10, 2014
- Messages
- 136
Hi All,
I don't have a Monte Carlo Simulation Add-in (if any of you know of a good free one please let me know), so what I did was use the randbetween() function and then used and =if to create probabilistic choices (ex. I am building a model to forecast financial projections, but not entirely comfortable with my assumptions, that is why I'm building in uncertainty. So for example I have small growth, mid growth, and large growth at 50% probable, 30%, and 20% respectively, therefore I built a randbetween(1,10) and then in the following cell and =if to say that if the randbetween() is between 1 and 5, then small growth of 3 new customers).
After I built in the various uncertainties, I built out a normal financial projections model (revenue, expenses, EBITDA) in order to forecast the bottom line 5 years out and used the =sum to total the 5 years of EBITDA. I created a button with a macro (the macro just clicks the F9 buttom but I linked it with the button so instead of having to use the keys, a less seasoned excel user can just click the button) to refresh the data.
Now I want to find a way to pull the data: Create a macro that goes to the summed up EBITDA, clicks the button, copies the summed up EBITDA, goes to a new sheet, pastes it, and inserts to the next cell. I want to do this so that I can use keys to get 1000 pieces of data to run an analysis on the different EBITDAs (basically artificially create a simulation). The problem I have run into during the recording of the macro is that the refresh button now doesn't work and when I copy/paste (even if I set the paste to just values) and click enter the cell i had copy and pasted into returned a N/A# I am not particularly an expert in macros, so maybe what I am trying to do is beyond its capabilities, but I figured I give this forum a shot.
Any advice/help would be greatly appreciated.
I don't have a Monte Carlo Simulation Add-in (if any of you know of a good free one please let me know), so what I did was use the randbetween() function and then used and =if to create probabilistic choices (ex. I am building a model to forecast financial projections, but not entirely comfortable with my assumptions, that is why I'm building in uncertainty. So for example I have small growth, mid growth, and large growth at 50% probable, 30%, and 20% respectively, therefore I built a randbetween(1,10) and then in the following cell and =if to say that if the randbetween() is between 1 and 5, then small growth of 3 new customers).
After I built in the various uncertainties, I built out a normal financial projections model (revenue, expenses, EBITDA) in order to forecast the bottom line 5 years out and used the =sum to total the 5 years of EBITDA. I created a button with a macro (the macro just clicks the F9 buttom but I linked it with the button so instead of having to use the keys, a less seasoned excel user can just click the button) to refresh the data.
Now I want to find a way to pull the data: Create a macro that goes to the summed up EBITDA, clicks the button, copies the summed up EBITDA, goes to a new sheet, pastes it, and inserts to the next cell. I want to do this so that I can use keys to get 1000 pieces of data to run an analysis on the different EBITDAs (basically artificially create a simulation). The problem I have run into during the recording of the macro is that the refresh button now doesn't work and when I copy/paste (even if I set the paste to just values) and click enter the cell i had copy and pasted into returned a N/A# I am not particularly an expert in macros, so maybe what I am trying to do is beyond its capabilities, but I figured I give this forum a shot.
Any advice/help would be greatly appreciated.
