DistCo, a large warehouse service company in the San Francisco Bay Area, stores pharmaceutical products for customers while they are in transit to local retailers. DistCo can store a maximum of 294,000 cases of products at its present facility. Because their business has been growing, the company’s management wonders if they should acquire other warehouses in 2020. The materials specialist has accumulated historical inventory data for 2015-2019 presented.

Questions

1. Identify possible data patterns in the historical data (inventory cases, 2015-2019). Draw the data plot using Excel. That will help you to see the possible data patterns. Does the historical data have a seasonal pattern? Trend pattern? Stationary pattern? Explain.

2. Based on the data patterns, select the following alternative forecasting methods: linear trend forecast, trend and seasonal forecast with binary variables, and moving average forecast with n=2 and n=4. Explain why these methods may be used for forecasting in this case.

3. Utilize Excel to identify the best forecast based on MAE and MAPE. Create a table with forecasting results. Apply the best forecast to estimate inventory in 4 quarters of 2020.

4. Answer the question of the case study: should DistCo acquire more warehouse capacity in 2020 (Hint: will the forecasted number of cases in the 4th quarter of 2020 be close to or greater than 292,000-294,000 cases)?

Do all your analysis and answers to the questions in Excel.

Year	Quarter	Period	Inventory (thousand of cases)
2015	Q1	1	176
	Q2	2	134
	Q3	3	186
	Q4	4	195
2016	Q1	5	189
	Q2	6	157
	Q3	7	195
	Q4	8	211
2017	Q1	9	205
	Q2	10	180
	Q3	11	212
	Q4	12	229
2018	Q1	13	223
	Q2	14	192
	Q3	15	234
	Q4	16	248
2019	Q1	17	239
	Q2	18	217
	Q3	19	271
	Q4	20	284

Answer rating: 100% (QA)

MA3 MA4 and Linear trend forecast Forecast Year Quarter Period Inventory MA3 MA4 Trend 2014 Q1 1 236 2306 Q2 2 204 2344 Q3 3 256 2382 Q4 4 265 232 242 ... View the full answer