Turning untidy data into a target rich environment

1. Turning untidy data into a target rich environment Hugh Thomson Principal Audit Manager City of Edinburgh Council

2. Barriers to Effective Controls • Size of organisation • Number of legacy systems • Corporate Governance • “Corporate Governance represents a model of self delusion of the triumph of process over purpose” Paul Moore former Head of Group Regulatory Risk at HBOS • Changing emphasis of audit

3. How CEC Uses IDEA • Audit sampling • plus ability to extrapolate results • Running data matches for clients • Summarising big data files for Corporate Accounts • Importing print and pdf files • Exporting / file reformat to excel

4. Zeroing in on target • Comparing databases without unique IDs • Initial search of population not sample • Asking searching questions of population • Identify potential high risk occurrences , or • Gain assurance that all seems well • Conversion of print / pdf reports to enable random sampling or analysis • Converting field types to allow comparison

5. Better understanding your data • Low level bit by bit approach • Duplicate bank accounts • No bank account details • CEC tenants • Social Landlords • Major private landlords • Contrived tenancies • Staff who are landlords

6. Leveraging what we can • Annual staff v benefits test for Revs & Bens • Ran whole Council • 2 significant peaks • Under £13,500 [ignored] • Over £30,000 [test checked Zero Hrs Supply staff] • Sampled middle and hit jackpot • Visibility + deterrent

7. Controls Testing • Continuous auditing rebranded • Effective [facts not opinion] • Can be set up in advance • Negates any down time / learning curve • Fulfils commitment to external auditor

8. Adding value to client • Fuel key fobs 1,600 risk • 3 Databases • Fuel fob no employee no • Driver permit does • Roads / Fleet no access to Payroll • Cleansed data, joined fuel and driver and matched against leavers • Gave weight to other recommendations

9. Showing the client something they don’t already know • Presentation to Revs & Bens SMT • all live claims over 105 • highest 30 • duplicate & missing NINOs • u25, no deps, no partner > single room rate • Not telling us anything / “Thinking ...” • Under 60 over £16,500 savings - Jackpot

10. Staff & Procurement • Aims • Compliance with Council’s Code of Conduct • Compliance with Procurement Laws / Procedures • Verify Value for Money • Target Potential Fraud • Method • Match Payroll v Supplier Database • Postcode + Leading Numbers from Address Exact Matches • Inexact Matches

11. Issues IDEA overcame • Edinburgh’s addressing system • Vendor address over 3+ fields • No unique identifier • Ability to exclude Carers • Split address into separate fields (excel) • Export just fields we needed

12. Results • Employee transition to self employed £24,000 jumps to £230,000 • Husband with minibus got £690,000 over 3 years • Staff invoicing us for same type of work • Partners of Education staff doing training in H&S , Communications etc • Trades / catering / transport v high risk

13. Corporate Procurement • Banging drum re EU compliance to no avail • Each department spend on suppliers > EU thresholds • matched against corporate and departmental contract database • asked Heads of Service to explain breaches

14. Multiple Contracts • DSOs 18 hour contracts to avoid ER NI • Multiple posts to earn required wage • IDEA well placed to identity multiple employee numbers on same NINO • Duplicate key exclusion • Field to match & field that should be different • Also picked up people sharing a NINO

15. Inhibitions & Charges • Council charge on property rather than force sale • Data for audit on robustness of process print report with some cells only populated at start of page / section / change of type • Imported using the populate empty cells function • Enabled sorting by address / random sampling

16. Stratified Random Sampling • As previous used populate empty cells to import NNDR reliefs & exemptions • Stratified random sample to ensure coverage of all categories • As a manager I will get exactly what I want tested • Results evidenced, repeatable & can be extrapolated

17. New Risks • DI Hatton from Police Scotland Counter Fraud Unit • Identified in Police but could be any org • Youngsters being placed in Police as sleepers by organised crime and accessing sensitive information after a fallow period • Staff may have had a drug habit in youth but now clean. After 2-3 years feel confident to restart socially. Filmed by dealer and passed on to organised crime for blackmail

18. Pre IDEA tidy up (Excel) • = find “ “ and @ left and @ mid • split addresses into separate cells • @ upper @ lower @ proper • force change to character set for compatibility • Conditional formatting for duplicates • Highlight anomalies and sort by colour

19. Controls don’t always do what intended • Humane squirrel traps • Rivets at Forth Road Bridge • Cash check the day before payday • Signatures on form for adding supplier

20. In Summary • Do a little bit at a time • Give clients evidence of control failure • Encourage staff to use IDEA or ask you to process • Advertise benefits • Import / enhance / analyse / export to excel • If you have a question about your organisations data IDEA can probably help answer it

21. That’s all Folks! • Thank you for your time • Feel free to contact me to talk through the detail of any part • hugh.thomson@edinburgh.gov.uk • Any Questions?