Forecast Topic and Relation to Box sizes for the years 2015 and 2016
We first determine the linearity of the data given. Using the weekly demand as the independent variable we determine the relationship between the forecasts for the two years. Creating a scatter plot, it can be deduced that the forecasts for both years show a linear relationship. The trend line based on the weekly forecasts shows a linear relationship exists between the forecasts and the prediction values as a result of the regression line. The data is more scattered which shows the demands for the weeks are more spread out across the weeks than the forecasts made.
Figure 1: scatter plot for number of weeks against 2015 forecast
Figure 2: scatter plot for number of weeks against 2016 forecasts
We therefore conduct multiple regression on the data and obtain the following:
Table 1: Regression statistics
SUMMARY OUTPUT | |
Regression Statistics | |
Multiple R | 0.007406182 |
R Square | 5.48515E-05 |
Adjusted R Square | -0.040759236 |
Standard Error | 515.5597836 |
Observations | 52 |
The total number of observations were the 52 weeks. Here we consider two goodness of fit measures; the R squared and adjusted R values. The R squared value obtained is 0.0000549 which gives the correlation between y and y-hat in our model (Carlberg[1]). Also, the R-squared value which is not closer to one implies that the data does not closely fit the regression line. The adjusted R squared value implies that 4.075% around the variation of the weekly demands can be explained in the two regressors; the 2015 and 2016 forecasts.
The regressions analysis will give us the coefficients that will be used in determining our regression line;
Therefore:
Table 2: coefficient statistics
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 391.4504559 | 560.259918 | 0.69869438 | 0.48804614 | -734.434001 | 1517.33491 |
2015 | 0.131655592 | 3.58832638 | 0.03668997 | 0.97088127 | -7.07935625 | 7.34266743 |
2016 | -0.027904138 | 4.08149667 | -0.00683674 | 0.99457287 | -8.22997877 | 8.17417049 |
The line regression line as a result of the least squares coefficients will be
The estimated regression intercept is 391.450 while the estimated coefficient for the forecasts in 2015 and 2016 are 0.132 and -0.028respectively. At The i^{th} observation of the forecasts, we can evaluate the hypotheses that
H_{0}: at 95% confidence level. Therefore, the p- value in the intercept is greater than α= 0.05. thus, we fail to reject the null hypothesis and conclude that the i^{th }observations of the intercept are not statistically significant. Also, the p-values for the 2015 and 2016 coefficients are greater than the significance level and therefore we fail to reject the null hypothesis and conclude that the two variables are not statistically significant and that the i^{th }observations in either are equal to zero (Jaggia and Kell[2]y). The t- statistic gives us information about a one-sided t-test conducted on the data. This also tests for the Ith observation being equal to zero.
From our fitted regression line, when the i^{th }weekly demand is zero, we can use this to determine the forecast for either year 2015 or 2016. Solving the quadratic equation will lead us to obtaining the forecast value for the following week for either year. The regression equation will therefore be:
Weekly demand= 391.450 + 0.132*2015 forecast – 0.028 *2016 forecast.
In determining the probability of the stock being under 40% we operate under the assumption that our data follows a normal distribution with mean µ and standard deviation 𝜎. Further the model that we will develop should be able to answer two major questions; will the inventory be replenished within the selling period and what will trigger the replenishment (Geoff and [3]Milner).
Thus, for a normal distribution with mean x and standard deviation 𝜎, then the distribution will be:
The normal distribution is symmetric, therefore the probability that demand will be less than the mean will be 50% while the probability that the demand will exceed the mean stock will also be 50%. The probability of stocking 40% less than the average is P(D> µ + 𝜎). Also, P( and P(D< µ)= 50%
Thus the probability that the stock will be under 40% of the average stock will be obtained by 100%- 90%= 10%.
References
Carlberg, Conrad George. Regression Analysis Microsoft Excel. Pearson Education, 2016. Print.
Relph, Geoff, and Catherine Milner. Inventory Management: Advanced Methods for Managing Inventory Within Business Systems. London: Kogan Page, 2015. Print.
Jaggia, Sanjiv, and Alison Kelly. Essentials Of Business Statistics. New York, NY: McGraw-Hill/Irwin, 2014. Print.
Albright, S C, Wayne L. Winston, Christopher J. Zappe, and S C. Albright. Data Analysis and Decision Making. Toronto, Ont: Nelson Education Ltd, 2009. Print.
Cramér, Harald. Random Variables and Probability Distributions. Cambridge: Univ. Pr, 2004.
[1] Carlberg, Conrad George. Regression Analysis Microsoft Excel. Pearson Education, 2016. Print.
[2] Jaggia, Sanjiv, and Alison Kelly. Essentials Of Business Statistics. New York, NY: McGraw-Hill/Irwin, 2014. Print.
[3] Relph, Geoff, and Catherine Milner. Inventory Management: Advanced Methods for Managing Inventory Within Business Systems. London: Kogan Page, 2015. Print.