This first post is a 'fishing' exercise to hopefully receive some guidance. I'm OK with creating a monte carlo simulation in excel, on a set of data given an average and stdev, based on the past 3 years results for various data filters. Each data filter produces a sub total profit/loss cash flow for the given filter.
However these filters run concurrently but with different result frequencies. For instance one filter might have 3000 results, another 500 and so on. All at differing time intervals. If you assumed that as a testing model hypothesising about the future, you could assume that the frequency of results are in proportion 'count' wise, but spaced timewise randomly.
The aim of the exercise is to try and find out the possible combined cash flow drawdowns that could be experienced as a model running consecutive filters. Any suggestions on where to start appreciated.
Cheers
Start with uploading a sample workbook in which you indicate very clearly (contrary to ....) what data you start with, which result you are looking for.
Continuing on with what I learnt above, I built an excel MonteCarlo test bed (attached).
Sheet1 is an abridged version as there are 10000 simulations as a test normally. What I'm after is a way to press a macro trigger on this sheet, the code goes to Sheet2. On Sheet2 is a SERIES column to denote ranges of cells. It copies the first range B2:B9 and pastes that into Sheet1 B3:M3. When it pastes, it brings with it the conditional format colour 'red'. I would then look to see the result of the simulation, and repress the macro button, which would clear cells B3:M3, go back to Sheet2 and copy the next range B10:B17 and paste that back on Sheet1 range B3:M3, with colour attached.
Cheers
Reply With Quote