Load required libraries

library(readxl) library(dplyr) library(ggplot2) library(forecast) library(lubridate) library(tidyr)

Read the data from the Excel file (Long Data sheet)

data <- read_excel(“Jia Wu Data.xlsx”, sheet = “Long Data”)

Convert Excel serial date to actual date (Excel origin for Windows)

data\(Date <- as.Date(data\)Date, origin = “1899-12-30”)

Create a time series object

ts_sales <- ts(data$Sales, start = c(1995, 1), frequency = 12)

Perform multiplicative decomposition to get trend and seasonal components

decomp <- decompose(ts_sales, type = “multiplicative”)

Extract trend and add to data frame

data\(Trend <- as.vector(decomp\)trend)

Calculate average seasonal factors by month

First, get the seasonal component

seasonal <- decomp$seasonal

Create a month factor

months <- rep(1:12, length.out = length(seasonal)) seasonal_df <- data.frame(Month = months, Seasonal = seasonal)

Average seasonal factors per month

avg_seasonal <- seasonal_df %>% group_by(Month) %>% summarise(Seasonal_Factor = mean(Seasonal, na.rm = TRUE)) %>% mutate(Month_Name = month.name[Month])

Fit SARIMA model: SARIMA(1,1,1)(1,1,1)[12]

sarima_model <- Arima(ts_sales, order = c(1, 1, 1), seasonal = c(1, 1, 1))

Forecast for 2010 (12 months)

fc <- forecast(sarima_model, h = 12, level = 95)

Create forecast data frame

fc_dates <- seq.Date(from = as.Date(“2010-01-01”), by = “month”, length.out = 12) fc_df <- data.frame( Date = fc_dates, Forecast = fc\(mean, Lower = fc\)lower, Upper = fc$upper )

Visualization 1: Current Trend in Monthly Sales

p_trend <- ggplot(data, aes(x = Date, y = Sales)) + geom_line(color = “blue”, size = 0.5) + geom_line(aes(y = Trend), color = “red”, size = 1) + labs(title = “Monthly Household Goods Sales with Trend (1995-2009)”, x = “Date”, y = “Sales (millions AUD)”) + theme_minimal() print(p_trend)

Visualization 2: Seasonal Patterns (Bar Plot of Average Seasonal Factors)

p_seasonal <- ggplot(avg_seasonal, aes(x = Month_Name, y = Seasonal_Factor)) + geom_bar(stat = “identity”, fill = “steelblue”) + labs(title = “Average Seasonal Factors by Month”, x = “Month”, y = “Seasonal Factor”) + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1)) print(p_seasonal)

Visualization 3: Monthly Forecast for 2010 with Confidence Intervals

p_forecast <- ggplot(fc_df, aes(x = Date, y = Forecast)) + geom_line(color = “green”, size = 1) + geom_ribbon(aes(ymin = Lower, ymax = Upper), fill = “lightgreen”, alpha = 0.3) + labs(title = “Forecasted Monthly Sales for 2010 with 95% CI”, x = “Date”, y = “Forecasted Sales (millions AUD)”) + theme_minimal() print(p_forecast)

Optional: Save plots to files

ggsave(“trend_plot.png”, p_trend, width = 10, height = 6) ggsave(“seasonal_plot.png”, p_seasonal, width = 8, height = 5) ggsave(“forecast_plot.png”, p_forecast, width = 10, height = 6)