Using AI to set audit materiality and pick your sample can save you hours, but it can also leave a hole in the file that a reviewer will find in minutes. This post shows how to build an Excel template that does both fast, while keeping the judgement and the audit trail where the standards expect them.

I spent six years in audit at BDO. Materiality and sample selection were the bits everyone rushed through, right? Planning deadline, partner waiting, a long listing of invoices staring back at you.

I built it this week, and it’s fast

This week I’ve been building a template that does both with AI. Excel plus a few prompts, nothing fancy. Pick the benchmark, apply the percentage, set performance materiality, pull the sample from the population.

It works. Super fast!

But honestly? Fast is exactly where the danger is.

AI gives you a number, not a reason

AI will happily give you a materiality figure. It won’t tell you whether that benchmark makes sense for this client.

ISA 320 says plainly that determining materiality is a matter of professional judgement. Its application material lists what should drive the benchmark: what the users of these particular statements focus on, the nature of the entity and where it is in its life cycle, how it’s financed, and how volatile the benchmark is. A property company funded mostly by bank debt and a loss-making start-up shouldn’t land on the same benchmark just because you typed the same prompt.

And it’s the auditing standards that make you write it down. ISA 320 requires the file to show overall materiality, any specific materiality levels, performance materiality and any revisions, together with the factors you considered in setting them. ISA 230 goes further: significant professional judgements must be documented well enough for an experienced auditor, with no previous connection to the audit, to understand them. Both sit in the IAASB Handbook of international standards, and ICAEW has a practical guide on documenting significant professional judgements if you want the detail.

So can AI draft the rationale? Sure, it’s good at that. But you challenge it, and your name goes on it.

“Pick 25 items for me” is not a sampling method

Same with sampling. Ask a chatbot to pick 25 items from a listing and it will. Now try explaining in a working paper how it chose them.

ISA 530 on audit sampling asks for things a prompt doesn’t give you. Design the sample around the purpose of the test and the characteristics of the population. Set a sample size that reduces sampling risk to an acceptably low level. Select items so that every sampling unit in the population has a chance of selection. The application material adds that the sample should be representative, so bias is avoided, and names random, systematic and haphazard selection as the main methods.

A language model “choosing” items from a list it has read isn’t a method you can describe, repeat or defend. My advice? Use AI to write the formula or the Power Query step, then let the formula do the selecting.

The audit trail is where files fall over

And the one that gets missed most, the audit trail. If a reviewer (or the regulator) asks how you got from population to sample, can you show every step? Inputs, thresholds, exclusions, how the random selection was generated.

The regulator doesn’t care that the AI did it. The file has to stand on its own.

One trap worth knowing before you build anything. Excel’s RAND function returns a new random number every time the worksheet is calculated. Build your selection on live RAND formulas, hit Refresh, and your sample quietly changes. Now your file shows 25 items you never tested, and the 25 you did test are gone. Does this ring a bell?

Build the template so the trail creates itself

Every assumption visible, every step reproducible. Hit Refresh and you get the same answer. Here’s how I’d set it up.

1. Lock the population. Load the listing with Power Query straight from the source file. Record the file name, the date you pulled it, the row count and the total, and reconcile that total to the trial balance before you do anything else.
2. Put every input on one assumptions tab. Benchmark, benchmark amount, percentage, performance materiality, the threshold for items you’ll test in full. Next to each one, a text cell with the reason, in your own words. That’s where the ISA 320 factors live.
3. Make exclusions explicit. Items above the threshold, items covered by other procedures, anything you strip out goes into its own table with a reason column. Then add a check: full population equals excluded items plus the population you sample from.
4. Generate the random numbers once, then freeze them. Create them with RAND, paste them as values, and note the date and who generated them. Select your sample from those frozen numbers. Now a refresh gives you the same 25 items every time, and the reviewer can follow exactly how they were picked.
5. Keep a log of what the AI did. If AI drafted the benchmark rationale or wrote your formulas, paste the prompt and the output into a log tab, and note what you changed. The reviewer sees where the tool helped and where your judgement came in.
6. Finish with check figures. Sample count, sample value, population reconciled, exclusions reconciled. If anything doesn’t tie, the template should tell you before the reviewer does.

That’s it! None of this needs a big budget. It needs a bit of discipline at the start, and then the template does the boring part every time.

AI speeds up the work. It doesn’t sign the opinion.

The speed is real, and I’m not giving it back. But speed was never the hard part of materiality or sampling. The hard part is a benchmark you can justify, a method you can explain, and a trail someone else can follow without calling you.

Those stay with you, whichever tool you use. Do yourself a favour and build them into the template from day one. Patching the file the night before review is no fun, trust me.

Auditors, would you trust an AI-built sample in your file? And if a reviewer opened it tomorrow, could they follow every step?

If you’d like help building a template like this for your team, it’s exactly the kind of finance automation work I do through Forte Consultum.