Can you please help me with the 2 questions at the end of this case study please
ID: 399620 • Letter: C
Question
Can you please help me with the 2 questions at the end of this case study please?
Exerci manufacturers cannot economically provide warehouse. Plans are to shift some of the sl ises E large box this personal service to his accounts. s are to shift some of the slower-moving boxes to the leased space Bob recognizes the danger of becoming too ent upon any one client and has enforced the pol There has always been an increase in demand for boxes just before the holiday season, when customers begin that no single customer can account for over 20 per- stocking up. This seasonality in demand has always substan- icy E cent of sales. Two of the distilleries account for 20 tially increased the difficulty of making a reliable forecast. E percent of sales each, and hence are limited in their pur- Bob feels that it is now important to develop an chases. Bob has persuaded the purchasing agents of improved forecasting method to help smooth production these two companies to add other suppliers, since this and warehousing volume. He has compiled the following E alternative supply protects them against problems demand data. E Bardstown might have such as shipping delays, paper Sales (in number of boxes) shortages, or labor problems. E 20X1 20X2 20X3 20X4 20X5 12,000 8,000 12,000 15,000 15,000 February 8,000 14,000 8,000 12,000 22,000 10,000 18,000 18,000 14,000 18,000 18,000 15,000 13,000 18,000 18,000 14,000 16,000 14,000 15,000 16,000 10,000 18,000 18,000 18,000 20,000 16,000 14,000 17,000 20,000 28,000 18,000 28,000 20,000 22,000 28,000 Such personal service, however, requires tight September 20,000 22,000 25,000 26,000 20,.000 Bardstown currently has over 600 customers with orders ranging from a low of 100 boxes to blanket orders for 50,000 boxes per year. Boxes are produced in January 16 standard sizes with special printing to customers March E specifications. Bardstown's printing equipment limits Apil E the print to two colors. The standardization and limited May printing allow Bardstown to be price-competitive with big producers while also providing service for small turers cannot provide orders and "emergency" orders that large box manufac- y August 27,000 27,000 28,000 28,000 30,000 November 24,000 26,000 18,000 20,000 22,000 December 18,000 10,000 18,000 22,000 28,000 Inventory control and close production scheduling. So October Bob has always forecast demand and prepared pro- on schedules on the basis of experience, but because ever-growing number of accounts and changes in onnel in customer purchasing departments, the accu y of his forecasts has been rapidly declining. The Questions ra 1. Develop a forecasting method for Bardstown and fore- cast the total demand for 20x6 2. Should Bob's experience with the market be factored ckorders is on the increase, late orders are com also mon, and inventory levels of finished boxes are increase. A seco nd warehouse has recently ause of the overcrowding in the main into the forecast? If so, how? trial and error to find the value of a that minimizes the MAD. Restrict the value of a to be between 0.05 and 0.30. Consider the following data for a children's toy: 5.1 period moving average. Then calculate and plot a five- eriod moving average. What do you observe in comparing 10 ot the following data and then calculate and plot a three- 5.4 Referring to the data in exercise I, use Excel's Solver or 5.5 of the three- and five-period moving averages? A S o Month JF M AM1Explanation / Answer
1. For the new forecasting method, seasonality and trend components of the demand will be considered together.
Trend component will be used to calculate total demand, and the seasonal component will provide the monthly demands.
We use linear regression to forecast the demand in form
Y=a+bX, where
a= demand for year X1,
b= multiplier of increase in yearly demand, and
X=the year after X1.
Using Values for X1 and X5, we get,
Y=195+17.5*X
Demand for year X6 will be 195 + 17.5*(5)=282.5
To incorporate seasonality, we calculate the seasonal indices by dividing the monthly averages by the average of monthly averages.
A (Jan)=12.4, A(all months)=18.58, Jan seasonal factor=12.4/18.58=0.667
And then the monthly demand is calculated by formula
D(Jan)=(0.667*282.5)/12
Please note that though the method for the total annual demand differs, seasonal index will be required for calculating the monthly demands by incorporating the seasonality.
2. Yes, Bob's experience should be incorporated in the forecast, but, it should be along with the inputs from the people who handle various customer accounts, and know about the industry as well as the product in focus. Reaching a consensus on which of the methods to use to calculate the total value of the forecasted demand for the following years will be the best and most efficient application of Bob's experience.
Year-> X1 X2 X3 X4 X5 X6 MonthlyAverage Seasonal Factor January 12 8 12 15 15 15.70852 12.4 0.667265 February 8 14 8 12 22 16.21525 12.8 0.688789 March 10 18 18 14 18 19.76233 15.6 0.839462 April 18 15 13 18 18 20.77578 16.4 0.882511 May 14 16 14 15 16 19.00224 15 0.807175 June 10 18 18 18 20 21.28251 16.8 0.904036 July 16 14 17 20 28 24.06951 19 1.022422 August 18 28 20 22 28 29.39013 23.2 1.24843 September 20 22 25 26 20 28.63004 22.6 1.216143 October 27 27 28 28 30 35.47085 28 1.506726 November 24 26 18 20 22 27.86996 22 1.183857 December 18 10 18 22 28 24.32287 19.2 1.033184 Total 195 216 209 230 265 282.5