Reports on Xero data
Each report is one formula on live data. Type it in a cell; when a formula refers to A2, list the values in column A first (for example with =list.<table>(<column>)) and fill the formula down.
=data.invoices.sum(amount_due_base_currency, ageing, A2) Receivables ageing. Open invoices in the buckets accountants use (Current, 1-30, 31-60, 61-90, Over 90), in your base currency. Put a bucket name in A2.
=data.invoices.sum(amount_due_base_currency, payment_status, "Overdue") Overdue invoices. Amount overdue, converted to the base currency with each invoice's own rate.
=data.profit_and_loss_by_month.sum(profit_impact, months_ago, 0) Net profit this month. From the profit and loss by month: months_ago 0 is the current month, 1 the month before.
=data.profit_and_loss_by_month.sum(profit_impact) Net profit, last 12 months. The rolling twelve-month result in one cell.
=data.balance_sheet_by_month_end.sum(balance, section, "Bank", months_ago, 0) Cash at bank at month end. Bank balances from the balance sheet by month end.
=data.invoice_lines.sum(line_amount_excl_tax, item_code, A2) Sales by item. Invoice lines per item code, before tax.
=data.invoice_lines.sum(line_amount_excl_tax, tracking_1_option, A2) Sales by tracking category. Revenue per tracking option (a region, a project, a department).
=data.bills.sum(amount_due_base_currency, payment_status, "Overdue") Bills overdue. Supplier bills past due, in the base currency.
Build it on your own data
Invoices, bills, bank transactions and your monthly profit and loss become tables that formulas, pivots and charts read directly, in your base currency.