International Disbursements – Outgoing schedules (excluding New Zealand) 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:

• One at a time, generate the Centrelink and Sweden Disbursement Reports in Cuba
• Select the following menu items from the Management menu on the main screen:
  • Intl Disbursement Report
  • Centrelink or Sweden
• Enter the first and last dates of the previous month in the From and To fields
• Take a note of the totals from both Cuba reports so the total amounts can be cross-checked later in the process
• Select the Excel icon to convert both reports to Excel files
• Copy and paste the Sweden report from the first customer (should be Cell A10) and paste the customers to the bottom of the Centrelink report
• Save a copy of the spreadsheet to the desktop as Centrelink Report (last month)
  Note: for the purpose of following this process, the spreadsheet will be referred to as the Centrelink report

Create an autosum at the bottom of column I. Check that the autosum equals the total of both Centrelink and Sweden reports noted in Step 1.

If this does not equal the sum of both reports check which customers have been missed when coping and pasting the two together.

If the autosum matches discard the Sweden report.

To format the Centrelink report:

• 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, L 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
• Add the amounts again using Autosum. The amount should match the total of the Centrelink Report + Sweden Report (from Step 1):
  • minus any amounts removed to go onto the NZ Schedule
  • plus any amounts added to this schedule from the NZ 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 (123)
• Highlight Columns A – C then Data > Subtotal > Remove All
• Save
• Insert a new column after Column B and name it Payee Name
• 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 (123)
• 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 H//find what = ’total’. Replace with = leave this blank/Replace all
• Delete Column B (which should be blank)
• Complete Autosum. This amount should match the amount from Step 2:
  • 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 R:\NAT\CSTS\CSSCWSNA\International Payments\$$ 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 follows:

• R:\NAT\CSTS\CSSCWSNA\International Payments \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 ‘Outgoing Schedule MMM YYYY’ or something similar. This will be referred to as the Outgoing Schedule for the rest of this OB. On the:
  • Centrelink Report go to File/open/browse/desktop and open the copy of the Outgoing Schedule from the desktop
  • Outgoing Schedule, insert a new column B (Between EFT Template and Currency). Name this ‘Amount’ and format it to be number with 2 decimal places
  • Outgoing Schedule use the VLOOKUP function to transfer the amount from the Overseas Payment Schedule Centrelink report to the Outgoing 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 Outgoing 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 Centrelink report. If they do not, go to Step 7
• The customers with N/A on the Outgoing Schedule do not have funds to be disbursed this month, and can be deleted (highlight rows to the bottom and press delete)
• Save

If the total of the Outgoing Schedule spreadsheet is different to the total of the Centrelink report 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 Outgoing Schedule

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

• Centrelink report - insert a new Column A
• Outgoing 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
• Centrelink report - use the VLOOKUP function to identify the discrepancies by comparing the Payee CSID column to the CSRN column on the Outgoing Schedule:
  • For example, in cell A2 enter = VLOOKUP (B2,D2-E2 [on the Outgoing 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 Centrelink report. Filter to find:

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

Note: both the Outgoing Schedule and the Centrelink Report 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 Outgoing 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 Outgoing Schedule:

• Open the Refunds Schedule which is located at R:\NAT\CSTS\CSSCWSNA\International Payments\$$ 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 Outgoing Schedule. You can now close the Refund spreadsheet

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

• Sort the Outgoing 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 Outgoing Schedule and fix any CSRNs that have turned red:
  • Add multiple amounts together, and delete rows to ensure that customers are only on the Outgoing Schedule once
• Use the Autosum function to total the new amount on the Outgoing 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 Outgoing Schedule 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 (EFT)
• 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 Outgoing Schedule create a new Column B (to the left of ‘Amount’):
  • Copy Column E (CSRN) and paste it into the new column. There will now be two CSRN columns, which are required to use for the VLOOKUP function

Create the schedules as follows:

• Use the filter on Column A (EFT Template) to select only Austria
• Perform an Autosum (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). Take note of the amount

In Windows Explorer, go to:

• R:\NAT\CSTS\CSSCWSNA\International Payments 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 Outgoing 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 Outgoing 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 Outgoing 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 Outgoing 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 (123)
• Save

Repeat this process for all other CAs, EFT and Cheque.

Note: do not cut and paste data into the WIRE (EFT) template, it causes formatting errors in the spreadsheet. Transfer the values into this sheet using the Vlookup described above.

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

• Check the EFT 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
• 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:
  • R:\NAT\CSTS\CSSCWSNA\International Payments\$$ to EFT Individual Customer. Copy the Template and paste it into the RBA Macro folder within the current financial year, for example R:\NAT\CSTS\CSSCWSNA\International Payments\$$ to EFT Individual Customer
  • 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 team 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. The attached spreadsheet has been password protected. The password will be forwarded in a separate email. Please advise who is currently in the Cash management EL 1 role and Aps 6 Financial Officer role.’
• Once confirmation of these roles is received email the password to the role occupiers
• Attach all schedules being sent to CAs, EFT, Cheque and the RBA Macro
• A singular excel spreadsheet to be sent with a tab for each country, EFT data, cheque data and any other payment data that is required
• Ensure the spreadsheet is password protected
• Move all emails including password related emails from sent items to the Disbursement mailbox folder – Finance/Schedules out
• Send an email to the international disbursement mailbox with the subtotals recorded at Step 9

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
• Keep all historic data records in the R drive folder pathway: R drive > NAT > CSTS > CSSCWSNA (ISP provisioned users only)