Challenge Scenario
In software-as-a-service (SaaS) and enterprise utility billing, customers frequently join mid-month. Invoicing them for the entire billing cycle would cause billing disputes, while delaying billing until the next month creates uncollected revenue.
As a Billing Operations Analyst, you are preparing monthly mid-cycle invoices based on a standard 30-day billing month:
- Active Service Days (Column E): Calculate elapsed active days inclusive of start and cutoff dates (
=End - Start + 1).
- Prorated Invoice (Column F): Determine daily rate (
Monthly Plan / 30) and multiply by active days.
- Billing Summary (B7 & C7): Sum total mid-month prorated revenue and compute the mean active tenure.
Your Sheet Layout
Before entering prorated billing formulas, take a moment to understand the sheet architecture. Organizing customer accounts, activation timelines, and billing calculations into clear functional zones prevents mid-month billing discrepancies and simplifies financial auditing.
The sheet is organized into three distinct operational zones:
- Client Contract Zone (Columns A–D): Holds customer names, agreed monthly recurring subscription fees, activation dates, and billing cycle cutoff dates.
- Proration Calculation (Columns E & F): Calculates inclusive active service days and computes the exact prorated invoice amount for each client.
- Billing Operations Summary (Rows 6–7): Sums total mid-cycle revenue to be collected and calculates average active days across onboarding accounts.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D4 |
Client Name, Monthly Plan, Activation, Cutoff |
Data (Contract Terms) |
Contract milestones and rates |
Read-only baseline client contracts and cycle dates. |
E2:E4 |
Active Days |
Timeline Zone |
=D2 - C2 + 1 |
Calculate inclusive active service days during the billing cycle. |
F2:F4 |
Prorated Invoice |
Revenue Calculation |
=(B2 / 30) * E2 |
Multiply the daily rate (Monthly Plan / 30) by active service days. |
B7 |
Total Prorated Billing |
Summary (Receivables) |
=SUM(F2:F4) |
Sum all prorated client invoice amounts. |
C7 |
Avg Active Days |
Summary (Tenure Mean) |
=AVERAGE(E2:E4) |
Calculate the average active service days across onboarded clients. |
This layout ensures that daily rate calculations are fully transparent to clients while rolling up into accurate monthly billing totals for finance.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column E (E2:E4), calculate inclusive Active Days using formula Cutoff - Activation + 1.
2
In Column F (F2:F4), calculate Prorated Invoice using (Monthly Plan / 30) * Active Days.
3
In cell B7, sum total prorated billing using SUM.
4
In cell C7, calculate average active days using AVERAGE.
Solution & Formula Breakdown
Prorating charges for partial-month service is essential in recurring revenue businesses. Let's see how each formula operates step by step.
1. Calculating Inclusive Active Days (E2:E4)
=D2 - C2 + 1
In billing, both the start date and the end date are considered active service days:
- If a customer activates on March 16 and billing cutoff is March 31, direct subtraction
31 - 16 = 15.
- However, counting every day from the 16th through the 31st inclusive gives 16 days.
- Adding
+ 1 ensures the customer is correctly billed for the activation day itself.
2. Calculating Prorated Invoices (F2:F4)
=(B2 / 30) * E2
Based on a standard 30-day billing cycle:
- Acme Corp (Row 2): Daily rate =
300 / 30 = $10/day. Prorated invoice = 10 * 16 = $160.
- Starlight Tech (Row 3): Daily rate =
600 / 30 = $20/day. Prorated invoice = 20 * 15 = $300.
- Nexus Global (Row 4): Daily rate =
450 / 30 = $15/day. Prorated invoice = 15 * 11 = $165.
3. Summary Operations Metrics (B7 & C7)
- Cell B7 (Total Billing):
=SUM(F2:F4) → Evaluates to 160 + 300 + 165 = $625.
- Cell C7 (Average Active Days):
=AVERAGE(E2:E4) → Evaluates to (16 + 15 + 11) / 3 = 14 days.
Why do we use a fixed 30-day denominator instead of actual calendar days (like 28 or 31)? In SaaS and commercial utilities, adopting a standard 30-day month (known as the 30/360 day count convention) prevents fluctuating daily charges across months of different lengths.