Project tracking

Donor and/or project tracking

This applies where a donor requires a report and there are some restrictions on what the funds can be spent on.

A donor may require separate reporting on separate tranches of funding. This will require identifying those tranches as separate “projects” associated with the donor.

Setting up for donor/project tracking

To track grants (revenue), expenditure, and cash movements by donor or project, one can use Xero’s Tracking features. This can be done without buying the add-on Projects module that costs extra and is primarily intended for detailed time and cost tracking against customer projects. Hence what is described assumes no use of the Projects module.

Xero supports 2 tracking categories with up to 100 values per category (potentially limiting the tracking possibilities).

It may be possible to use only one category for tracking projects; but if there are separate categories that need tracking within projects, you will need both categories. For example, if you want reports by project/donor that can distinguish restricted from unrestricted grants, you will need two categories.

Likewise if there are different classes of income that need tracking, separate from projects. In this case we assume that we want to class separately any money flowing through our bank account to be passed directly to beneficiaries (client transactions). We want to do this so that we can get a view of our accounts excluding all such flows.

Our accounts therefore need to show not only our total income and expenditure but also Restricted income and expenditure. In case there are client transactions in Xero, we need to exclude those from both the organisation totals and Restricted reporting. For this purpose we have named one tracking category: “Funding type”, which has values “Restricted” and “Client account pass thru”. If the value is not set, that implies it is an unrestricted transaction not destined for a client.

The other tracking category is named “Donor/project”. The default procedure for tracking any project is to track it by its name in the “Donor/project” category AND as “Restricted” under the “Funding type” category. It is possible but unlikely that we might want to track use of unrestricted funds, in which one would track the project only via the “Donor/project” category.

In the case that you want to start using Xero for Client transactions, then each Client project needs to be tracked by its name in the “Donor/project” category AND as “Client Account” under the “Funding type” category. Be aware, though, of the limit of 100 tracking values in the project category. If you know in advance that this won’t be enough, then don’t start down that route. (Also see the “Client account issues guide” for cases where client account money has passed through your accounts.)

Setting up donor/project tracking code

This is done by adding the new donor/project name as Donor/Project value. If the donor has no requirement to track different projects or different grant tranches separately, then just use the donor name and do not create new projects unnecessarily.

If the donor does need separate reporting by project or tranche, then we suggest using a combination of the donor name and tranche as the project name, as in “Lottery #2”.

The name is added as follows:

Otherwise using https://go.xero.com/Settings/AdvancedAccounting.

On the next page, select the Donor/Project tab, then “Add another option”, enter the new name and then press “Save”.

Recording income to a project

See the sections on “Invoicing” and “Restricted purpose grant” in the section: “Accounting for donor grants”.

Then see the “Donor grants” section in the section: “Reconciling money coming in”.

Allocating external costs to projects

See the “Costs attributable to a project” sections in the “Money out” section.

Attributing salary costs to a project

Background (you can skip if only interested in the recommended procedure)

This is not as straight forward as allocating external costs to a project. The reason is a weakness in Xero at the time of writing (and hence possibly to be rectified in the future).

You cannot currently set tracking codes for wage -related costs when doing the pay run.

You can set repeat journal entries to add tracking codes to part of the salary bill; likewise NI and pension. That works for P&L reports but the Tracking Transfers entry on cash flow reports prevents the allocation showing on fund flows.

If instead one adds tracking codes to Wages Payable payments as part of bank reconciliation, then the entries do not appear on P&L reports. This is unlike Bill payments. If tracking codes are added to bills, then you get the appropriate entries on both P&L and cash flow reports.

What is needed is a procedure that produces the right results for both accrual-based and cash flow based reports. 

Xero support advice on this is as follows:

“If your business only requires the use of one tracking category and a single category per employee, we recommend you use the Employee Groups field in the Payroll Settings menu. Go to Settings > Payroll Settings and update the Employee Group field with the required tracking category. Then go to Payroll > Employees > select employee > Employment tab and update the Employee Group field for each employee.

If you need to allocate a second layer of tracking to your employees, you need to set up timesheet categories. Go to Settings > Payroll Settings and update the Timesheet Categories field.

Please note you can only link one tracking category to employee groups and one to timesheets, and these cannot be the same for both.”

It is possible that this could be addressed by the Xero add-on module for Projects. However that is a significant extra cost and the ongoing recording of time and cost against each project is most likely unwarranted effort. Likewise for use of timesheets, even without the Projects module.

Without the Projects module, though, the Xero recommended method using Employee Groups only works if the entire time of an employee is dedicated to one and only one project per pay period. This is too restrictive to be a general solution to the issue but it is recommended in the case that an employee’s full costs are allocated to a project.

Note, though, that allocations made through the payroll cannot be changed afterwards, e.g. by “Find and Recode”. You would have to make reversing manual journal entries to do this.

Timesheets could be used but are a considerable burden on all involved if all hours have to be entered each month by someone. In the AU and NZ versions of Xero, one can save timesheet templates, which make it relatively easy to load template data into each month’s timesheet and approve it. This is not available in the UK version. Hence, unless you really do have materially variable time use which needs to be tracked, timesheets are not recommended for this purpose.

Because of these limitations in Xero and the resulting complications in accommodating both accrual and cash-based costs, the procedure recommended assumes that we do not need to supply cash flow based reports and are concerned only with accrual-based reporting as would be required for annual accounts.

General rules

Where the full cost of an employee is covered by funding, use “Full employee cost allocation” below.

Where a partial but relatively high percent of an employee is funded (70% or more), then use “Full employee cost allocation” until the funding is used up or will not fund another full month, then – if necessary – switch to the partial but low percent allocation process: “Partial cost allocation”. (This would not be appropriate if needing to provide quarterly reports to the donor as the reporting would be inaccurate at over those time windows.)

If partial but relatively low percent of an employee is funded (less than 70%), then use “Partial cost allocation”. (Again, also use this if frequent reporting to the donor is required and the employee is only partially funded.)

NOTE: With the “Full employee cost allocation” method it is not possible, as mentioned earlier, to track the expenditure for both tracking categories (e.g. add Funding type = Restricted). Hence some project funding that should be tracked as “Restricted” will be missed by the “Restricted” filter. The method, though, has the advantage of simplicity and not requiring manual journal entries.  If the inability to track for both categories at once is a show stopper, then use the “Partial cost allocation” for all employee costs.

Full employee cost allocation

For this case:

  • Have Employee Groups set to Donor/Project
  • Set the relevant Employee’s Employee Group (on the Employment tab) to the relevant project
Partial cost allocation

In the case of partial cost allocation

noted earlier, where a partial but relatively high percent of an employee is funded (70% or more), then use “Full employee cost allocation” above until the funding is used up or will not fund another full month, then – if necessary – switch to the process described here.

In this case, we will not attempt to capture pro rata pension and NICs accurately. If you like, the share of wage cost could be upped a bit as a proxy for covering these elements. At any rate, decide the monthly salary amount to be attributed. Then someone with Advisor rights and familiar with manual journals should set up either an ad hoc single journal entry or monthly repeating entries (with appropriate end date) to allocate that amount to the project. The screenshot below shows creating a repeating journal entry to allocate wage costs.

Note:

  • Cost allocation to the project is entered as a Debit, with a matching Credit towards the original (non-allocated) total
  • DO NOT CHECK “Show journal on cash basis reports”. It is checked by default for a new journal.

To allocate more general overheads or as an alternative way of allocating pension and NIC cost, use “Allocating staff related overheads to projects”, which follows.

Allocating staff overheads to projects

For the Pension and NIC costs associated with staff, see the section on allocating staff costs to projects.

Some funders also offer to cover general office overheads associated with staff or a portion of management time.

If the agreement is specifically to cover a portion of management time, then, having agreed the monthly cost that implies, have someone with Xero Advisor permissions set up a repeating manual journal to allocate that cost to the project each month.

If general overheads are covered, then it is expected that an agreed percentage of salary is presented for covering such costs and there is no obligation to show an allocation of funds across all elements of overheads pro rata to the employee’s “share” of the total.

It is up to the manager to decide what that percentage is and agree it with the funder. (Consideration should be given to not funding items which are already specifically covered by other funding, though.)

There is of course no accounting code for general overheads. It comprises a quite large group of accounting codes. To keep administration simple, the overhead element of a project should be allocated first to the most costly overhead element (e.g. Rent). If and when all that overhead cost has been fully funded by projects, additional general overhead funding should then be allocated to the next most costly overhead (e.g. Information Resources & subscriptions).

To be sure of being up to date with project cost allocation, set these allocations up with repeating manual journals. Otherwise make the allocations with ad hoc journals whenever reports are needed or at the financial year end.

In particular when using repeating journals, check from time to time if funding has exceeded or looks like exceeding the cost element being used. Switch to the next most costly overhead to avoid overfunding a cost element.

Project FY Reports

See Donor/Project reports