Invoice Spreadsheet

About this template

What each sheet does

Three sheets: Quotation, Transaction statement, Billing & collection — quote before the deal, a delivery line per shipment, then track payment. They are linked by formulas. The register reads issue dates and billed amounts from the statement, and the statement reads the paid total back from the register, so there is less retyping than it looks.

Entry order

1. Row 5 of the Quotation takes the number (sample QT-2026-0118), the date and the validity; rows 7–13 hold supplier and customer details.
2. In the line-item table, rows 19–28, enter only item, spec, quantity (F) and unit price (G). Amount, subtotal, VAT and total are formulas, and the headline figure in row 15 is =H31. Rows 27–28 only show an amount once both quantity and price are present.
3. After delivery, fill rows 9–19 of the Transaction statement with date, item · spec, quantity and unit price; supply value, VAT and total compute per row.
4. On Billing & collection, rows 9–18, type only the document number and client, then keep column F (Paid) up to date. Issue date (C) and Billed (E) are formulas pointing at the statement — do not overwrite them.
5. The collection summary and the quarterly split below recalculate on their own.

How the formulas calculate

• Quotation: =F19*G19 → =SUM(H19:H28) → =ROUND(H29*0.1,0) → =H29+H30, giving 33,950,000 / 3,395,000 / 37,345,000 with the shipped data.
• The statement rounds VAT per row with =ROUND(F9*0.1,0), so it can differ from the quotation by a won or two. Its “Received to date” cell is =’Billing & collection’!F19, and the register’s header total is =’Quotation’!H31.
• Register balance =E9-N(F9) and status =IF(G9<=0,”Paid in full”,IF(N(F9)=0,”Unpaid”,”Partial”)); the three status words it writes are Paid in full, Unpaid and Partial, and the summary groups those exact strings with COUNTIF and SUMIF.
• Row 27, =ROUND(D26/1.1,0), assumes every billed amount includes VAT — mixed zero-rated work breaks it.
• The quarterly split in rows 31–32 is real: =COUNTIFS($C$9:$C$18,”>=”&DATE(YEAR(MIN($C$9:$C$18)),1,1),$C$9:$C$18,”<“&DATE(YEAR(MIN($C$9:$C$18)),4,1)) and the matching SUMIFS, with labels built by TEXT. Only two quarter rows exist, Q1 and Q2, so despite the note under the table an invoice issued in Q3 or Q4 lands in neither — add the rows before you bill past June.

Examples

• Quotation row 3 — Barcode scanner (industrial) / unit / 4 / 4,200,000 → 16,800,000 automatically.
• Statement row — 2026-03-02 / Barcode scanner (industrial) — 1st batch / 2 / 4,200,000 → 8,400,000 supply value, 840,000 VAT, 9,240,000 total.
• Enter 5,000,000 paid against a 9,240,000 invoice and the outstanding balance becomes 4,240,000, the status flips to “Partial”, and the summary follows.

Entries that break the file

• Typing “Paid in full” over the status formula, after which the summary stops matching the bank.
• Leaving the sample formulas in the Paid column — several rows ship holding =E9 or =ROUND(E12*0.55,0) — and noting the real amount beside them. Overwrite the cell itself.
• Keeping the VAT row on zero-rated work; set it to zero and note why.
• Sending the register out with the internal “How to fill in” rows (36–41) still visible.