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 in the excel sheet.
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? Horizontal pattern? Explain.
2. Based on the data patterns, apply all of the following possible forecasting models: linear trend forecast, trend and seasonal forecast with dummy variables, and moving average forecast with periods k=2 and k=4. Explain why these methods may or may not be used for forecasting in this case.
3. Utilize Excel to identify the best forecasting model among the four models discussed in question 2 based on MAE and MAPE. Create a table with forecast accuracy measures MAE and MAPE for each of the four forecasting models. Apply the best forecasting model to forecast inventory in all 4 quarters of 2020.
4. Answer the question from 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)