3.1 This requires an open internet connection and currently must be done on an Open Arms laptop set up to access VERA data. The VERA Administrator can be consulted to access this. Once the update has been successfully completed, the file is copied to the appropriate CM9 folder for further reporting steps.
3.2 Using the refreshed JSSA Financial Report, the National Operations officer creates a corresponding JSSA Invoice Data Supporting Documentation report, which is emailed to the Open Arms Finance Officer.
3.3 VERA cannot be used to report Defence referrals to Open Arms group programs, so a manual calculation must be made based on data input by regions into the Defence Referrals to a Group Program spreadsheet (a new workbook is created each calendar year).
3.4 As the financial reports, invoicing data documents and invoices are corporate records, they are stored in CM9 [XXXXXXXX].
4. Refresh DVA JSSA (formerly AFS) Financial Report
4.1 Ensure that an approved thumb drive is in a USB slot of the laptop to transfer files between the laptop and DVA Citrix.
4.2 Turn on the laptop as normal, select the DVA User icon and enter the password: 1oneDVA1
4.3 VPN access is through the Cisco VPN client and using an assigned Athena Password.
4.4 If set up correctly, the VPN connection will enable the user secure access to the ‘back end’ of VERA.
4.5 Access the CM9 [XXXXXXXX] folder used to keep JSSA (AfS) invoice records and locate and open the previous month’s INVOICED report. If the previous month is not listed, copy and paste from the USB thumb drive.
4.6 Open the JSSA Financial Report and click Enable Content.
4.7 Click on the Invoiced Items Sheet
4.8 Remove All Contents except for the first row as shown below.
4.9 Click on client lookup sheet.
4.10 Locate the InvoiceDate column (Hint it is Column Number AH)
4.11 Click on the Filter Button within the column and Filter by Oldest to Newest.
4.12 Copy columns LineID and InvoiceDate only copy data that have invoice dates entered them.
Hint (refer to image over page):
- Select Cell AH2
- Hold Down Shift with your keyboard and press
- Whilst still holding Shift press the ‘Ctrl’ button and Press
- Then Press Ctrl and C to copy
- Paste in invoices items in InvoicedItems Sheet (go into cell A2 and paste).
- Go back to the ClientLookup Sheet and remove all contents within the InvoiceDate column (AH).
- Refresh the Data sheet (Select Data in the ribbon and then refresh all in the connections section.)
- Once the Data has refreshed, in Cell AH2, paste the following:
- Highlight all the data within the column AH InvoiceDate, except for the first row.
- With your keyboard, press Control-C to copy the cells.
- Right Click on the Cell AH2 and Select Paste Special, and Values.
- With the data still highlighted, using your keyboard, press Control-F – this will open the Find and Replace box.
- Click on the Replace tab and type in #N/A within the ‘Find what:’ line and click Replace All.
- Refresh your pivot tables in the sheet ToBeInvoiced by only selecting Refresh - DO NOT click Refresh all (if you do, click on undo).
- If any #N/A remain, there is a missing cart item within lookups sheet.
- Transfer File to DVA Main System
- Rename the refreshed DVA AFS Financial Report with the current date and the tag, ‘REFRESHED’.
- Copy the refreshed report to the thumb drive and from there, transfer it to the CM9 folder: XXXXXXXX.
5. Create Monthly JSSA Invoice Data Document
5.1 Open the refreshed JSSA Financial Report.
5.2 Open Template T713-08 – JSSA Invoice Data Supporting Documentation and ‘save as’ the new month and year (delete ‘T713-08 –’ and replace with ‘YYMM_Month 20xx’).
6. New Month’s Services Not Previously Invoiced – Tables
In the refreshed JSSA Financial Report:
6.1 It is important to ensure that the pivot tables showing the invoice amounts has been refreshed after the data refresh (only one table needs to be refreshed, then the other will refresh automatically) – refresh by right clicking with the cursor in the table area and then selecting refresh from the drop-down menu.
6.2 Filter for current year and last month (the month being invoiced) in the ‘To be invoiced’ table.
6.3 The tables show regions and site names. Using the small ‘–’ signs next to the regions, close any open site names.
6.4 Copy the invoice table with only the regions showing and paste it to the matching table in the JSSA Invoice Data document.
6.5 Then open the regions to show the site names (‘+’). Pending redesign of the report, numbers from states that are grouped to make a region may need to be manually tallied.
6.6 Then copy and paste to the matching table into the JSSA Invoice Data document. To finesse the table, it is worthwhile shading alternate regions (lightest grey), starting with FNQ, so that the region blocks are clearer to see.
7. New Month’s Services Not Previously Invoiced – Supporting Data
In the JSSA Financial Report:
7.1 Go to the ‘Client Lookup’ sheet.
7.2 Filter for required year and month using the yellow year and month columns (the Filter function may need to be activated first – via the DATA tab).
7.3 The invoice dates should all be blank/red: If cells have content, highlight cells and clear content.
7.4 Enter the current date (use xx/xx/20xx) for all records showing after the year/month filters have been applied (HINT: date the top cell; highlight the top cell; copy the top cell (Control-C); then Shift-Control- will take you to the last record and highlight all cells in between. Control-V will paste the date to all the highlighted cells).
7.5 Copy all showing records (Highlight Row 1(the header row); Shift-Control- will take you to the last record and highlight all records in between; then copy all records) and paste to the appropriate month sheet in the JSSA Invoice Data document – rename the sheet as required and adjust the column widths (select all columns used and double-click on the edge line of any one of these, in the column designation line (above row 1)).
7.6 In the JSSA Invoice Data document, delete the ‘Chargeable Status’ column (AF).
8. Previous Two Months’ Services – Carted Since Last Invoice
In the JSSA Financial Report:
8.1 Go to the Client Lookups’ sheet.
8.2 Filter for required year and previous two months using the yellow year and month columns. Also filter the invoice date for ‘blank’. This will select the services that have been entered in the VERA for the previous two months since the last invoice was generated.
8.3 Enter the current date (use xx/xx/20xx) for all records showing after the year/month and invoice date filters have been applied.
8.4 Copy all showing records (include the header row) and paste to the appropriate months sheet in the JSSA Invoice Data document – rename the sheet as required. Delete the ‘Chargeable Status’ column as before.
8.5 In the JSSA Invoice Data document open the tab New Invoices Table and right click anywhere on the table and Refresh data.
9. How to Generate a JSSA Invoice Data Document
9.1 The invoice amount must be presented as two invoices – one representing the V1 component and the other representing the V2 component. These components are calculated automatically in the ‘Split for invoicing’ table from the other tables (note that there is a line each for the current month’s invoice total and the previous months’ invoice total).
NOTE that the ‘Total due for...’ line needs to be updated to the current month.
9.2 Notify the National Finance Officer, who will undertake the final actions.
9.3 The invoice is prepared monthly in arrears. Hence the November invoice is done in December etc. The CM9 location of the invoicing data is: XXXXXXXX.
9.4 Save this spreadsheet in the same location naming the month in the file name.
9.5 Open the spreadsheet and calculate the V1 to V2 split from the month’s invoice supporting data. The split is recalculated periodically, and the Finance Officer should be consulted for changes. It is applied to the net value, GST amount, and gross amount of the month’s sessions and to the sessions that relate to the previous month but were not billed last month (refer to last month’s spreadsheet as a guide).
9.6 Refer to the invoice request template from the previous month, and update with the current month amounts as calculated above. An invoice template is to be completed for both V1 (Departmental) and V2 (Administered).
9.7 Email the templates to the ‘Agency Banking & Cash Management’ inbox. The Agency Banking and Cash Managemen team will then raise the invoices in DOLARS.
10. Send Invoice Package to Defence
10.1 When the invoices arrive, check them for accuracy and if they are accurate, email them to the ADF using the Email Text for Invoice Payments Template.
10.2 On a regular basis Agency Banking and Cash Management will run a debt recovery report. Any long outstanding accounts are followed up.
11. Additional Tables
11.1 The JSSA Invoice Data document has four necessary elements (illustrated below):
- Current month’s invoice tables
- Current month’s support data
- Previous two months’ services not invoiced table
- Previous two months’ support data
11.2 Current month’s invoice tables
- These tables are manually populated from the JSSA Financial Report as described in 6c and 6d.
11.3 Current month’s support data
- These data are manually populated from the JSSA Financial Report.
11.4 Previous two months’ new invoices table
- This table is automatically populated (you may need to ‘refresh’ the table – as before) when new data are added to the previous months’ support data tab and should look like this:
11.5 Previous two months’ support data
- These data are manually populated from the JSSA Financial Report as described in 8d. Populating this sheet should result in auto-populating the ‘Services not invoiced last month’ tables in the first and second sheets.
12. Parent Procedure
13. Related Templates
- No labels