What's in the workbook
- Profit and Loss: twelve monthly columns and a total, with income, cost of sales, gross profit and margin, operating expenses, net profit and margin. Rename the account lines to match your chart of accounts.
- Balance Sheet: this month and last month, from bank and debtors through to equity, with a check that net assets equal equity.
- KPIs: income, gross and net margin, debtor days, creditor days, current ratio and the change in your bank balance, for whichever month you pick.
- Commentary: five prompts (headline, income, costs, cash and debtors, actions) so the reader gets the story, not just numbers.
Yellow cells are inputs. Everything else is a formula, so you can see exactly how each figure is worked out.
How to fill it from Xero
- In Xero, go to Reporting > All reports and open the Profit and Loss. Choose the months you want (the ready-made format "Current financial year by month" is handy) and export to Excel.
- Copy the figures into the yellow cells on the Profit and Loss sheet, oldest month in Month 1 (Xero can list the newest month first). If you have more accounts than lines, insert rows inside a section so the totals pick them up.
- Run the Balance Sheet for the end of this month with last month alongside, export it, and copy those figures in too.
- On the KPIs sheet, enter which month is "this month", then add your commentary.
It's a general template, so check the figures and formulas suit your business before relying on them. Debtor and creditor days are rough monthly estimates; debtors include VAT or GST, so debtor days will read a little high.
Skipping the export step
If you send the same reports every month, the exporting and pasting is the dull part. Reportpost emails the Xero Profit and Loss, Balance Sheet and other standard reports on a schedule, as PDF or Excel, with comparison columns and an optional cover page on the PDF. The Excel version can feed a workbook like this one. See also the free cover page generator and the month-end checklist.