The excel file "Assignment3_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 submit the excel spreadsheet, including the answers to these questions, with your assignment:

(5 points) Report the mean, median, mode, range, standard deviation, and sample variance of the sales data.

(5 points) Find the regression output of 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.

(5 points) Using the regression model above, predict the sales trend. Explain.

(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. Please show all trend line for this section. To do this, follow the below procedure:

Perform the moving averages and central tendency method. (1 quarter will be 13 weeks)

Find the Seasonality and Irregularities

Find the Seasonality

Find the Deseasonality

Find the Trend

(20 points) Create the forecasted sales for the historic data and create future forecasted sales for the next 52 weeks. Show the forecasted sales trend line on the historic graph. Explain your forecasted sales and provide your recommendations.