Extracting data for IDEA analysisKate Crichton

IDEA User Group6 June 2013

Examples of audits

• Payroll– Additional payment– Payroll deductions– On-call and call-out– Pay modernisation and overtime

• Procurement• Space management• Severance payments

… more examples

• Utilities• Printing services• Bar• Student Withdrawals – HESA return• Non-salary payments• Intellectual property• Expenses

… still more examples

• Research – prep for REF2014• Studentships and research scholarships• Let property• Key Information Sets • Data Protection compliance• Authorisations• location-based audits

Summary of data requirements

• Payroll• Financial – purchases / sales • Purchase orders – who / what / how much /

which supplier• Expenses – who / authorised by / how much• Supplier bank account details• BACS files

Summary (contd)

• Authorisations • Student records• Course and degree enrolments• Researchers and research output• Intellectual property• Room dimensions and classifications• Departmental spreadsheets• Other orgs’ data – Research Councils

Authority

Internal Audit has the Court’s authority to access all documents, records, personnel andphysical properties which it considers relevant to audit assignments and necessary to fulfil itsresponsibilities. There is an obligation on all staff to provide all necessary assistance.

Approved by Court

Screen shots removed

• Slides have been removed to ensure privacy. Author happy to answer individual queries. Contact details at end.

• Slides show data extract screens from systems for finance, purchasing, staff & payroll, authorisations, intellectual property, research management, student records.

Extraction criteria – drop companies

@isini( "&" , SUPPLIER_NAME ) =0 .AND.@isini( "plc" , SUPPLIER_NAME )= 0 .AND. @isini( "Supplies" , SUPPLIER_NAME ) = 0 .AND. @isini( "College" , SUPPLIER_NAME )= 0 .AND. @isini( "trading" , SUPPLIER_NAME )= 0 .AND.@isini( "University" , SUPPLIER_NAME )= 0 .AND.@isini( "Edinburgh" , SUPPLIER_NAME )= 0 .AND. @isini( "centre" , SUPPLIER_NAME )= 0 .AND. @isini( "Agency" , SUPPLIER_NAME )= 0 .AND. @isini( "Design" , SUPPLIER_NAME )= 0 .AND. @isini( "limited" , SUPPLIER_NAME )= 0 .AND. @isini( "services" , SUPPLIER_NAME )= 0 .AND. @isini( "photography" , SUPPLIER_NAME )= 0 .AND. @isini( "International" , SUPPLIER_NAME )= 0

Extraction criteria: Pick out round numbers

AMOUNT - ((@int( AMOUNT /50))*50) = 0

case sensitive

• fred1 = FRED1

• Upper_case_userID = @upper(userID)

Key points

• Authority• Be assured, data is gettable!!• Speak to right person• Read-only access• Be inventive and creative• Reasonableness check• Training courses• Keep notes

Happy to answer questions

ContactKate.Crichton@ed.ac.uk