Excel Challenge 29: Calculate Running Totals

Challenge Scenario

In financial liquidity analysis and fundraising campaign tracking, monitoring daily intake alone is insufficient. Management needs to know the exact cumulative trajectory of incoming cash to evaluate when working capital targets and financing milestones will be crossed.

As a Treasury Analyst, you are tracking daily receipts for an active commercial launch with a $5,000 launch target:

Cumulative Revenue (Column C): Build an expanding running total using mixed reference anchors.
Goal Met Flag (Column E): Compare cumulative revenue against target ($5,000). Output "REACHED" when cumulative revenue meets or exceeds target, otherwise output "PENDING".
Portfolio Metrics (B8 & C8): Tally final cumulative revenue and count qualifying days.

Your Sheet Layout

Before writing your cumulative formulas, examine how this liquidity tracking worksheet is structured. Splitting the model into separate zones allows treasury teams to monitor day-by-day cash intake, observe cumulative progress against target thresholds, and verify portfolio milestones.

The sheet is divided into three key operational zones:

Daily Cash Intake Zone (Columns A & B): Contains chronological sales day records and the daily revenue amounts collected.
Running Total & Target Zone (Columns C–E): Accumulates revenue over time using expanding range references and checks whether cumulative intake meets the $5,000 launch milestone.
Campaign Performance Summary (Rows 7–8): Summarizes overall total intake and counts how many days the target benchmark was satisfied.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B5`	Sales Day, Daily Intake ($)	Data (Daily Log)	Daily cash inflow records	Read-only chronological daily sales figures.
`C2:C5`	Cumulative Revenue ($)	Accumulation Zone	`=SUM($B$2:B2)`	Build an expanding running total starting at $B$2.
`D2:D5`	Target Benchmark ($)	Benchmark Zone	Fixed benchmark target	Read-only $5,000 threshold reference for each day.
`E2:E5`	Goal Met Flag	Milestone Decision Zone	`=IF(C2 >= D2, "REACHED", "PENDING")`	Compare cumulative revenue against target benchmark.
`B8`	Final Cumulative	Summary (Total Intake)	`=SUM(B2:B5)`	Calculate the total revenue collected over all four days.
`C8`	Days to Reach Goal	Summary (Success Tally)	`=COUNTIF(E2:E5, "REACHED")`	Count how many days achieved the "REACHED" milestone status.

This layout ensures clear visibility into cashflow velocity, allowing managers to instantly spot the exact day when fundraising goals or capital requirements are met.

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: C2-C5

In Column C (C2:C5), calculate Cumulative Revenue using expanding SUM formula SUM($B$2:B2).

Objective #2

Cell: E2-E5

In Column E (E2:E5), output "REACHED" if Cumulative Revenue >= Target Benchmark, otherwise "PENDING".

Objective #3

Cell: B8

In cell B8, calculate total intake using SUM(B2:B5).

Objective #4

Cell: C8

In cell C8, count how many days achieved "REACHED" using COUNTIF.

Solution & Formula Breakdown

Calculating running totals is one of the most useful skills in business modeling. Let's see how mixed references make this effortless.

1. Building Expanding Running Totals with SUM (C2:C5)

=SUM($B$2:B2)

The secret to running totals is using a mixed cell reference:

The start anchor $B$2 is absolute (locked with dollar signs). It will never change when you copy the formula down.
The end anchor B2 is relative (unlocked). As you drag the formula down, the end row shifts automatically.
Cell C2 (Day 1): Evaluates SUM($B$2:B2) = 1,200.
Cell C3 (Day 2): Evaluates SUM($B$2:B3) = 1,200 + 1,800 = 3,000.
Cell C4 (Day 3): Evaluates SUM($B$2:B4) = 1,200 + 1,800 + 2,400 = 5,400.
Cell C5 (Day 4): Evaluates SUM($B$2:B5) = 1,200 + 1,800 + 2,400 + 1,600 = 7,000.

2. Milestone Threshold Check (E2:E5)

=IF(C2 >= D2, "REACHED", "PENDING")

On Day 1 ($1,200) and Day 2 ($3,000), cumulative revenue is below $5,000 → "PENDING".
On Day 3 ($5,400) and Day 4 ($7,000), cumulative revenue meets or exceeds $5,000 → "REACHED".

3. Executive Rollup Metrics (B8 & C8)

Cell B8 (Total Intake): =SUM(B2:B5) → Evaluates to $7,000.
Cell C8 (Days Reached): =COUNTIF(E2:E5, "REACHED") → Evaluates to 2 days.

Why not write =C2 + B3 in cell C3? While that also produces 3,000, if any row above is deleted or sorted later, a chain-link formula breaks with a #REF! error. Using =SUM($B$2:B3) is much safer and more robust!