XYZ Analysis

Author

Tony Wang

Executive Summary

This project applies ABC–XYZ inventory analysis to the Brazilian Olist e-commerce dataset to identify products that require different levels of inventory management attention. The ABC analysis classifies products according to their contribution to total revenue, while the XYZ analysis classifies products according to the predictability of their monthly demand. Combining the two approaches produces a nine-category ABC–XYZ matrix that considers both economic importance and demand variability.

The analysis covers 32,951 products. The ABC results show a strong concentration of revenue: approximately 25.9% of products (Class A) generate 80% of total revenue, while Class C products represent 39.8% of products but contribute only around 5% of revenue.

The XYZ analysis indicates that 50% of products are classified as X, 30% as Y, and 20% as Z. Importantly, the Z category accounts for approximately 55.3% of total forecast error, indicating that a relatively small proportion of products with unpredictable demand creates a disproportionate forecasting challenge.

The combined ABC–XYZ analysis shows that AX and AY products represent only 23.4% of all products but generate approximately 75.3% of total revenue. AX products combine high revenue contribution with relatively predictable demand, whereas AY products have similarly high economic importance but greater demand variability. At the other end of the spectrum, CZ products account for 10.7% of products but only 1.31% of revenue, suggesting that simplified inventory controls may be appropriate for this group.

Overall, the analysis demonstrates how combining value-based classification (ABC) with demand-predictability classification (XYZ) can provide a more informative basis for inventory prioritisation than either method alone. The results can help businesses focus forecasting, replenishment, and inventory-control resources on products where they are likely to have the greatest financial impact.

Note: The XYZ analysis is based on 26 months of monthly observations, representing approximately two annual cycles. Therefore, its forecastability classifications should be interpreted as exploratory rather than as definitive long-term demand forecasts.

Prepare the data

library(cABCanalysis)
library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   4.0.0     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(dplyr)
library(readr)
library(lubridate)
library(tsutils)
Registered S3 methods overwritten by 'tsutils':
  method          from   
  print.nemenyi   greybox
  summary.nemenyi greybox
# Load data
df <- read_csv("olist_order_items_dataset.csv")
Rows: 112650 Columns: 7
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr  (3): order_id, product_id, seller_id
dbl  (3): order_item_id, price, freight_value
dttm (1): shipping_limit_date

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
head(df)
# A tibble: 6 × 7
  order_id          order_item_id product_id seller_id shipping_limit_date price
  <chr>                     <dbl> <chr>      <chr>     <dttm>              <dbl>
1 00010242fe8c5a6d…             1 4244733e0… 48436dad… 2017-09-19 09:45:35  58.9
2 00018f77f2f0320c…             1 e5f2d52b8… dd7ddc04… 2017-05-03 11:05:13 240. 
3 000229ec398224ef…             1 c777355d1… 5b51032e… 2018-01-18 14:48:30 199  
4 00024acbcdf0a6da…             1 7634da152… 9d7a1d34… 2018-08-15 10:10:18  13.0
5 00042b26cf59d7ce…             1 ac6c36230… df560393… 2017-02-13 13:57:51 200. 
6 00048cc3ae777c65…             1 ef92defde… 6426d21a… 2017-05-23 03:55:27  21.9
# ℹ 1 more variable: freight_value <dbl>
# Category the sales by month for each sku
df_monthly <- df |>
  mutate(month = floor_date(shipping_limit_date, "month"))

# Group monthly demand by sku
monthly_demand <- df_monthly |>
  group_by(product_id, month) |>
  summarise(
    quantity = n(),
    .groups = "drop"
  )

Turn monthly demand into a product * month matrix

demand_matrix <- monthly_demand |>
  pivot_wider(
    names_from = month,
    values_from = quantity,
    values_fill = 0
  )

print(demand_matrix)
# A tibble: 32,951 × 27
   product_id   `2018-05-01` `2017-12-01` `2018-08-01` `2018-04-01` `2017-09-01`
   <chr>               <int>        <int>        <int>        <int>        <int>
 1 00066f42aee…            1            0            0            0            0
 2 00088930e92…            0            1            0            0            0
 3 0009406fd74…            0            1            0            0            0
 4 000b8f95fcb…            0            0            2            0            0
 5 000d9be29b5…            0            0            0            1            0
 6 0011c512eb2…            0            1            0            0            0
 7 00126f27c81…            0            0            0            0            2
 8 001795ec6f1…            0            6            0            0            0
 9 001b237c0e9…            0            0            1            0            0
10 001b72dfd63…            0            2            0            0            3
# ℹ 32,941 more rows
# ℹ 21 more variables: `2017-11-01` <int>, `2018-01-01` <int>,
#   `2017-02-01` <int>, `2017-03-01` <int>, `2017-07-01` <int>,
#   `2017-08-01` <int>, `2017-05-01` <int>, `2017-06-01` <int>,
#   `2017-04-01` <int>, `2018-03-01` <int>, `2017-10-01` <int>,
#   `2018-02-01` <int>, `2018-06-01` <int>, `2018-07-01` <int>,
#   `2017-01-01` <int>, `2018-09-01` <int>, `2016-10-01` <int>, …

The monthly demand is arranged in longer format where each row represents a unique sku, and monthly sales on rest of the columns

Conduct XYZ analysis

x <- demand_matrix |>
  column_to_rownames("product_id") |>
  as.matrix()

x_t <- t(x)   # now: months = rows, products = columns

# Conduct analysis
z <- xyz(x_t, m = 12)

# Print results
print(z)
XYZ analysis
         Errors %
Z (20%): 55.337
Y (30%): 42.804
X (50%): 1.859
# Plot results
plot(z)

Interpretation of the analysis results

  • Class X: There are 50% of the products in class X, which contribute only 8.741% forecasting error. It means that about half of the products sold are relatively predictable

  • Class Y: There are 30% of the products in class Y, which contribute only 8.094% forecasting error. These are the products that have more variability than those in Class X, therefore more active monitoring is needed

  • Class Z: The 20% products contribute to over 83% of forecasting error. Some highly erratic low-volume products might need to be deleted. For the rest, shorter replenishment cycles are needed to avoid stockouts

Prepare annual sales data & Conduct ABC analysis

# Conduct Analysis
annual_sales <- df %>%
  group_by(product_id) %>%
  summarise(
    value = sum(price, na.rm = TRUE),
    units_sold = n()
  ) %>%
  arrange(desc(value)) %>%
  mutate(
    grand_total = sum(value),
    pct = value / grand_total,
    cum_pct = cumsum(pct),
    abc_class = case_when(
      cum_pct <= 0.8 ~ "A",
      cum_pct <= 0.95 ~ "B",
      TRUE ~ "C"
    )
  )

# Conduct the analysis
abc_result <- cABC_analysis(annual_sales$value, PlotIt = TRUE, useGGPlot = TRUE)
Warning in cABC_postprocess_classes(Aind, Bind, Cind, Data, sorted_data, : Found 1 duplicate value(s) spanning multiple classes.
    Reassigning all occurrences to the class with the most instances or based on
    distance to boundary if tied. Consider checking data and plot to confirm data 
    is suitable for ABC analysis.
Coordinate system already present.
ℹ Adding new coordinate system, which will replace the existing one.

# Generate summary
executive_summary <- annual_sales %>%
  group_by(abc_class) %>%
  summarise(
    total_skus = n(),
    sku_percentage = n() / nrow(annual_sales) * 100,
    total_revenue = sum(value),
    revenue_percentage = sum(value) / max(grand_total) * 100
  )

print(executive_summary)
# A tibble: 3 × 5
  abc_class total_skus sku_percentage total_revenue revenue_percentage
  <chr>          <int>          <dbl>         <dbl>              <dbl>
1 A               8535           25.9     10873150.              80.0 
2 B              11301           34.3      2038874.              15.0 
3 C              13115           39.8       679620.               5.00

Interpretation of the ABC analysis results

  • Class A: 25.9% of SKUs, contributing 80% revenue

  • Class B: 34.3% of SKUs, contributing 15% revenue

  • Class C: 39.8% of SKUs, contributing 5% revenue

Combine ABC and XYZ results

# Get xyz results
xyz_results <- tibble(
  product_id = demand_matrix$product_id,
  xyz_class = as.vector(z$class),
  forecastability = as.vector(z$value),
  xyz_rank = as.vector(z$rank)
)
  
# Combine xyz and abc results
abc_xyz <- annual_sales %>%
    left_join(xyz_results, by = "product_id") %>%
    mutate(
      abc_xyz_class = paste0(abc_class, xyz_class)
    )

table(abc_xyz$abc_class, abc_xyz$xyz_class)
   
       X    Y    Z
  A 3704 4014  817
  B 5481 3576 2244
  C 7291 2295 3529

Matrix has been generated to demonstrate where every SKU is categorized in both ABC and XYZ class

Calculate Revenue Importance

abc_xyz_summary <- abc_xyz %>%
  group_by(abc_class, xyz_class) %>%
  summarise(
    skus = n(),
    revenue = sum(value, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  mutate(
    sku_pct = skus / sum(skus) * 100,
    revenue_pct = revenue / sum(revenue) * 100
  )

abc_xyz_summary
# A tibble: 9 × 6
  abc_class xyz_class  skus  revenue sku_pct revenue_pct
  <chr>     <chr>     <int>    <dbl>   <dbl>       <dbl>
1 A         X          3704 5237768.   11.2       38.5  
2 A         Y          4014 5004974.   12.2       36.8  
3 A         Z           817  630407.    2.48       4.64 
4 B         X          5481  970150.   16.6        7.14 
5 B         Y          3576  680516.   10.9        5.01 
6 B         Z          2244  388209.    6.81       2.86 
7 C         X          7291  371162.   22.1        2.73 
8 C         Y          2295  131077.    6.96       0.964
9 C         Z          3529  177381.   10.7        1.31

Interpretation of the summary

  • AX: 11.2% of SKUs, contributing 38.5% revenue, economically important and predictable

  • AY: 12.2% of SKUs, contributing 36.8% revenue, high-value but variable

  • AZ: 2.48% of SKUs, contributing 4.64% revenue, high-value but unpredictable

  • The rest 74% of SKUs but only 20% revenue, less financial importance. SKUs in CZ might need to be deleted to simply inventory.

Management Matrix & Action Plan

X Y Z
A AX: tight automated control AY: active forecasting AZ: individual review
B BX: standard control BY: periodic review BZ: cautious stocking
C CX: simple replenishment CY: simplified control CZ: minimise stock / order on demand