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 2022. The materials specialist has accumulated historical inventory data for 2017-2021 presented in 350_Case_3.xlsx.

Use Microsoft Excel to answer all the questions below. Please upload one single Excel solution file on Blackboard by the due date, with each question answered on separate sheet of the same Excel file. Submission of the Excel solution file via email is not acceptable.

Questions:

Identify possible data patterns in the historical data (inventory cases, 2017-2021). 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. (10 points)

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. (5 + 5 + 5 + 5 = 20 points)

Utilize Excel to identify the best forecasting model among the four models discussed in question 2 based on MAE and MAPE. Make 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 2022. (2 + 2 + 2 + 2 + 4 = 12 points)

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

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