Xero guides - Managed Fund Statements & year-end adjustments (part 2)

The final step with the managed fund statement process is to ensure the updated categorisations in SS are correctly reflected in Xero. To ensure this all works there are a couple of concepts that need to be understood.

  1. For a lot of managed funds/ETF SS estimates the payment splits and these are posted to Xero at the time of the payment
  2. When you reconcile the payment in Xero to the transaction posted (via the API) from SS - this link is effectively locked
  3. At year end, when the actual payment splits are provided by the manager, you need to reallocate the payments (as covered in this post: Xero guides - Managed Fund Statements (part 1))
  4. To ensure the link to Xero remains correct, you need to go back into the transactions in Xero and unreconcile them before reposting the new splits from SS to Xero.

[As an aside - you can also just make the adjustments in SS and not fix the link to Xero; and then post a manual journal in Xero to ensure the payment splits are right, but this will mean the xero link in SS will not align. Also you need to understand which splits are posted where - which is too hard for me to remember. So I go the long way. As you’ll see below…]

Here are the steps I go through. I have a spreadsheet which lists all the Trusts statements I’m waiting on (36 at last count) down the side and these steps across the top. I tick each off horizontally as I do them:

Work flow per statement

  1. Receive tax statement from registry
  2. SS: Agree totals from the statement to the totals in taxable income report to statement - main differences usually relate to July payments that relate to the June year end. Go back into the July payment in SS and put the ex date as 1 July. (In rare cases the july payment may need to be split in two with part relating to the prior and part relating to the current year end. That’s an issue for another time!)
  3. Xero: Confirm total cashflows in bank ac agree to SS (filter on name and date)
  4. SS: Input new component splits into taxable income report - ensure totals before and after align [I used to take a snip and then wait after the unreconcile process was sorted - I now just hold off hitting enter until step 6]
  5. Xero: Click through to each transaction (though to separate tab) & then unreconcile, click OK
  6. SS: Enter new components in taxable income report [from snip]
  7. SS: On each pmt in tax report: click (though to separate tab), post to xero, and SAVE (if ‘post to zero’ is showing as already reconciled then go back and unreconcile in Xero per step 5)
  8. Xero: Reconcile each item, check ‘find& match’. (Sometimes there a minor 1 or 2 cent difference. Just use the minor difference capability in xero.)

For payment made as DRP’s

  1. If a dividend is paid as a DRP then the transaction is posted from SS to Xero as a manual journal (in my set up) so you don’t need to do the ‘unreconcile’ process noted above.
  2. After you’ve adjusted the split in the taxable income report, you need to open each transaction showing on the taxable income report and then click ‘save changes’. This will push the updated journal through to Xero - and that’s it!

This is a painful process for funds that pay monthly and then inform you (sometimes as late as October) that the payments are split in a stack of ways. SS does v well at dealing with this - but the Xero linking process described above will mean you need to go into each transaction in SS and in Xero. The process is unreconcile in Xero, update in SS, push each transaction from SS to Xero, then reconcile again in Xero. It’s a pain and time consuming but I find the workflow above eases the pain somewhat. I don’t think this is an issue with SS - more how the API works between Xero and SS

Year end accrual
As an extra step to ensure my taxable income report from SS aligns with income in Xero (important for SMSF audits and tax returns) you need to raise a year end accrual for July payments that relate to the June year end. For this to work correctly ensure the July payment in input to SS with a 1 July ex-date - this will ensure it’s picked up on the Taxable income report correctly

  1. SS: Run Taxable Income report. Select 1 July items (with ‘i’)
  2. SS: Open payment, then open Xero link. Take a snip and save as jpg
  3. Xero open new manual jnl. Copy ‘receive money’ details to reversing yr end jnl. Dr Post cash side to ‘Unpaid trust dist’ns/sundry rec’ls’

Again - for DRP’s if the July div that applies to June for tax went through as a DRP - just go into the transaction in SS and change the ‘paid date’ to 30 June. This will change the date on the Xero journal to 30 June - so it will appear in the correct period.

Hope this helps

See also
Xero guides - Managed Fund Statements (part 1) )