Data analytics

Internal Audit → Data Analytics

Import a population — a payment run, a joiner list, an access export — agree it is the whole population, then test it and draw a sample from it. Everything a report later says about what was examined traces back to here.

Why completeness comes first

A test over a population nobody proved was complete reads like evidence and isn't. If someone exports a spreadsheet, runs a duplicate-payment test over it and reports "no duplicates", that conclusion is worth nothing unless the spreadsheet was the whole population — and the report it lands in will not say which it was.

So a dataset arrives as a draft. You can look at it, read its column profile and preview its rows — but nothing can be tested over it until you record how you satisfied yourself it is complete. Once you have, its completeness is agreed and it locks.

Status	What you can do
Draft — not yet testable	Preview, profile, edit its details, record completeness, delete it
Completeness agreed	Everything above is frozen. This is the population testing will cite.

Importing

Import extract takes a CSV, .xlsx or .xls file. Give it a name and, ideally, the source system it came from — ERP — Accounts Payable tells the next reader more than export_final_v2.csv does.

What the file has to look like

The first row that has anything in it is the heading row; everything below it is data, and blank rows are dropped. A few files are refused outright, each for the same reason — a population you cannot reconcile to the source report is not worth testing:

The file	What you are told
Has a column with no heading	Every column needs a heading — an unnamed column cannot be reconciled to the source report
Has two columns with the same heading	Two columns share a heading. Give each one a distinct name
Has headings but no rows, or nothing at all	That file has no data in it
Is an Excel file renamed to .csv	That CSV could not be read
Has more than 100,000 rows	The row count, and that this is not the right tool for that population
Has more than 150 columns, or more than 5 million values	The count, and to drop the columns the test does not need
Is larger than 50 MB	The size, and to split it by period

A workbook is read from its first worksheet. If the extract you want is on a later tab, save that tab as its own file before importing. Up to 150 columns are read. Rows shorter than the heading row are padded, not rejected — a trailing empty cell is not a defect.

100,000 rows is a stated ceiling, not a discovered one. Above it the import is refused rather than truncated: importing part of a population would make every test over it misleading, and quietly is the worst way to find that out. Split the extract by period, or test it in a dedicated analytics tool.

The column profile

On import every column is profiled — over every row, not a sample — and you get its type, how many values are filled, how many are distinct, and for numeric columns the total. That total is the figure you will reconcile against the source report.

Type inference is deliberately strict. A column is only numeric when every value in it is a number; only a date when every value is a date. One "see credit note" in an amount column makes the whole column text, with no total. That is the single most common reason a control total will not tie, and the tool tells you rather than quietly coercing and producing a figure that is wrong in a direction nobody checks. Blanks are not counted against a column — N/A, - and empty cells are read as absent, not as text.

Excel formula cells import as their last computed result, which is the figure the source report showed. Dates import as the date the cell says, whatever your server's timezone.

Amounts and dates are tidied into one form when they are stored. A finance export usually writes 15,000.50 and 15/01/2026; those are stored as 15000.5 and 2026-01-15, because that is the form the tests can read. The profile keeps a couple of your originals beside the column so you can still see what the file said.

A date column has to say which half is the day. 15/01/2026 is unmistakable — there is no fifteenth month — so the whole column is read that way. A column where every value could be read both ways, like 05/03/2026, stays text and says so, with a note asking you to export it as YYYY-MM-DD. Guessing would make every weekend-posting and cut-off test wrong in a direction you could not check afterwards.

Personal data

Columns that look like they hold bank accounts, national identifiers, email addresses, phone numbers, salaries, dates of birth or personal names are flagged at import — first by the column heading, and then by the shape of the values, so a column called ref full of IBANs is still caught. The flags show on the dataset list, against the column in the profile, and as a banner on the dataset itself.

The data stays on your own server. What was imported, and which columns were flagged, is written to the audit log at the moment of import.

Recording completeness

Four methods, on the dataset page. Pick the one that matches what you actually did — the method you choose decides what you are asked for.

Method	What you record	What the tool contributes
Record count	How many records the source system reported	The number of rows it imported
Control total	Which column to total, and the figure on the source report	The total of the imported rows
System owner confirmed	A note saying who confirmed the extract covers the full period	Nothing — the note is the evidence
Other	A note explaining how you established completeness	Nothing — the note is the evidence

Only numeric columns are offered as the column to total, which is why a stray N/A in an amount column has to be dealt with at source rather than worked around here.

A variance is allowed. An unexplained one is not. If your extract totals 138,151.25 and the source report says 140,000.00, that is fine — write down what accounts for the difference. Credit notes posted after the extract was taken, a different cut-off, an excluded company code. What you cannot do is leave the difference sitting there unexplained and carry on. The same applies to a record count that does not match.

Where a note is required it has to be a sentence, not a word. "Timing" is not an explanation of a 1,848.75 variance; the person reading this in nine months is the point of writing it.

Agreeing completeness

Record completeness as agreed stays disabled until the assertion holds, and says beside it what is still missing — the method, a source figure, or the explanation. When you press it, the dataset locks, and your name and the date go on the record next to a sentence describing how completeness was established:

Control total of amount agreed to the source report.

14,207 records imported against 14,240 reported by the source (variance −33); explained.

After that the assertion cannot be edited and the dataset cannot be deleted. It is the basis of any testing done over it, and editing it afterwards would rewrite the basis of work already finished. If the extract has changed, import it again — that is a new dataset, with its own file fingerprint.

The file fingerprint

Every import records a SHA-256 hash of the file. If you import what should be the same extract twice, the hash tells you whether it really was the same file.

Reading a dataset

• Columns — the profile: type, filled, distinct, total.
• First 25 rows — a preview, so you can see the extract parsed the way you expect before you reconcile anything. It is a look at the data, not a browser for it; the full population is held for testing, not for reading by hand.
• The heading carries the row and column counts, the source system, the file name and the first half of the file hash.

Who can do what

Action	Who
See the datasets and their profiles	Anyone who can see Internal Audit — viewer and above
Import an extract, record completeness, agree it	Anyone who can perform fieldwork — analyst and above
Delete a draft dataset	Organisation administrator
Delete a dataset whose completeness is agreed	Nobody. It may be the population behind completed testing.

Unlike engagement work, a dataset is not scoped to an engagement team: the organisation's audit function shares one library of imported populations.

How analytics relates to your audits

Data analytics is a tool in its own right, not a part of any one audit. It lives in its own menu, it works on populations your function has imported, and it does not appear inside a control assessment.

It reaches your audit work in exactly one way: when a result proves something, you export it and attach the file to an audit step as evidence. From that point the file behaves like every other piece of evidence — reviewed with the working paper, carried into the report, retained under the same policy.

Nothing is bound. The analysis does not become one step's property, the same result can support two steps if two steps rely on it, and removing the evidence later does not reach back and change the analysis.

That ordering is deliberate, and it matches how the work actually goes: get the extract, look at it, find something, and only then decide which part of the audit it supports. The earlier design asked which step a test belonged to before the test had been run, and it showed — a treasury sample was recorded against a vendor-onboarding step, twice, with nothing objecting.

Testing a population

Open a population under Data Analytics and use the Tests section.

Run a test opens a panel that asks for two things, because the first narrows the second:

1. The test. Seven of them, each with a line saying what it catches. A test the chosen extract cannot support is disabled and says why — Benford's Law over 412 rows, a value test on a population with no numeric column.
2. The parameters. The columns offered are filtered to the right type, so you cannot total a text column by accident.

Only populations whose completeness you have agreed can be tested at all.

Test	What it catches
Duplicate records	Duplicate payments, vendors, invoice numbers
Gaps in a sequence	Missing cheques, purchase orders, journal numbers
Value tests	Splitting below an approval limit, round numbers, outliers
Date anomalies	Weekend or holiday postings, backdating, entries outside the period
Match two populations	Leavers with active accounts; a vendor bank account matching an employee's
Benford's Law	Figures that may have been fabricated or manipulated
Stratification	The shape of the population, and aging bands

Weekend and holiday tests use your organisation's calendar. The default weekend is Friday and Saturday. A holiday test needs the holidays recorded, and says so rather than reporting nothing.

Benford's Law refuses more often than it runs, on purpose. It needs at least 500 values and a naturally occurring amount. Over an invoice number, or over 60 rows, it produces a statistic nobody should act on — so it declines and tells you which condition failed.

Reading a result

A result is a worklist, not a verdict. The top of a run is the triage: how many items met the criteria, how many you have explained, how many you recorded as errors, and how many became findings.

Three things the wording is careful about:

• Zero is not a pass. A run that matched nothing reads "No items met the test criteria." It never says passed, and it is not coloured like success. One test over one population is not a conclusion about a control.
• A duplicate pair is one item. You explain "paid twice" once, not once per copy. The item carries both rows.
• A gap is a range. A cheque that was never issued has no row to point at, so the item is the missing range and its count.

Above 10,000 matching items the run stores the first 10,000 and tells you the true number, with a prompt to narrow the test. It does not quietly truncate.

Deciding what each item is

Every item starts unexplained, and the worklist opens filtered to those, because they are the work. Each one is then:

Decision	Means
Explained	Investigated, not an error. Needs a note.
Error	A real error, below the reporting threshold. Needs a note.
To report	One to raise as a finding. Needs a note.

Every decision except leaving an item open needs a note, so the field appears as soon as you choose one and nothing is saved until you have written something.

"To report" does not create a finding. It marks the item and carries that mark into the exported file. The finding itself is raised where findings are raised — on the audit step, from its working paper's exception verdict — so every finding in the system came through the same door.

Select several items and decide them together. A duplicate run over a real payment file returns a lot of items with the same explanation; select them, write one note, and record them in a single action.

A signed-off audit step does not freeze your analysis — the two are separate. What it does refuse is new evidence: you cannot attach a file to a working paper a reviewer has already approved. Return the step for rework first.

Sampling

Sampling works the same way testing does, and in the same two places.

Sampling has its own section under a population, beside Tests. A test examines the whole population for something a computer can check; a sample picks items for a person to inspect. The two are separate because choosing between them is a real decision, not a setting.

Like a test, a sample is attached to an audit step as evidence when you are ready. The exported file carries the method, the size and its basis, the selection seed, every selected item with what testing found, and the conclusion those outcomes support — so the step's "items examined" is backed by a document that says where the items came from and how they were chosen.

Choosing a size

Two calculators, because they answer different questions:

• Attribute (testing whether a control operated): confidence, tolerable deviation rate, expected deviation rate. The familiar 95 % confidence, 5 % tolerable, 0 % expected gives 59 items.
• Monetary unit (testing value): population value, tolerable misstatement, expected misstatement, confidence. It returns a sampling interval as well as a count, and selection is proportional to value, so a large invoice is likelier to be picked than a small one.

Both correct for small populations, and if the risk model asks for more items than the population holds, the whole population is examined and the panel says so. The inputs are stored with the sample, so the basis for the size is on the record rather than in somebody's memory.

Judgmental samples need a written reason. That reason is their entire basis.

Working the sample on a spreadsheet

Testing 56 items means chasing 56 documents, usually over a week, usually not in front of this screen. So the sample goes out and comes back:

1. Export sample downloads the drawn rows with three empty columns — Outcome, Note, and Audited amount.
2. Work it wherever you work: email it to the team, split it, annotate it.
3. Upload worked sample reads it back and records every outcome at once.

The file is matched on its Row index column, so you can reorder, re-sort and add columns of your own without breaking it. Anything else it is careful about:

• It reads the words people actually type — exception, fail, deviation; ok, pass, none; untested, pending, not tested.
• It refuses yes and no, because nobody can tell whether the column meant "exception?" or "ok?" — and a wrong guess is a wrong conclusion.
• A word it cannot read is reported on its line number; it is not defaulted.
• A blank outcome stays untested. It is never read as "no exception", because that would turn work nobody did into evidence that a control operates.

After the upload it tells you how many outcomes it applied, which row indexes did not belong to this sample, what it could not read, and how many items are still untested. You can upload again after fixing the file; the last answer for a row wins.

Recording what you found

You can also record outcomes one item at a time in the browser, which is the right way for a small sample. Each drawn item is untested, no exception or exception. Two rules that matter:

• Untested items are counted and shown, never treated as clean. While any item is untested there is no deviation rate, no upper limit and no conclusion — the panel says so in words.
• The exception count is derived from the items. It stops being a number you type; it is recomputed every time you record an outcome, so the exception figure in your report and its sample size come from the same evidence.

Once tested, an attribute sample reports the upper deviation limit against your tolerable rate. This is usually the number that decides: one deviation in 59 items looks like 1.7 %, but the population could plausibly be above 5 %, and the sample then does not support relying on the control. A monetary-unit sample reports projected misstatement against tolerable misstatement.

Drawing again does not replace an earlier sample. Both stay, and the Sampling section lets you switch between them — two auditors sampling one population are not overwriting each other's work.

The four numbers on the working paper

The audit step's Testing tab asks for the population, sample size, method and exceptions. Those stay yours to state. Analytics does not write them: you attach the sample's exported file as evidence and record the figures that file supports, the same way you would from any other document.

This matters for a case that is easy to lose: a walkthrough or an interview has items examined and no population, and that is legitimate work. A field that fills itself in from somewhere else cannot express it.

How long the data is kept

The rows of an imported population are the sensitive part: payroll lines, bank accounts, national identifiers, copied out of a source system so tests can run over them. They are not kept indefinitely.

When you agree a population's completeness, it gets a retention date — two years later by default, and your organisation can set a different period. After that date an overnight job deletes its rows.

What is deleted is the copy of the data. What stays is the audit evidence. The dataset, its column profile, the completeness assertion with your name on it, every test run with its parameters and results, every item you dispositioned and every sample item you tested all survive the purge — deleting those would remove the basis of a finding while the report that cites it is still in issue.

A purged population reads "rows purged on ...", its preview is empty, and running a new test over it is refused rather than returning "no items met the criteria" over an empty table. To test it again, import the extract afresh.

Settings your organisation controls

Under audit settings, a manager can set:

• The working week. Which days count as the weekend, so "posted on a weekend" means the right thing. The default is Friday and Saturday. At least one day has to remain a working day.
• Public holidays, which the holiday-posting test compares against. Without them that test says it has nothing to compare against rather than reporting nothing.
• How long dataset rows are kept, in months, from the date completeness was agreed.

Not part of this

Continuous monitoring, live connections to source systems, scheduled runs and a scripting language are deliberately out of scope. The tool tests a population you imported and can account for.

Related

• Audit engagements — where fieldwork, steps and workpapers live.
• Audit reporting — what the report says about what was examined.
• Roles and teams — what "fieldwork" means as a permission.