Please answer in an Excel File because that is the requirement. And please show all the work in an Excel file so all the formulas used to get the answer NOT just the answer. $($Every piece of data must come from a formula to obtain the grade$).$ Thank you.

Question $1$

Ray & Sons Water Depot sells pallets of water jugs. Monthly sales for the last $7$ months of $2023$ were as

follows:

$1\mathrm{.}1.$ Forecast the sales volume for January $2024$ using each of the following methods and round each

result to whole numbers:

$1\mathrm{.}1\mathrm{.}1.$ Na$$ve method considering both Nov. and Dec. data.

$1\mathrm{.}1\mathrm{.}2.$ A five$-$month simple moving average.

$1\mathrm{.}1\mathrm{.}3.$ A weighted average using $0\mathrm{.}60$ for Dec., $0\mathrm{.}30$ for Nov. and $\mathrm{.}10$ for Oct.

$1\mathrm{.}1\mathrm{.}4.$ Exponential smoothing using a smoothing constant equal to $0\mathrm{.}30$ and a monthly forecast of

Question $2$

A local traffic monitoring agency received data on speeding tickets for the past few months, as well as

two sets of forecasts made by their analysts. Analyzing this data is important to understand driver

behaviour. See table below.

$2\mathrm{.}1.$ Calculate the MAD for forecasts $1$ and $2.$ Show value on $2$ decimal points.

$2\mathrm{.}2.$ Calculate the MSE for forecasts $1$ and $2.$ Show value on $2$ decimal points.

$2\mathrm{.}3.$ Calculate the MAPE for forecasts $1$ and $2.$ Show value on $2$ decimal points.

$2\mathrm{.}4.$ Based on your forecast error analyses, which of the two forecasts is the most accurate and why?

Question $3$

As spring approaches, John and Mary need to forecast the number of guests they will have in their trailer park. John's estimate is that in the new year they will have $900$ guests. In addition, below is the number of guests they have had over the last $3$ years.

Month $202120222023$ January $757874$ February $120123128$ March $125130134$ April $110113126$ May $646676$ June $825250$ July $526567$ August $905684$ September $607552$ October $515386$ November $647150$ December $798650$

$3\mathrm{.}1.$ Based on the data above, calculate the seasonal index for each month. Show results using $2$ decimal points.

$3\mathrm{.}2.$ Based on the data above and the seasonal index on question $3\mathrm{.}1$ above, calculate the seasonal volume by month for the new fiscal year. Show results using whole numbers.

$3\mathrm{.}3.$ Identify $3$ operational decisions that may be impacted by this forecast.