CASE #$3$

INVENTORY FORECASTING

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 $2024.$ The materials specialist has accumulated historical inventory data for $2019-2023$ presented in $350\_$Case$\_3.$xlsx$.$

Use Microsoft Excel to answer all the questions below. Please upload one single Excel solution file on Canvas by the due date, with each question answered on a 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, $2019-2023).$ 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, separately implement each of the following four forecasting models: $($i$)$ linear trend forecast, $($ii$)$ trend and seasonal forecast with dummy variables, and moving average forecast with periods $($iii$)k=2$ and $($iv$)k=4.$ Explain why these