Q1. The excel file "Assignment2_Data.xlsx" includes beer retail sales data (Qc) over 52 weeks from January 1, 2015, to December 24, 2015. The variable Qc represents the total unit sales of the beer (six-packs, in cans, 72oz) across a large number of U.S. retailers. You must forecast the future sales. Please send the printout of your excel spreadsheet, including the answers to these questions, with your assignment: 1. (5 points) Report the mean, median, mode, range, standard deviation, and sample variance of the sales data. 2. (5 points) Plot the sales data over time and fit a linear trend line to these data and display the regression equation and the R2 for the trend line. 3. (5 points) Using the regression model above, predict the sales for the next period (next 52 weeks). Report the sale in a new column as "Trend forecast". 4. (25 points) Due to the fact that the above prediction does not account for seasonal fluctuations, the forecasting error may be significant (notice that sales are high in summer). Using the ratio-to-trend method, adjust seasonal fluctuations. Follow the below procedure: I. Predict the sales for the past weeks (Week 1 to 52) using the trend line in part 1. II. Compute the actual to predicted sales ratio for all past weeks by dividing the given Qc by the predicted values for the same week. Report the ratio in a new column as "Differential ratio". III. Calculate the quarterly average differential ratio (the first 13 weeks comprise the first quarter). This is the ratio-to-trend adjustment. Include one data entry for each quarter in a new column with these findings (i.e., four data entries in total). IV. To get the adjusted forecast, multiply the trend forecast for the first and the second 52 weeks by a corresponding quarterly ratio. Report your results in a new column as "Adjusted trend forecast". V. Plot the adjusted trend forecast on the chart over time.