Pro workbook: Invoice, Register, Clients, Items, Setup, Formulas. Sample data, real formulas.
| INVOICE | Invoice no. | 2026-037 | |||
| Fathom Studio BV | Date | 11 Sep | |||
| Unit 4, The Foundry, Springfield ST 00000 | Terms (days) | 30 | |||
| [email protected] · +1 000 000 0000 | Due | 11 Oct | |||
| VAT / tax ID: 00000000 | |||||
| Bill to | |||||
| Client | Northwind Bank | ||||
| Address | 1 Harbour Row, Springfield ST 00000 | ||||
| [email protected] | |||||
| Reference | Onboarding redesign — sprint 3 | ||||
| Description | Qty | Unit price | Amount | ||
| Product design, day | 6 | 950.00 | 5,700.00 | ||
| Engineering, day | 4 | 1,100.00 | 4,400.00 | ||
| Workshop, half day | 1 | 600.00 | 600.00 | ||
| Travel (at cost) | 1 | 212.40 | 212.40 | ||
| Subtotal | 10,912.40 | ||||
| 5.0% | Discount | -545.62 | |||
| 20.0% | Tax | 2,073.36 | |||
| Total | 12,440.14 | ||||
| Amount due by | 11 Oct | ||||
| Payment | |||||
| Bank: Example Bank · IBAN XX00 0000 0000 0000 · BIC EXAMPLEX · Reference: the invoice number | |||||
| Thank you. Late payments accrue 2% per month after the due date. |
| Invoice register | ||||||||||||
| One row per invoice sent. Outstanding, days overdue and the aging buckets are calculated. | ||||||||||||
| Today | 2026-09-11 | Type =TODAY() to keep it live | ||||||||||
| Number | Date | Client | Total | Paid | Outstanding | Due | Days overdue | Status | Aging | Outstanding | Invoices | |
| 2026-031 | 2026-07-02 | Halden Health | 8,400.00 | 8,400.00 | 2026-07-16 | Paid | Not yet due | 31,690.14 | 3 | |||
| 2026-032 | 2026-07-15 | Northwind Bank | 12,650.00 | 12,650.00 | 2026-08-14 | Paid | 1–30 days | 6,800.00 | 1 | |||
| 2026-033 | 2026-07-30 | Civic Data Trust | 5,100.00 | 5,100.00 | 2026-09-13 | Paid | 31–60 days | 0.00 | 0 | |||
| 2026-034 | 2026-08-20 | Tessera Logistics | 9,900.00 | 4,950.00 | 4,950.00 | 2026-09-19 | Part paid | Over 60 days | 0.00 | 0 | ||
| 2026-035 | 2026-08-20 | Halden Health | 6,800.00 | 0.00 | 6,800.00 | 2026-09-03 | 8 | Overdue | Total outstanding | 38,490.14 | ||
| 2026-036 | 2026-08-28 | Northwind Bank | 14,300.00 | 0.00 | 14,300.00 | 2026-09-27 | Open | Invoiced this year | 69,590.14 | |||
| 2026-037 | 2026-09-11 | Northwind Bank | 12,440.14 | 0.00 | 12,440.14 | 2026-10-11 | Open | Collected this year | 31,100.00 | |||
| Next number | 2026-038 | |||||||||||
| The current invoice's total comes from the Invoice sheet; once sent, overtype it with the number so it stays fixed. | ||||||||||||
| Next number assumes the pattern YYYY-NNN and that this year started at 031 — change the +31 to your starting count. | ||||||||||||
| Clients | |||
| Pick a client on the invoice and the address, email and terms fill in from here. | |||
| Client | Address | Terms (days) | |
| Northwind Bank | 1 Harbour Row, Springfield ST 00000 | [email protected] | 30 |
| Halden Health | 22 Ward Street, Springfield ST 00000 | [email protected] | 14 |
| Tessera Logistics | Unit 9, Depot Road, Springfield | [email protected] | 30 |
| Civic Data Trust | Town Hall Annex, Springfield | [email protected] | 45 |
| Items | |
| Things you sell, with a default price. Pick one on the invoice and the price fills in; overtype it for a one-off. | |
| Item | Unit price |
| Product design, day | 950.00 |
| Engineering, day | 1,100.00 |
| Research, day | 850.00 |
| Workshop, half day | 600.00 |
| Design system licence | 2,400.00 |
| Travel (at cost) | 0.00 |
| Setup | |
| Tax rate and defaults. | |
| Tax rate | 20.0% |
| Default terms (days) | 30 |
| Set the tax rate to 0 if you don't charge it; the Tax line then shows 0.00 and the total equals the subtotal less discount. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Invoice · Address / Email / Terms | INDEX(Clients!$B$5:$B$24, MATCH($B$8, Clients!$A$5:$A$24, 0)) | Finds the chosen client on the Clients sheet and returns the column beside it. IFERROR shows blank until a client is picked. |
| Invoice · Unit price | INDEX(Items!$B$5:$B$24, MATCH(A16, Items!$A$5:$A$24, 0)) | Default price for the item picked from the dropdown. Type a number over it for a one-off price. |
| Invoice · Amount | IF(OR(A16="",C16=""),0,N(C16)*N(D16)) | Qty × price; empty lines give 0, hidden by the format. |
| Invoice · Discount / Tax | -ROUND(F29*D30,2) / =ROUND((F29+F30)*D31,2) | Discount off the subtotal; tax on the discounted amount. Both rounded to cents so the total is what a bank will accept. |
| Invoice · Due | F2+F3 | Invoice date plus the client's terms. |
| Register · Outstanding | N(D6)-N(E6) | Total minus paid. |
| Register · Days overdue | IF(F6<=0,0,MAX(0,$B$3-G6)) | Days past the due date, only while something is still owed. |
| Register · Status | IF(F6<=0,"Paid",IF(H6>0,"Overdue",IF(N(E6)>0,"Part paid","Open"))) | Nested IF in order of certainty: nothing owed → Paid; past due → Overdue; some paid → Part paid; else Open. |
| Register · Aging buckets | SUMIFS(F6:F205, H6:H205, ">=1", H6:H205, "<=30") | Outstanding amounts whose days-overdue fall in the bucket. |
| Register · Next number | TEXT(YEAR($B$3),"0")&"-"&TEXT(COUNTIF(A6:A205,"2026-*")+31,"000") | Counts this year's invoices and formats the next sequence number as YYYY-NNN. |