Excel BI - Excel Challenge 911

🔰 The instruction inside 911 Billing Installments.xlsx says: > Work out the schedule of payment for each client.

Illustration for Excel BI - Excel Challenge 911

Challenge Description

🔰 The instruction inside 911 Billing Installments.xlsx says: Work out the schedule of payment for each client. Where billing date is last date of month as per billing cycle. Divide the amount equally as per billing frequency. > > Start Date: 2/20/25, Billing Frequency: Quarterly, Hence, first billing will happen = End of month of (Start Date + 3 months) = 31-May-25. The input table includes: The goal is to determine: 1. how many billing periods fit into the contract window, 2. the billing date for each period, 3. the equal installment amount for each billing row.

Solutions

R Solution

library(tidyverse)
library(readxl)
library(lubridate)

path <- "Excel/900-999/911/911 Billing Installments.xlsx"
input <- read_excel(path, range = "A2:E7")
test <- read_excel(path, range = "G2:I16")

result = input %>%
  mutate(
    intervals = interval(start = `Start Date`, end = `End Date`) %/%
      months(1) +
      1,
    period = case_when(
      `Billing Frequency` == "Monthly" ~ intervals,
      `Billing Frequency` == "Quarterly" ~ ceiling(intervals / 3),
      `Billing Frequency` == "Annually" ~ ceiling(intervals / 12),
      TRUE ~ NA_real_
    )
  ) %>%
  uncount(period, .remove = FALSE) %>%
  mutate(rn = row_number(), .by = `Client Name`) %>%
  mutate(
    mult = case_when(
      `Billing Frequency` == "Monthly" ~ 1,
      `Billing Frequency` == "Quarterly" ~ 3,
      `Billing Frequency` == "Annually" ~ 12,
      TRUE ~ NA_real_
    )
  ) %>%
  mutate(
    `Billing Date` = ceiling_date(`Start Date`, unit = "month") %m+%
      months(rn * mult) -
      days(1)
  ) %>%
  mutate(`Installment Amount` = Amount / period) %>%
  select(`Client Name`, `Billing Date`, `Installment Amount`)

all.equal(result, test)
#> [1] TRUE

R Analysis

• Logic: Compute the inclusive number of months between start and end.; Convert that span into the number of billing periods based on frequency.; Repeat each client row once per installment..
• Strengths: The key idea is that the billing count is not the same as the raw month count.
• Areas for Improvement: The approach assumes the workbook structure and naming conventions stay stable, so any changed input shape would need minor adjustments.
• Gem: text ceiling(8 / 3) = 3

Python Solution

import pandas as pd
from pandas.tseries.offsets import MonthEnd

path = "900-999/911/911 Billing Installments.xlsx"
input_df = pd.read_excel(path, usecols="A:E", skiprows=1, nrows=5)
test = pd.read_excel(path, usecols="G:I", skiprows=1, nrows=14)

freq_months = {"Monthly": 1, "Quarterly": 3, "Annually": 12}


def month_diff(start, end):
    return (end.year - start.year) * 12 + (end.month - start.month) + 1


rows = []
for row in input_df.itertuples(index=False):
    step = freq_months[row[3]]
    periods = -(-month_diff(row[1], row[2]) // step)
    amount = row[4] / periods
    for i in range(1, periods + 1):
        bill_date = row[1] + pd.DateOffset(months=i * step) + MonthEnd(0)
        rows.append((row[0], bill_date, amount))

result = pd.DataFrame(rows, columns=["Client Name", "Billing Date", "Installment Amount"])

print(result.equals(test))
# True

Python

The Python version follows the same structure: map each billing frequency to a month step.; compute how many billing periods are needed..

Difficulty Level

Easy

Once the core pattern is recognized, the implementation is short and direct.

Related Challenges

• Excel Challenge 925
• Excel Challenge 930
• Excel Challenge 914