Forecasting Final Project: Predicting Grocery Store Sales

Data Preparation

library(forecast)
library(zoo)
library(readxl)
library(dplyr)
library(purrr)
library(tidyr)
library(ggplot2)
library(lubridate)
library(knitr)

file <- "mrtssales92-present.xlsx"

Importing the data file

sheets <- excel_sheets(file)
sheets <- sheets[sheets != "2025"]

template <- read_excel(file, sheet = "2024", skip = 0)
standard_cols <- names(template)

read_and_align <- function(sheet_name) {
  df <- read_excel(file, sheet = sheet_name, skip = 0)
  
  missing_cols <- setdiff(standard_cols, names(df))
  for (col in missing_cols) {
    df[[col]] <- NA
  }
  
  df <- df[, standard_cols]
  df$Year <- sheet_name
  return(df)
}

all_data <- map_dfr(sheets, read_and_align)

Adjusting the data set to use all of the sheets in the excel file (years)

colnames(all_data)
##  [1] "Estimates of Monthly Retail and Food Services Sales by Kind of Business: 2024"
##  [2] "...2"                                                                         
##  [3] "...3"                                                                         
##  [4] "...4"                                                                         
##  [5] "...5"                                                                         
##  [6] "...6"                                                                         
##  [7] "...7"                                                                         
##  [8] "...8"                                                                         
##  [9] "...9"                                                                         
## [10] "...10"                                                                        
## [11] "...11"                                                                        
## [12] "...12"                                                                        
## [13] "...13"                                                                        
## [14] "...14"                                                                        
## [15] "...15"                                                                        
## [16] "Year"
all_data <- all_data %>% select(-`Estimates of Monthly Retail and Food Services Sales by Kind of Business: 2024`)

Removed unnecessary column

colnames(all_data)[1] <- "BusinessType"
colnames(all_data)[2] <- "January"
colnames(all_data)[3] <- "February"
colnames(all_data)[4] <- "March"
colnames(all_data)[5] <- "April"
colnames(all_data)[6] <- "May"
colnames(all_data)[7] <- "June"
colnames(all_data)[8] <- "July"
colnames(all_data)[9] <- "August"
colnames(all_data)[10] <- "September"
colnames(all_data)[11] <- "October"
colnames(all_data)[12] <- "November"
colnames(all_data)[13] <- "December"
colnames(all_data)[14] <- "TotalSales"

Renaming columns for clarity

all_data <- all_data %>% 
  filter(!is.na(January), !is.na(BusinessType))

Removing rows that contain entirely NA Values

keep_types <- c("Grocery stores")

monthly_data <- all_data %>%
  filter(BusinessType %in% keep_types)

Removed the rows of all the business types grocery stores. This business type was chosen due to it being an essential industry that would have less noise from external disruptions (such as the COVID-19 pandemic) compared to other industries within the data,

monthly_data <- monthly_data %>%
  pivot_longer(cols = January:December, 
               names_to = "Month", 
               values_to = "MonthlySales")


monthly_data <- monthly_data %>%
  mutate(Month = match(Month, month.name), 
         Date = make_date(Year, Month, 1)) 

monthly_data$MonthlySales <- gsub(",", "", monthly_data$MonthlySales)
monthly_data$MonthlySales <- as.numeric(monthly_data$MonthlySales)

monthly_data <- monthly_data %>%
  arrange(Year, Month) %>%  
  mutate(MonthlySales = as.numeric(MonthlySales))

Pivoted the data for easier plotting

grocery_data <- monthly_data %>%
  filter(BusinessType == "Grocery stores", !is.na(TotalSales)) %>%
  arrange(Date)

Removed NA rows

BusinessType TotalSales Year Month MonthlySales Date
Grocery stores 332595 1992 1 26916 1992-01-01
Grocery stores 332595 1992 2 25848 1992-02-01
Grocery stores 332595 1992 3 26846 1992-03-01
Grocery stores 332595 1992 4 27196 1992-04-01
Grocery stores 332595 1992 5 28472 1992-05-01
Grocery stores 332595 1992 6 27641 1992-06-01
Grocery stores 332595 1992 7 29062 1992-07-01
Grocery stores 332595 1992 8 28240 1992-08-01
Grocery stores 332595 1992 9 27088 1992-09-01
Grocery stores 332595 1992 10 28181 1992-10-01
Grocery stores 332595 1992 11 27235 1992-11-01
Grocery stores 332595 1992 12 29870 1992-12-01
Grocery stores 336704 1993 1 27191 1993-01-01
Grocery stores 336704 1993 2 25395 1993-02-01
Grocery stores 336704 1993 3 27528 1993-03-01
Grocery stores 336704 1993 4 27729 1993-04-01
Grocery stores 336704 1993 5 28701 1993-05-01
Grocery stores 336704 1993 6 28189 1993-06-01
Grocery stores 336704 1993 7 29662 1993-07-01
Grocery stores 336704 1993 8 28088 1993-08-01
Grocery stores 336704 1993 9 27646 1993-09-01
Grocery stores 336704 1993 10 28150 1993-10-01
Grocery stores 336704 1993 11 27706 1993-11-01
Grocery stores 336704 1993 12 30719 1993-12-01
Grocery stores 345938 1994 1 27519 1994-01-01
Grocery stores 345938 1994 2 25829 1994-02-01
Grocery stores 345938 1994 3 28874 1994-03-01
Grocery stores 345938 1994 4 28152 1994-04-01
Grocery stores 345938 1994 5 29015 1994-05-01
Grocery stores 345938 1994 6 29332 1994-06-01
Grocery stores 345938 1994 7 29947 1994-07-01
Grocery stores 345938 1994 8 29403 1994-08-01
Grocery stores 345938 1994 9 28724 1994-09-01
Grocery stores 345938 1994 10 28703 1994-10-01
Grocery stores 345938 1994 11 28702 1994-11-01
Grocery stores 345938 1994 12 31738 1994-12-01
Grocery stores 351910 1995 1 28263 1995-01-01
Grocery stores 351910 1995 2 26422 1995-02-01
Grocery stores 351910 1995 3 29331 1995-03-01
Grocery stores 351910 1995 4 28718 1995-04-01
Grocery stores 351910 1995 5 30004 1995-05-01
Grocery stores 351910 1995 6 29848 1995-06-01
Grocery stores 351910 1995 7 30269 1995-07-01
Grocery stores 351910 1995 8 30052 1995-08-01
Grocery stores 351910 1995 9 29009 1995-09-01
Grocery stores 351910 1995 10 28702 1995-10-01
Grocery stores 351910 1995 11 29247 1995-11-01
Grocery stores 351910 1995 12 32045 1995-12-01
Grocery stores 361010 1996 1 28805 1996-01-01
Grocery stores 361010 1996 2 27837 1996-02-01
Grocery stores 361010 1996 3 29865 1996-03-01
Grocery stores 361010 1996 4 28976 1996-04-01
Grocery stores 361010 1996 5 30971 1996-05-01
Grocery stores 361010 1996 6 30089 1996-06-01
Grocery stores 361010 1996 7 30986 1996-07-01
Grocery stores 361010 1996 8 31353 1996-08-01
Grocery stores 361010 1996 9 29132 1996-09-01
Grocery stores 361010 1996 10 30298 1996-10-01
Grocery stores 361010 1996 11 30636 1996-11-01
Grocery stores 361010 1996 12 32062 1996-12-01
Grocery stores 368251 1997 1 30169 1997-01-01
Grocery stores 368251 1997 2 27653 1997-02-01
Grocery stores 368251 1997 3 31140 1997-03-01
Grocery stores 368251 1997 4 29503 1997-04-01
Grocery stores 368251 1997 5 32034 1997-05-01
Grocery stores 368251 1997 6 30173 1997-06-01
Grocery stores 368251 1997 7 31860 1997-07-01
Grocery stores 368251 1997 8 31664 1997-08-01
Grocery stores 368251 1997 9 29797 1997-09-01
Grocery stores 368251 1997 10 30962 1997-10-01
Grocery stores 368251 1997 11 30680 1997-11-01
Grocery stores 368251 1997 12 32616 1997-12-01
Grocery stores 373968 1998 1 30329 1998-01-01
Grocery stores 373968 1998 2 27630 1998-02-01
Grocery stores 373968 1998 3 30155 1998-03-01
Grocery stores 373968 1998 4 30477 1998-04-01
Grocery stores 373968 1998 5 32091 1998-05-01
Grocery stores 373968 1998 6 30864 1998-06-01
Grocery stores 373968 1998 7 32698 1998-07-01
Grocery stores 373968 1998 8 31862 1998-08-01
Grocery stores 373968 1998 9 30662 1998-09-01
Grocery stores 373968 1998 10 31860 1998-10-01
Grocery stores 373968 1998 11 31034 1998-11-01
Grocery stores 373968 1998 12 34306 1998-12-01
Grocery stores 390127 1999 1 31178 1999-01-01
Grocery stores 390127 1999 2 29101 1999-02-01
Grocery stores 390127 1999 3 32107 1999-03-01
Grocery stores 390127 1999 4 31614 1999-04-01
Grocery stores 390127 1999 5 33375 1999-05-01
Grocery stores 390127 1999 6 32230 1999-06-01
Grocery stores 390127 1999 7 34209 1999-07-01
Grocery stores 390127 1999 8 32602 1999-08-01
Grocery stores 390127 1999 9 32286 1999-09-01
Grocery stores 390127 1999 10 32593 1999-10-01
Grocery stores 390127 1999 11 32172 1999-11-01
Grocery stores 390127 1999 12 36660 1999-12-01
Grocery stores 398380 2000 1 31008 2000-01-01
Grocery stores 398380 2000 2 30195 2000-02-01
Grocery stores 398380 2000 3 32867 2000-03-01
Grocery stores 398380 2000 4 32658 2000-04-01
Grocery stores 398380 2000 5 33728 2000-05-01
Grocery stores 398380 2000 6 33527 2000-06-01
Grocery stores 398380 2000 7 34158 2000-07-01
Grocery stores 398380 2000 8 33874 2000-08-01
Grocery stores 398380 2000 9 32985 2000-09-01
Grocery stores 398380 2000 10 32858 2000-10-01
Grocery stores 398380 2000 11 33633 2000-11-01
Grocery stores 398380 2000 12 36889 2000-12-01
Grocery stores 414006 2001 1 32920 2001-01-01
Grocery stores 414006 2001 2 31183 2001-02-01
Grocery stores 414006 2001 3 34514 2001-03-01
Grocery stores 414006 2001 4 33349 2001-04-01
Grocery stores 414006 2001 5 35707 2001-05-01
Grocery stores 414006 2001 6 34859 2001-06-01
Grocery stores 414006 2001 7 35055 2001-07-01
Grocery stores 414006 2001 8 35553 2001-08-01
Grocery stores 414006 2001 9 34038 2001-09-01
Grocery stores 414006 2001 10 34504 2001-10-01
Grocery stores 414006 2001 11 34968 2001-11-01
Grocery stores 414006 2001 12 37356 2001-12-01
Grocery stores 415614 2002 1 34202 2002-01-01
Grocery stores 415614 2002 2 31711 2002-02-01
Grocery stores 415614 2002 3 35514 2002-03-01
Grocery stores 415614 2002 4 32854 2002-04-01
Grocery stores 415614 2002 5 36111 2002-05-01
Grocery stores 415614 2002 6 34573 2002-06-01
Grocery stores 415614 2002 7 35516 2002-07-01
Grocery stores 415614 2002 8 35662 2002-08-01
Grocery stores 415614 2002 9 33263 2002-09-01
Grocery stores 415614 2002 10 34531 2002-10-01
Grocery stores 415614 2002 11 35299 2002-11-01
Grocery stores 415614 2002 12 36378 2002-12-01
Grocery stores 423815 2003 1 35039 2003-01-01
Grocery stores 423815 2003 2 32122 2003-02-01
Grocery stores 423815 2003 3 34916 2003-03-01
Grocery stores 423815 2003 4 34514 2003-04-01
Grocery stores 423815 2003 5 36695 2003-05-01
Grocery stores 423815 2003 6 34861 2003-06-01
Grocery stores 423815 2003 7 36737 2003-07-01
Grocery stores 423815 2003 8 36280 2003-08-01
Grocery stores 423815 2003 9 34354 2003-09-01
Grocery stores 423815 2003 10 35520 2003-10-01
Grocery stores 423815 2003 11 35454 2003-11-01
Grocery stores 423815 2003 12 37323 2003-12-01
Grocery stores 437033 2004 1 36141 2004-01-01
Grocery stores 437033 2004 2 33389 2004-02-01
Grocery stores 437033 2004 3 35556 2004-03-01
Grocery stores 437033 2004 4 35542 2004-04-01
Grocery stores 437033 2004 5 37378 2004-05-01
Grocery stores 437033 2004 6 36014 2004-06-01
Grocery stores 437033 2004 7 37806 2004-07-01
Grocery stores 437033 2004 8 36257 2004-08-01
Grocery stores 437033 2004 9 36018 2004-09-01
Grocery stores 437033 2004 10 36656 2004-10-01
Grocery stores 437033 2004 11 36547 2004-11-01
Grocery stores 437033 2004 12 39729 2004-12-01
Grocery stores 453608 2005 1 36849 2005-01-01
Grocery stores 453608 2005 2 33982 2005-02-01
Grocery stores 453608 2005 3 37869 2005-03-01
Grocery stores 453608 2005 4 36574 2005-04-01
Grocery stores 453608 2005 5 38392 2005-05-01
Grocery stores 453608 2005 6 37876 2005-06-01
Grocery stores 453608 2005 7 38934 2005-07-01
Grocery stores 453608 2005 8 38276 2005-08-01
Grocery stores 453608 2005 9 37498 2005-09-01
Grocery stores 453608 2005 10 37910 2005-10-01
Grocery stores 453608 2005 11 38030 2005-11-01
Grocery stores 453608 2005 12 41418 2005-12-01
Grocery stores 467702 2006 1 37177 2006-01-01
Grocery stores 467702 2006 2 35210 2006-02-01
Grocery stores 467702 2006 3 38502 2006-03-01
Grocery stores 467702 2006 4 37940 2006-04-01
Grocery stores 467702 2006 5 39992 2006-05-01
Grocery stores 467702 2006 6 39226 2006-06-01
Grocery stores 467702 2006 7 40008 2006-07-01
Grocery stores 467702 2006 8 39989 2006-08-01
Grocery stores 467702 2006 9 38434 2006-09-01
Grocery stores 467702 2006 10 38988 2006-10-01
Grocery stores 467702 2006 11 39658 2006-11-01
Grocery stores 467702 2006 12 42578 2006-12-01
Grocery stores 487107 2007 1 39317 2007-01-01
Grocery stores 487107 2007 2 36836 2007-02-01
Grocery stores 487107 2007 3 40464 2007-03-01
Grocery stores 487107 2007 4 38898 2007-04-01
Grocery stores 487107 2007 5 41832 2007-05-01
Grocery stores 487107 2007 6 40912 2007-06-01
Grocery stores 487107 2007 7 41248 2007-07-01
Grocery stores 487107 2007 8 41522 2007-08-01
Grocery stores 487107 2007 9 39838 2007-09-01
Grocery stores 487107 2007 10 40568 2007-10-01
Grocery stores 487107 2007 11 41559 2007-11-01
Grocery stores 487107 2007 12 44113 2007-12-01
Grocery stores 507170 2008 1 41527 2008-01-01
Grocery stores 507170 2008 2 39593 2008-02-01
Grocery stores 507170 2008 3 42248 2008-03-01
Grocery stores 507170 2008 4 40635 2008-04-01
Grocery stores 507170 2008 5 44191 2008-05-01
Grocery stores 507170 2008 6 41955 2008-06-01
Grocery stores 507170 2008 7 43701 2008-07-01
Grocery stores 507170 2008 8 43657 2008-08-01
Grocery stores 507170 2008 9 41046 2008-09-01
Grocery stores 507170 2008 10 42482 2008-10-01
Grocery stores 507170 2008 11 42431 2008-11-01
Grocery stores 507170 2008 12 43704 2008-12-01
Grocery stores 505961 2009 1 42623 2009-01-01
Grocery stores 505961 2009 2 38221 2009-02-01
Grocery stores 505961 2009 3 40984 2009-03-01
Grocery stores 505961 2009 4 41449 2009-04-01
Grocery stores 505961 2009 5 43681 2009-05-01
Grocery stores 505961 2009 6 41798 2009-06-01
Grocery stores 505961 2009 7 43479 2009-07-01
Grocery stores 505961 2009 8 42642 2009-08-01
Grocery stores 505961 2009 9 41199 2009-09-01
Grocery stores 505961 2009 10 42674 2009-10-01
Grocery stores 505961 2009 11 42169 2009-11-01
Grocery stores 505961 2009 12 45042 2009-12-01
Grocery stores 516776 2010 1 42647 2010-01-01
Grocery stores 516776 2010 2 39751 2010-02-01
Grocery stores 516776 2010 3 43092 2010-03-01
Grocery stores 516776 2010 4 41902 2010-04-01
Grocery stores 516776 2010 5 44262 2010-05-01
Grocery stores 516776 2010 6 42544 2010-06-01
Grocery stores 516776 2010 7 44220 2010-07-01
Grocery stores 516776 2010 8 43092 2010-08-01
Grocery stores 516776 2010 9 42318 2010-09-01
Grocery stores 516776 2010 10 43210 2010-10-01
Grocery stores 516776 2010 11 43410 2010-11-01
Grocery stores 516776 2010 12 46328 2010-12-01
Grocery stores 543585 2011 1 43974 2011-01-01
Grocery stores 543585 2011 2 40939 2011-02-01
Grocery stores 543585 2011 3 44748 2011-03-01
Grocery stores 543585 2011 4 45257 2011-04-01
Grocery stores 543585 2011 5 45980 2011-05-01
Grocery stores 543585 2011 6 45547 2011-06-01
Grocery stores 543585 2011 7 46826 2011-07-01
Grocery stores 543585 2011 8 46111 2011-08-01
Grocery stores 543585 2011 9 44557 2011-09-01
Grocery stores 543585 2011 10 45405 2011-10-01
Grocery stores 543585 2011 11 45729 2011-11-01
Grocery stores 543585 2011 12 48512 2011-12-01
Grocery stores 559838 2012 1 45145 2012-01-01
Grocery stores 559838 2012 2 43860 2012-02-01
Grocery stores 559838 2012 3 47061 2012-03-01
Grocery stores 559838 2012 4 45425 2012-04-01
Grocery stores 559838 2012 5 48072 2012-05-01
Grocery stores 559838 2012 6 46880 2012-06-01
Grocery stores 559838 2012 7 47144 2012-07-01
Grocery stores 559838 2012 8 47716 2012-08-01
Grocery stores 559838 2012 9 45743 2012-09-01
Grocery stores 559838 2012 10 46677 2012-10-01
Grocery stores 559838 2012 11 47033 2012-11-01
Grocery stores 559838 2012 12 49082 2012-12-01
Grocery stores 571820 2013 1 46822 2013-01-01
Grocery stores 571820 2013 2 43601 2013-02-01
Grocery stores 571820 2013 3 48614 2013-03-01
Grocery stores 571820 2013 4 45077 2013-04-01
Grocery stores 571820 2013 5 49297 2013-05-01
Grocery stores 571820 2013 6 47528 2013-06-01
Grocery stores 571820 2013 7 48547 2013-07-01
Grocery stores 571820 2013 8 49058 2013-08-01
Grocery stores 571820 2013 9 46291 2013-09-01
Grocery stores 571820 2013 10 48082 2013-10-01
Grocery stores 571820 2013 11 48715 2013-11-01
Grocery stores 571820 2013 12 50188 2013-12-01
Grocery stores 597839 2014 1 48986 2014-01-01
Grocery stores 597839 2014 2 44951 2014-02-01
Grocery stores 597839 2014 3 48848 2014-03-01
Grocery stores 597839 2014 4 48545 2014-04-01
Grocery stores 597839 2014 5 51589 2014-05-01
Grocery stores 597839 2014 6 49284 2014-06-01
Grocery stores 597839 2014 7 51201 2014-07-01
Grocery stores 597839 2014 8 51140 2014-08-01
Grocery stores 597839 2014 9 48699 2014-09-01
Grocery stores 597839 2014 10 50889 2014-10-01
Grocery stores 597839 2014 11 50870 2014-11-01
Grocery stores 597839 2014 12 52837 2014-12-01
Grocery stores 612467 2015 1 51357 2015-01-01
Grocery stores 612467 2015 2 46894 2015-02-01
Grocery stores 612467 2015 3 50921 2015-03-01
Grocery stores 612467 2015 4 49813 2015-04-01
Grocery stores 612467 2015 5 52863 2015-05-01
Grocery stores 612467 2015 6 50574 2015-06-01
Grocery stores 612467 2015 7 52686 2015-07-01
Grocery stores 612467 2015 8 51675 2015-08-01
Grocery stores 612467 2015 9 49785 2015-09-01
Grocery stores 612467 2015 10 51495 2015-10-01
Grocery stores 612467 2015 11 50849 2015-11-01
Grocery stores 612467 2015 12 53555 2015-12-01
Grocery stores 624509 2016 1 51528 2016-01-01
Grocery stores 624509 2016 2 48478 2016-02-01
Grocery stores 624509 2016 3 51962 2016-03-01
Grocery stores 624509 2016 4 50258 2016-04-01
Grocery stores 624509 2016 5 52756 2016-05-01
Grocery stores 624509 2016 6 52039 2016-06-01
Grocery stores 624509 2016 7 53080 2016-07-01
Grocery stores 624509 2016 8 52186 2016-08-01
Grocery stores 624509 2016 9 51134 2016-09-01
Grocery stores 624509 2016 10 52388 2016-10-01
Grocery stores 624509 2016 11 52517 2016-11-01
Grocery stores 624509 2016 12 56183 2016-12-01
Grocery stores 649954 2017 1 52314 2017-01-01
Grocery stores 649954 2017 2 48987 2017-02-01
Grocery stores 649954 2017 3 54117 2017-03-01
Grocery stores 649954 2017 4 53385 2017-04-01
Grocery stores 649954 2017 5 55446 2017-05-01
Grocery stores 649954 2017 6 53933 2017-06-01
Grocery stores 649954 2017 7 54890 2017-07-01
Grocery stores 649954 2017 8 54809 2017-08-01
Grocery stores 649954 2017 9 53862 2017-09-01
Grocery stores 649954 2017 10 54268 2017-10-01
Grocery stores 649954 2017 11 55143 2017-11-01
Grocery stores 649954 2017 12 58800 2017-12-01
Grocery stores 668136 2018 1 54586 2018-01-01
Grocery stores 668136 2018 2 50649 2018-02-01
Grocery stores 668136 2018 3 57005 2018-03-01
Grocery stores 668136 2018 4 53249 2018-04-01
Grocery stores 668136 2018 5 57521 2018-05-01
Grocery stores 668136 2018 6 55813 2018-06-01
Grocery stores 668136 2018 7 56401 2018-07-01
Grocery stores 668136 2018 8 56728 2018-08-01
Grocery stores 668136 2018 9 54685 2018-09-01
Grocery stores 668136 2018 10 55809 2018-10-01
Grocery stores 668136 2018 11 56542 2018-11-01
Grocery stores 668136 2018 12 59148 2018-12-01
Grocery stores 690828 2019 1 57172 2019-01-01
Grocery stores 690828 2019 2 51279 2019-02-01
Grocery stores 690828 2019 3 57247 2019-03-01
Grocery stores 690828 2019 4 56009 2019-04-01
Grocery stores 690828 2019 5 59680 2019-05-01
Grocery stores 690828 2019 6 57759 2019-06-01
Grocery stores 690828 2019 7 59322 2019-07-01
Grocery stores 690828 2019 8 59800 2019-08-01
Grocery stores 690828 2019 9 55833 2019-09-01
Grocery stores 690828 2019 10 57735 2019-10-01
Grocery stores 690828 2019 11 58722 2019-11-01
Grocery stores 690828 2019 12 60270 2019-12-01
Grocery stores 756300 2020 1 57873 2020-01-01
Grocery stores 756300 2020 2 54907 2020-02-01
Grocery stores 756300 2020 3 72560 2020-03-01
Grocery stores 756300 2020 4 62967 2020-04-01
Grocery stores 756300 2020 5 66893 2020-05-01
Grocery stores 756300 2020 6 62379 2020-06-01
Grocery stores 756300 2020 7 65184 2020-07-01
Grocery stores 756300 2020 8 62869 2020-08-01
Grocery stores 756300 2020 9 60370 2020-09-01
Grocery stores 756300 2020 10 62376 2020-10-01
Grocery stores 756300 2020 11 62362 2020-11-01
Grocery stores 756300 2020 12 65560 2020-12-01
Grocery stores 790027 2021 1 63473 2021-01-01
Grocery stores 790027 2021 2 57967 2021-02-01
Grocery stores 790027 2021 3 63609 2021-03-01
Grocery stores 790027 2021 4 62593 2021-04-01
Grocery stores 790027 2021 5 66993 2021-05-01
Grocery stores 790027 2021 6 65591 2021-06-01
Grocery stores 790027 2021 7 67853 2021-07-01
Grocery stores 790027 2021 8 67414 2021-08-01
Grocery stores 790027 2021 9 65925 2021-09-01
Grocery stores 790027 2021 10 68458 2021-10-01
Grocery stores 790027 2021 11 67787 2021-11-01
Grocery stores 790027 2021 12 72364 2021-12-01
Grocery stores 854434 2022 1 68645 2022-01-01
Grocery stores 854434 2022 2 62914 2022-02-01
Grocery stores 854434 2022 3 69207 2022-03-01
Grocery stores 854434 2022 4 69280 2022-04-01
Grocery stores 854434 2022 5 72406 2022-05-01
Grocery stores 854434 2022 6 71739 2022-06-01
Grocery stores 854434 2022 7 73433 2022-07-01
Grocery stores 854434 2022 8 72174 2022-08-01
Grocery stores 854434 2022 9 70554 2022-09-01
Grocery stores 854434 2022 10 72959 2022-10-01
Grocery stores 854434 2022 11 73557 2022-11-01
Grocery stores 854434 2022 12 77566 2022-12-01
Grocery stores 873641 2023 1 71401 2023-01-01
Grocery stores 873641 2023 2 66738 2023-02-01
Grocery stores 873641 2023 3 72628 2023-03-01
Grocery stores 873641 2023 4 71428 2023-04-01
Grocery stores 873641 2023 5 74526 2023-05-01
Grocery stores 873641 2023 6 72344 2023-06-01
Grocery stores 873641 2023 7 74151 2023-07-01
Grocery stores 873641 2023 8 74166 2023-08-01
Grocery stores 873641 2023 9 72032 2023-09-01
Grocery stores 873641 2023 10 72725 2023-10-01
Grocery stores 873641 2023 11 74018 2023-11-01
Grocery stores 873641 2023 12 77484 2023-12-01
Grocery stores 891279 2024 1 72355 2024-01-01
Grocery stores 891279 2024 2 68885 2024-02-01
Grocery stores 891279 2024 3 74506 2024-03-01
Grocery stores 891279 2024 4 70496 2024-04-01
Grocery stores 891279 2024 5 76465 2024-05-01
Grocery stores 891279 2024 6 73686 2024-06-01
Grocery stores 891279 2024 7 75931 2024-07-01
Grocery stores 891279 2024 8 75923 2024-08-01
Grocery stores 891279 2024 9 72532 2024-09-01
Grocery stores 891279 2024 10 75667 2024-10-01
Grocery stores 891279 2024 11 76232 2024-11-01
Grocery stores 891279 2024 12 78601 2024-12-01

Full table of the dataset

Data Exploration

grocery_ts <- ts(grocery_data$MonthlySales, start = c(1992, 1), frequency = 12)

plot(grocery_ts)

Data displays an upward trend with regular seasonal dips and peaks

seasonplot(grocery_ts)

Plot confirms seasonality with higher sales in December and March. This is likely tied to holidays.

acf(grocery_ts, main = "ACF - Grocery Store Sales")

pacf(grocery_ts, main = "PACF - Grocery Store Sales")

Model Development and Selection

Since the data isn’t stationary, we will be using a hold-out sample.

horizon <- 1
test <- window(grocery_ts, start = tail(time(grocery_ts), 36)[1])
m <- length(test)

models <- c("ANN", "MNN", "AAN", "MAN", "MMN", "AAA", "MAA", "MAM", "MMM")
k <- length(models)

mae <- array(NA, c(m - horizon + 1, k))
rmse <- matrix(NA, nrow = m - horizon + 1, ncol = k)
mape <- matrix(NA, nrow = m - horizon + 1, ncol = k)


for (i in 1:(m - horizon + 1)) {
  itsample <- grocery_ts[1:(length(grocery_ts) - m + i - 1)]
  itsample <- ts(itsample, frequency = frequency(grocery_ts), start = start(grocery_ts))
  
  for (j in 1:k) {
    fit <- ets(itsample, model = models[j], damped = FALSE)
    frc <- forecast(fit, h = horizon)$mean
    actual <- grocery_ts[(length(itsample) + 1):(length(itsample) + horizon)]
    
    errors <- actual - frc
    mae[i, j] <- mean(abs(errors))
    rmse[i, j] <- sqrt(mean(errors^2))
    mape[i, j] <- mean(abs(errors / actual)) * 100
  }
}

mae_summary <- colMeans(mae, na.rm = TRUE)
rmse_summary <- colMeans(rmse, na.rm = TRUE)
mape_summary <- colMeans(mape, na.rm = TRUE)

results <- data.frame(
  Model = models,
  MAE = round(mae_summary, 2),
  RMSE = round(rmse_summary, 2),
  MAPE = round(mape_summary, 2)
)

print(results[order(results$MAE), ])
##   Model     MAE    RMSE MAPE
## 8   MAM  974.59  974.59 1.35
## 6   AAA 1017.01 1017.01 1.42
## 9   MMM 1049.73 1049.73 1.45
## 7   MAA 1050.48 1050.48 1.46
## 3   AAN 2119.78 2119.78 2.97
## 4   MAN 2165.70 2165.70 3.04
## 5   MMN 2199.29 2199.29 3.08
## 1   ANN 2336.35 2336.35 3.23
## 2   MNN 2361.01 2361.01 3.26

Best model is MAM. It scored the lowest across all three metrics (MAE, RMSE, and MAPE). MAE of MAM is 974.59. Lowest performing model (MNN) had a MAE of 2361.01. In addition to its performance, MAM is a good model due to how it handles variability, upward trends, and seasonal cycles, making it a good fit for our data.

Forecast and Accuracy

mam_model <- ets(grocery_ts, model = "MAM")
mam_forecast <- forecast(mam_model, h = 12, level = c(80, 95))

Forecast next 12 months with 80% and 95% prediction intervals

plot(mam_forecast,
     main = "MAM ETS Model Forecast for Grocery Store Sales",
     ylab = "Monthly Sales",
     xlab = "Year")

summary(mam_model)
## ETS(M,A,M) 
## 
## Call:
## ets(y = grocery_ts, model = "MAM")
## 
##   Smoothing parameters:
##     alpha = 0.2988 
##     beta  = 0.0057 
##     gamma = 0.0692 
## 
##   Initial states:
##     l = 27411.1642 
##     b = 46.3363 
##     s = 1.0697 0.9941 0.9984 0.9777 1.0168 1.0324
##            0.999 1.0286 0.9775 1.0017 0.9197 0.9843
## 
##   sigma:  0.0194
## 
##      AIC     AICc      BIC 
## 7702.334 7703.953 7770.018 
## 
## Training set error measures:
##                    ME     RMSE      MAE       MPE     MAPE      MASE       ACF1
## Training set 68.87231 974.9066 568.1365 0.1198108 1.248161 0.3621563 0.06432259

Summary of MAM model

accuracy(mam_model)
##                    ME     RMSE      MAE       MPE     MAPE      MASE       ACF1
## Training set 68.87231 974.9066 568.1365 0.1198108 1.248161 0.3621563 0.06432259

Accuracy

print(mam_forecast)
##          Point Forecast    Lo 80    Hi 80    Lo 95    Hi 95
## Jan 2025       75003.28 73137.83 76868.72 72150.32 77856.23
## Feb 2025       69801.47 67987.11 71615.83 67026.64 72576.29
## Mar 2025       77154.82 75063.76 79245.89 73956.82 80352.83
## Apr 2025       74508.35 72407.18 76609.52 71294.88 77721.82
## May 2025       78956.03 76643.43 81268.62 75419.22 82492.83
## Jun 2025       76510.65 74186.96 78834.35 72956.87 80064.44
## Jul 2025       78802.09 76324.12 81280.05 75012.37 82591.80
## Aug 2025       78207.08 75664.25 80749.91 74318.15 82096.00
## Sep 2025       75400.17 72868.40 77931.94 71528.17 79272.18
## Oct 2025       77422.07 74740.39 80103.75 73320.79 81523.35
## Nov 2025       77741.39 74966.57 80516.21 73497.67 81985.11
## Dec 2025       82152.11 79133.38 85170.84 77535.36 86768.86
autoplot(mam_forecast) +
  ggtitle("Grocery Store Sales Forecast using MAM Model") +
  ylab("Monthly Sales") +
  xlab("Year") +
  theme_minimal()