Residual Analysis: An R Project ON How Adidas Quarterly Sales Are Affected By Quarter Of The year, Region, And By Product Part 1

Introduction

This dataset is An Adidas sales dataset that have information on the sales of Adidas products, number of units sold, the total sales revenue, the location of the sales, the sales outlets and method of sales.

This data is based on the Adidas dataset, it is very granular and each row in that dataset represents a line item for a purchase at one of 6 Retailers outlet spread across all the states in the five Region of USA. there is no missing value in this dataset. this project has two part to it, part one seeks to answer project statement using Residual analysis and part two seeks to use Dummy variables in regression to explain the project statement

Data Source

The data used in this analysis is available publicly on Kaggle.com. https://www.kaggle.com/datasets/heemalichaudhari/adidas-sales-dataset

Project Statement:

“How are quarterly sales affected by quarter of the year, region, and by product?

Step 1

Load the Libraries

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.1     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.2     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.1     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the ]8;;http://conflicted.r-lib.org/conflicted package]8;; to force all conflicts to become errors

library(corrplot)

corrplot 0.92 loaded

library(rmarkdown)
library(jtools) # For tabulating and visualizing results from multiple regression models
library(ggstance)

Attaching package: 'ggstance'

The following objects are masked from 'package:ggplot2':

    geom_errorbarh, GeomErrorbarh

library(huxtable)

Attaching package: 'huxtable'

The following object is masked from 'package:dplyr':

    add_rownames

The following object is masked from 'package:ggplot2':

    theme_grey

Descriptive Analysis

Data Preparation (ETL)

load dataset as a dataframe object and checking the dimension with dim(), it has 9648 rows and 12 columns.

Adidias <- read.csv('C:/Users/ebene/Documents/Adidias.csv')
# Save the data frame as an RDS file
saveRDS(Adidias, file = "C:/Users/ebene/Documents/Adidias.rds")
Adidias <- readRDS("C:/Users/ebene/Documents/Adidias.rds")
dim(Adidias)

[1] 9648   12

Getting to Know the Dataset and checking for missing values

str(Adidias)

'data.frame':   9648 obs. of  12 variables:
 $ Retailer        : chr  "Foot Locker" "Foot Locker" "Foot Locker" "Foot Locker" ...
 $ Retailer_ID     : int  1185732 1185732 1185732 1185732 1185732 1185732 1185732 1185732 1185732 1185732 ...
 $ Invoice_Date    : chr  "01/01/2020" "02/01/2020" "03/01/2020" "04/01/2020" ...
 $ Region          : chr  "Northeast" "Northeast" "Northeast" "Northeast" ...
 $ State           : chr  "New York" "New York" "New York" "New York" ...
 $ City            : chr  "New York" "New York" "New York" "New York" ...
 $ Product         : chr  "Men's Street Footwear" "Men's Athletic Footwear" "Women's Street Footwear" "Women's Athletic Footwear" ...
 $ Price_per_Unit  : int  50 50 40 45 60 50 50 50 40 45 ...
 $ Units_Sold      : int  1200 1000 1000 850 900 1000 1250 900 950 825 ...
 $ Total_Sales     : int  600000 500000 400000 382500 540000 500000 625000 450000 380000 371250 ...
 $ Operating_Profit: int  300000 150000 140000 133875 162000 125000 312500 135000 133000 129938 ...
 $ Sales_Method    : chr  "In-store" "In-store" "In-store" "In-store" ...

colSums(is.na(Adidias))

        Retailer      Retailer_ID     Invoice_Date           Region 
               0                0                0                0 
           State             City          Product   Price_per_Unit 
               0                0                0                0 
      Units_Sold      Total_Sales Operating_Profit     Sales_Method 
               0                0                0                0 

From the data structure above, there are columns data, Retailer_ID and Invoice_Date. These variable do not provide enough information to perform linear regression analysis, which can be removed, but we need to get quarterly sales from date, hence the date will be useful,

Summary of dataset.

rmarkdown::paged_table(Adidias)

Adidias<- Adidias %>% 
select(-Retailer_ID)# romoves the column Retiasler
summary(Adidias)## Step 3.1 converting to date using lubridate function

   Retailer         Invoice_Date          Region             State          
 Length:9648        Length:9648        Length:9648        Length:9648       
 Class :character   Class :character   Class :character   Class :character  
 Mode  :character   Mode  :character   Mode  :character   Mode  :character  
                                                                            
                                                                            
                                                                            
     City             Product          Price_per_Unit     Units_Sold    
 Length:9648        Length:9648        Min.   :  7.00   Min.   :   0.0  
 Class :character   Class :character   1st Qu.: 35.00   1st Qu.: 106.0  
 Mode  :character   Mode  :character   Median : 45.00   Median : 176.0  
                                       Mean   : 45.22   Mean   : 256.9  
                                       3rd Qu.: 55.00   3rd Qu.: 350.0  
                                       Max.   :110.00   Max.   :1275.0  
  Total_Sales     Operating_Profit Sales_Method      
 Min.   :     0   Min.   :     0   Length:9648       
 1st Qu.:  4254   1st Qu.:  1922   Class :character  
 Median :  9576   Median :  4372   Mode  :character  
 Mean   : 93273   Mean   : 34425                     
 3rd Qu.:150000   3rd Qu.: 52063                     
 Max.   :825000   Max.   :390000                     

Converting Date to Datetype

creating a column for quarter, I had to break date into quarter then concatenate date and quarter and quarter_char to be able to answer the project statement

library(lubridate)
# Convert 'Invoice_Date' column to Date type
Adidias$Invoice_Date <- dmy(Adidias$Invoice_Date)

# Get quarters
Adidias$quarter <- quarter(Adidias$Invoice_Date)
# Create a new column 'date_quarter'
Adidias$date_quater <- paste(Adidias$Invoice_Date, Adidias$quarter, sep = "-")
# Create a new column 'quarter_char' representing quarters as characters
Adidias <- Adidias %>%
  mutate(quarter_char = case_when(
    quarter == 1 ~ 'First',
    quarter == 2 ~ 'Second',
    quarter == 3 ~ 'Third',
    quarter == 4 ~ 'Fourth',
    TRUE ~ as.character(quarter)
  ))

# View the updated data frame
#print(Adidias)

Renaming my data columns

Adidias <- rename(Adidias, total_sales = 'Total_Sales',
                     operating_profit = 'Operating_Profit',
                     invoice_date = 'Invoice_Date',
                     price_per_unit = 'Price_per_Unit',
                     units_sold = 'Units_Sold',
                     sales_method = 'Sales_Method',
                     region = Region,
                     retailer = Retailer,
                     state = State,
                     city = City,
                     product = Product)

The number of transaction or number of sales unit made for each product that was sold in one hand and knowing the retailer with the most product unit sold in other hand: Foot Locker had the most unit sold and was followed by West Gear. For retailers with the least number of sales transactions is Walmart. Men’s Street Footwear and Men’s Athletic Footwear had the most sales unite. Men’s Apparel and Women’s Athletic Footwear had the lowest sold unit

#Examine PROD_NAME
library(data.table)

Attaching package: 'data.table'

The following objects are masked from 'package:lubridate':

    hour, isoweek, mday, minute, month, quarter, second, wday, week,
    yday, year

The following objects are masked from 'package:dplyr':

    between, first, last

The following object is masked from 'package:purrr':

    transpose

setDT(Adidias)
Adidias[, .N , "product"]

sort(table(Adidias$retailer), decreasing = T)

  Foot Locker     West Gear Sports Direct        Kohl's        Amazon 
         2637          2374          2032          1030           949 
      Walmart 
          626 

Period of Analysis

c(min(Adidias$invoice_date), max(Adidias$invoice_date))

[1] "2020-01-01" "2021-12-31"

data used from 1 January 2020 up to 31 December 2021

Region Unit Sold

West Region has the most Adidas purchase transactions and Southeast Region is the area with the fewest adidas purchase transactions

sort(table(Adidias$region), decreasing = T)

     West Northeast   Midwest     South Southeast 
     2448      2376      1872      1728      1224 

from the summary() function, there were days no unite was sold per day and there were days that highest average unite sold per day was 1,275

Days No Product Was Sold

zero_sales <-Adidias %>%
    filter(units_sold == "0")
zero_sales

On June 5, 2021 and June 11, 2021 in Omaha, Midwest region, state of Nebraska in second quarter, Woman’s Athletic Footwear products did not sell at Foot Locker. it should be investigated what happend in the month of June to avoid future occurrence.

Sales Method Performance

Adidas Online sales method performed better and in-store has the leaset performance

  sort(table(Adidias$sales_method), decreasing = T)

  Online   Outlet In-store 
    4889     3019     1740 

Exploratory Analysis

Statistical Analysis

Correlation relationship on Unit Sold and opration_profit, the first code gives the output of the correlation between the two variables with out the correlation matrix,the code snippets gives the correlation matrix. At 0.89, the relationship between oprational_profit and units_sold is directly proportional.

cor(Adidias$units_sold, Adidias$operating_profit)

[1] 0.8923794

cor(Adidias[,c('operating_profit', 'units_sold')])

                 operating_profit units_sold
operating_profit        1.0000000  0.8923794
units_sold              0.8923794  1.0000000

Regression Opreation_profit On units_sold With A Scatter Plot

the stat_smooth function from the ggplot2 package was used to add regression function on the plot. The “lm” value for the method parameter indicates that I am using a linear model to add a trend line to the scatter plot.

ggplot(Adidias, aes(x =units_sold , y = operating_profit)) +
  geom_point() +
  stat_smooth(method = 'lm')

`geom_smooth()` using formula = 'y ~ x'

conceptually, this algorithm calculates the slope and intercept of a line that mathematically reduces the sum of the distance between all of the data points and the line.

Residuals Analysis

created a linear model to predict operational by regressing units_sold from the Adidas data frame, and then look at a summary of the model.

lm_a <- lm(operating_profit  ~ units_sold, data = Adidias)
summary(lm_a)

Call:
lm(formula = operating_profit ~ units_sold, data = Adidias)

Residuals:
    Min      1Q  Median      3Q     Max 
-108758  -12327     263   10588  225529 

Coefficients:
              Estimate Std. Error t value Pr(>|t|)    
(Intercept) -23568.799    388.809  -60.62   <2e-16 ***
units_sold     225.719      1.162  194.21   <2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Residual standard error: 24460 on 9646 degrees of freedom
Multiple R-squared:  0.7963,    Adjusted R-squared:  0.7963 
F-statistic: 3.772e+04 on 1 and 9646 DF,  p-value: < 2.2e-16

Residuals are simply the difference between the actual observations of opration_revenue that were used to create the model and the values of unit_sales that are fitted from the model.

Creating Residual DataFrame

To look at some specific observations I will first creating a dataframe that has the actual values of Unit_sales, the opreating_revenue, and two new columns: One fittedOpreating_profit, this will be created using the coefficient estimates from the linear model. The second column will be created, residuals, by subtracting the values from the fittedOpreating_profit, column from the opreating_profit.

resid <- Adidias %>%
  select(operating_profit, units_sold) %>%
  mutate(fittedOpreating_profit = -23568.799 + 225.719*units_sold
         , residuals = operating_profit - fittedOpreating_profit)
head(resid)

The first row indicates that when the value of Unit_sold is equal to 1,200, the fitted value of opreating_profit is 247,294.0. However, the actual oprating_profit of 300,000 was above the amount by 52,706. In other words, the actual value of opreating_profit is above the line created by the linear model.

looking at the second row, which indicates that when the value of unit_sold is equal to 1000, the fitted value of opreating_profit is 202150.2. This time, the actual value of opreating_profit of 150000 is below that amount by 52150.20 meaning that it falls below the line created by the linear model.

Business Management Application of Residuals

As a Business manager of Adidas Sales that wants to see “How are quarterly sales affected by quarter of the year, region, and by product? can have a clue from residual analysis If we think of the fitted values as a target, or expectation of what opreating_profit should be, then the residual tells us whether that revenue is more or less than expected. In other words, it can be thought of as a variance.

Residual Analysis Explaining the project statement

# Create a dataframe with residuals and identifying information
resids2 <- Adidias %>%
  select(retailer,product,region, date_quater, units_sold, operating_profit)
resids2$fittedOpreating_profit = lm_a$fitted.values
resids2$residuals = lm_a$residuals

# Get the five best performing store/quarter combinations
best <- resids2 %>%
  arrange(desc(residuals)) %>%
  .[1:4,]
# Get the five worst performing store/quarter combinations
worst <- resids2 %>%
  arrange(residuals) %>%
  .[1:4,] %>%
  arrange(desc(residuals))

# Combine the five best and worst into one dataframe and display them
bestWorst <- bind_rows(best,worst)
bestWorst

• Best stores are Walmart and Sports Direct, best product is Women’s Apparel the store at Southeast beat their goal during second and third quarters for 2021 by at least $198,601.2
• The store at South also beat its expectation during third and fort quarter by at least $154,993.3.
• Worst stores are West Gear and Sports Direct in the West and South for Men’s Athletic Footwear and Men’s Street Footwear.
• The store at West missed its expected quarterly oprating_profit for first quarters of 2021 by at least $108,757.6.
• The store in the South also missed its expected quarterly oprating_profits for third quarter in 2021 by about $102,934.7.

Recommendation

The manager, may want to look into the two stores that under performed during 2021 to work on improving their performance. In contrast, He may want to look into the two stores that outperformed during 2021 to find out if their best practices can be replicated in other locations.

Part Two

‘Residual Analysis: An R Project ON How Adidas Quarterly Sales Are Affected By Quarter Of The year, Region, And By Product’ Part 2 Using Dummy variables to analyse Adidas quarter of the year product performances

Project Statement:

“How are quarterly sales affected by quarter of the year, region, and by product?

Qualitative Variables

I will be using Qualitative variable quarter of the year, however, machine learning algorithms, including regression, rely on numeric values. So we have to convert qualitative variables to numeric variables.

Dummy Variables

What is often done is a series of binary variables is used to capture the different levels of the qualitative variable. Specifically, we would replace the quarter of the year variable, quarter_NoYear, with three variables: Second, Third, and Fourth. The values in these columns take on a value of 1 if the observation fits into that category, and a value of zero otherwise. We only need three columns because if they all have a value of 0, then that means the observation fits into the first quarter.

Here’s a dataframe to illustrate that idea with a bit more detail:

data.frame('quarter_char' = c('First', 'Second', 'Third', 'Fourth'),
                 'quarterSecond' = c(0, 1, 0, 0),
                 'quarterThird' = c(0, 0, 1, 0),
                 'quarterFourth' = c(0, 0, 0, 1))

Relationship between perating_profit on quarter_char column

Regression of operating_profit on quarter_char column, which as a data type of factor.

lm_b<- lm(operating_profit  ~ quarter_char, data = Adidias)
summary(lm_b)

Call:
lm(formula = operating_profit ~ quarter_char, data = Adidias)

Residuals:
   Min     1Q Median     3Q    Max 
-40494 -33080 -26092  18040 355040 

Coefficients:
                   Estimate Std. Error t value Pr(>|t|)    
(Intercept)           27460       1093  25.115  < 2e-16 ***
quarter_charFourth     6925       1558   4.446 8.86e-06 ***
quarter_charSecond     7779       1554   5.005 5.68e-07 ***
quarter_charThird     13142       1544   8.510  < 2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Residual standard error: 54000 on 9644 degrees of freedom
Multiple R-squared:  0.007533,  Adjusted R-squared:  0.007225 
F-statistic:  24.4 on 3 and 9644 DF,  p-value: 1.006e-15

There is a coefficient estimate for the second through fourth quarters, but not for the first quarter. The intercept represents the estimate of operating_profit for the first quarter, and the coefficient estimates for the other variables represent the difference between that quarter from the first quarter. the operating_profit for third quarter is 13,142 higher than the baseline estimated and 7,779 higher than the baseline estimated in second quarter.

Manual Comparison of Mean

Let’s create a manual comparison by calculating the mean value of operating_profit for each quarter.

Adidias %>%
  group_by(quarter_char) %>%
  summarize(meanProfit = mean(operating_profit)) %>%
  ungroup()

The value of meanProfit for the second quarter, 35,238.62 is higher than the value of meanProfit for the first quarter by 7,778.78, which is represented by the coefficient estimate on quarter_cherSecond in the regression model.

There’s a similar relationship for the other two quarters and the difference from the first quarter and their coefficient estimates.

In terms of statistical significance, all the coefficients are highly significant (p-values < 0.001). This means that the quarterly variables have a significant impact on operating profit.

Comparing two simple regression model

lm_a <- lm(operating_profit  ~ units_sold, data = Adidias)

lm_b<- lm(operating_profit  ~ quarter_char, data = Adidias)
export_summs(lm_a, lm_b) # Create a nice looking table for comparing the regression results

Registered S3 methods overwritten by 'broom':
  method            from  
  tidy.glht         jtools
  tidy.summary.glht jtools

	Model 1	Model 2
(Intercept)	-23568.80 ***	27459.84 ***
	(388.81)	(1093.36)
units_sold	225.72 ***
	(1.16)
quarter_charFourth		6925.40 ***
		(1557.79)
quarter_charSecond		7778.78 ***
		(1554.15)
quarter_charThird		13142.38 ***
		(1544.35)
N	9648	9648
R2	0.80	0.01
*** p < 0.001; ** p < 0.01; * p < 0.05.

The table indicates the key takeaways from both linear models: * Coefficient estimates: * Model 1, the coefficient estimate is -23568.80 for the intercept and is 225.72 for Unit_sold. * For model 2, the coefficient estimate is 27459.84 for the intercept and is 6925.40 for quarter_charFourth, 7778.78 for quarter_charSecond and 13142.38 for quarter_charThird * The standard errors are in parentheses below these coefficient estimates. * N stands for number of observations, and they are both based on 9648 observations. * R2 is the R-squared, which is much larger for model 1, 80%, than for model 2, 1%. This means that model 1 explains more variation in opratiing_profit than model 2, and is better for making predictions.

Visualizing coefficients and standard errors

Let’s use the jtools package to plot the coefficients and standard errors to help visualize the results.

library(jtools)
plot_summs(lm_a, lm_b)

Loading required namespace: broom.mixed
Loading required namespace: broom.mixed

This visualization is excellent. It clearly shows that the coefficient on Unit_sold from model 1 is positive, and has a much smaller standard error relative to the positive coefficient on quarter_char from model 2. The long orange whiskers and the short, barely visible blue whiskers represent the standard error, or the range in which the actual value could be.

The Unique Effect of Quarter of the Year

Quarter of the year may have a significant effect on quarterly oprating_profit after controlling for the percentage of sales that come from other products. Let’s test this out by including it with the unit_sold variables that we have already investigated.

lm_c <- lm(operating_profit  ~ units_sold + quarter_char,data = Adidias)
export_summs(lm_a, lm_b, lm_c)

	Model 1	Model 2	Model 3
(Intercept)	-23568.80 ***	27459.84 ***	-29406.31 ***
	(388.81)	(1093.36)	(567.75)
units_sold	225.72 ***		226.42 ***
	(1.16)		(1.15)
quarter_charFourth		6925.40 ***	11268.92 ***
		(1557.79)	(696.44)
quarter_charSecond		7778.78 ***	6660.84 ***
		(1554.15)	(694.48)
quarter_charThird		13142.38 ***	4882.99 ***
		(1544.35)	(691.36)
N	9648	9648	9648
R2	0.80	0.01	0.80
*** p < 0.001; ** p < 0.01; * p < 0.05.

regression analysis with the addition of the “units_sold” variable yields the following results:

The coefficient for the “units_sold” variable is 226.424 with a standard error of 1.152. This means that for each additional unit sold, the operating profit is estimated to increase by 226.424. The coefficient is highly significant (p-value < 0.001), indicating a strong positive relationship between units sold and operating profit.

The coefficient for the “quarter_charFourth” variable is 11,268.915 with a standard error of 696.438. This coefficient represents the difference in operating profit between the fourth quarter and the baseline quarter, while holding the number of units sold constant. The positive coefficient suggests that the fourth quarter tends to have higher operating profit compared to the baseline quarter.

All coefficients are highly significant (p-values < 0.001), indicating their strong relationship with operating profit.

The multiple R-squared value is 0.8019, indicating that the model with “units_sold” and quarterly variables explains approximately 80.19% of the variability in operating profit. The adjusted R-squared value is 0.8018, which takes into account the number of predictors in the model.

The F-statistic is 9756 with a p-value < 2.2e-16, indicating that the overall model is highly significant.

This change is effectively communicated by visualizing the coefficients from all three models.

plot_summs(lm_a, lm_b, lm_c)

Loading required namespace: broom.mixed
Loading required namespace: broom.mixed
Loading required namespace: broom.mixed

Conclusion

Based on Model 2 and Model 3, it appears that the quarter_charThird variable had the largest positive coefficient and was statistically significant in both models. This suggests that the third quarter (quarter_charThird) had the most significant positive impact on the dependent variable compared to the other quarters (quarter_charFourth and quarter_charSecond). However, the practical significance and implications may vary depending on the specific situation or domain being studied.