1. Why last month’s numbers were changing
A job gets booked. Four weeks later, the customer cancels. Someone updates the status, and last month’s sales total drops.
That was the problem we were working through with a client: the monthly and quarterly reports kept changing after the team had reviewed them. Cancellations had been infrequent, so the reporting rule hadn’t received much attention.
Before building anything, we needed to answer two questions: What was changing the old totals, and how should those changes count?
The existing report grouped jobs by their original sale date and included only jobs currently marked Sold. A March booking appeared in March’s total. Cancel it in April, and it no longer passed the filter. March’s report dropped even though the cancellation happened in April.
Illustrative example · fictional amounts
One cancellation. Two ways to report it.
A job is booked for $10,000 in March and canceled in April. Here’s what each reporting view shows:
Current Sold-status filter
March: $0
The job is no longer marked Sold, so it disappears from March’s total.
This answers: how much of March’s work is still sold today?
Closed month + dated change
March: +$10,000
Original booking stays.
April: −$10,000
Cancellation counts when it takes effect.
This answers: what happened in each month?
But cancellations weren’t the only cause. With AI’s help, I compared an April 5 copy of the source sheet with the current records to trace the differences to individual jobs. We also found changes to contract amounts. If an $8,000 contract became $9,500 while keeping its March sale date, March’s total increased by $1,500.
Looking only at the total would have hidden those separate causes. Comparing the individual records helped us see what changed; checking the source records and the report’s filters helped us explain why.
Illustrative example · fictional jobs and amounts
What changed inside the total?
These three jobs started at $24,000. Their current value is $14,500. Here’s the $9,500 difference:
Job A · Cancellation
Originally booked: $10,000
Current value: $0
Dated adjustment:
−$10,000
Job B · Contract increase
Originally booked: $8,000
Current value: $9,500
Dated adjustment:
+$1,500
Job C · Contract decrease
Originally booked: $6,000
Current value: $5,000
Dated adjustment:
−$1,000
$24,000 − $10,000 + $1,500 − $1,000 = $14,500
These amounts explain the reporting logic; they are not client results.
So the problem we needed to solve was this: keep a dependable record of what was booked when the month closed, while showing later cancellations and contract changes in the month they should count.
If you’re trying to trace a disputed number, the reporting reconciliation checklist gives you a starting point.
2. Keep the booking. Date the changes.
We first had to agree on what the report should mean. A live view answers, “How much of the work booked in March is still sold today?” A closed-month view answers, “What did we record when March closed, and what changed afterward?”
Both can be useful. We wanted the closed-month view, with later activity still visible.
For this workflow, we chose to close the previous month on the 5th. After the close, the original booking stays in its original month. Later cancellations and contract changes count in the month of their effective date.
That gave us three rules to build around:
Keep the original booking. A later status change shouldn’t remove it from the closed month.
Record the change separately. An $8,000 contract increasing to $9,500 creates a +$1,500 adjustment, with its effective date. Entering $9,500 as the adjustment would count too much.
Reverse the remaining value when a job is canceled. If that $8,000 job had already increased by $1,500, its cancellation needs to reverse $9,500.
In the illustrated example, March stays at its closed $24,000. If all three changes take effect in April, April contains a net adjustment of −$9,500, alongside any new April bookings.
That can make a month’s net booked sales negative if cancellations and reductions exceed new bookings. We need to show that activity. We’re measuring booked sales here; accounting revenue recognition and any effect on bonuses still need their own agreed policies.
3. Build it in Google Sheets
Once we had the rules, we could build the sheet and automation around them. There were two jobs: calculate the dated sales activity, and protect the original booking after the month closed.
First, record each sale and later change with its own date. The calculations in Google Sheets retain the original sale and record adjustments and cancellations against their effective dates. That gives the report a source that follows the rule we chose, instead of relying on the current Sold status.
Then automate the monthly close. We used Google Apps Script to:
Identify the jobs that qualify for the previous month’s close.
Protect their original booking fields while leaving cancellation dates, notes, and approved adjustment fields editable.
Append a snapshot of the recorded sales events and write a close log.
Check which month has already closed so another run doesn’t duplicate the close.
We also added checks for missing amounts, invalid dates, and other source-data problems. Records that need review are flagged rather than quietly treated as zero.
Before relying on the dashboard, check that it reads the dated sales events rather than the old current-status filter. Test a cancellation and a contract change through to the displayed total. A new snapshot preserves history from that point forward; it can’t recover every earlier edit.
Reliable totals are one part of a useful report. In our dashboard design case study, we worked through the next part: organizing the information around the questions the team needed to answer.
The workflow
How the monthly close works
- Close last month on the 5th
Preserve the original bookings and save a snapshot.
- Record later changes separately
Keep cancellations and contract adjustments editable, with their effective dates.
- Show what changed—and when
The report uses the original bookings plus dated adjustments.
Checking the result
Does the report follow the rules?
A cancellation or contract change needs to land in the right month without rewriting the original booking. Check the source record and follow the change through to the dashboard total.
Clear rules made AI more useful
I estimate AI helped us work through this in roughly half the time. It helped with the comparison and the code, but we still had to decide when to close the month, how to handle contract changes, and when a cancellation should count. Getting clear on those rules gave us something concrete to build and test.
If your team keeps revisiting a report because the numbers no longer match what you discussed, start by finding out what changed and agreeing on how it should be reported. Then build the automation around that.
Still spending time explaining why last month’s numbers changed? Book a call with Jamie. Bring the report and the question your team keeps getting stuck on.
Related reading
Why reporting needs to support decisions — how shared information helped a team working across 23 brands.
