Question $3$ Demand Estimation and Forecasting $(30$ marks$)$

The file Comm$172$ Assignment$1$ Data $2024.$xlsx contains store sales data for Heineken beer. The data for this question are in the Question $3$ sheet. The variable Qc is the total unit sales of Heineken beer $($six$-$packs, in cans, $72$oz$)$ across many U$.$S$.$ stores. The data cover $52$ weeks from January $1,2001$ to December $24,2001.$ You must forecast future sales. Please include with your assignment the printout of your excel file showing your answers to these questions:

a$)(10$ points$)$ Plot the sales data $($Qc$)$ over time and fit a linear trend line to these data. On the same chart, extend your trend line for $52$ weeks ahead and display the regression equation for the trend line.

b$)(8$ points$)$ Using the estimated regression coefficients, forecast your sales for $52$ weeks ahead. Report your results $($for each week from $53$ to $104)$ in a new column titled $$Trend forecast.$$

c$)(12$ points$)$ The forecasting error could be large because the forecast above is not adjusted for seasonal fluctuations $($notice that sales are high in summer$).$ Adjust for seasonal fluctuations using the ratio$-$to$-$trend method.

To do that, proceed as follows:

i$)$ Using the estimated regression coefficients, predict sales $($Qc$)$ for all past weeks in the data $($from $1$ to $52).$

ii$)$ Compute the ratio of actual to predicted sales for all past weeks.

iii$)$ Compute average ratio by quarters $($the first $13$ weeks comprise the first quarter$).$ This is the ratio$-$to$-$trend adjustment. Report these results in a new table, with one data entry for each quarter $($i$.$e$,$ four data entries in total$).$

iv$)$ Multiply the forecasted values for $52$weeks ahead $($from the column $$Trend forecast$)$ by a corresponding quarterly ratio to obtain the adjusted forecast. Report your results in a new column titled $$Adjusted forecast,$$ next to the column $$Trend forecast.$$

v$)$ Display the adjusted forecast on the chart with sales data over time