### Description

The Poisson distribution provides the probability that $k$ events occur, given that you expect $\mu$ events to occur (assuming that events are independent from each other and that they arrive at a constant rate). The cumulative probability distribution (cdf) gives the probability that $k$ or less events occur, given that you expect $\mu$ events to occur. The cdf for the Poisson distribution is gives as: $$ P(K<=k) = F(k) = e^{-\mu} \sum_{j=0}^{k}\frac{\mu^j}{j!} $$

The following Excel VBA function calculates the smallest value of X such that Poisson Distribution cdf, evaluated at $k_{\alpha}$ , equals or exceeds $\alpha$. This is the inverse Poisson distribution (sometimes called the reverse Poisson distribution or percentile function). $$ k_{\alpha} = F^{-1}(\alpha) $$ $$ min(k_{\alpha}) \ \ \text{such that} \ \ \alpha \leq e^{-\mu} \sum_{j=0}^{k_{\alpha}}\frac{\mu^j}{j!} $$

This formula can be used to calculate the appropriate stock levels such that the Probability of Nil Stock Out risk is lower than your desired levels. $\mu$ is defined as the expected number of demands during the period being analysed. PNSO is defined as $P = 1- PSNO$ then $X+1$ will be appropriate level of stock to hold.

Where $\mu$ is less than or equal to 10, this function simply calculates X by starting at $X = 0$ and increasing X until the conditions are met. For $\mu$ larger than 10, an initial estimate for X is obtained using the normal approximation, then X is tested to ensure it is the correct value. The normal approximation uses the following estimate: $$ k_{\alpha} \cong \left \lfloor \sqrt{\mu}\Phi^{-1}(\alpha)+\mu \right \rfloor$$

### Example:

A shipping company has received notification from the OEM that a particular widget is on its last manufacturing run and has requested if the user would like to make a Life of Type purchase. The user is planning a system upgrade in 6 years, and so wishes to purchase enough spares such that the Probability of Nil Stock Out (PNSO) is 5%. The average annual usage of the item is 12 a year. The user currently has 20 stock on hand. How many should the user purchase?

$$\alpha = 1- PNSO = 0.95$$ $$\mu = 12 * 6 = 72 \ \ \textit{(expected usage)}$$ $$X = POISSONINV(\alpha, \mu) = 86$$ $$\textit{Purchase} = X - 20 = 66$$

**If you have an improvement to this code, please post below.**

Function PoissonInv(dP As Double, dMu As Double) As Variant 'POISSONINV is the inverse of the Poisson cumulative distribution function (cdf) ' with parameter mu. X = PoissonInv(P, MU) returns the smallest value of X, ' such that the Poisson cdf evaluated at X, equals or exceeds P. ' ' This function will use a simply iterative method when MU is less than 10. When ' MU is greater than 10, an initial guess for X is first obtained from a normal ' approximation, after which the initial guess iterates up and down to ensure it ' is the correct answer. ' 'SYNTAX ' X = PoissonInv(P, MU) where P is the probability between 0 and 1 and MU is the ' expected number of events. ' 'EXAMPLE ' P = POISSON(8, 20, TRUE) = 0.002087259 ' X = POISSONINV(0.002087259, 20) = 8 ' ' ' Author: Andrew O'Connor <andrew.oconnor@relken.com> ' Date: 10 Jul 2014 ' Copyright: 2014 Relken Engineering ' These variables are used to simplify this summation: ' dCDF = dCDF + Exp(-dMu) * dMu ^ iX / .Fact(iX) Dim iX As Long ' number of events Dim dCDF As Double ' cumulative distribution function of iX Dim dExpMu As Double ' Exp(-dMu) Dim dTerm As Double ' incremental term ' These terms are used to conduct a normal approximation of ' the Poisson Distribution Dim dX As Double ' normal approximation for iX Dim dSigma As Double ' Signma for normal approximation Dim dMuThreshold As Double ' Threshold for u above which the normal approximation us used 'Set Threshold after which a normal distribution is used dMuThreshold = 10 'Determine method of calculation If (dP < 0 Or dP >= 1) Or dMu < 0 Then 'Raise error PoissonInv = CVErr(xlErrValue) ElseIf dMu > dMuThreshold Then 'Use normal approximation 'Obtain initial estimate dSigma = Sqr(dMu) dX = WorksheetFunction.NormInv(dP, dMu, dSigma) iX = WorksheetFunction.Max(WorksheetFunction.RoundUp(dX, 0), 0) dCDF = WorksheetFunction.Poisson(iX, dMu, True) 'If the approximation was lower than dP increase iX If dCDF < dP Then Do While dCDF < dP iX = iX + 1 dCDF = dCDF + WorksheetFunction.Poisson(iX, dMu, False) Loop Else 'If approximation was higher than dP, find smallest iX Do While dCDF >= dP dCDF = dCDF - WorksheetFunction.Poisson(iX, dMu, False) iX = iX - 1 Loop 'Take back last subtraction, plus add one to get dCDF < dP iX = iX + 1 End If Else 'Use iterative approach 'Prepare calculation variables dExpMu = Exp(-dMu) dTerm = dExpMu 'Loop through each iteration until the required probability 'is obtained. The number of loops is the answer Do While dCDF < dP 'Update cumulative function dCDF = dCDF + dTerm 'Add iteration iX = iX + 1 'Update addition term dTerm = dTerm * dMu / iX Loop 'Take back the last addition iX = iX - 1 End If PoissonInv = iX End Function