I wonder if I'm lacking the terminology to phrase this question: I would like to model, in Excel, the risk of a fairly rare event, E, happening over time, where time is divided into chunks called periods, each with n trials. So the event has a low probability (p < 0.1%) of occurring but a high variable impact, hence the client's interest.
If the event impact were constant I would use the inverse binomial function to model the occurance of events in each period as BINOM.INV(n, p, RAND())
. A possible approach is then to layer the impact distribution (assume Normal) on top of this: NORM.INV( RAND(), $\bar{x}$, $s$ ). By repeating this many times I can build up a monte-carlo simulation model (into which I need to feed other parameters).
It feels contrived and unrobust, probably inevitably: I don't have much data on how often E occurs, or its impact impact, so clearly I can't be confident about when these events will occur in the future.
Which doesn't stop people asking.
Here's some data: In a sample of 7000 "trials", the event happened 5 times with impacts 6.3, 6.3, 6.7, 6.8 and 7.6. There are 100 trials per period.
My specific questions, which may not be the right ones, are:
- Given the need to model this event, how appropriate is this combined simulation model?
- What better ways are there to model these kinds of events - that have both a probability of occurring and an variable impact?
Thanks in advance for your patience and ideas.