(Solved): DistCo, a large warehouse service company in the San Francisco Bay Area, stores pharmaceutical prod ...

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. Show each step please. Questions: 1. 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. 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. 3. Utilize Excel to id entify 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 2024. 4. Answer the question from the case study: should DistCo acquire more warehouse capacity in 2024? (Hint: will the forecasted number of cases in the 4 th quarter of 2024 be close to or greater than 292,000-294,000 cases?) Simple Forecasts