International Disbursements - Other bulk payments out 277-08050000

Child support payments collected in Australia and sent to authorities for disbursement to payees in:

• Austria
• Belgium
• Canada, British Columbia and Ontario
• Czech Republic
• Finland
• Germany, Bundesamt and DIJUF
• Netherlands
• New Zealand
• Norway
• Slovakia
• Sweden
• Switzerland
• UK MPBC
• USA - Pennsylvania and Colorado

On the first working day of each month:

• Generate the Centrelink Disbursement Report in Cuba
• Select the following menu items from the Management menu on the main screen:
• Intl Disbursement Report
• Centrelink
• Enter the first and last dates of the previous month in the From and To fields
• Print Cuba screen so the total amount can be cross-checked later in the process
• Select the Excel icon to convert the report to an Excel file
• Calculate the sum of the amounts using the Autosum function in Excel. If the total:
• matches the Cuba report, delete the Autosum from the spreadsheet
• does not match the Cuba report, investigate the cause of the difference
• Save a copy of the spreadsheet to work from
  Note: for the purpose of following this process, the spreadsheet will be referred to as the Overseas Payment spreadsheet

Format the Overseas Payment spreadsheet, this will form the schedule to send to Child Support Cash Management Team:

• Delete rows 1-7 and 9
• Add filter to line 1 (highlight row 1, Data tab, Filter)
• Identify any NZ In customers mistakenly on this spreadsheet:
• Select the filter on Column K and type NZ into the search
• Remove these customers in full from the spreadsheet, and
• Record this information as it needs to be included on the New Zealand bulk payments out schedule
• For any NZ customers that are removed update the disbursement method in Cuba to International/New Zealand. Keep a note of the dollar amounts for the records removed so the new total amount can be reconciled
• Add any records previously identified on the New Zealand bulk payments out schedule that should have been on this schedule. Keep note of the dollar amounts for the records added so the new total amount can be reconciled. See International disbursements - New Zealand bulk payments out
• Delete Columns C, D, E, F, G, H, J, K and M (highlight them all and delete at the same time)
• Format Column A to be numeric with zero decimal places
• Filter Column A by smallest to largest
• Save to the desktop (for example, as ‘International Payments March 2025’)
• Add the amounts again using Autosum. The amounts should match the amount on the Cuba print out:
• minus any amounts removed to go onto another schedule
• plus any amounts added to this schedule from another schedule
• Highlight remaining Columns A, B, C (not global)
• Select Data > Subtotal and configure the wizard with the following options Payee Child Support Identity (CSID) > SUM > Total $AU > Replace current subtotals > Summary below data > OK
• Untick any other populated fields. The purpose of this is to ensure if there are multiple payments for the same payee, they are consolidated into one amount
• Confirm the grand total matches our previous figure, then delete any blank rows and the grand total row

• Highlight spreadsheet by selecting Global > copy > paste special > values
• Highlight Columns A – D then Data > Subtotal > Remove All
• Save
• Insert a new column after Column B and name it Payee
• From the top of Column A come down to the first time the word Total is at the end of the CSID. This will also be in BOLD. If the first customer only received 1 disbursement this month, this will be Row 3
• Move across to Column C in the same row. If you are in row 3 (if not see the next dot point) enter the following formula in Cell C3: = if (b3,b2,b2) and copy the formula all the way down to the bottom. This will copy the payee name so it is on the same row as this CSID & the word Total in Column A
• If you are in cell C4 or lower you need to adjust the Formula. C4 would be = if (b4,b3,b3) and C5 would be = if (b5,b4,b4) etc
• Global > copy > paste special > values
• Save

• Filter by smallest to largest on Column A – Payee CSID
• Scroll down to where the data on the spreadsheet changes from being the individual payments for the receiving parent to the total received. The CSID will be BOLD and have the word Total after the CSID
• From the first row without the word Total and CSID in BOLD, highlight back up to row 2 and delete all the rows
• Save
• Remove the word ‘Total’ from ‘Payee CSID’ column. Control F/replace tab/find what = ’total’. Replace with = leave this blank/Replace all
• Rename Column C – Payee Name
• Delete Column B (which should be blank)
• Complete Autosum. This amount should match the amount on the Cuba print out, less any amounts removed from the spreadsheet for New Zealand customers, and plus any amounts added to the spreadsheet:
• Make a note of this amount
• Delete the Autosum cell

Recalled funds show on the payee's Disbursements window. The money was not disbursed as it was recalled via Cash Management. The spreadsheet and schedules are created from information extracted from Cuba, these payments appear on the report but need to be removed. They did not actually occur.

They are recorded on the Recalls spreadsheet that is located at \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\Payments In and Out\$$ to Refunds and Recalls.

Complete the following steps to remove or amend the amounts of recalled funds:

• Open the Recalls spreadsheet
• Check the tab at the bottom is the correct month
• Perform an Autosum on the amount column
• Save a copy of the spreadsheet before continuing. This is a safety measure in case any data is lost while working on the spreadsheet:
• Delete the copy as soon as the schedules are completed
• For each recall, use the Ctrl F function to locate the customer on the Overseas Payment spreadsheet by searching for the CSID. If the amount being recalled is:
• the same as the amount on the Overseas Payment spreadsheet, remove the entire entry on the Overseas Payment spreadsheet
• less than the amount on the Overseas Payment spreadsheet, the amount on the Overseas Payment spreadsheet is reduced by the recall amount
• If there is more than one recall amount for a customer:
• add the amounts together and either subtract the total from the customer's payment amount, or
• delete the entire entry as outlined above
• Complete Autosum on the schedule. The grand total should be the previous subtotal from step 4 minus the recall total. Make a note of this amount
• Save

• In Windows Explorer, locate the International Address Register (known in International as the Master Address Register (MAR)) as followed:
• \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\International Address Register
• Save a copy on the desktop to work from. It must be a copy, DO NOT move the original. To copy:
• right mouse click > copy > open the Desktop > right mouse click and paste
• Rename the MAR on the Desktop to ‘Centrelink Schedule MMM YYYY’ or something similar. This will be referred to as the Centrelink Schedule for the rest of this OB. On the:
• Overseas Payments spreadsheet go to File/open/browse/desktop and open the copy of the Centrelink Schedule from the desktop
• Centrelink Schedule, insert a new column B (Between EFT Template and Currency). Name this ‘Amount’ and format it to be number with 2 decimal places
• Centrelink Schedule use the VLOOKUP function to transfer the amount from the Overseas Payment Schedule to the Centrelink Schedule
• In cell B2, Formulas/Insert Function/VLOOKUP (in the select a function box)/OK Note: if you do not have VLOOKUP in the select a function box, type VLOOKUP in the Search for a function box
• Lookup value = D2 (CSID) on the Centrelink Schedule, Table array = International Payment spreadsheet Columns A, B, C from row 2 to the bottom, Col index num = 3, Range lookup = false
• Copy the formula down to the bottom (do not double click the bottom corner of the cell) and then Global>copy/paste special values
• Save
• Sort the Centrelink Schedule in order of disbursed amounts lowest to highest (filter by Column B)
• Scroll down to the first customer with #N/A – take note of the number of payments and perform an Autosum in the cell with the first #N/A – delete this Autosum
• Ensure the number of payments and amount match the Overseas Payment Schedule. If they do not, go to Step 7
• The customers with N/A on the Centrelink Schedule do not have funds to be disbursed that month, and can be deleted (highlight rows to the bottom and press delete)
• Save

If the total of the Centrelink Schedule spreadsheet is different to the total of the Overseas Payments spreadsheet investigate to identify which customers are missing. Check to see if there:

• are a different number of entries on each spreadsheet
• is a duplicated customer record on the Centrelink Schedule

This commonly occurs if the customer has not been added to the MAR. On the:

• Overseas Payment spreadsheet - insert a new Column A
• Centrelink Schedule - Highlight the whole document (global) and then select Data > Sort by > Child Support Reference Number (CSRN) > tick the box My data has headers > OK
• Overseas Payment spreadsheet - use the VLOOKUP function to identify the discrepancies by comparing the Payee CSID column to the CSRN column on the Centrelink Schedule:
• For example, in cell A2 enter = VLOOKUP (B2,D2-E2 [on the Centrelink Schedule] & drag to the bottom, 2, false)
• Copy the formula down and then copy/paste special values
• Save

Any discrepancies will be identified with #N/A in Column A on the Overseas Payments spreadsheet. Filter to find:

• Add these customers to the Centrelink Schedule
• The Original MAR needs updating with the details of the customers that were missing
• Close the Overseas Payment spreadsheet

Note: both the Centrelink Schedule and the Overseas Payments spreadsheet must match.

Refunds (or any payments of excess cash) that are recorded on a spreadsheet throughout the month, now need to be incorporated onto the Centrelink schedule. See International Disbursements – Refunds and issue of excess cash.

If a payee has money to be disbursed, the refund amount is added to that disbursement to prevent multiple payments to a payee.

Complete the following steps to add refunds to the Centrelink Schedule:

• Open the Refunds Schedule which is located at \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\Payments In and Out\$$ to Refunds and Recalls, and ensure you are on the current months tab
• Make a copy of the Refunds schedule, this is a safety measure in case any date is lost while working the spreadsheet
• Delete the copy as soon as the schedules are completed
• Perform an Autosum on the amounts column of the refunds and make a note of this amount, then delete the Autosum cell
• Select all customers on the Refund spreadsheet (Column A to M), then copy and paste them on to the bottom of the Centrelink Schedule. You can now close the Refund spreadsheet

To check that each customer is on the Centrelink Schedule only once:

• Sort the Centrelink Schedule by Column D (CSRN) then select Column D
• Select the Home tab > Conditional formatting > Highlight Cell Rules > Duplicate Values. From the dialogue box, format cells that contain = duplicate, values with = light red fill with Dark red text, select OK
• Now scroll down the Centrelink Schedule and fix any CSRNs that have turned red:
• Add multiple amounts together, and delete rows to ensure that customers are only on the Centrelink Schedule once
• Use the Autosum function to total the new amount on the Centrelink Schedule. It should add up to the initial amount from Cuba, plus or minus any customers moved to or from another schedule:
• Minus the recalls and plus the refunds
• Save

• Sorting by Column A – Divide the Centrelink Schedules into schedules for Central Authorities (CAs) that are to receive payments electronically:
• Austria
• Belgium
• Canada, British Columbia and Ontario
• Czech Republic
• Finland
• Germany
• Germany – Bundesamt
• MPBC (Maintenance Payments Business Centre)
• Netherlands
• New Zealand
• Norway
• Slovakia
• Sweden
• Switzerland
• USA - Pennsylvania (PASCDU) and Colorado
• WIRE transfers
• The customers that are blank in Column A or have USA – California will receive cheque payments from Services Australia and will form the cheque schedule
• On the Centrelink Schedule create a new Column B (to the left of ‘Amount’):
• Copy Column E (CSRN) and paste it into the new column.
  You will now have two CSRN columns, but this is necessary for the VLOOKUP function we are about to use

Create the schedules as follows:

• Use the filter on Column A (EFT Template) to select only Austria
• Perform an Autosum and record the amount (or highlight all amounts in the total column, a count and total will be in the footer on the right hand side of the screen)
• In Windows Explorer, go to \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\Payments In and Out and open the Austria Folder:
• Copy the spreadsheet named Austria Payment Template
• Open the folder for the correct financial year and paste the copy of the Austria Payment Template
• Rename the payment template to fit in with the same naming format as previous months
• In the Centrelink Schedule select file>open and open this month’s Austria Schedule that you just created. In the first customers Total AUD cell select Formulas Tab > Insert Function > VLOOKUP, then complete the wizard where:
• Lookup Value = Payee CSID on the Austria Payment Template,
• Table Array = Columns B C on the Centrelink schedule (Filtered to show Austria)
• Column index = 2
• Range lookup = false
• Drag the VLOOKUP to the bottom of the Austria Schedule
• Use the filter on the headings of the Austria Schedule to sort the amounts smallest to largest. Customers getting payments will be at the top:
• Highlight the totals, and check the total and the count are the same as on the Centrelink Schedule
• Delete all the customers with #NA as they are not receiving a payment. Make sure the total at the bottom is correct and matches the amount in the Centrelink Schedule. If they do not match investigate why. Customers might be missing from the MAR, or from the CAs Payment Template
• Global > copy > paste values

Save

Repeat this process for all other CAs listed above.

Note: do not cut and paste data into the WIRE transfer template, it causes formatting errors in the spreadsheet.

Resources has a link for contact details for the Child Support Cash Management Team.

Before sending the WIRE Transfer template to Child Support Cash Management Team:

• Check the EFT Transfer schedule for potential errors. The Template has conditional formatting to highlight issues with the different cells. For example, postcodes, character lengths, spaces before and after data. Replace any apostrophes with a space
• Ensure that the EFT Payment schedule does not contain decimal places in the amount column:
• Create a new column in Column F (next to Amount)
• From the first row enter the formula =e2*100. Drag this formula to the bottom of the sheet
• Copy and paste the heading from Column E to Column F
• Copy and paste values on Column F, and then delete Column E. All amounts should now have no decimal places
• The RBA Macro must also be sent to Cash Management along with the EFT Payment schedule. The template for the RBA macro is located within the folder that contains the EFT Payment Template -:
• \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\Payments In and Out\$$ to EFT Individual Customer
• Copy the Template and paste it into the RBA Macro folder within the current financial year, for example \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\Payments In and Out\$$ to EFT Individual Customer\Schedules 2023-2024\RBA MACRO
• Rename the macro to follow the format of previous months
• Open the newly saved Reserve Bank of Australia (RBA) Macro:
• Enable Macros and select Ok twice to clear the dialogue box
• Open the EFT Payment Schedule created for this month
• Copy all customers on the EFT Payment Schedule from Column C to Column Y
• Paste this information into the RBA Macro from the first empty cell in Column D (which will be below Automated Clearing House (ACH) payments to CAs)
• Fill in the values of the schedules being sent to the CAs that have ACH payments. The bank details are already entered on to the RBA Macro. Only the amount with NO DECIMAL places is required
• Select the default variables tab at the bottom of the RBA macro and select generate file. The Macro will identify any issues. If the errors are Column B and C this is expected, as the columns are blank and will be filled in by Cash Management. Correct all errors identified outside of this

Save

When all the schedules have been completed:

• add up the totals for each country, EFT, and Cheque. Ensure that it balances with the amount obtained at the end of Step 8
• email the schedules to Child Support Cash Management with a subject line such as ‘Outgoing International Payment Schedules June 2024’
• enter text in the email body. For example, 'Please find attached the outgoing schedules for June 2024’
• attach all schedules being sent to CAs, EFT, Cheque and the RBA Macro
• move the email from sent items to the Disbursement mailbox folder – Finance/Schedules out

A week after completing the Schedules, Cash Management send an email with a PDF and excel attachment showing, of the Batch Payment Summary Reports. Included in the attachments is the exchange rates that have been applied to payments.

Apply conversion exchange rates to schedules

• Open the schedules completed at the start of the month one at a time
• Enter the conversion details from the printouts on the schedules, such as AU$ amount, Foreign amount and exchange rate
• If a Transfer Date is required on a schedule enter the date provided in the attachments
  Note: ACH and WIRE will have different dates, be sure to check carefully
• Each schedule may be slightly different in layout and information required. Review the schedule completed the previous month for the same country to ensure the same formatting is used
• The AU$ amount on the top left of the schedule should match the Autosum amount at the bottom of the schedule
• Schedules require the individual AUD amounts for each customer to be converted into foreign currency. Use a simple formula in the row containing the first customer:
• In the foreign currency cell use a formula, type the exchange rate manually to 5 decimal places: =Sum (AUD amount*exchange rate). Copy the formula to the bottom of the customers receiving payments
• Complete Autosum and ensure amounts match those entered on the top left of the schedule as provided by the attachments received from Cash Management
• Save the updated schedule
• Repeat this process for all of the Central Authorities who receive

Create an email addressed to the Team Leader of the Central Authority Team:

• Enter the subject as per this example: ‘June 2024 Payment Schedule’
• Use the following template for the body of the email:
• ‘Hello,
• Please find attached the June 2024 Services Australia - Child Support Payment Schedule
• It would be appreciated if you could confirm you have received this email by replying via our secure messaging service
• Please ensure you inform our department of any contact detail changes regarding these schedules
• Regards,’
• Include the email address/es of the CA that the schedule will be emailed to. Link to the addresses here - Central Authorities
• Ensure signature block is included in the email
• Ensure the relevant schedule is attached to the email
• Send as ‘Official: Sensitive’ from the International Disbursement Mailbox
• When the Team Leader has approved, they will reply by email to CSA.International.Disbursement mailbox
• Select forward on the email. Add the CAs email from the body of the email to the To field
• Tidy up the subject line to show that the email is not a reply
• Remove all information from the body of the email other than the template above and send the email as official to the CA
• Move the approved email from the Team Leader to the Disbursement mailbox folder – Finance/TT emails to CAs/TT Approval from TL
• Move all sent emails from the sent folder to the Disbursement mailbox folder – Finance/TT Emails to CAs