Step 1: Define scope
The scope of data for the audit included expenditure from the seven departments for the financial years 2014–15, 2015–16 and 2016–17.
We used this expenditure data to perform the following tests:
- categorise spend across the seven departments
- identify expenditure categories for potential new SPCs
- determine whether contract leakage was occurring in select SPCs
- understand the spread of expenditure across different suppliers.
Step 2: Data extraction
To perform these tests, we extracted data from departments' accounts payable systems. The departments use these systems to pay invoices for goods and services. All departments use Oracle Financials as their accounts payable system. Data is stored in Oracle Financials in multiple data tables.
A script was written to extract data from the tables in Oracle Financials. This script was originally created by DEDJTR, and then the same script was used to extract data from the other departments' accounts payable systems to ensure a consistent approach.
Once the departments had completed the data extraction, a secure transfer link was shared with the departments so that they could upload this data to VAGO's password-secured database. After receiving confirmation that the data upload was complete, we downloaded the data into a secure folder. A manual review was performed to ensure that all data tables had been extracted and received.
Step 3: Transformation of data
The data provided from all departments was uploaded into a secure server at VAGO to be collated, cleaned and manipulated to make it fit for purpose. All departments use Oracle Financials differently, which means that the data tables extracted from accounts payable systems were not consistent across all departments and could not be readily matched and compared. We used two methods to standardise and categorise these transactions:
- Chart of accounts—each department uses a chart of accounts to map, organise and categorise expenditure. This chart provides a complete listing of all accounts used by the department to categorise spend for reporting. We used these charts of accounts to categorise each transaction across all departments. However, departments use different charts of accounts, which means we could not uniformly categorise across all departments.
- ANZSIC 2006—to uniformly code transactions across the departments, we used ANZSIC. VAGO obtained the Australian Business Register database, which uses ANZSIC to categorise suppliers based on their Australian Business Numbers. Each supplier is assigned an ANZSIC category based on its Australian Business Number—for example, the supplier Hays Recruitment is assigned the ANZSIC category Recruitment Services. The Australian Business Register database was loaded onto VAGO's server and each supplier was matched with a specific categorisation. The categorisation was then rolled up into broader categories to facilitate multiple layers of analysis.
Step 4: Data validation
We used the following methods to validate the cleaned and consolidated data:
- aggregated numbers were generated and presented to audited departments for validation
- a sense-check validation of a sample of accounts with publicly available annual reports of the agencies
- based on feedback as part of the two steps above, data investigations were conducted where the data did not reconcile and adjustments and amendments were made to the dataset in consultation with departments.
Step 5: Reporting
We loaded the validated dataset to a business intelligence tool, Qlik Sense, to create visualisations and reports to gain insights from the data. Typical reports and visualisations included:
- spend by agency
- spend by Australian Business Register categorisation
- spend by supplier by agency
- spend by chart of accounts categorisation.
We reconciled the numbers from the business intelligence tool with the raw data tables received to ensure the completeness and accuracy of the data received, transformed and extracted to Qlik Sense.
Figures E1 provides examples of information on our dashboard.
Main categories of expenditure of the seven departments, 2014–15 to 2016–17
Source: VAGO, based on data provided by DTF, DPC, DJR, DHHS, DET, DELWP and DEDJTR.