For most annual accounts reporting, the key reports will be the annual income and expenditure and a balance sheet that is consistent with the net income and shows the change from the previous year.
For month to month financial management, an actual vs budget report will be the principal monitoring tool. Donors may also want reports on the use of their funds, some of which relates to the money received and spent. The time intervals for these will not necessarily coincide with the financial year.
Note that reports are customisable, see “customising-reports”. Also that you can make a report a favourite by clicking on the star icon. This brings that report into the report list shown when you click “Accounting”.
I have a short section on UK Charities SORP-compliant Statement of Financial Affairs (SOFA) and related reporting, following some guidance on the typical reports and how to produce them.
Actual vs Budget
There is a standard and usable “Budget Variances” report under Accounting/Reports/Financial/More reports. (You can pin it to the “Accounting” menu by clicking on the star next to it.) For me, it is a bit cluttered and it is also good to have the full year budget visible as a column. So there is a quick guide under “Customising reports” to creating an alternative report.
My version has the default view limited to Funding types “Restricted” and “Unassigned” so as to exclude Client account payments passing through our bank account.
Variances and what to make of them
The main point of the “Actual vs Budget” report is to identify deviations from the expected budget via the “Variance” column. To avoid misleading numbers, be sure that the period you select is for complete months only (i.e. avoid ending part-way through a month). We are looking for the larger negative or positive variances with a view to understanding what lies behind them.
If the budget was realistic as to timing, the most straightforward message from variances is that we are receiving or spending more or less than expected. If this comes as a surprise, look at the details of the related expenditure to figure out what is going on (e.g. by clicking the related expenditure figure).
But a variance may also be due to the timing of the revenue or expenditure not being as predicted by the budget (without any implications for total expenditure during the year). Time spent investigating such cases is wasted time. So try to ensure that timings in the budget are realistic. Using the previous year’s expenditure as a starting point, as mentioned under “Budget”, is a good first approximation. If you think the revenue timing should be predicable from the previous year’s outcome – not so likely as for expenditure, I imagine – , then do the same to get your revenue budget.
Revenue and expenditure statement
This is a Profit and Loss report by another name. I suggest using the new style Profit and Loss report. You can customise it not least in order to change the wording to that more suitable to a charity, e.g. “Revenue and expenditure” and “Surplus/deficit” instead of “Profit”.
Save as a custom report in order to retain the customisation and if you check “Make custom report default”, then this report will pop up if you select the new style “Profit and Loss” report from the reports menu.
You might also have the default view limited to Funding types “Restricted” and “Unassigned” so as to exclude Client account payments passing through bank accounts.
This report can be found at Accounting/Reports/Financial/Balance Sheet (New).
This is a listing of the value, on the selected date, of all bank accounts (and other assets), along with money owing to others or owed to you. The difference in the net worth of the organisation from the previous year should be the net income from the income and expenditure statement.
If you have been reconciling all bank transactions appropriately and making correct entries for items that do not immediately affect cash holdings (e.g. invoices, bills, pay runs, depreciation), then the balance sheet automatically produced by Xero should be a true representation of your assets, liabilities, and hence net worth.
It is important, then, that you check the year-end values for all items in the balance sheet with their true values for that date. If they all check out, then you have your year-end accounts. If they don’t check out, then you need to run down the source of the error and create or edit entries in Xero accordingly.
Generally, to check items in the balance sheet, you are going to start with examining the year’s transactions behind the closing figure by clicking on the blue number in the balance sheet report. (Tip: if you right click to Open in new tab, then it is quicker to go back to the balance sheet again or flip between views.)
See the “Fixed Assets” section on registering and running depreciation of fixed assets. Note, though, that if you are fully depreciating assets at the time of their purchase, then those assets do not need to go on a register and their cost is treated like any other immediate expense. (Strictly speaking this may not be best practice, but as long as it is unlikely that the asset will subsequently be sold, then this is simpler and quite appropriate.)
Assets are shown in the balance sheet with their original values and then accumulated depreciation shown by asset type. Clicking on the original value line will show only changes (additions and disposals) made during the year. Clicking on the accumulated depreciation will show the depreciation applied during the year, either automatically via a “Run depreciation” action in Xero or via a manual journal entry.
These should be the easiest to verify, at least if you have been prompt and thorough in reconciling transactions.
If there is any difference between what Xero has as the balance and your bank or other account shows, then track down what item or items are responsible for the difference.
A simple reconciliation gap can be identified by running a “Reconciliation report” on the account. See the link to do this below. This, of course, show gaps up to the present day, not the last financial year end; but if anything is hanging over from the last year, it should show.
The difference in values itself may be a clue to what is wrong. The ultimate recourse is to get CSV exports from both Xero and the bank and compare the two.
For Accounts Receivable and Payables, Xero offers “Aged Receivables” and “Aged Payables” reports. If you want to check these are correct, you probably want to use the “Detail” version of these reports. In the main you are checking that those listed have really not been paid (i.e. no pending or misallocated transaction reconciliation) and that there are not bills or invoices which should have been entered by the year end but are missing from the list.
For Current Asset or Creditor Accruals, the report generated by clicking on the value shown in the Balance Sheet should suffice. What you are expecting to see is that last year’s balance was paid or accrued down and this year end balance is the sum of all the accruals that you know have occurred and/or would normally expect.
For example, for Creditor Accruals, you should see Debit entries totalling the year start balance and the year end balance being the sum of new credit entries, which you are confident are complete and correct. If the sum of debit entries does not total the starting balance, you should identify what is accounting for the difference and satisfy yourself that it really does remain outstanding.
Employee-related payables are Wages, PAYE tax, NI contributions, and pension payments. Where payments relating to the last pay run have been made before the year-end, then the balance should be zero. Otherwise the amounts should be the values generated by the last pay run. (It is possible that wages for some but not all staff have been paid. In such a case, only the wages not yet paid by year-end should show as the balance.)
If the balances are not so easily explained, then use a process similar to that described above for Accounts payable to identify unmatched payables or payments.
You can create reports for income and expenditure to date in the financial year by using your normal total revenue and cost report (which either is or will have been based on the standard “Profit and Loss” report and accessed via the “Accounting” menu) and filtering it via “Filter” for the project(s) in question, thus:
Clicking “Filter” brings up the one or two tracking categories you could filter by:
Click on one to get the options for that category:
Select the project(s) you want a report for and press “Apply”.
The filters to be applied are then shown thus:
If you want to cancel one, just click the ‘x’ to the right of the name. You can add further filters from the other category by just repeating the process for that category.
When you are done choosing filters, press “Update” and the report will show income and expenditure for the period only relating to that project (or those projects).
You can Save as a Custom Report or Export via the respective buttons at the bottom right of the page. If saving, remember: use a different name from the base report and DO NOT make the report the default report for Profit and Loss.
If editing the layout, be sure to “Save as ..” before quitting, otherwise the changes will not endure. If meaning to create a new report, rather than simply update the main one, then make sure to change the name of the report.
If you check more than one project for filtering, you will get the figures for all those projects combined. This is unlikely to be what you want. It won’t show you the figures for each project separately. To do that, edit the layout, as in the example under “Customising reports” below, in order to have a column for each project. Personally, I find it helpful to save as a permanent report one showing all current projects and the totals for all those projects. It gives management and trustees an overview of what is happening with all restricted funding. (Note that you can get a report for all active projects plus unassigned (untracked) revenue and expenditure under the old-style Profit and Loss by choosing the “Compare Donor/Projects” option. However this does not provide the flexibility of options that customising the new style report does; hence the advice to do the latter. It is, though, a very painless way of seeing all income and expenditure by active project either for the current year (the default) or for another period, selected by then going to “Date Range”. If that is all you need, then don’t bother with customising reports as advised below.)
Note that Xero reports created for a particular date range type sometimes do not work well for other date range types. You have to change the date range in the report editor and then save as a different report if you want to be sure of correct reporting for a different date range type.
If you are allocating costs to projects via Manual Journal entries, note that single entries in the future will be in reports covering the journal entry date, but repeating entries (e.g. each month or quarter) will not appear in reports until the date for the entry has passed.
The “new” style reports in Xero allow quite a lot of helpful report customisation. You can customise the language of the reporting to make it more suitable to your activities. For example, changing out terms like “profit & loss”, “sales”, “cost of sales” if these are inappropriate.
More importantly, you can change rows and columns displayed and, for example, then get a totals row or column for a customised set of data.
Three important things to be aware of:
- An edited report does not stay as edited unless you save it via “Save as”. So if you want to use the new report again do save it as a Custom or Draft report. Unless you are sure that the edit is intended to change the base report, do not use the same name for the report as the base report name.
- If the base report is set up for reporting in relation to a financial year, then customised versions trying to report for custom dates spanning financial years won’t work. You will need instead to use custom date range options available in the old style reports and “Save as Draft” from there. See “Custom date ranges for project expenditure” as an example below.
- If you start with a filtered report (say, Restricted Funding Type) and then edit the report (for, say, specific projects), you will lose the original filter as soon as you press Done for the editing. Up to that point the editor will show the filter applied as a report sub-heading but the resulting report will not show it and it won’t be applied. (Hence the difficulty in compiling matrix reports using one tracking category for each access. See Matrix report using tracking categories.)
We can illustrate customisation via showing how to add or delete a project from an “Active projects YTD” report.
Active Project YTD actuals report
Start by using as the base report the overall Revenue & Expenditure (“Profit and Loss”) report. Click the “Edit layout” link at bottom left. Change the title at the top to one you think suitable (here it is “Project Actuals YTD”) and then click on the column shown (here headed “2020”). At the right are shown details for the column, including the type of “Date Range” and the name. To change the name, double click in the column header itself (the blue cell) and edit the name.
Now go right to the top and click the column icon to add a new column:
Choose the “Donor/Project” option. Then in the righthand pane, click the drop down for “Donor/Project” and select one of the projects you want included in the report, as below.
Add further projects in the same way. Then to create a total for all those projects, create a final Donor/Project column and then in the drop down, check all the projects that you want included in the total. Note that you can edit the column names via clicking the the (blue) cell of the selected column. You can also change the order of the columns via drag and drop in the main panel.
Now delete the original “Date” column (here headed “2020”) by selecting it and then clicking the trash icon in the top bar.
Then press “Done” at right on the top bar. VERY IMPORTANT: now save the report as a custom report via the “Save as.. Custom” at bottom right of the page. Give the report a suitable name (probably the same as the report title) and DO NOT check “Make custom report the default”, as that would make this the default Revenue & Expenditure report.
You can add projects or subtract them from this report by bringing up the report from Accounting/Reports/Custom and selecting the report (or directly from Accounting if you have made it a favourite), then clicking on the Edit button at bottom left.
Note that any change in the columns will NOT CHANGE the values in the “All Projects” column. You thus need to select that column and adjust projects to be included in that column. Hence every time you make a change to the projects shown, the “All Projects” set of projects needs to be adjusted.
When you have finished editing, press the “Done” button at top right, then, if you want to keep the format, “Save as” at bottom right. Choose a new name if you want to create a separate report from the original (retaining the original report); otherwise keep the existing name.
Matrix report using tracking categories
SORP Oct 2019 has the following example analysis on p 48:
This kind of report is not difficult if the Activities mapped are tracked via a tracking category and the analysis can be computed via Account codes and grouped appropriately. But if Activities are tracked via one tracking category and the column entries by the other, then I have not found an easy way of getting out such a report and in fact the new reports do not even let you filter for one tracking code while having columns of data for each code of the other category. You can do that in the old Profit and Loss reports by selecting the “Date range” tab, which provides an “options” button where you can filter by values of both tracking categories.
Having done that, produce and export as an Excel spreadsheet the data for all codes in one category when filtering by one code in the other. Then compile the analysis you need by combining the data in all the exported sheets appropriately.
For example, for the expenditure analysis in Table 3 above, first get the “Total” entries for each project (Activity) by selecting “Do not filter” for Funding Type and “All projects” for the Projects tracker, as below:
Update the report and export as Excel. Delete columns (projects) with Zero expenditure and “Unassigned”. Copy and Paste as “123” all remaining Total Expenses row so that you can then delete all other rows in the table. Then repeat this process with each of the values (including “Unassigned”) for Funding type, and entering the Total Expense values for each project (and total for all projects), one row for each funding type, in the first unfiltered spreadsheet.
You may notice that if you play with date ranges on the Project Actuals YTD report, you only get results for the FY in which the later date resides.
Here is a way to get custom date reports. You will need to start with the “old style” Profit and Loss report found via Accounting/Reports/Financial Statements / Profit and Loss. Then click on the three dots by Profit and Loss and scroll down the pop-up menu until you see “View an older version of this report”. That’s the report you want.
Click the “Show Date Range” tab to provide a view not only allowing a custom date range but also showing “More options” via which you can filter the figures for the desired project:
You can then set the date range and the project for the report:
Notice that you can only select one project for this filtered report. The result on pressing “Update” will be the total for each line of the Revenue & Expenditure report over the selected period, shown with a column heading (somewhat misleadingly) being the last date of the period. Neither the heading nor, for example, the title “Profit and Loss” are editable in this style of report but you can export to Excel and make the changes there.
Save as Draft via the button at bottom left if you want to keep the report for use at another time. I would suggest saving your first such report as a “template” for custom date reports with an appropriate name. Although it does not come up quite as when saved, it provides an easier starting place than if you have to remember how to start from scratch.
You can get a custom date report for all projects plus unassigned revenue and costs by FIRST choosing the “Compare Donor/projects” on the “Common Formats” tab and THEN changing the date range (but not if you set the date range first).
Actual vs Budget report
As noted elsewhere, you might want a cleaner Actual vs Budget report than the standard “Budget Variance” report. Here is my suggestion. Start with the new style Profit and Loss report
Start editing this report via the “Edit layout” button at bottom left. Change the title at the top to one you think suitable (here it is “Actual vs Budget”) and then click on the column shown (here headed “2020”). At the right are shown details for the column, including the type of “Date Range” and the name. To change the name, double click in the column header itself (the blue cell) and edit the name.
Now go right to the top and click the column icon to add a new column:
Choose the “Budget” option and select “Year to date” as the date range. You probably want to edit the column name to something shorter than the default name. Here I chose “YTD Budget”.
Add a third column of type “Formula”. Then insert the formula of the first column minus the second. I have named this column “Variance”.
Finally a fourth column, this time a Budget type and the date range set to “Financial Year”. Edit the name if you want it changed. You should now have four columns looking like the shot below.
Press “Done” to see the resulting report. Assuming you have the “Date Range” for the report set as “This Financial Year”, the YTD Budget figures will show (weirdly) the same as the full year budget. Set the date range to This Month, “Update” and you will get the figures you want.
Remember to save as a custom report otherwise you will lose all this good work. Saving allows you also to change the name of the report (from the original “Profit and Loss”). If you check “Make custom report default”, then this report will pop up if you select the new style “Profit and Loss” report from the reports menu. Up to you whether you want this or not.
SOFA and Related Reporting (UK)
I reproduce here 2 tables from the UK Charities SORP (FRS 102). Both are obligatory requirements, at least in their general intent – not to the letter of the detail.
Table 2 shows the requirements for a Statement of Financial Affairs (SOFA), except that small charities (less than £500k income) have the option not to show expenditure by activity (e.g. Raising funds vs Charitable activities) but instead to only show it by type of expenditure (e.g. Salaries, rent, etc.).
Obviously, a typical Revenue and Expenditure statement generated in Xero will show expenditure by type (Accounting code) not by activity. So if you feel you should show expenditure by activity, then the most practical approach is probably to use different accounting codes for different types of activity (e.g. have separate normal Salaries code and Salary for fund raising code) and group the presentation of the accounts accordingly. Some costs may always fall into a particular category and these cases don’t change anything in accounting practice – only in presentation; but the example of splitting Salary costs between different categories does make the accounting harder. Often it may not even be possible to allocate costs appropriately in reconciling transactions – or may in practice result in lots of errors; in these cases doing the allocation via journal entries in retrospect is probably simplest and safest.
If splitting expenditure by activity is just a case of allocating, say, one third of the manager’s cost to Raising Funds, then it is probably easiest and safest to do this manually, after getting the relevant data out of Xero rather than within Xero.
That said, some comments on producing Tables 1 and 2.
Table 2 columns for revenue and expenditure by fund type (and the total) can be produced by customising a Profit and Loss report in the same way as discussed above for revenue and expenditure by project. In this case use “Funding Type” for creating new columns. Choose “Financial Year” reports for the Date Range. For the “Prior period total funds” column, choose the previous year in the year selector under the Date Range box.
You should note that this split of money flows by Fund Type only gives the right result if all related transactions and journal entries are correctly coded for Fund Type. This may seem easy enough but if you are allocating an employee cost to a project via using Employment Groups, then it is not possible at the same time to code their costs as, for example, “Restricted”. If you want accurate Fund Type reporting in Xero, then you cannot use “Employment Groups” for per project (i.e. projects within the restricted fund type) cost allocation. You have to use journal entries instead.
To show “Transfers between funds”, you can create an Expense accounting code for this via the Chart of Accounts or ask your accountant to do this for you. You could name it “Interfund Transfer In/(Out)”. Suggested settings are as below:
To make the transfers, do a manual journal entry – or ask your accountant to do so – debiting the fund you are moving funds from and crediting the one receiving the funds, as in the example below:
To build a report showing the equivalent of the “Transfers between funds” and “Reconciliation” in SORP Tables 1 and 2, add a Group as a row in the relevant Income/Expenditure report (Profit and Loss in the default reports), having used the “Edit Layout” button to bring up the customisation page:
Drag this to below the Net Income/(Expenditure) row (Profit after Tax in the default report) and change its heading and settings thus:
Then drag the “Interfund transfers” row from the “Administrative Expenses” or similar Group into the new “Movement in funds” group. Then selecting that row, insert a new row as a Formula. Insert Net Income/(Expenditure) as the formula and the same as the Row heading:
Once “Done” and saved, this should produce lines at the end of the Income/Expenditure report like these:
Note: as regards “Other recognised gains (losses)”, these should be recordable in Xero and included in the computation of net income. Splitting out the presentation of these from normal operating income can be done by creating row groups for these and moving the related rows into their correct group.
For the “Reconciliation” rows, you have to (as far as I can tell) do some rather tricky customisation moves in order to be able see and use “Funds brought forward” via the “Retained earnings” account code. First create a new Schedule via the grid button at the top:
This creates a completely separate table below the main one, like this:
Click in “Add accounts” and add #960 “Retained earnings”. Now the challenge is to move this row into the main report. If you try and drag it there, it may not seem possible. But try first clicking on one of the group headers in the main table and then drag the “Retained earnings” over the main table. Try to get the blue line below the bottom row before dropping from the drag action. (Not the end of the world if it ends up dropping elsewhere.)
Remove the new, still Untitled schedule. Click on the “Retained earnings” row and click Group selection at top right and then adjust to these settings:
Then add another row as Formula to the group, inserting “Movement in funds” as the formula and “Net movement in funds” as the heading.
When “Done”, remember to save your report via “Save as” at bottom right; otherwise you will lose your edits.
The added group should appear below the Movement in funds group something like:
Table 1 is essentially a version of the revenue and expenditure by project report discussed earlier (though it has been pivoted). You don’t have to follow this exact presentation. You can instead follow the presentation of Table 2 and, in the same manner as for that table, add the “Fund Reconciliation” rows at the bottom to derive “Fund balances carried forward”.