Excel BI - Excel Challenge 889

excel-challenges

excel-formulas

🔰 Calculate the Commission for each transaction based on the Cumulative Year-to-Date Sales for that specific Sales_Rep.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 889

Challenge Description

🔰 Calculate the Commission for each transaction based on the Cumulative Year-to-Date Sales for that specific Sales_Rep. The tiered tax are applied at cumulative YTD level.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/889/889 CYTD Commission Calculation.xlsx"
input <- read_excel(path, range = "A1:C21")
test <- read_excel(path, range = "D1:D21")

result = input %>%
  mutate(year = year(Date)) %>%
  mutate(CumsumSales = cumsum(Sale_Amount), .by = c(Sales_Rep, year)) %>%
  mutate(
    cum_comm = pmin(CumsumSales, 10000) *
      0.05 +
      pmax(pmin(CumsumSales - 10000, 10000), 0) * 0.10 +
      pmax(CumsumSales - 20000, 0) * 0.15
  ) %>%
  mutate(
    tier_comm = cum_comm - lag(cum_comm, default = 0),
    .by = c(Sales_Rep, year)
  )

all.equal(result$tier_comm, test$`Answer Expected`, check.attributes = FALSE)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import numpy as np

path = "Excel/800-899/889/889 CYTD Commission Calculation.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=21)
test = pd.read_excel(path, usecols="D", nrows=21)

input['Date'] = pd.to_datetime(input['Date'])
input['year'] = input['Date'].dt.year
input['CumsumSales'] = input.groupby(['Sales_Rep', 'year'])['Sale_Amount'].cumsum()
input['cum_comm'] = (
    np.minimum(input['CumsumSales'], 10000) * 0.05 +
    np.maximum(np.minimum(input['CumsumSales'] - 10000, 10000), 0) * 0.10 +
    np.maximum(input['CumsumSales'] - 20000, 0) * 0.15
)
input['tier_comm'] = input.groupby(['Sales_Rep', 'year'])['cum_comm'].diff().fillna(input['cum_comm']).astype('int64')

print(input['tier_comm'].equals(test['Answer Expected'])) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.