Transaction Statement Excel Template — Auto-Calculate Totals and Print

Manually writing transaction statements after every delivery invites calculation errors and makes it hard to track past records. This template auto-calculates supply amounts, VAT, and totals the moment you enter item details, and produces a print-ready statement instantly.

Key Features

🧾 Auto-Generated Statements

• Enter item name, specification, quantity, and unit price → amount, VAT, and total auto-calculated
• Up to 20 line items per statement
• Supply total, tax total, and grand total computed automatically
• Remarks field for delivery terms and payment conditions

🏢 Client Master Management

• Register client name, representative, business registration number, business type, address, and contact
• Select a client code when drafting a statement to auto-fill details
• Store up to 100 client records

📊 Transaction Ledger

• Auto-logged history of every issued statement by date
• Monthly and per-client transaction summaries
• Receivables tracking with payment status checkbox

🖨️ Print-Ready Layout

• A4 format optimized for printing
• Supplier and buyer information auto-populated
• Seal and signature fields included

How to Use

Step 1: Set Up Company Information

Enter your company (supplier) details in the ‘Settings’ sheet. This information appears automatically at the top of every statement.

Step 2: Register Clients

Add your major clients in the ‘Clients’ sheet. Once registered, you can select them from a dropdown when creating statements.

Step 3: Create a Statement

In the ‘Statement Entry’ sheet, select a client and enter item details. All amounts are calculated automatically.

Step 4: Print and Send

Review the finished statement on the ‘Print’ sheet and print it. Retain one copy each for supplier and buyer.

Tips

Use It for Month-End Reconciliation

The ‘Ledger’ sheet shows per-client monthly transaction totals. Use this as a reference when issuing tax invoices or reconciling accounts at month-end.

Track Receivables Consistently

Check off payments in the ledger to see outstanding receivables at a glance. Unpaid items are automatically highlighted in red.

Best Practices

Standardize Item Names Across All Statements

If the same product appears as “Widget A” on one statement and “Widget Type-A (Standard)” on another, your Ledger sheet becomes impossible to sort and summarize. Maintain a consistent product naming convention and use the item dropdown to select from a predefined list whenever possible. Consistent names make monthly reconciliation and per-product revenue analysis far more reliable.

Issue Statements on the Same Day as Delivery

Generate and send the transaction statement on the delivery date, not days or weeks later. Late-issued statements create confusion about payment due dates and make month-end reconciliation harder for both parties. The auto-generated statement number includes the issue date, so issuing on the delivery date keeps your records chronologically clean.

Reconcile the Ledger with Bank Deposits Weekly

Do not wait until month-end to check which invoices have been paid. Every Friday, open the Ledger sheet and check off payments that match your bank deposits. This weekly discipline ensures the receivables highlighted in red are genuinely outstanding, and you can follow up on overdue payments while they are still fresh — collection rates drop sharply after 60 days.

FAQ

How do I handle tax-exempt items?

Select ‘Tax-exempt’ in the tax category column on the ‘Statement Entry’ sheet. VAT for that item will be set to zero.

Are statement numbers assigned automatically?

Yes, sequential numbers are auto-generated based on the issue date in the format ‘TS-YYYYMMDD-001’.

Can this replace a tax invoice?

Transaction statements and tax invoices are separate documents. You must issue an electronic tax invoice separately for VAT reporting purposes.

How do I handle partial deliveries or split shipments?

Create a separate statement for each partial delivery, referencing the same purchase order number in the Remarks field. Each statement will have its own unique auto-generated number but can be traced back to the original order. At month-end, the Ledger sheet groups all statements by client, so partial deliveries are consolidated in the monthly summary automatically.

Can I issue statements in foreign currencies?

The template calculates amounts in your base currency by default. To issue a statement in a foreign currency, change the currency symbol in the Settings sheet and enter prices in the foreign currency. Note that you will need to handle exchange rate conversion separately for your own accounting records. Add the applied exchange rate in the Remarks field for reference.