Excel VBA Code - Inverse Poisson - Calculating Stock Levels

Andrew O'Connor
Relken Engineering
Have a question or want to speak with Andrew O'Connor ? Contact us with your details.

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$$

 

File Attachment: 
PreviewAttachmentSize
POISSONINV.XLS39.5 KB

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

  1. Function PoissonInv(dP As Double, dMu As Double) As Variant
  2. 'POISSONINV is the inverse of the Poisson cumulative distribution function (cdf)
  3. ' with parameter mu. X = PoissonInv(P, MU) returns the smallest value of X,
  4. ' such that the Poisson cdf evaluated at X, equals or exceeds P.
  5. '
  6. ' This function will use a simply iterative method when MU is less than 10. When
  7. ' MU is greater than 10, an initial guess for X is first obtained from a normal
  8. ' approximation, after which the initial guess iterates up and down to ensure it
  9. ' is the correct answer.
  10. '
  11. 'SYNTAX
  12. ' X = PoissonInv(P, MU) where P is the probability between 0 and 1 and MU is the
  13. ' expected number of events.
  14. '
  15. 'EXAMPLE
  16. ' P = POISSON(8, 20, TRUE) = 0.002087259
  17. ' X = POISSONINV(0.002087259, 20) = 8
  18. '
  19. '
  20. ' Author: Andrew O'Connor <andrew.oconnor@relken.com>
  21. ' Date: 10 Jul 2014
  22. ' Copyright: 2014 Relken Engineering
  23.  
  24. ' These variables are used to simplify this summation:
  25. ' dCDF = dCDF + Exp(-dMu) * dMu ^ iX / .Fact(iX)
  26. Dim iX As Long ' number of events
  27. Dim dCDF As Double ' cumulative distribution function of iX
  28. Dim dExpMu As Double ' Exp(-dMu)
  29. Dim dTerm As Double ' incremental term
  30.  
  31. ' These terms are used to conduct a normal approximation of
  32. ' the Poisson Distribution
  33. Dim dX As Double ' normal approximation for iX
  34. Dim dSigma As Double ' Signma for normal approximation
  35. Dim dMuThreshold As Double ' Threshold for u above which the normal approximation us used
  36.  
  37. 'Set Threshold after which a normal distribution is used
  38. dMuThreshold = 10
  39.  
  40. 'Determine method of calculation
  41. If (dP < 0 Or dP >= 1) Or dMu < 0 Then 'Raise error
  42. PoissonInv = CVErr(xlErrValue)
  43.  
  44. ElseIf dMu > dMuThreshold Then 'Use normal approximation
  45. 'Obtain initial estimate
  46. dSigma = Sqr(dMu)
  47. dX = WorksheetFunction.NormInv(dP, dMu, dSigma)
  48. iX = WorksheetFunction.Max(WorksheetFunction.RoundUp(dX, 0), 0)
  49. dCDF = WorksheetFunction.Poisson(iX, dMu, True)
  50.  
  51. 'If the approximation was lower than dP increase iX
  52. If dCDF < dP Then
  53. Do While dCDF < dP
  54. iX = iX + 1
  55. dCDF = dCDF + WorksheetFunction.Poisson(iX, dMu, False)
  56. Loop
  57. Else
  58. 'If approximation was higher than dP, find smallest iX
  59. Do While dCDF >= dP
  60. dCDF = dCDF - WorksheetFunction.Poisson(iX, dMu, False)
  61. iX = iX - 1
  62. Loop
  63. 'Take back last subtraction, plus add one to get dCDF < dP
  64. iX = iX + 1
  65. End If
  66.  
  67. Else 'Use iterative approach
  68. 'Prepare calculation variables
  69. dExpMu = Exp(-dMu)
  70. dTerm = dExpMu
  71.  
  72. 'Loop through each iteration until the required probability
  73. 'is obtained. The number of loops is the answer
  74. Do While dCDF < dP
  75. 'Update cumulative function
  76. dCDF = dCDF + dTerm
  77. 'Add iteration
  78. iX = iX + 1
  79. 'Update addition term
  80. dTerm = dTerm * dMu / iX
  81. Loop
  82. 'Take back the last addition
  83. iX = iX - 1
  84.  
  85. End If
  86.  
  87. PoissonInv = iX
  88.  
  89. End Function