For a product with a set shelf life, obsolescence will occur whenever units sold during the shippable life are less than the starting inventory at the beginning of that period. Varying demand can usually be modelled as a random variable following a probability distribution such as the gamma distribution (see my prior blog: A Probability Distribution for Demand Variability).
The gamma probability density function is:
where 
is the gamma function. The mean of the gamma probability density function is ab and the standard deviation is 
.
Read on to see the derivation of formulas for expected value of obsolescence using the gamma probability density function. Or if you prefer to skip the derivation, click here to see the formulas for an Excel spreadsheet and an example.
In all formulas below, the following symbols are used:
- k – starting inventory
- X – Demand during the shippable life (assumed to be a gamma distribution)
- a – Shape parameter of the gamma distribution.
- b – Scale parameter of the gamma distribution.
- P(x<k) – probability demand is less then k, i.e. probability of obsolescence.
- U – Unsold or surplus units (obsolescence). U = max(0, k − X).
- E(·): expected value of a random variable.
- Var(·): variance of a random variable.
The probability of obsolescence for an item that has a gamma distribution for demand is:
The expected amount of obsolescence is E(U)= k*F(k,a,b)-abF(k,a+1,b), which is derived below.
Derivation of Expected Value of Obsolescence
If the starting inventory is k units and demand is X, then obsolescence is k–X. So then the obsolescence can be defined as the random variable:
We can compute the expected value of U = G(X) as follows:
Since U = 0 when X < 0 and when X > k, when we apply this theorem to U, we get
And since U = 0 for x > k, this can also be written as
[1]
Since f is the Gamma density function
, we have
Recognizing the first term as the cumulative distribution function, and using the identity 
for the second term, this last line becomes
Which we can recognize as E(U)= k*F(k,a,b) – abF(k,a+1,b)
Alternate Derivation of Expected Value of Obsolescence
We can take another approach to compute expected obsolescence: compute the expected obsolescence given that it is nonzero, and multiply by the probability that obsolescence is nonzero.
The expected sales, given that obsolescence is not 0 is
And, since we are thinking of N as a constant,
so
and by equation [1], this is the same value as we computed above.
Formulas for Estimating Obsolescence in Excel
The Excel function for the Gamma Distribution probability function is GAMMA.DIST(x,alpha,beta,cumulative)
The GAMMA.DIST function syntax has the following arguments:
- X (Required): The quantity at which you want to evaluate the distribution.
- a (Required): shape parameter of the gamma distribution.
- b (Required): scale parameter of the gamma distribution.
- Cumulative (Required): A logical value that determines the form of the function. If cumulative is TRUE, GAMMA.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
Below formulas are to calculate estimated obsolescence using gamma distribution with a known mean and standard deviation:
Known quantities:
- k – Starting inventory
- Demand_Mean – mean quantity of demand during the shippable life
- Demand_SD – standard deviation of demand during the shippable life
Calculations:
From the properties of the gamma distribution
So
a = shape parameter for gamma distribution
= E(X)2 / Var(X) = (Demand_Mean / Demand_SD)^2
b = scale parameter for gamma distribution
= Var(X) / E(X) = Demand_SD^2/ Demand_Mean
Prob_Ob = P(U>0) = Probability of obsolescence
= GAMMA.DIST(N,a,b,TRUE)
Ex_Obs = E(U) = Expected obsolescence
= k*GAMMA.DIST(k,a,b,TRUE) – a * b * GAMMA.DIST(k,a+1,b,TRUE).
Example of Estimating Obsolescence in Excel
Consider the example where a food manufacturer has a minimum run of 525 cases for a product with a shippable life of three months. The average demand is 200 cases per month with a standard deviation of 100 cases per month.
k (Starting inventory) = minimum run of 525 cases
(assuming no remaining inventory at time of production)
Demand_Mean (mean quantity of demand during the ship life) = 600 cases
(200 cases per month * three months)
Demand_SD (standard deviation of demand during ship life) = 173.21 cases
(100 cases per month * Sqrt(3) months)
Calculations:
a = shape parameter for gamma distribution
= (Demand_Mean / Demand_SD)^2
= (600 / 173.21) ^2
= 12
b = scale parameter for gamma distribution
= Demand_SD^2/ Demand_Mean
= 173.21^2/600
= 50
Prob_Ob = P(U>0) = Probability of obsolescence
= GAMMA.DIST(N,a,b,TRUE)
= GAMMA.DIST(525,12,50,TRUE)
= 36.13%
Ex_Obs = E(U) = Expected obsolescence
= k*GAMMA.DIST(k,a,b,TRUE) – a * b * GAMMA.DIST(k,a+1,b,TRUE)
= 525 * 0.3613 – 12 * 50 * 0. 2580
= 34.88 units