# Financial Statements (P&L + Cash Flow) **Date:** 2026-06-01 **Status:** Draft **Spec:** 170 **Tier:** Business+ **Depends on:** `invoices-core`, `expenses-module`, `partial-payment-recording`, `bad-debt-writeoff`, `multi-currency`, `foundation-auth-rbac` **Referenced by:** `israeli-tax-reports`, `bank-statement-import`, `admin-reports-analytics` --- ## Overview Provides Profit & Loss (P&L) and Cash Flow statements for the tenant's business. These are not accounting-grade financial statements (no double-entry bookkeeping) but management-level financial summaries derived from invoices, expenses, and payment records. Suitable for personal use by freelancers and small businesses, and as source data for their accountant. --- ## P&L Statement ### Structure ``` ┌──────────────────────────────────────────────────────────────┐ │ Profit & Loss [Year: 2026 ▾] │ │ [Export PDF] [Excel] │ │ │ │ Revenue │ │ ─────────────────────────────────────────────────────────── │ │ Gross revenue (invoiced) ₪ 420,000 │ │ Less: Bad debt write-offs (₪ 12,700) │ │ Less: Credit notes issued (₪ 8,500) │ │ ─────────────────────────────────────────────────────────── │ │ Net revenue ₪ 398,800 │ │ │ │ Expenses │ │ ─────────────────────────────────────────────────────────── │ │ By category: │ │ Office & Equipment ₪ 12,400 │ │ Software & Subscriptions ₪ 8,200 │ │ Travel ₪ 6,800 │ │ Professional Services ₪ 22,000 │ │ Marketing ₪ 4,400 │ │ Other ₪ 1,200 │ │ ─────────────────────────────────────────────────────────── │ │ Total expenses ₪ 55,000 │ │ │ │ ─────────────────────────────────────────────────────────── │ │ Gross profit ₪ 343,800 │ │ Gross margin 86.2% │ │ │ │ Contractor payouts ₪ 48,000 │ │ ─────────────────────────────────────────────────────────── │ │ Net profit (before tax) ₪ 295,800 │ │ │ │ ⓘ Excludes personal income tax and national insurance. │ │ For tax preparation, consult your accountant. │ └──────────────────────────────────────────────────────────────┘ ``` ### Data sources | P&L line | Data source | |----------|-------------| | Gross revenue | `SUM(invoices.total)` WHERE `status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID')` AND `tax_issue_date` IN period | | Credit note deductions | `SUM(ABS(invoices.total))` WHERE `source = 'credit_note'` AND `tax_issue_date` IN period | | Bad debt write-offs | `SUM(invoices.total)` WHERE `bad_debt_at` IS NOT NULL AND `bad_debt_at` IN period | | Net revenue | gross_revenue − credit_notes − bad_debts | | Expenses by category | `SUM(expenses.amount)` WHERE `status = 'COMPLETED'` AND `expense_date` IN period, grouped by `expense_category` | | Contractor payouts | `SUM(payout_bills.net_amount)` WHERE `status = 'PAID'` AND `paid_at` IN period | | Gross profit | net_revenue − total_expenses | | Net profit | gross_profit − contractor_payouts | **Multi-currency:** all ILS amounts used. Foreign currency invoices use `invoices.total_ils`. Expenses with `original_currency` use `expenses.amount` (the ILS equivalent already stored). ### Period options - Monthly (single month) - Quarterly (Q1–Q4) - Annual (full year) - Custom date range Comparison mode: show current period vs. previous period (same duration) side-by-side with % change. --- ## Cash Flow Statement Tracks actual money movement (received and paid) vs. accrual-basis revenue recognition. ``` ┌──────────────────────────────────────────────────────────────┐ │ Cash Flow [Jun 2026 ▾] │ │ [Export] │ │ │ │ Cash from operations │ │ ─────────────────────────────────────────────────────────── │ │ Payments received from customers +₪ 85,400 │ │ Expenses paid −₪ 12,200 │ │ Contractor payouts −₪ 8,000 │ │ ─────────────────────────────────────────────────────────── │ │ Net operating cash flow +₪ 65,200 │ │ │ │ Outstanding receivables (end of period) │ │ ─────────────────────────────────────────────────────────── │ │ Invoices TAX_ISSUED (unpaid) ₪ 48,300 │ │ Invoices PARTIALLY_PAID (balance due) ₪ 7,200 │ │ ─────────────────────────────────────────────────────────── │ │ Total receivables ₪ 55,500 │ │ │ │ ⓘ Cash flow based on actual payment records. │ │ Comparison against bank statement: import statement → │ └──────────────────────────────────────────────────────────────┘ ``` ### Data sources | Cash flow line | Data source | |----------------|-------------| | Payments received | `SUM(invoice_payments.amount)` WHERE `paid_at` IN period | | Expenses paid | `SUM(expenses.amount)` WHERE `status = 'COMPLETED'` AND `expense_date` IN period | | Contractor payouts | `SUM(payout_bills.net_amount)` WHERE `paid_at` IN period | | Net operating cash | received − expenses − payouts | | TAX_ISSUED receivables | `SUM(invoices.total)` WHERE `status = 'TAX_ISSUED'` (point-in-time) | | PARTIALLY_PAID balance | `SUM(invoices.total - invoices.amount_paid)` WHERE `status = 'PARTIALLY_PAID'` | --- ## API ``` GET /api/reports/pl → P&L statement data query: { from: YYYY-MM-DD, to: YYYY-MM-DD, compare?: boolean } Response: { period: { from, to }, revenue: { gross, credit_notes, bad_debts, net }, expenses: { total, by_category: Record }, contractor_payouts: number, gross_profit: number, net_profit: number, gross_margin_pct: number, comparison?: { ... same structure for comparison period ... } } Requires: reports:read GET /api/reports/pl/xlsx → Excel download Requires: reports:export GET /api/reports/cashflow → Cash flow statement data query: { from, to } Response: { received_from_customers: number, expenses_paid: number, contractor_payouts: number, net_operating: number, receivables: { tax_issued: number, partially_paid_balance: number } } Requires: reports:read GET /api/reports/cashflow/xlsx → Excel download Requires: reports:export ``` --- ## Performance — supporting indexes P&L and cash-flow scan invoices, expenses, payments, and payouts over arbitrary per-tenant date ranges; the yearly tax reports (specs 171, 175) hit the same paths. Specify covering indexes so these are range scans, not seq scans: ```sql CREATE INDEX idx_expenses_report ON expenses(tenant_id, status, expense_date); CREATE INDEX idx_invoice_payments_rpt ON invoice_payments(tenant_id, paid_at); CREATE INDEX idx_payout_bills_rpt ON payout_bills(tenant_id, status, paid_at); CREATE INDEX idx_invoices_report ON invoices(tenant_id, status, tax_issue_date); ``` (`invoice_payments.tenant_id` is denormalized for this index; if absent, index on `(invoice_id, paid_at)` and join.) --- ## Shared export utilities (xlsx + CSV) — used by specs 170, 171, 175 All financial Excel/CSV exports go through one shared writer to guarantee two properties: 1. **Hebrew/RTL correctness:** every worksheet sets `worksheet.views[0].rightToLeft = true` and an explicit Hebrew-capable font (Arial/David) on header + data cells. Without this, Hebrew labels render LTR/garbled in Excel. 2. **Formula-injection neutralization:** any cell whose string value begins with `=`, `+`, `-`, `@`, tab (`\t`), or carriage-return (`\r`) is prefixed with a single quote (`'`) before writing. This matters because attacker-influenced free text — notably bank-transaction `description` (payer memo, via `bank-statement-import`) flowing through expenses, and customer/vendor names — reaches xlsx/CSV cells and would otherwise execute as a formula when the recipient opens the file. (The prior security audit covered email-template XSS only, not spreadsheet export injection.) The PCN874 CSV additionally requires CP1255 output encoding (see spec 171). --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | Management accounts | Not double-entry bookkeeping | Full bookkeeping requires journal entries, trial balance, chart of accounts — scope beyond Zync v1; management P&L is useful and achievable | | Accrual for P&L, cash for cash flow | Standard accounting distinction | P&L = when invoiced (earned); cash flow = when paid; both views are needed for a freelancer to understand their business | | Gross margin shown | Not EBITDA or other metrics | Gross margin is the primary KPI for freelancers; EBITDA requires depreciation/amortization data not tracked in Zync | | No tax provision | Not computed | Personal income tax in Israel depends on personal circumstances, deductions, credits, and national insurance contributions — not computable from invoice/expense data alone | | Business+ tier only | Not Freelancer | Single-freelancer use case on Freelancer tier is served by the simpler revenue and expense reports; P&L requires multi-source aggregation appropriate for Business+ multi-user contexts | | Payout expense uses `net_amount` | Not `payout_bills.amount` | Payout expense lines use `payout_bills.net_amount` (post-withholding amount the contractor actually receives) for P&L and cash flow consistency; matches the cash flow section and spec 175 (`bituach-leumi`) |