International Disbursements - New Zealand bulk payments in 277-08060000

Format the spreadsheet and convert it to a .prn file before the 14th of each month:

• Open the schedule and save a copy to desktop
• Check the Autosum provided by NZ in cell D8. Select this cell and check the function box. It should read =Sum($D$10:$D$99996). Make sure the amount in cell D8 matches the amount in B4
• In the footer, select the + to create a new worksheet:
  • Select rows 1 to 8 on sheet 1 and cut and paste them into sheet 2. These details will be used later
  • Delete the blank rows 1 to 8 on sheet 1
• Highlight Column D using the right mouse. Select Format Cells
  • Format the cells to be a number with 2 decimal places, make sure the use 1000 separator (,) is not ticked. The figures in Column D will no longer have $ or commas (,)
• Highlight Row 1 and select the Data tab from the Tools bar, then select Filter
• Sort the spreadsheet by Payer Child Support Identity (CSID). Using the dropdown at the top of Column C select Sort smallest to largest
• Check the schedule to make sure all customers have a CSID and IRD:
  • Use the dropdown filter at the top of Column C and Column E
  • Check to see if there are any blanks by scrolling to the bottom
  • If there are, deselect the Select all box at the top and select Blanks
  • Attempt to locate missing CSIDs or IRDs. A name search can be used in Cuba or previous schedules
  • If unable to locate the missing details advise SAS National who will follow up with New Zealand Inland Revenue Child Support (NZIRCS)
• Insert a new column after Column A (Payer Last Name) and Column C (Payer First Name) so the new columns are Column B and Column D
• Select File > Open > Browse:
  • Go to R:\TAS\HOB-188-Collins\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ Schedules and Errors\NZ Error Report (Sysout)
  • Open file NZ schedule – SYSOUT Prevention

• In cell B2 on the schedule create a VLOOKUP where the lookup value is the Child support identity (CSID):
  • Table Array is all rows in Columns B, C and D of the NZ Schedule – SYSOUT Prevention spreadsheet
  • Column index is 3
  • Range lookup is false
• Drag this formula to the end of the schedule
• In cell D2 create a VLOOKUP where the lookup value is the CSID:
  • Table Array is all rows in Columns B, C and D of the NZ Schedule – SYSOUT Prevention spreadsheet
  • Column index is 2
  • Range lookup is false
• Drag this formula to the end of the schedule

Select Global > Copy > Save values

• Columns B and D will now have first and last names of the customers who need to be changed from the NZ schedule to match the records in Cuba:
  • Sort the spreadsheet by Column B (A to Z) all the customers requiring updates are at the top
  • Scroll down to the first #N/A
  • Investigate and add customer to schedule and to NZ Schedule - SYSOUT Prevention
  • Copy and paste the heading from Column A to Column B and from Column C to Column D
  • Delete Column A and Column C
• Check a sample of CSIDs in Cuba to make sure the customer name, IRD and CSID match
• Perform an Autosum to confirm the total amount is the same as at the start
  • Delete Autosum cell
• Select Sheet 2 from the footer:
  • Copy rows 1 – 8 back into the top of sheet 1. (click in cell A1 - insert - copied fields - yes)
• Right mouse, select sheet 2 and Delete
• Save this into the appropriate file: R:\NAT\CSTS\CSSCWSNA\Software\International Payments\$$ Received from NZ, schedule in\SCHEDULES
• Check for duplicates - Highlight columns CSID and IRD. Conditional Formatting > Highlight Cell Rules > Duplicate Values > Light Red Fill With Dark Red Text. This will make any duplicate customers appear as a red cell. If there are duplicates and it is for:
• the same customer, add the figures together
• a different customer, investigate

Insert formula to convert NZ$ to AU$ in cell F10:

• Create a formula in cell F10 to multiply the exchange rate that was provided by NZIR with the cell that contains the NZ$ amount (column D)
  For example, if the exchange rate that was provided in cell B6 was 0.78411, the formula entered in cell F10 would be =(0.78411*D10)
• Double click (assisted technology use Select > Select) on the bottom right corner of Cell F10 to copy the formula, this will produce the AU$ value for all customers
• Select Column D, then the Home tab on tools ribbon:
  • Select Format Painter. The cursor will change to a paint brush
  • Click to select Column F, and the format now from Column D will be reflected in Column F
• Add a heading into Column F - Payer Amount $AU
• Add an Autosum at the bottom of Column F. Make sure the AUD amount matches the amount in B5
• Open Cuba and search for an organisation. Use NZIR to bring up the NZ Employer profile. Check the amount received from NZ in Cuba matches the amount in the spreadsheet
• Copy>Paste Special>Values>Ok
• Save
• IMPORTANT - Resave the spreadsheet again but this time back to the desktop. Save the spreadsheet for our records, now turn it into a .prn file.
• Delete rows 1 to 8
• Save

After payment information is received from NZIR and the .prn file has been created and saved to the correct location, check that NZIR have sent the funds and been received by Child support.

• The correct account for NZIR to deposit funds into is:
  • BSB: 092-009
  • Account number: 116755
  • Account name: CSA OFFICIAL RECEIPTS
• Open employer CSID 109036845959 on Cuba
• In the Outstanding box on the bottom RHS there should be an amount showing under Payment List. This amount should be the same as Payment Schedule (PRN) sent to Child Support from NZIR
• Select the Payments List link or access via Accounting/Payment Lists/Find
• The Payments box should have a Received and Posted date showing when the payment has cleared into the Services Australia bank account and the amount. This confirms the payment has been received and is in the correct bank account

Note: the funds must be in the Child Support bank account before IT process the .prn file on the 15th of the month

Cut and paste the columns to make sure they are in the following order with the following formats.

• Column A: Surname (General)
• Column B: First name (General)
• Column C: CSRN (Number 0 decimals)
• Column D: AUD (Number 2 decimals)
• Column E: Overseas ID (Number 0 decimals)
• Column F: USD (Number 2 decimals)
• Delete the headings in Row 1 and in Cell A1 enter the CSRN for the NZIRCS organisational profile – 109036845959. Make sure this is formatted as a Number with zero decimal places and left justified
• In cell B1 add the date the schedule was received (as used when naming the schedule in Table 2 > Step 1). Use the format dd/mm/yyyy. Format cell in short date
• In cell C1 add the conversion rate provided on the NZ Schedule

Format spreadsheet:

• Right justify Colum F
• Left justify columns A, B, C, D and E
• Delete any Autosum amounts at the bottom of the Schedule
• Save (keep this on the desktop, if there are errors later, come back to the sheet saved at this stage)

Save file as .prn

• Save the file again to the desktop as a .PRN file with the name OSA_ input_ data

In the Save as type dropdown menu, select Formatted Text (Space delimited) (*prn)

Format the file in a specific way or it will not load properly. There is a template called Ruler for Batchloading schedules that checks the formatting is correct.

• Open the .prn data file from the Desktop as a notepad (hold mouse over file icon, right click, select open, choose option to open as a notepad)
• Open the Ruler for Batchloading schedules located at G:\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ schedules/ruler for Batchloading schedules
• Copy and paste the content from the ruler notepad into the prn notepad (see below)

• Check each column and make sure they are in the following positions:
  • Column 1: Left justified and starts at position 1
  • Column 2: Left justified and starts at position 26
  • Column 3: Left justified and starts at position 53
  • Column 4: Left justified and starts at position 71
  • Column 5: Left justified starts at position 87
  • Column 6: Right justified and ends at position 117
• If the file is in the correct format, remove the ruler from the prn file. Delete the headings as well
• Enter the Child Support Reference Number (CSRN) for NZ as the first line of the .prn file. The CSRN is 109036845959. The second line should be the first customer

Data will not load unless the above conditions are set. If the .prn file with the ruler does not match the positions listed above:

• return to the schedule saved as an excel document on the desktop
• check the column widths to make sure they are correct

The ICT system checks the OSA deductions folder daily for .prn files. With NZ run this file on the 14th of the month.

Therefore, if the date the .prn was created is before the 14th of the month, send a copy of the .prn to the International Disbursement mailbox, then any Disbursement Service Officer can submit Then set a calendar appointment in outlook for 9am on the 14th of the month. If the 14th is a weekend then set this for the last working day before the 14th. Add the disbursement team colleagues to the appointment.

On the 14th of the month the file needs to be moved to: \\ntidcsacroc\d\Inetpub\ftproot\Prod\OSA Schedule

If the file is not moved or is saved in the incorrect file format the ICT Batchloading system will not pick it up and the payments will be delayed.

Once formatted and saved, IT will pick up the file on the hour. An ‘OSA Schedule Load Report’ email is generated and sent to International Disbursement mailbox on the hour.

Check the errors in the report and look in Cuba on errors, make sure the figure matches.

From the Employer Profile > Go > OSA Schedule Report List > Select the date of the schedule ran (usually todays date).

If 'No Record found for specified criteria' appears this is because there are no errors and the file will Reconcile overnight. If it does not appear, to see full list of payments and customers, select looking glass > Untick Display Records in Error Only > Find.