As a student in BUS $373,$ you have been hired as an Operations Manager for Nester$$s market

at SFU campus. Your task is to determine the appropriate quantity of Pantene shampoo to order for Year

$2024.$ The store has provided you with historical yearly sales data for the product $($Question $2-$Assignment

$1.$xlsx$$ uploaded to $$Demand Management $($Forecasting$)$ module of Canvas$).$ Your job is to analyze the

data and make accurate forecasts for the next year by completing the following tasks:

$[$If you use Excel to solve this question, submit the excel file along with your solutions to the assignment.

Note that you need to explain your answers to the following questions in detail, and just submitting the

calculations done by excel is not enough.$]$

$($a$)$ Plot the historical annual sales data for Pantene shampoo.

$($b$)$ Analyze the data to determine if there is any trend or seasonality present. Explain your findings

and how they will inform your forecasting decisions.

$($c$)$ Evaluate Nester$$s current forecasting method, which uses the average sales from the last two

years to estimate sales for the next year.

$$ Use this method to estimate the demand of $2024.$

$$ Identify any drawbacks in the current method and explain why it may not be effective.

$($d$)$ Suggest an alternative forecasting method that would be more appropriate for estimating

Pantene shampoo sales. Justify your choice of model by evaluating it against their current

forecasting method discussed in part $($c$),$ using measures of error such as Mean Absolute Deviation

$($MAD$),$ and Mean Squared Error $($MSE$).$ Using this alternative method, estimate the demand of

$2024.$

$($e$)$ Now, assume that as an operations manager, you think that shampoo sales might be associated

with the annual inflation rate $($The inflation rates are included in $$price data$$ sheet of $$Question $2-$

Assignment $1.$xlsx$).$ Use a linear regression model to test this association. According to the

regression results, by each unit of inflation rate change, how much does the shampoo sale vary?

According to $$Statistica$.$com$$ website, the inflation rate of $2024$ is expected to be $2\mathrm{.}39\%$;

considering this rate and your regression model, what is the forecast for the demand of Pantene

shampoo in $2024?$ Compare the performance of this forecasting model to your suggested method

at part $($d$)$ using the measures of error such as Mean Absolute Deviation $($MAD$),$ and Mean Squared

Error $($MSE$).$

Year Sales $($Units of Shampoo$)$

$1987202$

$1988145$

$1989183$

$1990150$

$1991120$

$1992250$

$1993280$

$1994350$

$1995250$

$1996292$

$1997285$

$1998310$

$1999245$

$2000215$

$2001203$

$2002238$

$2003221$

$2004297$

$2005233$

$2006254$

$2007222$

$2008197$

$2009476$

$2010410$

$2011350$

$2012422$

$2013453$

$2014385$

$2015436$

$2016415$

$2017405$

$2018360$

$2019400$

$2020441$

$2021320$

$2022250$

$2023301$