This analysis provides a comprehensive audit of inventory efficiency and demand drivers across five retail locations and five distinct product categories (Clothing, Electronics, Furniture, Groceries, and Toys). The study leverages a dataset of 73,100 transaction and inventory records spanning a two-year period to identify root causes for low-turnover stock and to evaluate the effectiveness of current pricing and promotional strategies.
Inventory Efficiency: Identify “Dead Stock” (Zombie SKUs) and assess the accuracy of current replenishment logic.
Demand Analysis: Quantify the impact of discounts, competitor pricing, and holiday events on sales volume.
Operational Optimization: Provide data-driven recommendations to reduce carrying costs and maximize profit margins.
Despite high forecast accuracy (WMAPE consistently below 8%), the replenishment system continues to trigger orders for low-turnover items at a stagnant Ordered_Ratio of approximately 0.40. This indicates that the automated logic fails to account for product lifecycle decay, leading to “forced” overstocking of obsolete items.
Through regression and distribution analysis, the study revealed significant Price Inelasticity:
Competitor Undercutting: Being “Lower than Competitor” yielded no statistically significant lift in median sales volume.
Promotional Lift: Sales density remained identical during “Holiday/Promotion” periods compared to baseline days.
Discount Effectiveness: Flat trend lines across all categories suggest that deep discounting is eroding margin without driving incremental volume.
Implement “Demand Decay” Triggers: Update the replenishment algorithm to automatically cap or halt orders when annual turnover velocity drops below a 50% threshold.
Shift to Margin-Optimization: Given the lack of promotional lift, the business should reduce broad-scale discounting in categories like Electronics and Clothing, where demand is non-discretionary.
Targeted Liquidation: For identified “Zombie SKUs,” standard discounting is ineffective. These items should be bundled or offloaded via secondary channels to clear warehouse space for high-velocity inventory.
This project was executed using R (tidyverse/ggplot2), utilising annualised normalisation techniques to ensure fair performance comparisons across varying product lifecycles. All findings are supported by a rigorous audit of over 73,000 data points, ensuring high statistical confidence in the observed demand patterns.
library(readr)
library(tidyverse)── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──✔ ggplot2 4.0.2 ✔ dplyr 1.2.1
✔ tibble 3.3.1 ✔ stringr 1.4.0
✔ tidyr 1.2.0 ✔ forcats 0.5.1
✔ purrr 1.2.2 ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()library(dplyr)
library(readr)
library(hexbin)
library(readr)df <- read_csv("retail_store_inventory.csv")Rows: 73100 Columns: 15
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (6): Store ID, Product ID, Category, Region, Weather Condition, Seasona...
dbl (8): Inventory Level, Units Sold, Units Ordered, Demand Forecast, Price...
date (1): 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 × 15
Date `Store ID` `Product ID` Category Region `Inventory Level`
<date> <chr> <chr> <chr> <chr> <dbl>
1 2022-01-01 S001 P0001 Groceries North 231
2 2022-01-01 S001 P0002 Toys South 204
3 2022-01-01 S001 P0003 Toys West 102
4 2022-01-01 S001 P0004 Toys North 469
5 2022-01-01 S001 P0005 Electronics East 166
6 2022-01-01 S001 P0006 Groceries South 138
# ℹ 9 more variables: `Units Sold` <dbl>, `Units Ordered` <dbl>,
# `Demand Forecast` <dbl>, Price <dbl>, Discount <dbl>,
# `Weather Condition` <chr>, `Holiday/Promotion` <dbl>,
# `Competitor Pricing` <dbl>, Seasonality <chr>glimpse(df)Rows: 73,100
Columns: 15
$ Date <date> 2022-01-01, 2022-01-01, 2022-01-01, 2022-01-01, …
$ `Store ID` <chr> "S001", "S001", "S001", "S001", "S001", "S001", "…
$ `Product ID` <chr> "P0001", "P0002", "P0003", "P0004", "P0005", "P00…
$ Category <chr> "Groceries", "Toys", "Toys", "Toys", "Electronics…
$ Region <chr> "North", "South", "West", "North", "East", "South…
$ `Inventory Level` <dbl> 231, 204, 102, 469, 166, 138, 359, 380, 183, 108,…
$ `Units Sold` <dbl> 127, 150, 65, 61, 14, 128, 97, 312, 175, 28, 150,…
$ `Units Ordered` <dbl> 55, 66, 51, 164, 135, 102, 167, 54, 135, 196, 153…
$ `Demand Forecast` <dbl> 135.47, 144.04, 74.02, 62.18, 9.26, 139.82, 108.9…
$ Price <dbl> 33.50, 63.01, 27.99, 32.72, 73.64, 76.83, 34.16, …
$ Discount <dbl> 20, 20, 10, 10, 0, 10, 10, 5, 10, 0, 10, 20, 0, 0…
$ `Weather Condition` <chr> "Rainy", "Sunny", "Sunny", "Cloudy", "Sunny", "Su…
$ `Holiday/Promotion` <dbl> 0, 0, 1, 1, 0, 1, 1, 0, 0, 1, 1, 0, 0, 0, 0, 1, 0…
$ `Competitor Pricing` <dbl> 29.69, 66.16, 31.32, 34.74, 68.95, 79.35, 36.55, …
$ Seasonality <chr> "Autumn", "Autumn", "Summer", "Autumn", "Summer",…# Find records with NA
nrow(df)[1] 73100df_clean <- na.omit(df)
nrow(df_clean)[1] 73100# Find duplicate records
df_clean <- unique(df_clean)
nrow(df_clean)[1] 73100# Calculate the total number of years in the dataset
total_days <- as.numeric(max(df$Date) - min(df$Date))
total_years <- total_days / 365.25
# Update the Efficiency Test
master_inventory_audit <- df_clean %>%
group_by(`Store ID`, `Product ID`, Category) %>%
summarise(
Total_Volume = sum(`Units Sold`),
Avg_Inventory = mean(`Inventory Level`),
Avg_Volume = mean(`Units Sold`),
Avg_Ordered = mean(`Units Ordered`),
Ordered_Ratio = Avg_Ordered / Avg_Inventory,
# 1. Accuracy Test (WMAPE)
WMAPE = (sum(abs(`Units Sold` - `Demand Forecast`)) / sum(`Units Sold`)) * 100,
# Annualized Turnover Ratio
Turnover_Ratio = (Total_Volume / Avg_Inventory) / total_years,
.groups = "drop"
)
head(master_inventory_audit)# A tibble: 6 × 10
`Store ID` `Product ID` Category Total_Volume Avg_Inventory Avg_Volume
<chr> <chr> <chr> <dbl> <dbl> <dbl>
1 S001 P0001 Clothing 20592 281. 144
2 S001 P0001 Electronics 15778 253. 122.
3 S001 P0001 Furniture 19897 270. 129.
4 S001 P0001 Groceries 24592 283. 150.
5 S001 P0001 Toys 19512 274. 138.
6 S001 P0002 Clothing 22342 268. 135.
# ℹ 4 more variables: Avg_Ordered <dbl>, Ordered_Ratio <dbl>, WMAPE <dbl>,
# Turnover_Ratio <dbl>Master_inventory_audit has generated a few parameters to test the efficiency of the inventory management across category and stores. Let us take a close look below
wmape <- master_inventory_audit %>%
select(`Store ID`, `Product ID`, Category, WMAPE) %>%
arrange(desc(WMAPE))
head(wmape)# A tibble: 6 × 4
`Store ID` `Product ID` Category WMAPE
<chr> <chr> <chr> <dbl>
1 S002 P0014 Clothing 8.13
2 S002 P0008 Groceries 7.84
3 S004 P0007 Furniture 7.65
4 S002 P0015 Groceries 7.64
5 S004 P0007 Toys 7.57
6 S003 P0003 Toys 7.55tail(wmape)# A tibble: 6 × 4
`Store ID` `Product ID` Category WMAPE
<chr> <chr> <chr> <dbl>
1 S005 P0002 Clothing 5.00
2 S003 P0013 Furniture 4.98
3 S001 P0020 Groceries 4.92
4 S004 P0006 Groceries 4.83
5 S002 P0009 Groceries 4.79
6 S005 P0020 Electronics 4.77ggplot(master_inventory_audit, aes(x = Category, y = WMAPE, fill = Category)) +
geom_boxplot(outlier.colour = "red", outlier.shape = 1) +
facet_wrap(~`Store ID`) +
labs(title = "Forecast Accuracy (WMAPE) Distribution by Store and Category",
subtitle = "Calculated across a 2-year annualized period",
x = "Product Category",
y = "WMAPE (Weighted Mean Absolute Percentage Error)") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1), # Tilts labels for readability
legend.position = "none")
By implementing a Weighted MAPE (WMAPE) approach to account for intermittent demand, the analysis reveals a high-performing forecast model with error rates consistently below 7% across all five categories. This suggests the current replenishment logic is highly effective at minimizing both stock-outs and overstock capital
ggplot(data = df_clean, aes(x = `Demand Forecast`, y = `Units Sold`)) +
geom_point(color = "steelblue") +
geom_smooth(method = "loess", color = "red") +
facet_grid(`Store ID` ~ Category) +
labs(title = "Sales vs. Forecast Accuracy")`geom_smooth()` using formula = 'y ~ x'
The Correlation Coefficient shows that the Demand Forecast is quite accurate to actual units sold
ggplot(data = df_clean, aes(x = `Inventory Level`, y = `Units Sold`)) +
geom_point(color = "Steelblue") +
geom_smooth(method = "lm",color = "red") +
facet_grid(`Store ID` ~ Category) +
labs(title = "Inventory Level vs. Units Sold")`geom_smooth()` using formula = 'y ~ x'
While the demand forecast is highly accurate (WMAPE < 7%), the Inventory Productivity analysis reveals diminishing returns. Specifically in Clothing and Electronics, inventory levels exceeding 350 units do not correlate with increased sales volume. This identifies an opportunity to reduce safety stock levels by ~20% without impacting service levels
ggplot(data = df_clean, aes(x = `Inventory Level`, y = `Units Ordered`)) +
geom_point(color = "Steelblue") +
geom_smooth(method = "loess", color = "red") +
facet_grid(`Store ID` ~ Category) +
labs(title = "Inventory Level vs. Units Ordered")`geom_smooth()` using formula = 'y ~ x'
The trend line of Inventory and Units Ordered is almost flat, indicates non linear relationship between the two. Suggest the business should transition from ‘Fixed-Quantity’ ordering to ‘Min-Max’ or ‘Reorder Point’ (ROP) logic. By syncing the high-accuracy demand forecast with the procurement signal, the company could eliminate the current over-ordering seen at high inventory levels.
turnover_ratio <- master_inventory_audit %>%
select(`Store ID`, `Product ID`, Category, Turnover_Ratio) %>%
arrange(desc(Turnover_Ratio))
head(turnover_ratio)# A tibble: 6 × 4
`Store ID` `Product ID` Category Turnover_Ratio
<chr> <chr> <chr> <dbl>
1 S003 P0019 Furniture 47.1
2 S001 P0007 Electronics 46.3
3 S004 P0011 Clothing 45.8
4 S003 P0016 Groceries 45.6
5 S003 P0020 Toys 44.8
6 S001 P0008 Furniture 44.7tail(turnover_ratio)# A tibble: 6 × 4
`Store ID` `Product ID` Category Turnover_Ratio
<chr> <chr> <chr> <dbl>
1 S005 P0003 Clothing 29.0
2 S002 P0015 Groceries 28.9
3 S004 P0009 Electronics 28.9
4 S003 P0003 Clothing 28.9
5 S004 P0011 Toys 28.7
6 S001 P0007 Clothing 28.1# Turnover ratio by category
ggplot(master_inventory_audit, aes(x = Category, y = Turnover_Ratio, fill = Category)) +
geom_boxplot() +
theme_minimal() +
labs(title = "Turnover Distribution by Category")
# Turnover ratio by store
ggplot(master_inventory_audit, aes(x = `Store ID`, y = Turnover_Ratio, fill = Category)) +
geom_boxplot() +
facet_wrap(~Category, scales = "free_y") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
theme_minimal() +
labs(title = "Turnover Distribution by Category")
Turnover Distribution range from 30 - 45， suggest there is room to improve for those with low turnover ratio
# Find combinations where Turnover is low (e.g., < 10)
low_turnover_stock <- master_inventory_audit %>%
filter(Turnover_Ratio < 60) %>%
arrange(Turnover_Ratio)
print(low_turnover_stock)# A tibble: 500 × 10
`Store ID` `Product ID` Category Total_Volume Avg_Inventory Avg_Volume
<chr> <chr> <chr> <dbl> <dbl> <dbl>
1 S001 P0007 Clothing 15781 281. 126.
2 S004 P0011 Toys 16751 292. 129.
3 S003 P0003 Clothing 15929 276. 124.
4 S004 P0009 Electronics 15293 265. 123.
5 S002 P0015 Groceries 15295 265. 120.
6 S005 P0003 Clothing 15861 274. 130.
7 S002 P0014 Clothing 15081 256. 112.
8 S004 P0011 Groceries 16101 271. 134.
9 S005 P0008 Toys 15905 267 123.
10 S004 P0018 Toys 15547 260 121.
# ℹ 490 more rows
# ℹ 4 more variables: Avg_Ordered <dbl>, Ordered_Ratio <dbl>, WMAPE <dbl>,
# Turnover_Ratio <dbl>library(ggrepel)
ggplot(master_inventory_audit, aes(x = WMAPE, y = Turnover_Ratio, color = Category)) +
geom_point(alpha = 0.5) +
# Only label the products that are in your 'dead_stock' list
geom_text_repel(data = low_turnover_stock,
aes(label = `Product ID`),
size = 3,
show.legend = FALSE) +
facet_wrap(~`Store ID`) +
theme_minimal() +
labs(title = "Inventory Performance by Store",
subtitle = "Labels identify SKUs with Turnover < 60",
x = "Forecast Error (WMAPE %)",
y = "Turnover Ratio")
The plot shows the products in the low_turnover_stock are in the Danger Zone, Further investigation could be done to those products on the list
# Assuming 'df' has daily sales records
safety_stock_summary <- df_clean %>%
filter(`Product ID` %in% low_turnover_stock$`Product ID`) %>%
group_by(`Store ID`, `Product ID`) %>%
summarise(
avg_daily_sales = mean(`Units Sold`),
sd_daily_sales = sd(`Units Sold`),
# Assume a lead time of 5 days and 95% service level (Z = 1.645)
Safety_Stock = 1.645 * sd_daily_sales * sqrt(7),
Reorder_Point = (avg_daily_sales * 1) + Safety_Stock,
.groups = "drop"
)
# Join back to see the "Excess"
optimization_report <- low_turnover_stock %>%
left_join(safety_stock_summary, by = c("Store ID", "Product ID")) %>%
mutate(Excess_Inventory = Avg_Inventory - Reorder_Point)
optimization_report$Excess_Inventory [1] -340.4541 -312.0827 -298.2519 -359.6650 -318.8141 -357.7389 -335.6982
[8] -333.1737 -350.8434 -320.0248 -350.2938 -327.4086 -335.4685 -344.0046
[15] -350.4839 -334.9431 -337.6215 -329.7327 -337.9399 -346.2035 -334.3885
[22] -313.2483 -346.8980 -317.7373 -302.0285 -335.1292 -332.6983 -351.7546
[29] -313.0253 -320.7562 -339.2789 -334.1201 -343.1497 -306.6859 -355.1140
[36] -294.8308 -359.2094 -314.5995 -346.0749 -332.6192 -324.0292 -318.9149
[43] -315.4826 -361.2436 -332.8754 -326.3729 -315.7201 -324.1394 -342.7123
[50] -341.1520 -330.6477 -325.8521 -324.8858 -328.5220 -308.5762 -326.8297
[57] -358.2307 -297.5165 -351.6678 -364.2705 -334.1949 -344.9765 -290.7819
[64] -337.2150 -366.2476 -310.4660 -341.0028 -341.3993 -334.6777 -362.3799
[71] -346.1529 -318.8323 -317.1477 -326.8667 -314.3699 -371.9889 -342.9148
[78] -347.6382 -309.8409 -335.0589 -368.0798 -329.4052 -319.3044 -320.0216
[85] -328.5177 -328.9989 -312.4742 -348.4919 -339.8536 -340.6977 -343.6061
[92] -304.6266 -313.9837 -344.0764 -340.3989 -338.8981 -349.0323 -329.0257
[99] -320.7427 -347.2722 -331.4815 -346.8414 -336.2244 -347.9596 -355.1689
[106] -331.6193 -348.9363 -328.2749 -348.9087 -342.5344 -356.5264 -327.3991
[113] -337.5117 -319.5804 -334.1086 -324.4334 -346.5530 -339.8263 -324.2794
[120] -334.1184 -337.2391 -334.1697 -320.8256 -324.4787 -349.4403 -325.0187
[127] -376.1675 -354.9853 -335.8772 -309.1564 -347.8809 -319.2940 -329.2398
[134] -325.5807 -327.4541 -333.5046 -329.0900 -319.5962 -355.2605 -309.2470
[141] -310.2716 -329.1536 -339.2830 -320.9001 -329.1737 -328.5895 -320.6619
[148] -333.4578 -347.1238 -344.7359 -346.4618 -324.4653 -341.9185 -326.9846
[155] -348.0383 -337.7290 -340.8727 -316.9613 -318.8735 -331.7801 -332.6431
[162] -354.9797 -330.8704 -352.7161 -350.2875 -327.7134 -328.9083 -332.9438
[169] -331.9229 -343.7138 -345.6446 -336.1530 -330.5480 -300.8057 -337.6789
[176] -322.2991 -324.3708 -327.7984 -333.4701 -314.3864 -323.8869 -321.3640
[183] -354.2594 -319.1291 -324.8011 -336.6641 -326.0738 -299.0943 -324.4532
[190] -345.1813 -321.4920 -343.8972 -345.4049 -334.1074 -388.8157 -342.9669
[197] -320.6785 -327.3916 -299.1702 -362.3688 -356.7513 -342.1507 -306.3521
[204] -348.4436 -347.8495 -346.9641 -356.2865 -329.0791 -300.6885 -366.4835
[211] -350.5722 -358.0081 -312.2313 -322.0407 -363.0023 -371.3833 -336.1593
[218] -334.5938 -311.8779 -329.5798 -357.6788 -340.1733 -305.2088 -347.3122
[225] -316.2064 -311.5196 -351.4274 -357.3634 -343.6631 -344.8570 -362.2213
[232] -329.4640 -326.3627 -361.0284 -339.8757 -328.7351 -326.5355 -320.9741
[239] -335.8146 -353.3917 -333.4763 -324.9212 -310.8323 -315.1547 -328.1465
[246] -325.6070 -338.4384 -357.2254 -322.6068 -352.4990 -334.6443 -354.4600
[253] -364.5337 -321.3308 -308.4244 -357.5937 -345.8213 -345.6331 -346.2786
[260] -346.9989 -349.0956 -342.4334 -332.5124 -320.3731 -300.3543 -307.4373
[267] -351.0591 -354.7746 -342.2967 -349.4858 -327.7165 -346.7261 -351.8080
[274] -327.8085 -327.0144 -323.5422 -306.7490 -345.6137 -334.2642 -344.2159
[281] -350.3409 -337.7708 -360.3712 -340.2775 -365.5997 -310.4307 -337.9669
[288] -343.3943 -343.6542 -353.0899 -341.7573 -324.8287 -338.9913 -349.1169
[295] -308.9092 -364.1548 -335.0706 -325.6293 -358.0525 -328.1461 -357.9399
[302] -322.4020 -350.7127 -352.0089 -335.8803 -317.3016 -313.4371 -321.0191
[309] -346.1948 -343.1190 -313.3169 -345.2340 -356.0930 -362.0873 -341.0731
[316] -349.1659 -362.1242 -306.3273 -332.2719 -323.7320 -338.6350 -333.2256
[323] -315.1355 -321.8496 -326.8027 -326.2802 -350.2264 -340.7787 -333.1703
[330] -351.3361 -331.6308 -352.7134 -335.5358 -355.1760 -346.5328 -341.7097
[337] -370.0600 -343.7628 -340.5914 -357.5437 -327.4266 -330.9990 -343.7677
[344] -323.5271 -342.3720 -330.0768 -330.2964 -320.0156 -334.3437 -355.4433
[351] -323.4112 -304.5341 -327.6803 -358.7600 -331.3311 -332.8034 -319.5909
[358] -306.5761 -319.1968 -326.8607 -352.1105 -330.3567 -302.8003 -353.8118
[365] -341.0626 -345.0122 -337.9961 -326.0651 -343.4225 -349.6858 -312.5180
[372] -338.0308 -312.1024 -360.3936 -366.8714 -341.1319 -340.6392 -340.3831
[379] -353.2710 -345.9340 -331.2733 -348.4708 -323.3288 -315.5008 -308.9340
[386] -355.5285 -316.2461 -354.6259 -345.1286 -349.5005 -340.9194 -344.0506
[393] -332.6423 -346.2668 -313.4341 -347.4919 -335.4595 -350.1572 -338.0567
[400] -324.7118 -353.3429 -357.8883 -313.0002 -336.1326 -295.2864 -333.4628
[407] -357.1616 -321.1643 -337.3345 -327.0925 -340.9500 -354.4434 -380.6659
[414] -334.4074 -330.5621 -329.4297 -306.4658 -350.5558 -320.0153 -336.8592
[421] -335.9513 -331.6593 -344.9660 -346.0313 -312.6291 -340.5045 -350.9427
[428] -382.0395 -340.9850 -310.5046 -341.4327 -312.0876 -330.1727 -357.8239
[435] -340.0197 -347.2145 -342.5929 -340.5914 -324.1590 -353.0085 -331.6274
[442] -335.6396 -327.4168 -342.7951 -348.3166 -344.0174 -336.2296 -339.8584
[449] -340.7862 -316.8085 -361.5711 -321.1449 -333.4544 -314.3004 -346.8612
[456] -339.8248 -344.5065 -333.8753 -336.2803 -327.6477 -349.6693 -329.4087
[463] -352.0730 -358.3488 -341.8897 -339.8827 -331.8771 -317.3499 -355.9040
[470] -336.7777 -347.7366 -379.3761 -336.6833 -315.2432 -333.7320 -323.5711
[477] -360.7858 -332.8158 -373.4780 -330.5773 -315.3787 -334.0154 -324.7373
[484] -375.6873 -319.5921 -326.3395 -332.0088 -346.3724 -325.2353 -338.1264
[491] -323.1813 -320.7854 -366.3586 -297.8430 -342.1187 -329.3992 -355.0932
[498] -341.0734 -357.3564 -355.6591It turns out that almost all the products are understocked rather than overstocked, as the current inventory is less than the safety stock level. This indicates that the business is working on lean inventory. However, not all SKUs are achieving the same performance.
# 1. Create the comparison metric
master_inventory_audit <- master_inventory_audit %>%
mutate(
Status = if_else(Turnover_Ratio < 60, "Flagged (Relative Low)", "Portfolio Average"),
Weeks_of_Supply = Avg_Inventory / (Total_Volume / 160 * 7) # 160 day period
)
# 2. Plot the Distribution
ggplot(master_inventory_audit, aes(x = Weeks_of_Supply, fill = Status)) +
geom_density(alpha = 0.5) +
geom_vline(xintercept = 0.5, linetype = "dashed", color = "darkred") +
annotate("text", x = 0.6, y = 1, label = "Industry 'Lean' Standard", color = "darkred") +
scale_fill_manual(values = c("Flagged (Relative Low)" = "#e74c3c", "Portfolio Average" = "#3498db")) +
labs(title = "Validation Audit: Are the 'Flagged' Items Actually Dead Stock?",
subtitle = "Analysis shows 'Dead Stock' still exceeds industry-standard efficiency levels",
x = "Weeks of Supply", y = "Density") +
theme_minimal()
The diagram coincides with the finding above that the general stock level is much less than the industrial standard, coincide with the findings above
ggplot(low_turnover_stock, aes(x = Category, y = Ordered_Ratio, fill = Category)) +
facet_wrap(~`Store ID`) +
geom_boxplot() +
theme_minimal() +
labs(title = "Ordered Distribution by Category - Low Turnover")
The Ordered Ratio is between 0.35 - 0.5 across the low turnover stocks, which suggests that even though these products have low turnover, the system is still ordering around 40% of the current stock, this is something that could be improved
df_clean <- df_clean %>%
mutate(Discounted_price = Price * (1 - Discount/100),
Lower_than_competitor =
if_else (Discounted_price < `Competitor Pricing`, 1, 0))
ggplot(df_clean,
aes(x = as.factor(Lower_than_competitor),
y = `Units Sold`,
fill = as.factor(Lower_than_competitor))) +
geom_boxplot() +
facet_grid(`Store ID`~ Category) +
scale_x_discrete(labels = c("0" = "Higher than Comp", "1" = "Lower than Comp")) +
theme_minimal() +
labs(title = "Does being cheaper than competitors drive volume?",
subtitle = "Comparing sales performance when price is undercut",
x = "Price Position", y = "Units Sold", fill = "Cheaper?")
Price Competitiveness vs. Volume Sensitivity “An analysis across five stores and five categories revealed that undercutting competitor pricing does not yield a statistically significant increase in sales volume. This price inelasticity suggests that current demand is driven by non-price factors.
Key Recommendation: Transition from a ‘Price-Led’ strategy to a ‘Margin-Optimization’ strategy. Specifically for low-turnover SKUs, the data suggests that further discounting is ineffective for inventory liquidation and that these items should be transitioned out of the active assortment.
ggplot(df_clean,
aes(x = as.factor(`Holiday/Promotion`),
y = `Units Sold`,
fill = as.factor(`Holiday/Promotion`))) +
geom_boxplot() +
facet_grid(`Store ID` ~ Category) +
scale_x_discrete(labels = c("0" = "Non Holiday", "1" = "Holiday/Promotion")) +
theme_minimal() +
labs(title = "Promotional Lift Analysis",
x = "Day Type",
y = "Units Sold",
fill = "Legend")
Quantitative analysis of promotional impact reveals a lack of holiday-driven sales lift across all categories. Median units sold remained consistent during promotional periods, indicating that demand is inelastic to seasonal events. This supports a shift toward a ‘Lean Replenishment’ model, as stock-piling for holiday peaks results in unnecessary carrying costs without a guaranteed increase in turnover.
ggplot(df_clean, aes(x = Seasonality, y = `Units Sold`, fill = Seasonality)) +
geom_boxplot(alpha = 0.7) +
facet_grid(`Store ID`~ Category) +
theme_minimal() +
labs(title = "Seasonal Sales Performance by Category",
subtitle = "Identifying peak demand periods across the fiscal year",
x = "Season",
y = "Units Sold") +
theme(legend.position = "none") # Legend is redundant since x-axis is labeled
The sales in different seasons do not show a clear pattern, indicating the demand is largely consistent throughout the year
############################# The End ###################################