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
• 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 the 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. 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:

• Identify any NZ In customers mistakenly on this spreadsheet, use Control F and search for NZ IN. Remove these customers in full from the spreadsheet and record the information to be included on the New Zealand bulk payments out schedule
• 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
• Add the amounts again using Autosum. The amounts should match the amount on the Cuba print out, less any amounts removed from the spreadsheet and plus any amounts added to the spreadsheet
• Save the spreadsheet
• Delete rows 1-7 and 9
• Delete Columns C D E F G H and J (highlight them all and delete them at the same time)
• Format Column A to be numeric with zero decimal places:
• Sort by Payee CSID A-Z
• Save
• Highlight remaining columns (not global). Data>Subtotal>Payee 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
• Delete any blank rows at bottom of spreadsheet between the last line of information and the grand total line

• Highlight spreadsheet by selecting Global>copy>paste special>values
• Insert a new column after column B and name it Payee 2
• Insert a new column after column E and name it Address 2
• Use the Excel tool Format Painter so blank column H is formatted the same as column G, then name column H Overseas Authority 2
• In cell C3 add the following formula: =if(b3,b2,b2) and copy the down. This will copy the payee name so it is on the same line as their CSID & the word Total in Column A
• In cell F3 add the following formula: = if(e3,e2,e2) and copy the down. This will copy the address so it is on the same line as their CSID and the word Total in Column A
• In cell H3 add the following formula: =if(g3,g2,g2) and copy the formula down. (This will mostly be zeros)
• Global>copy>paste special>values
• Highlight columns A – H then Data>Subtotal>Remove All
• Save

• Global>Data>Sort>Payee CSID>tick My data has headers
• The spreadsheet data should now be separated into 2 clearly different types of lists
• From Row 2 highlight all rows until the there is a clear change in format between the top half and bottom half of the spreadsheet
• Delete the top half from Row 2
• Save
• Remove the word ‘Total’ from ‘Payee CSID’ column (Control F/replace/’total’ with blank/replace all)
• Delete columns B, E & G
• 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 Financial Accounting Branch. 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 Z/Old files/Payments In and Out/$$ to Overseas/17-18 Schedules, Refunds & Recalls/Recalls 2017-2018/Recalls 2017-2018.xls.

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 amount minus the recall total. Make a note of this amount
• Save

Refunds (or any payments of excess cash) that are recorded on a spreadsheet throughout the month as they are processed, now need to be incorporated onto the Overseas Payment spreadsheet. 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 Overseas Payments spreadsheet:

• Open the Refunds Schedule which is located at Z/International Disbursement/Old files/Payments In and Out/$$ to Overseas/17-18 Schedules, Refunds & Recalls/Refunds 2017-2018/Refunds 2017-2018.xls and ensure 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 and make a note of this amount, then delete the Autosum cell
• To locate the customer, one at a time, copy the customer CSIDs highlighted in yellow on the Refund Schedule. Search (using Ctrl F) on the Overseas Payments spreadsheet. These customers are receiving a regular disbursement as well as a refund:
• Amend the amount on the Overseas Payment spreadsheet to include the amount on the Refund Schedule
• Make a note of any amounts added to the Payments Overseas spreadsheet from the Refunds Schedule
• delete the entire yellow row from the Refund Schedule
• All remaining customers on the Refunds schedule who are not also receiving a disbursement need to be added to the Overseas Payments spreadsheet. Copy these customers onto the bottom of the spreadsheet and ensure copied data lines up to the Headers etc
• Use the Autosum function to total the new amount on the Payments Overseas spreadsheet
• The grand total of the spreadsheet plus any Refund amounts added, less any Recall amounts removed should add up to the original amount in Step 1
• Save

• Open the Master Address Register (MAR), save a copy on your desktop to work from. Close the original MAR. Work from the copy. Sort the customer by CSID and close the original and copy
• On the overseas payments spreadsheet go: File/open/browse/desktop and open the copy of the MAR
• On the MAR copy use the VLOOKUP function to compare the CSID column to the Payee CSID column on the Overseas Payments spreadsheet
• For example, in cell A2 enter =vlookup(D2 – on the MAR,A2-C2 – on the overseas payment s/s & drag to the bottom, 3, false)
• Copy the formula down to the bottom (do not double click the bottom corner of the cell) and then copy/paste special values
• Save
• Sort the MAR copy spreadsheet in order of disbursed amounts
• Highlight the whole document (global) and then select Data>Sort by>Amount>Order by Smallest to Largest>tick the box My data has headers> OK
• The customers with N/A on the MAR Copy spreadsheet do not have funds to be disbursed that month and can be deleted
• Autosum the total of Column A on the MAR copy spreadsheet. Ensure this matches the total on the Payments Overseas spreadsheet. If they don't, complete Step 8

If the total of the MAR copy spreadsheet is different to the total of the Payments overseas 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 MAR
• This commonly occurs if the customer has not been added to the MAR. On the Overseas Payment spreadsheet:
• insert a new Column A
• On the MAR copy:
• Highlight the whole document (global) and then select Data>Sort by>CSRN>tick the box My data has headers> OK
• On the Overseas Payment spreadsheet use the VLOOKUP function to identify the discrepancies by comparing the Payee CSID column to the CSRN column on the MAR copy spreadsheet:
• For example, in cell A2 enter =vlookup(B2,D2-E2[on the MAR copy] & 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 MAR
• The Original MAR needs updating with the details of the customers that were missing
• Close the Overseas Payment spreadsheet

Note: both the MAR copy spreadsheet and the Payments overseas spreadsheet must match.

• Open the saved copy of refunds. Paste the data onto the bottom of the MAR copy
• Divide the MAR copy spreadsheet into schedules for Central Authorities (CAs) that are to receive payments electronically:
• Austria
• Belgium
• Canada, British Columbia and Ontario
• Finland
• Germany
• Germany – Bundesamt
• MPBC (Maintenance Payments Business Centre)
• Netherlands
• New Zealand
• Norway
• Slovakia
• Sweden
• Switzerland
• USA - Pennsylvania (PASCDU) and Colorado
• WIRE transfers
• The remaining customers on the MAR copy spreadsheet are for all other international customers and Central Authorities who receive direct (cheque) payments from Services Australia CSA and will also form a schedule
• Create the schedules as follows:
• Use filters to locate customers with a Payee Name of National Insurance Coll Agency (Norway)
• Copy header and cut these customers onto a new blank spreadsheet
• Delete all columns apart from Amount, CSRN and Payee Ref
• Sort spreadsheet by CSRN column by smallest to largest
• Complete Autosum and name the cell under the Autosum cell so that the Central Authority it is can be identified, for example, Norway
• Save to Desktop
• Repeat this process for:
• Netherlands (L.B.I.O.)
• Germany (DIJUF)
• Germany (Bundesamt)
• Switzerland (Filter by country and then cases with a Payee Reference starting with L)
• Finland (KELA)
• British Columbia (FMEP Payment Services)
• Ontario (Family Responsibility Officer)
• Belgium (SPF Finance)
• Austria (Federal Ministry of Justice)
• MPBC (Maintenance Payments Business Centre)
• Pennsylvania (PASCDU)
• Colorado
• WIRE transfers (search in Column B)

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

• Each spreadsheet created and saved to Desktop for the CA customer and individual WIRE transfer payments must be entered as a schedule and saved in the respective folders eg: Z/International Disbursement/Old files/Payments In and Out, and select the relevant C.A.:
• Open the template for example, Z/International Disbursement/Old files/Payments In and Out/All Eft templates
• Save a copy of the template to the appropriate folder and name sequentially for example, $$ Overseas/20-21 Schedules, Refunds & recalls/Overseas schedule 2020-2021/2. All schedules August 2020
• The amount from the spreadsheet previously saved to Desktop for that CA or WIRE transfer list needs to be entered against the correct customer on the schedule
• Use a VLOOKUP (or Ctrl F etc.) to locate the customer (CSID) from the spreadsheet on the Schedule and then copy the amount from the spreadsheet to the Schedule
• Repeat for all customers on the spreadsheet
• Delete any customers from the Schedule who have not received a payment that month
• The formatting of each schedule is slightly different, check the previous months schedule if needed
• When all CA payment customers and the WIRE transfer customers have been removed from the MAR copy spreadsheet the remaining individual customers form the Combined Overseas schedule. This is the schedule used to create cheque payments for all customers and CAs whose disbursements are not made by bulk electronic payment
• Save to Z/International Disbursement/Old files/Payments In and Out/$$ Overseas/20-21 Schedules, Refunds & Recalls/Schedules 2020-2021/All schedules and select the tab labelled Cheques
• Copy and paste the data form the MAR copy into the schedule
• Add up the CA schedules plus the WIRE schedule and Cheque Payments schedule. The total must match the amount on the original print out for the data obtained from Cuba, less refunds and plus recall amounts
• All schedules must emailed to the Team Leader for scrutiny and approval prior to sending them to finance or the individual Central Authorities.

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 WIRE Transfer template for potential errors. For example postcodes, incorrect letter case, incorrectly keyed SWIFT codes. Replace any apostrophes with a space
• run the RBA Macro over the WIRE/EFT template to identify errors or missing information

Do not cut and paste information into the template. It causes formatting errors.

Email the schedule to Child Support Cash Management with a sequential subject line such as '03. Payments Overseas September 2022.'

Enter text in the email body, for example:

'Please find attached the Services Australia CSA September 2022 payment and refund schedules’

For New Zealand: Include the following details on the header of the NZ schedule spreadsheet:

• PAYMENT DETAILS:
• Payment Ref: Services Australia Child Support
• Payment ID: 076180032
• BENEFICIARY DETAILS: Reference EMP 30042020

A week after completing the Schedules, Cash Management send an email with a PDF of Batch Payment Summary Report files attached. Included in the attachments is the exchange rates that have been applied to payments:

• Record the exchange rate used for the currencies on the ACH/EFT templates
• File the email from Cash Management in the Disbursement Mailbox and in the X/Z drives

Apply conversion exchange rates to schedules

• Open the Schedules completed at the start of the month one at a time. For example Z/International Disbursement/Old files/Payments In and Out$$ to Sweden/2012-2013 FYear/03.Sweden Payments September 2012
• 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 the email from Finance was received
• 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.
• Some Schedules require the individual AUD amounts for each customer to be converted into the currency. Complete as follows:
• In the column to the right of the blank foreign currency cell on the row of the first customer, enter the exchange rate. Copy this all the way down
• In the blank foreign currency cell enter a formula to multiply the customers AUD amount in the cell to the left by the exchange rate in the cell to the right, e.e. =F8*H8. Copy the formula down
• Highlight foreign currency column, copy/paste special/values
• Delete column with exchange rate
• Complete Autosum and ensure amounts match those entered on the top left of the Schedule
• Save the updated Schedule
• Format page so it fits to one page wide (review previous month for correct formatting) and print
• Email the CA a copy of the undated Schedule

When team leader has approved and forwarded email to CSA.International.Disbursement mailbox, using the standard email subject line and text, email as an attachment each Schedule to the matching Central Authority.

• Enter the Subject as June Payment Schedule
• Ensure the signature block is included in the email
• Ensure the Schedule is attached to the email
• Send as ‘Official: Sensitive’ from the International Disbursement Mailbox