Combine standard normal distribution with uniform distribution
$begingroup$
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
probability-distributions normal-distribution combinations uniform-distribution
asked Dec 14 '16 at 11:14
Jan VJan V
113
$endgroup$
add a comment |
$begingroup$
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
probability-distributions normal-distribution combinations uniform-distribution
asked Dec 14 '16 at 11:14
Jan VJan V
113
$endgroup$
$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 '16 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 '16 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 '16 at 15:03
add a comment |
$begingroup$
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
probability-distributions normal-distribution combinations uniform-distribution
asked Dec 14 '16 at 11:14
Jan VJan V
113
$endgroup$
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
probability-distributions normal-distribution combinations uniform-distribution
probability-distributions normal-distribution combinations uniform-distribution
asked Dec 14 '16 at 11:14
Jan VJan V
113
asked Dec 14 '16 at 11:14
Jan VJan V
113
asked Dec 14 '16 at 11:14
Jan VJan V
113
asked Dec 14 '16 at 11:14
Jan VJan V
113
asked Dec 14 '16 at 11:14
Jan VJan V
113
113
$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 '16 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 '16 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 '16 at 15:03
add a comment |
$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 '16 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 '16 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 '16 at 15:03
$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 '16 at 11:24
$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 '16 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 '16 at 14:50
$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 '16 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 '16 at 15:03
$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 '16 at 15:03
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
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 $Usim 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 $Rtriangleq 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
answered Dec 15 '16 at 12:35
yokiyoki
786517
$endgroup$
$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 '16 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 '16 at 14:53
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 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 '16 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 '16 at 9:31
|
show 4 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "69"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmath.stackexchange.com%2fquestions%2f2058370%2fcombine-standard-normal-distribution-with-uniform-distribution%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
$begingroup$
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 $Usim 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 $Rtriangleq 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
answered Dec 15 '16 at 12:35
yokiyoki
786517
$endgroup$
$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 '16 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 '16 at 14:53
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 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 '16 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 '16 at 9:31
|
show 4 more comments
$begingroup$
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 $Usim 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 $Rtriangleq 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
answered Dec 15 '16 at 12:35
yokiyoki
786517
$endgroup$
$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 '16 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 '16 at 14:53
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 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 '16 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 '16 at 9:31
|
show 4 more comments
$begingroup$
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 $Usim 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 $Rtriangleq 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
answered Dec 15 '16 at 12:35
yokiyoki
786517
$endgroup$
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 $Usim 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 $Rtriangleq 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
answered Dec 15 '16 at 12:35
yokiyoki
786517
edited Dec 16 '16 at 5:14
answered Dec 15 '16 at 12:35
yokiyoki
786517
answered Dec 15 '16 at 12:35
yokiyoki
786517
answered Dec 15 '16 at 12:35
yokiyoki
786517
786517
$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 '16 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 '16 at 14:53
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 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 '16 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 '16 at 9:31
|
show 4 more comments
$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 '16 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 '16 at 14:53
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 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 '16 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 '16 at 9:31
$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 '16 at 14:50
$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 '16 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 '16 at 14:53
$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 '16 at 14:53
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 at 5:15
$begingroup$
I added some VBA-based code according to your comments.
$endgroup$
– yoki
Dec 16 '16 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 '16 at 9:29
$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 '16 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 '16 at 9:31
$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 '16 at 9:31
|
show 4 more comments
Thanks for contributing an answer to Mathematics Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmath.stackexchange.com%2fquestions%2f2058370%2fcombine-standard-normal-distribution-with-uniform-distribution%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
$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 '16 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 '16 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 '16 at 15:03