Turning untidy data into a target rich environment

Turning untidy data into a target
rich environment
Hugh Thomson
Principal Audit Manager
City of Edinburgh Council
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
• Audit sampling
– plus ability to extrapolate results
• Running data matches for clients
• Summarising big data files for Corporate
• Importing print and pdf files
• Exporting / file reformat to excel
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
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
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
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
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
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
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
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
• Employee transition to self employed £24,000
jumps to £230,000
• Husband with minibus got £690,000 over 3
• 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
Corporate Procurement
• Banging drum re EU compliance to no avail
• Each department spend on suppliers > EU
– matched against corporate and departmental
contract database
– asked Heads of Service to explain breaches
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
Inhibitions & Charges
• Council charge on property rather than force
• 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
• Enabled sorting by address / random sampling
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
• Results evidenced, repeatable & can be
New Risks
• DI Hatton from Police Scotland Counter Fraud
• 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
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
Controls don’t always do what
Humane squirrel traps
Rivets at Forth Road Bridge
Cash check the day before payday
Signatures on form for adding supplier
In Summary
• Do a little bit at a time
• Give clients evidence of control failure
• Encourage staff to use IDEA or ask you to
• Advertise benefits
– Import / enhance / analyse / export to excel
• If you have a question about your
organisations data IDEA can probably help
answer it
That’s all Folks!
• Thank you for your time
• Feel free to contact me to talk through the
detail of any part
• [email protected]
• Any Questions?

similar documents