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 2023. The materials specialist has accumulated historical inventory data for 2018-2022 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: 1. Identify possible data patterns in the historical data (inventory cases, 2018-2022). 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) 2. 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 methods may or may not be used for forecasting in this case. (5 + 5 + 5 + 5 = 20 points) 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 2023. (2 + 2 + 2 + 2 + 4 = 12 points) 4. Answer the question from the case study: should DistCo acquire more warehouse capacity in 2023? (Hint: will the forecasted number of cases in the 4 th quarter of 2023 be close to or greater than 292,000-294,000 cases?) (8 points)

Case #3 * B & EB Share S File Edit View Insert Format Data Tools Extensions Help Q 5 2 5 5 100% $ % .0 123 Arial 8 + B T A A1 fx Year A B C D E F G H I J K L Year Quarter Period Inventory (thousand of cases) 2018 Q1 1 176 W N Q2 2 134 Q3 3 186 Q4 A 195 2019 Q1 5 189 Q2 6 157 Q3 195 Q4 8 211 10 2020 Q1 9 205 11 Q2 10 180 12 Q3 11 212 13 Q4 12 229 14 2021 Q1 13 223 15 Q2 14 192 16 Q3 15 234 17 Q4 16 248 18 2022 Q1 17 239 19 Q2 18 217 20 Q3 19 271 21 20 284 22 23 24 25 26 27 28 + Sheet1