I am facing an optimization problem in my business environment that hopefully you guys can help me with. To give you some background on the topic, I am trying to calculate the inventory (called "safety stock") that is needed to buffer for variability in customer demand (more specifically forecast error) so we can guarantee a certain service level to our customers. Let's say this service level should be 97%.

Now, assuming forecast error follows the normal distribution, I could easily calculate the required "safety stock" as $z * \sigma_{forecast error}$, where z in this case would be 1.88 (97%). However, because of specific reasons, we always have an extra buffer of inventory (called "cycle stock") on top of the safety stock. This cycle stock follows a uniform distribution and can vary between 0 units and X units.

As a consequence of this cycle stock, the safety stock that I would calculate with the basic formula ($z * \sigma_{forecast error}$ ) is too high. This is an opportunity for us to improve, our inventory holding cost is relatively high and we need to cut inventory wherever we can. On the other hand, we don't want to keep too few inventory and lose customers...

The question therefore is: how can I combine the standard normal distribution (z) with the uniform distribution into one formula such that I can calculate the correct (lower) safety stock level which would still allow for a total 97% service level?

Appreciate your help!



  • $\begingroup$ I didn't quite understand your formulation ($z$ is standard normal, so it can be negative. Why multiply to obtain a standard deviation of the forecast error?), but in general you can write $n=z+u$ where $u$ is the cycle stock, uniformly distributed, and then $n$ will have a density that's defined by a convolution between the densities: $f_1(n) = f_2(z)*f_3(u)$ where $f_2(z)$ is a normal density and $f_3(u)$ is a uniform density (provided they are independent). Then using $f_1(n)$ is your density and you can calculate anything, say $P(n>a)$ for some parameter $a$. $\endgroup$
    – yoki
    Dec 14, 2016 at 11:24
  • $\begingroup$ Safety stock is expressed in units, so is the std dev of forecast error. The expected service level with the basic formula is 50% when forecast error follows the norm dist and no safety stock is held: 50% chance that you sell more than forecast and 50% chance that you sell less. Therefore, in my case z cannot be < 0 since we cannot hold negative inventory. In reality however, the expected service level without holding safety stock is >50% given that we always have some "cycle" stock. Hence, we should hold less safety stock to achieve a certain service level vs. basic formula (min. 0 units). $\endgroup$
    – Jan V
    Dec 14, 2016 at 14:50
  • $\begingroup$ I guess n_old = z * std dev should actually be n_new = z * std dev - u since u is always positive and n_old should be reduced when we take cycle stock into account? It's not fully clear to me how I can do this convolution. I would like to enter e.g. 97% as a service level and the formula then returns n_new. Can you help? Thanks a lot! $\endgroup$
    – Jan V
    Dec 14, 2016 at 15:03

1 Answer 1


I'm still not sure I follow your formulation, but I'll solve according to my understanding and feel free to correct me.

Current situation: you have some fixed quantity $x$ and you have some "noise" (uncertainty in demand or forecast error) $N$. The overall demand is therefore $$Y=x+N.$$ Now, you would to increase your quantity such that the probability of someone asking for more that you have is less than 3% (i.e. 97% service level). Then, you find $m$ such that $$P(Y>m)<0.03$$ $$P(x+N>m)<0.03$$ $$1-P(N<m-x)<0.03$$ $$0.97<P(N<m-x)$$ $$P(N<1.89)<P(N<m-x)$$ $$1.89<m-x$$ $$m>x+1.89$$ so you need to add 1.89 to your quantity to be sure that in 97% certainty you'll have enough stock.

Desired situation: you have an additional uniformly distributed quantity $U\sim \mathcal U[0,u]$ of between 0 and $u$ items that's added to your stock. So you have: $Z=x+N+U$ (unrelated to your $z$ in the question), and you need to figure out what's the value of $m$ such that $$P(Z>m)<0.03,$$ exactly as before, only now $Z$ contains another quantity.

Before you could have easily calculated this since you have the normal distribution tables. But now, $P(Z>m)=P(x+N+U>m)=P(N+U>m-x)=1-P(N+U<m-x)$, and $R\triangleq N+U$ is not normally distributed. Its distribution is given by the following convolution: $$f_R(r) = (f_N*f_U)(r) = \int_{-\infty}^\infty f_U(t) f_N(r-t) dt$$ $$ = \frac{1}{u} \int_0^u f_N(r-t) dt$$ $$ = \frac{1}{u} \int_{r-u}^{r} f_N(t) dt$$ Now, since $f_N(t)$ is a normal density, we get $$f_R(r) = \frac{1}{u} [\Phi(r)-\Phi(r-u)]$$

To get back to our problem, we have

$$0.97<P(R<m-x)$$ $$0.97<F_R(m-x)$$ $$F^{-1}_R(0.97)<m-x$$ $$m>x+F^{-1}_R(0.97)$$

where $F^{-1}_R(r)$ is the inverse cdf of $R$. We have the density, $f_R(r)$. We need to numerically integrate it to get the cdf and then find the value in which it is equal to $0.97$. Fortunately, we can use MATLAB (or any other software), and get:

enter image description here

The code is:

resolution = 0.01;
u = 5; val = 0.97;

r = -10:resolution:10;

fr = 1/u * ( normcdf(r) - normcdf(r-u) );
dr = r(2)-r(1);
Fr = cumsum(fr*dr);

[~,idx] = min( (Fr-val).^2 );
Fr_inv_at_val = r(idx);

subplot(211); plot(r,fr,'linewidth',2), title('f_R(r)');
hold off; plot(r,Fr,'linewidth',2), 
hold on; plot(r(idx),val,'go','linewidth',2)

title(sprintf('F_R(r),   F_R^{-1}(%1.3f)=%1.3f',val,Fr_inv_at_val));

So, the value we want is $m>5.67$, the value we need to add to the current stock, for an arbitrary choice of $u=5$ and certainty of $0.97$.

If I check it by simulation:

x = 10;
u = 5;
N = randn(tot_exps,1);
U = rand(tot_exps,1)*u;

Y = x+N+U;
m = 5.67;
res = mean(Y>x+m)

the value of res is the average number of times the "customer" ($Y$) wanted more than I kept ($x+m$), then indeed I get values very close to 3%.


According to your comments, here's a general way to solve it, using Excel.

The main equation here is $$P(N>m+c)<0.03$$ $$1-P(N<m+C)<0.03$$ $$0.97<P(N-C<m)$$ Let $R=N-C$, then $$0.97<F_R(m)$$ $$F_R^{-1}(0.97)<m$$ so, we need to do the following: a. get $f_R(r)$ for $R=N+C$ where $N$ is standard normal and $C$ is some uniformly distributed variable. Note that I took $C$ instead of $-C$ but due to the uniform distribution it doesn't matter as long as the boundaries are correct. Similarly to before, $f_R(r)$ is given by: $$f_R(r) = \frac{1}{u-v} [\Phi(r-v)-\Phi(r-u)]$$ where $u,v$ are the boundaries of the uniform distribution. b. Get the cdf via $F_R(r)$ by cumulative summation (numerical integration). c. find the value of $r$ for which $F_R(r)=0.97$, i.e. find $F^{-1}_R(0.97)$.

Now, I'm pretty rusty in Basic so I wrote up something in Excel, but you might be able to do much better. Nevertheless, the code produces the correct result. The main idea in this file is to write the values for the cdf in Excel cells, and then use a VBA script to calculate $F_R(r)$ in the desired values and look for the correct $r$ that matches $0.97$.

u = 5
v = 0
desired_val = 0.97

max_cell = 2001
dr = Sheet1.Cells(1, 16) ' dr

For i = 1 To max_cell
    r = Sheet1.Cells(i, 15)
    s = 1 / (u - v) * (WorksheetFunction.Norm_Dist(r - v, 0, 1, True) - WorksheetFunction.Norm_Dist(r - u, 0, 1, True))
    Sheet1.Cells(i + 1, 14) = Sheet1.Cells(i, 14) + s * dr
Next i

' now we have the values of the cdf of the random variable R in the N column

' next, we need to find which item in N column (the cdf) is closest to 0.97

chosen_idx = 1
min_diff = 1000 ' some high value

For i = 1 To max_cell
    cur = Abs(Sheet1.Cells(i, 14) - desired_val)
    If cur < min_diff Then
        chosen_idx = i
        min_diff = cur
    End If
Next i
' now we have the index of the suitable value for which F_R = 0.97. to invert we just take the value from O in this index
chosen = Sheet1.Cells(chosen_idx, 15)
Sheet1.Cells(8, 8) = chosen

I'm not sure if it's allowed to attach files here, but I attach a link to the xlsx file itself: https://www.dropbox.com/s/1cvfrkna8vbged3/Book1.xlsm?dl=0

  • $\begingroup$ Thanks again for your detailed reply, really appreciate it! As you say, demand x is fixed and known. We can leave x out of all equations. We are interested in buffering for the variability of the forecast error N. In the current situation, you suggest to keep 1.89 extra stock, but given that this is derived from the std norm dist I would assume this is actually 1.89 * std dev forecast error (in units), cf. problem statement? The desired situation is slightly different than you stated, I believe it should be: P(N > m + c) < 0.03, where m = z * std dev fcst error and c is uniformly distributed. $\endgroup$
    – Jan V
    Dec 15, 2016 at 14:50
  • $\begingroup$ Unfortunately I am not very familiar with the mathematical magic, even with your detailed steps I wouldn't be able to solve it myself, but would anyway be really interested to follow your thought process. Would you be able to adjust your calculations to the above comments? I would need to implement this solution in our excel based model, not sure if you are familiar with that / VBA? If not, I can try to implement the cdf somehow myself or ask someone's help later on :) $\endgroup$
    – Jan V
    Dec 15, 2016 at 14:53
  • $\begingroup$ I added some VBA-based code according to your comments. $\endgroup$
    – yoki
    Dec 16, 2016 at 5:15
  • $\begingroup$ The std dev of forecast error should be captured somewhere in the equation, as this is what determines m to a large extent. Makes sense right? Products with a high degree of sales predictability require a smaller buffer than products for which forecast is bad. Remember that m = z * std dev forecast error. How does that fit in FR(r)? Traditionally, we said that any product that requires 50% service level does not need a buffer, cf. norm distr (m=0). But now, in the desired situation, (because of C) we will be able to say that no buffer is required below e.g. 55%. Thanks a lot! $\endgroup$
    – Jan V
    Dec 16, 2016 at 9:29
  • $\begingroup$ By the way, given that m is a buffer of inventory in units, in reality it cannot be negative so m >= 0. Hope it's clear, if not, please let me know! $\endgroup$
    – Jan V
    Dec 16, 2016 at 9:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .