Question $2$

Demand can never be static and easily predicted. At times, demand can be cyclical or seasonal. It's important for operations managers to at least understand the seasonal pattern and plan their supply and demand fulfillment strategy better. Seasonal adjustment plays a crucial role in official statistics, allowing for timely interpretation of time series data. Its primary purpose is to eliminate systematic calendar$-$related fluctuations linked to specific times of the year$-$what we commonly refer to as seasonal effects.

A fashion retailer has noticed there is a seasonal pattern in the fashion and implications to their sales and operations. The marketing department has collected monthly sales data over $5$ years as shown in table $1$ below. The marketing department manager wanted to understand the seasonal index and to predict the average monthly sales for $2024.$

$\backslash$table$[[,],[,2059,2000,2001,\frac{2028}{38},45],[$lan$,23,26,3,55,97],[$feb$,43,38,52,30,33],[$Mar$,19,38,20,24,26.],[$Apr$,13,319,29,20,35],[$May$,23,27,39,36,33],[,30,20,23,24,25],[$hat$,32,20,19,21,23],[$Ans$,14,19,19,23,28],[$Sep$,38,293,25,28,32],[$OCt$,20,29,32,35,42],[$Nor$,27,29,52,63,78],[$Dec$,46,3,,,]]$

a$)$ Construct a table that shows the total yearly demand over $5$ years.

$\backslash$table$[[$Year$,1,2,3,4,5],[$Total Demand,,,,,$]]$

b$)$ From the yearly demand $(5$ years$),$ construct a simple linear regression model. What is the slope and intercept? $($Hint: Use MS Excel Solver$)$

$\backslash$table$[[$Intercept$,],[$Slope$,]]$

c$)$ From the regression model, estimated the sales for sixth year $($i$.$e $2024).$ Use the slope and intercept $(y=mx+c)$ to estimate the sales forecast.

$\backslash$table$[[$Year $(2024),6],[$Forecast$,],[$Avd Mth Demand,$]]$

d$)$ Using a seasonal index, construct a table to indicates the monthly seasonal demand for $2024.$ Visualize and comment on the sales forecast.

$($Hint: The table should have parameter of seasonal demand, deseasonal average demand, seasonal index and Seasonal Index Adjusted Forecast $2024)$

$[50$ Marks$]$