CASE FILE 04/13CLASS: SAMPLE BUILDDATA: REALISTIC, NOT CLIENTTYPE: Billing & invoicing automation

Sample build: realistic data, built to show how I'd handle this work. Not client work.

Sample buildBilling & invoicing automation

Recurring billing and contract tracker (Xero + ClickUp)

8 of 11 billing steps automated from signed contract to paid invoice, 5/5 checks pass

Sample build · realistic data, built to show how I'd handle this work · not client work · every figure is computed from the files in the appendix

  • 8 of 11steps automated
  • 5/5checks pass
  • 6contracts in the register
  • 3,000USD held, not billed

01Summary

  1. 6 signed contracts in 2 currencies drive one invoice schedule: retainers bill on their day, milestones wait for the task.
  2. 8 of 11 steps from won deal to paid invoice run on their own; approving drafts and sign-off stay with a person.
  3. An expired contract is held, not billed, and 5 of 5 control checks pass.

8 of 11 steps from won deal to paid invoice run on their own; approving drafts and sign-off stay with a person.

Key point, computed from the sample files

02The scenario

In this scenario invoices are raised from memory and a calendar, so retainers, milestones and renewals are easy to miss or bill twice.

Exhibit 1: Current-state process map: 5 manual steps across 3 roles
Current-state swimlaneSalesFinanceXeroDeal won1Copies terms to asheet2Invoice typed byhand3Chases frommemory4Renewal noticedlate5

Source: sample files

03Issues in the data

Exhibit 2: Issues in the scenario, counted from the sample data
3,000USD scheduled against a contract that has ended, held instead of invoiced
4open invoices needing a reminder stage
2,340GBP equivalent 30+ days overdue
1contract inside its renewal notice window

Source: sample files

04How I approached it

  • A contract register with type, currency, term, billing day and renewal notice; status is worked out by formula.
  • Milestone billing driven by a 'task done' flag, as a percentage of the contract value.
  • An invoice schedule of 12 rows with a duplicate key and a contract-window check on every row.
  • An AR tracker that sets the reminder stage from days overdue.
  • An automation map of 11 steps from won deal to paid invoice, and a Checks tab.
Exhibit 3: Target design for the scenario
Future-state architectureTRIGGERDeal won in ClickUpMilestone task doneREGISTERContract registerInvoice schedule +duplicate keyXERORepeating invoicesDraft milestoneinvoicesRemindersREVIEWFinance approvesdraftsChecks tab at monthend

Source: my design and the sample files

05How this applies to your business

  1. Assess and designMap the current process, size the gaps from your own data, agree the design and a written scope.
  2. Build and testBuild in your own tools with the checks built in, then run real cases end to end.
  3. Document and hand overSOPs, a walkthrough and a handover, so your team runs it without me.
Exhibit 4: Responsibilities (R responsible, A accountable, C consulted, I informed)
ActivityBusiness ownerSpecialist (me)Your team
Keep the contract register currentACR
Build the automationARI
Approve draft invoicesAIR
Month-end checks and sign-offACR

Source: sample plan

06Controls and checks

Exhibit 5: Control framework: 6 checks, status read from the files
CheckWhat it catchesStatus
No duplicate invoice keys in the scheduleOne contract, one period, one invoice. Stops double billing.PASS
Milestone % adds to 100% (C-103)Every dollar of the project is billed once.PASS
Milestone % adds to 100% (C-104)Same for the one-off project.PASS
Every retainer live in Nov has a cleared Nov invoiceNo retainer is missed in the month.PASS
Schedule rows held outside a contract windowExpired or unrenewed contracts are held, not invoiced.INFO
Month total = cleared + waiting rows for the monthThe summary agrees with the schedule.PASS

Source: sample files (Checks / Controls tab, test output or run log)

07What the files show

Exhibit 6: What the sample files show
  • 8 of 11steps automated
  • 5/5checks pass
  • 6contracts in the register
  • 3,000USD held, not billed

Source: sample files, measured by script. Sample data, not a client result.

  • No duplicate invoice keys in the schedule: PASS
  • Milestone % adds to 100% (C-103): PASS
  • Milestone % adds to 100% (C-104): PASS
  • Every retainer live in Nov has a cleared Nov invoice: PASS

08Risks and how the build handles them

Exhibit 7: Risks and mitigations
RiskMitigation built in
An invoice is raised twiceEvery row has a contract | period | milestone key; the check fails on any repeat
An expired contract keeps billingRows outside the contract window are held and counted
A milestone is billed earlyMilestone rows wait until the project task is marked done

Source: sample design

09Appendix: files

A. Screenshots of the deliverable

Automation map: each step from won deal to paid invoice, marked Auto, Review or Manual (control)

Exhibit 8: Automation map: each step from won deal to paid invoice, marked Auto, Review or Manual (control)

Source: sample files

Invoice schedule driven by the contract register, with a duplicate key on every row

Exhibit 9: Invoice schedule driven by the contract register, with a duplicate key on every row

Source: sample files

Checks tab: control checks with expected and actual values

Exhibit 10: Checks tab: control checks with expected and actual values

Source: sample files

Contract register: one row per signed contract with terms, billing day and renewal notice

Exhibit 11: Contract register: one row per signed contract with terms, billing day and renewal notice

Source: sample files

B. Downloadable files

FileWhat it isSize
Recurring-Billing-and-Contract-Tracker.xlsxWorkbook: contracts, milestones, invoice schedule, AR tracker, automation map, month summary and checks24.3 KB

Sample build: realistic data, built to show how I'd handle this work. Names are placeholders and no client data is used.

Have a similar problem in your business?

Hire me on Upwork (opens in a new tab)