There is a quiet problem at the centre of most rare-event risk models: you do not have the data. A pipeline rupture, a well control incident, a tank-truck spill — these are, by design, things that almost never happen to any single operator. You cannot sit and wait twenty years to accumulate a personal record of them. And yet the model needs a number. How often does this happen?
The number has to come from outside. Somebody, somewhere, has already counted these events across a whole industry and published the result. The skill is not in generating the frequency yourself — it is in finding the right external source, reading it correctly, and scaling its number to your own situation. That is the subject of this article. The vehicle is the Poisson distribution, implemented in @RISK as RiskPoisson, and the external source is a real, citable dataset on oil spills during transport.
What the Poisson distribution does
Rees (2015) describes the Poisson distribution as the model for the number of times a discrete event occurs within a fixed window — a period of time, a length of pipe, a volume of traffic — when the average rate of occurrence is constant. It has exactly one parameter: lambda (λ), the average number of events per unit of exposure. That single number does all the work.
// lambda = average number of events per unit of exposure.
// Returns a whole number each iteration: 0, 1, 2, 3, …
// Example: an average of 2.4 spills a year →
=RiskPoisson(2.4)
Two features make the Poisson worth knowing. First, its mean and its variance are both equal to λ. You do not supply a spread separately — the spread is locked to the average by the structure of the process itself. Second, its shape shifts with λ in a useful way. When λ is small, the distribution is almost a yes/no event: either nothing happens this year, or one thing does. As Rees notes, below an intensity of roughly 0.15 the chance of two or more events in a single period drops under one percent, so a low-intensity Poisson behaves like a simple Bernoulli trial. When λ is large, the distribution fills out, becomes symmetric, and starts to look like a normal curve.
It shows up wherever you count independent events over a fixed exposure: customers arriving in a queue per minute, goals in a football match, emails landing in an hour, flaws per metre of weld — and, for our purposes, spills per year across a transport network. The mental test for whether Poisson fits is simple: am I counting how many times something happens, where each occurrence is roughly independent and the underlying rate is steady? If yes, Poisson is your first candidate.
The problem: you own exposure, not history
Here is the situation a real operator is in. They know precisely how much product they move and how far they move it. What they do not have is a long internal log of spills, because spills are rare and the company has not been running long enough — or carefully enough — to build one. They own the exposure. They do not own the frequency.
This is the everyday case for external sourcing, and it is more common than the textbooks admit. The move is to find a body that has aggregated the events across the whole industry, extract a rate expressed per unit of exposure, and then multiply that rate by your own exposure to get your own λ. The published source gives you a rate; your operation supplies the scale; λ falls out of the two.
The external source: a real oil-spill dataset
For oil spills in transport, one of the cleanest published sources is a study presented at the 1985 Oil Spill Conference by Walter, DiGregorio, Kooyoomjian and Eby — analysts from the US Department of Transportation and the Environmental Protection Agency. They pulled together roughly 26,500 incidents from federal reporting systems and built an early predictive model of spill risk by mode of transport.
What makes the study so usable for our purpose is that the authors chose the Poisson process themselves. Having assumed that spills occur at random, they state plainly that "the occurrence of spills can be approximated using the Poisson process." We are not retrofitting a distribution onto unwilling data — we are following the original analysts.
And they hand us exactly the thing we need: a table of spill frequency per billion ton-miles by transport mode. A ton-mile is simply one ton of product carried one mile — a clean measure of exposure that travels across operators of any size. Their figures, rounded, are roughly 44.6 spills per billion ton-miles for highway transport, 9.4 for vessels, 7.8 for rail, and 0.48 for pipelines. Those are rates per unit of exposure. They are ready to be scaled.
From a published rate to your own lambda
Setting. A pipeline operator moves crude through its system and wants to model how many reportable spills it might see in a year, to size its emergency-response capacity and its contingency budget.
Exposure. The system carries 50 million tonnes a year over an average haul of 100 miles. That is 50 × 100 = 5,000 million ton-miles, or 5 billion ton-miles of exposure per year.
Borrowed rate. From Walter et al. (1985), the pipeline rate is 0.48 spills per billion ton-miles per year.
Lambda. λ = rate × exposure = 0.48 × 5 = 2.4 spills per year. That single number is the whole model.
Building the model
The workbook is one Excel file with four sheets, and the structure mirrors the way the logic actually flows. The INPUTS sheet holds the borrowed rate, your exposure, and the λ they produce. The MODEL sheet is the @RISK engine. The POISSON_CHECK sheet reproduces the entire distribution in native Excel so the model can be read and verified without the add-in. A READ_ME sheet records where the number came from and how to recalibrate it.
On the INPUTS sheet, λ is never typed in by hand. It is computed, so the chain from source to parameter is visible and auditable.
Lambda (C18): =C7*C14 // published rate × your exposure = 0.48 × 5 = 2.4
On the MODEL sheet, RiskPoisson is written once, pointing at that λ cell. The draw is named and tagged as an output so the result can be read straight from the @RISK report.
=RiskPoisson(INPUTS!C18,RiskName("Annual_Spills"),RiskOutput())
// One whole number per iteration, drawn around an average of 2.4.
That is the entire model. Everything downstream — a contingency figure, a response-capacity sizing, an environmental-liability estimate — is arithmetic that multiplies this count by something. The workbook includes one such downstream cell as an illustration, using a flat planning cost per spill, with a clear note that modelling how large each spill is belongs to a separate distribution. We will come back to that.
Reading the output
Run the simulation, or just read the native POISSON_CHECK sheet, and the distribution tells a clear operational story for λ = 2.4.
Each of those numbers is a planning input. The 9% clean-year probability tells you not to budget on the assumption that a quiet year is normal — it is the exception. The mode of two, against a mean of 2.4, shows the slight right skew that a count distribution always carries: bad years pull harder than good years. And the roughly one-in-ten chance of five or more spills is exactly the tail a response plan has to be able to absorb. A frequency model earns its keep precisely here — by turning a single borrowed rate into a shape you can budget against.
"The published source gives you a rate. Your operation gives you the scale. Lambda is the product of the two — and nothing about that step is a guess."
The vintage trap
Now the honest part, and the most important discipline in external sourcing. The 1985 figure is old. A careful reader will ask whether it can still be trusted — and they are right to ask, because the answer is no, not as a current number.
Spill frequency has fallen dramatically since the 1980s. The International Tanker Owners Pollution Federation, which keeps the long-run record for marine spills, reports that tanker spills of seven tonnes or more dropped from about 79 per year in the 1970s to roughly six per year in the 2010s — a reduction of over 90% — while large spills above 700 tonnes fell from 24.5 a year to under two. Strikingly, that decline happened as global oil trade grew, which rules out "less oil moved" as the cause. The drivers were structural: double-hull mandates after Exxon Valdez, the phase-out of single-hull tankers, and charterer vetting regimes that put commercial penalties on substandard operators.
This is why the objection is a gift rather than a flaw. The whole point of external sourcing is that data has a date stamp, and λ is something you update, not something you carve in stone. The 1985 study is a perfect teaching specimen because it is old: it shows you the mechanics of borrowing a rate, and the modern collapse in spill numbers shows you why you always check the vintage before you trust the borrowed rate. Treat the rate as a living input wired to a source, and the model stays honest as the world changes.
Choosing the distribution with the @RISK Agent
Selecting a distribution, getting the syntax exactly right, and knowing the sensible alternatives is mechanical work — and it is precisely the kind of work the @RISK Agent is built to accelerate. Used well, it shortens the distance between "I have a counting problem" and "I have a correct, named, output-tagged function in my model."
What it is good for — and where the line sits
The Agent helps with three things on a model like this one: confirming the distribution choice from how you describe the problem, handing you exact function syntax, and surfacing alternatives you might not have reached for.
On syntax, it will give you the full expression — the distribution wrapped in RiskName and RiskOutput, pointing at your λ cell — so the result lands in the report with a label rather than as an anonymous number.
On alternatives, it earns its place. If your observed counts are over-dispersed — variance noticeably larger than the mean, which a pure Poisson cannot represent — it will suggest a Negative Binomial instead, the natural model when the rate itself wobbles. If λ is tiny, it will note that a Bernoulli approximation is cleaner. And if you eventually need to model spill size as well as count, it will point you toward a compound Poisson, pairing the frequency draw with a severity distribution.
The line, though, is firm. The Agent helps you pick the shape and write the function. It does not set λ for you. The parameter and its provenance — which source, which mode, which vintage, scaled to which exposure — stay with the analyst. AI accelerates the calibration; it does not own the number. The question to keep asking of any frequency in a model is: underneath this output, whose data set the rate, from what year, and does it still hold?
Verifying the output
Two checks confirm the model is behaving. First, the simulated mean should sit on λ, and the simulated variance should sit on λ as well — the Poisson signature of mean equalling variance. In the native simulation on the POISSON_CHECK sheet, five hundred draws land close to 2.4 on both, and the @RISK run with ten thousand iterations tightens that further. If the mean and variance diverge meaningfully, your data is not Poisson, and that is the signal to move to a Negative Binomial.
Second, the probability of a zero-spill year has a closed form: it is e raised to the power of minus λ. For λ = 2.4 that is about 0.091. The POISSON_CHECK sheet computes it natively, so you can read the exact figure and confirm the @RISK output matches it rather than taking the simulation on faith.
=POISSON.DIST(0,2.4,FALSE) // → 0.091, i.e. e^(−λ)
Simulation settings
Ten thousand iterations with Latin Hypercube sampling give stable probabilities on a count distribution of this size — the P(0), the mode and the upper-tail probability all settle well within that budget. A single simulation is enough; add a second only to check sensitivity to the random seed.
Frequency, not severity
One boundary is worth stating plainly, because the original 1985 study makes the same point about its own data: this model answers how many, not how big. Spill volume is dominated by a handful of very large events, and a Poisson count cannot capture that heavy tail. To model the size of each spill you add a second distribution and combine the two into a compound model — frequency drawn from RiskPoisson, severity drawn from a skewed distribution layered on top. That is the natural next step, and the subject of the follow-up article.
When you cannot generate a frequency from your own history, borrow one — but borrow it deliberately. Find the source, read the rate per unit of exposure, scale it to your operation, check its vintage, and wire λ to where it came from. RiskPoisson is the easy part. The discipline around the number is the craft.