Combine standard normal distribution with uniform distribution

Question

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!

Thanks.

Jan

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:

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)');
subplot(212); 
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:

tot_exps=10000;
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%.

EDIT:

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