Omid - Challenge 25

data-challenges
advanced-exercises
🔰 S1: Calculate AVG spending value as Average Inventory * Value per Unit.
Published

March 24, 2026

Illustration for Omid - Challenge 25

Challenge Description

🔰 S1: Calculate AVG spending value as Average Inventory * Value per Unit.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("files/CH-025 ABC analysis.xlsx", range = "B2:D14")
test  = read_excel("files/CH-025 ABC analysis.xlsx", range = "L2:M14")

result = input %>%
  mutate(avg_spend_value = `AVG Inventory (unit)` * `Value per unit ($)`) %>%
  arrange(desc(avg_spend_value)) %>%
  mutate(total_spend = sum(avg_spend_value),
         cum_spend = cumsum(avg_spend_value),
         cum_percent = cum_spend / total_spend * 100,
         Class = case_when(
           cum_percent <= 80 & row_number() <= n() * 0.2 ~ "A",
           cum_percent <= 95 & row_number() <= n() * 0.5 ~ "B",
           TRUE ~ "C"
         )) %>%
  select(Product = `Item Code`, Class)

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Builds the intermediate columns that drive the final result

  • Strengths:

    • The R solution stays close to the workbook rule and keeps the transformation compact.

  • Areas for Improvement:

    • The code assumes the sheet structure and source ranges remain stable.

  • Gem:

    • The strongest part of the solution is choosing the right intermediate representation before shaping the final output.
import pandas as pd

input_data = pd.read_excel("CH-025 ABC analysis.xlsx", usecols="B:D", skiprows=1, nrows=13)
test = pd.read_excel("CH-025 ABC analysis.xlsx", usecols="L:M", skiprows=1, nrows=13)

result = (
    input_data.assign(avg_spend_value=lambda df: df["AVG Inventory (unit)"] * df["Value per unit ($)"])
    .sort_values("avg_spend_value", ascending=False)
    .assign(
        total_spend=lambda df: df["avg_spend_value"].sum(),
        cum_spend=lambda df: df["avg_spend_value"].cumsum(),
    )
)
result["cum_percent"] = result["cum_spend"] / result["total_spend"] * 100
result["Class"] = result.apply(
    lambda r: "A" if r["cum_percent"] <= 80 and r.name < len(result) * 0.2
    else ("B" if r["cum_percent"] <= 95 and r.name < len(result) * 0.5 else "C"),
    axis=1,
)
result = result[["Item Code", "Class"]].rename(columns={"Item Code": "Product"})

print(result.equals(test))

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Builds the intermediate columns that drive the final result

  • Strengths:

    • The Python version follows the same rule in a direct dataframe-oriented implementation.

  • Areas for Improvement:

    • The code assumes the workbook layout remains stable, so any sheet redesign would require small adjustments.

  • Gem:

    • The implementation stays close to the original workbook rule instead of adding unnecessary abstraction.

Difficulty Level

This task is moderate:

  • The business rule is readable, but the workbook still requires careful implementation to reach the expected layout.