International Disbursements - New Zealand bulk payments out (CS) 277-08030000

Child Support payments collected in Australia for New Zealand Inland Revenue cases are sent electronically once a month to NZIR for appropriate distribution.

On the first working day of the month, generate the New Zealand Disbursement Report in Cuba by selecting the following menu items from the Management menu on the main screen:

• Intl Disbursement Report
• New Zealand

Key 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.

Convert the report to an Excel file by clicking on the Excel icon. 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. If it does not match, investigate the cause of the difference.

Save a copy of the spreadsheet to work from.

Format the spreadsheet, this will form the NZ Out Payments Schedule

• Delete rows 1 - 7 and 9
• Identify any customers that are not NZ In cases and should be on 'Other bulk payment out' Schedule. Sort the Address column by A-Z (or filter as an alternate search method)
• Identify any Payees who do not have NZIR cases and should not be on this schedule, for example look for a normal customer address. Addresses listed as 'NZ IN CASE DO NOT TOUCH', or 'BRYCE STREET' are correct and remain on this Schedule
• Remove any customers identified who should not be on this Schedule and add them to the 'Other bulk payments out' Schedule. Update the Disbursement method in Cuba to International/Centrelink and ensure the customer is correctly entered on the MAR or appropriate payment template. Keep note of the dollar amounts for the records removed to reconcile the new total amount
• Add any records previously identified on the 'Other bulk payments out' Schedule that should have been on this schedule. Keep note of the dollar amounts for the records added to reconcile the new total amount. See International disbursements - Other bulk payments out (CS), Format the Spreadsheet
• 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

Recalled funds show on the payee's Disbursements window but the money did not disburse as it was recalled via the Financial Accounting Branch. As the spreadsheet and schedules are created from information extracted from Cuba, these payments appear on the report but must be removed, as they did not actually occur.

They are recorded on the NZ Recalls spreadsheet that is located at Z/Old files/Payments In and Out/$$ to NZ IRD/Recalls/Recalls. Select the appropriate financial year.

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

• Open the NZ Recalls spreadsheet
• Check the tab at the bottom, locate the correct month
• 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
• Open the spreadsheet that was worked on in Step 2
• For each recall, use the Ctrl F function to locate the customer on the NZ Out Payments Schedule by searching for the CSID
• If the amount to be recalled is the same as the amount on the NZ Out Payments Schedule, the entire entry is removed from the Schedule. If the amount to be recalled is less than the amount on the NZ Out Payments Schedule, the amount on the Schedule is reduced by the recall amount. Note: if there is more than 1 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 (Step 2.6) minus the recall total. Make a note of this amount
• Save

Refunds (or any payments of excess cash) are processed throughout the month and collated on a spreadsheet, which is later used when creating the 'Other bulk payments out' Schedule.

However, if a payee has money to be disbursed via the NZ Out Payment Schedule and a refund, the refund amount is added to the disbursement amount to prevent multiple payments to the same payee.

Complete the following steps to add refunds to the NZ Payments Out Schedule:

• Open the NZ Refunds spreadsheet: Z/Old files/payments In and Out/$$ to NZ IRD/Refunds/Refunds. Select the appropriate financial year
• Check the tab at the bottom to be sure the correct month is being looked at
• Open the spreadsheet worked on in Steps 2 and 3
• For each refund, check to see if the customer is receiving a payment on the NZ Out Payments Schedule by searching for the CSID (use the Ctrl F function)
• If there is a refund for a customer receiving a disbursement, add the refund amount to the disbursement amount on the NZ Out Payments Schedule
• Complete Autosum on the Schedule, the grand total should be the previous amount (step 3.7), plus any refunds added in. Make a note of this amount
• Save

Make 2 copies of the schedule. Send 1 form to Cash Management Team and 1 to NZIR. The formatting and requirements of each schedule are slightly different:

• Child Support Cash Management Team, go to Step 2
• NZIR, go to Step 3

Save the Schedule for Cash Management to Z:\International Disbursement\Old files\Payments In and Out\$$ to NZ IRD\Schedules to FMG\select the appropriate financial year and name sequentially

Save the Schedule for NZIR to Z:\International Disbursement\Old files\Payments In and Out\$$ to NZ IRD\Schedules To NZ\select the appropriate financial year and name sequentially

• Delete columns C D H J K L M. This leaves Payee CSID, Payee Name, Case Number, Payer Name, Payer International Reference, Total AU$
• Format column A to be numeric with zero decimal places
• Sort Payee CSID column by A-Z. For example, Highlight Global – Data – Sort – Tick My data has headers – sort by CSID column A
• Save
• Highlight Columns A to F (not global). Data > Subtotal > Payee CSID > SUM > Total $AU > Replace current subtotals > Summary below data > OK (see image below). The purpose of this is to ensure that if there are multiple payments to the same payee, they are consolidated into 1 amount
• Save

• Highlight spreadsheet by selecting Global > (right click) copy > (right click) paste special > values
• Insert a new column after Payee Name, Case Number, Payer Name, Payer International Reference. Use the same column names as the original columns, with a 2 on the end, for example Payee Name 2
• Delete any empty rows at the bottom of the spreadsheet between the last line of payment information and the grand total line
• In cell C3 add the following formula: =if(B3,B2,B2) and copy the formula down. This will put the RP's name on the same line as their CSID and the word Total in Column A
• In cell E3 add the following formula: =if(D3,D2,D2) and copy the formula down
• In Cell G3 add the following formula: =if(F3,F2,F2) and copy the formula down
• In Cell I3 add the following formula: =if(H3,H2,H2) and copy the formula down
• Save
• Global > copy > paste > values
• Save

Highlight spreadsheet by selecting:

• Global > Data > Subtotal > Remove All
• Save

• Sort Payee CSID column by A-Z. For example, (Highlight Global – Data – Sort – Tick My data has headers – sort by CSID column A)
  All CSID totals will now be at the bottom of the spreadsheet
• From Row 2 highlight all rows until there is a clear change in format between the top half and bottom half of the spreadsheet
• Delete the top half from Row 2
• Remove the word 'Total' from 'Payee CSID' column
• Save
• Ensure Column A is a number format
• Delete blank columns (B, D, F, H)
• Remove the number 2 from the column headings previously added
• Complete Autosum, this should be the same as the amount on the Cuba print out, less the recall amounts removed and plus the refund amounts added
• Remove the zeros from the last row in Columns B, C, D and E. Add the word 'Total' back into the bottom cell in Column A (Grand Total)
• Filter each column to ensure there is no information missing. For example, IRD
• Add to the NZ Tab of the Schedules Spreadsheet being sent to Cash Management as part of International Disbursements - Other Bulk Payments Out
• Save and close

• Delete columns C D E H J K L M. This leaves Payee CSID, Payee Name, Payer Name, Payer International Reference, Total AU$
• Format column A to be numeric with zero decimal places
• Sort Payee CSID column by Smallest to Largest
• Save
• Highlight Columns A to E (not global). Data > Subtotal > Payee CSID > SUM > Total $AU > Replace current subtotals > Summary below data > OK
• Save
• Highlight spreadsheet by selecting Global > (right click) copy > (right click) paste special > values

• Insert a new column after Payee Name, Payer Name, Payer International Reference. Use the same column names as the original columns, with a 2 on the end. For example, Payer Name 2
• Delete any empty rows at the bottom of the spreadsheet between the last line of payment information and the grand total line
• In Cell C3 add the following formula: =if(B3,B2,B2) and copy the formula down
• In cell E3 add the following formula: =if(D3,D2,D2) and copy the formula down
• In Cell G3 add the following formula: =if(F3,F2,F2) and copy the formula down
• Save
• Global > copy > paste > values
• Save

• Highlight spreadsheet by selecting Global > Data > Subtotal > Remove All
• Save

• Sort Payee CSID by A-Z. For example, (Highlight Global – Data – Sort – Tick My data has headers – sort by CSID Column A)
• All CSID totals will now be at the bottom of the spreadsheet
• 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
• Remove the word 'Total' from 'Payee CSID' column
• Save
• Delete blank columns (B, D, F)
• Remove the number 2 from the column headings previously added
• Complete Autosum, this should be the same as the amount on the Cuba print out, less the recall amounts removed and plus the refund amounts added
• Remove the zeros from the last row in columns B and C. Add 'Grand Total' into the bottom cell in Column B
• Save
• Check each column to ensure there is no information missing. For example, IRD.
  Update Cuba as required

• Some Payees have multiple Payers. When this occurs all funds received are added to one Payer only, rather than attributed to each Payer for their payment amount
• Open the spreadsheet called “RPs with Multiple Payers” located at G:\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ Schedules and Errors
• Search for each of the Payees on the “RPs with Multiple Payer” spreadsheet on the schedule, by using the CSID
• If a Payee is on the schedule, open their profile in Cuba and go to their Payee Disbursement window. Check each disbursement relevant to this schedule to see if there are multiple Payers
• If there is a payment from a different Payer to the one on the schedule, add a new line and add the missing Payer. Adjust both Payer amounts so they balance the original amount
• Save
• Some Payers have multiple NZIR Payees on Cuba. This can cause the Payers to appear twice on the schedule. Each Payer must only appear once on the schedule
• Sort the schedule by Payer International reference
• In F2 type the following formula =IF(D2=D3,”YES”,”NO”). Drag the F2 cell to the bottom to copy the formula down
• Copy and Paste Values for Column F
• Either scroll through the schedule looking to identify any cells in Column F marked YES, or add a filter/search for anything marked “YES” as this indicates a duplicate entry for the Payer. Add the totals together on one line and delete the other line/s so there is only one entry per Payer
• When finished, delete Column F
• Save

• Delete Column A and B so that only Payer Name, Payer International Reference and Total AU$ remain
• Open the previous month’s schedule, for example Z:\International Disbursement\Old files\Payments In and Out\$$ to NZ IRD\Schedules To NZ\”relevant year”:
• Copy the top 6 rows and insert them into the top of the schedule
• Adjust the date range to be correct
• Delete the dollar figures copies from the previous month
• Ensure there are no hyphens or spaces in the IRD column Control F, Replace (-) with nothing
• Remove $$ from Column C. Right click, format, number with 2 decimal places
• Save and close (this will be sent to New Zealand later as part of the Emailing Central Authorities process in International Disbursements - Other bulk payments out