Problem 3 [25 points]: [You must use Excel]: Florida Dairies…
Problem 3 : : Florida Dairies, Inc. supplies milk to several retailers throughout the state of Florida. Demand data for its Branded Milk (in million gallons) for the previous 12 months is as follows: Month Demand (million gallons) 1 2.80 2 2.90 3 3.05 4 3.30 5 3.10 6 2.95 7 3.00 8 3.20 9 3.15 10 2.75 11 3.10 12 3.25 Note: You should be able to copy-paste the above table into an Excel worksheet. If you have any issues, type the numbers into the sheet yourself and perform the analysis. Use proper tables (templates) that I have demonstrated in my recordings as I taught you while teaching the topic. a) Enter the data into Excel. Plot the time series. What pattern do you observe in the historical data? b) Use the following three methods and prepare three forecasting tables with errors for the given demand data . 3-month Moving Average (3m-MA) method; Exponential Smoothing method with smoothing constant = 0.4; 2-month Moving Average (2m-WMA) method with the weights 0.7 and 0.3 (with the higher weight going to the most recent data point); c) Calculate the demand for month 13 for all the three methods. d) Calculate Forecast Accuracy Measures – MAD (or MAE), MSE, and MAPE. e) Recommend the best method among the above three for implementation. What was your basis for such a recommendation? f) Plot the time series with Demand and Forecasts under the above three methods tried? Make a statement on what you observe from the Demand time series plot with the three forecasts.