Monetary unit sampling in Excel with Copilot or Claude: worked audit example
Monetary unit sampling selects audit items in proportion to their dollar value, so a $126,500 invoice is far more likely to be tested than a $4,410 one. The method fits receivables confirmations, payables completeness work, inventory price testing and any balance where overstatement is the main risk. Internal auditors and controllers preparing for an external audit use the same logic to choose which invoices, journal entries or fixed asset additions to pull for support.
Excel can run the whole selection with four ordinary formulas: a cumulative total, a sampling interval, a documented random start and a lookup. Copilot in Excel and Claude for Excel can build those formulas from a plain-language request and explain each step, which saves time on a task that many teams still do by hand. They can also introduce errors that are hard to see in the finished list, such as a random start that changes on every recalculation or a large item counted twice.
The worked example below uses a 20-invoice receivables population of $660,000 and a sample of 8 selections. With a random start of 21,347 and an interval of $82,500, the correct selection is 7 distinct invoices worth $442,335, because one invoice is larger than the interval and is hit twice.
··········
MONETARY UNIT SAMPLING: HOW THE SELECTION WORKS.
In monetary unit sampling each dollar in the population is a sampling unit. The selected dollar points to the invoice or line that contains it, and that item is tested in full. A population of $660,000 therefore holds 660,000 units, and an item of $42,300 contains 42,300 of them.
Systematic selection is the most common way to pick the units. The auditor divides the population value by the sample size to get the sampling interval, chooses a random start between 1 and the interval, and then takes every interval-th dollar after the start. Each selection point is matched to the item whose cumulative range contains it.
Two consequences follow from the arithmetic. Any item equal to or larger than the interval is certain to be selected, and an item more than twice the interval can be hit two or more times. Items with zero or negative balances contain no positive monetary units, so they are never selected by this method and need a separate procedure, which is a common gap in AI-built workbooks.
The sample size itself comes from the audit methodology: tolerable misstatement, expected misstatement and the confidence level the auditor needs. Audit sampling is governed by PCAOB AS 2315 Audit Sampling for public company audits, AICPA AU-C 530 Audit Sampling for other US audits and ISA 530 Audit Sampling internationally. The selection workbook takes the sample size as an input; it should never be invented by an AI assistant.
··········
COPILOT IN EXCEL AND CLAUDE FOR EXCEL: DOCUMENTED REQUIREMENTS.
Both assistants work inside the workbook, which keeps the population, the formulas and the selection in one file the reviewer can inspect. The requirements below come from the vendors' own documentation, checked on November 13, 2026.
........
Item | Copilot in Excel | Claude for Excel |
Who can use it | Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Copilot subscription, or a Copilot Chat-eligible business or enterprise plan | Claude Pro, Max, Team and Enterprise plans |
Where it runs | Edit, plan and chat modes on Windows, Mac and the web | Excel on the web, Excel on Windows with Microsoft 365, Excel on Mac 16.46 or later |
Workbook conditions | Editing requires Calculation Options set to Automatic; unsupported formats such as Strict Open XML can fail | Data tables and macros or VBA are unsupported |
Vendor caution | Review and verify anything Copilot creates; Microsoft advises against relying on it for decisions in finance | Not recommended for audit-critical calculations without verification |
Data handling | Workbook changes are visible to anyone with access to the file | Conversations, including workbook content Claude reads, are sent to Anthropic; backend deletion within 30 days with stated exceptions |
........
Microsoft also documents that Copilot conversations cannot be deleted one by one; the user can delete all Copilot activity history instead. For enterprise users, availability depends on the tenant configuration set by the administrator.
Anthropic's documentation for Claude for Excel says answers include cell-level citations, that the add-in warns before overwriting existing data, and that it asks for confirmation before risky operations. It also warns about prompt injection: files from external sources, such as client-provided ledgers or vendor exports, can contain hidden instructions. Audit teams routinely receive populations from clients, so a trusted working copy of the file is the right starting point.
Teams on Microsoft 365 Enterprise or Business plans can also use Python in Excel, which Microsoft documents as available on Windows, the web and Mac Current Channel, with calculations running in the Microsoft Cloud. The selection below does not need it.
··········
BUILDING THE MONETARY UNIT SAMPLE IN EXCEL STEP BY STEP.
The illustrative population sits in columns A and B of a sheet named Population, with invoice numbers in A2:A21 and amounts in B2:B21. The amounts are fictional and chosen to show the edge cases.
........
Invoice | Amount (USD) | Invoice | Amount (USD) |
INV-1001 | 18,450 | INV-1011 | 52,775 |
INV-1002 | 42,300 | INV-1012 | 11,630 |
INV-1003 | 7,980 | INV-1013 | 19,995 |
INV-1004 | 126,500 | INV-1014 | 73,480 |
INV-1005 | 15,275 | INV-1015 | 6,125 |
INV-1006 | 33,600 | INV-1016 | 38,210 |
INV-1007 | 9,840 | INV-1017 | 24,560 |
INV-1008 | 61,250 | INV-1018 | 14,390 |
INV-1009 | 4,410 | INV-1019 | 47,820 |
INV-1010 | 28,900 | INV-1020 | 22,510 |
........
The first step reconciles the population before any selection. The total of B2:B21 is $660,000, and it must agree with the receivables subledger and the trial balance at the test date. A sample drawn from an incomplete population says nothing about the missing items.
The second step builds the cumulative columns. Column C holds the running total and column D the first dollar of each invoice's range, so every invoice owns the dollars from D to C.
C2: =SUM($B$2:B2)
D2: =C2-B2+1
(fill both down to row 21)The third step sets the inputs. The sample size of 8 is typed in G1, the interval is calculated in G2, and the random start goes in G3 as a typed value. Microsoft's documentation for the RAND function states that a new random number is returned every time the worksheet is calculated, and that pressing F9 in the formula bar converts the formula into a fixed value. The same approach applies to RANDBETWEEN: generate the start once, convert it to a value, and record who generated it and when.
G1: 8
G2: =SUM(B2:B21)/G1
G3: 21347 (generated once with =RANDBETWEEN(1,G2), converted to a value, documented)The fourth step creates the selection points and matches each one to an invoice. XLOOKUP with match mode 1 returns the first cumulative total equal to or greater than the point, which is the invoice containing that dollar. A COUNTIFS column on the population sheet shows how many times each invoice was hit.
I2: =G3+(SEQUENCE(G1)-1)*G2
J2: =XLOOKUP(I2#,C2:C21,A2:A21,,1)
E2: =COUNTIFS($I$2#,">="&D2,$I$2#,"<="&C2)
(fill E2 down to row 21)··········
[[ADPLUS_300x250_1]]
PROMPTS FOR COPILOT AND CLAUDE ON THE SAMPLING WORKBOOK.
A good prompt names the sheet, the ranges, the inputs and the checks, and forbids the assistant from choosing the sample size or regenerating the random start. The same prompt works in either assistant.
On the Population sheet, invoices are in A2:A21 and amounts in B2:B21.
Build a monetary unit sample with systematic selection.
Sample size is in G1 (do not change it). Random start is the typed value in G3 (do not replace it).
1. Add a running total in column C and the first dollar of each range in column D.
2. Put the sampling interval in G2 as total amount divided by G1.
3. List the selection points in column I and the invoice that contains each point in column J, using XLOOKUP.
4. In column E, count how many selection points fall in each invoice's range.
5. Flag any invoice with an amount of zero or below, and any invoice larger than the interval.
Do not use RAND or RANDBETWEEN anywhere. Explain each formula you add.A second prompt asks the assistant to check its own work against totals the reviewer can verify by hand.
Check the sample: confirm the sum of column E equals G1, list the distinct selected
invoices with their amounts, give the total selected value and its share of the
population total, and confirm the population total agrees to 660,000.··········
EXPECTED MONETARY UNIT SAMPLE RESULT FOR THE EXAMPLE.
The interval is $660,000 divided by 8, or $82,500. The selection points are 21,347 and every $82,500 after it. The table shows the correct match for each point, computed independently of any AI output.
........
Point | Selection point | Invoice | Cumulative range |
1 | 21,347 | INV-1002 | 18,451 to 60,750 |
2 | 103,847 | INV-1004 | 68,731 to 195,230 |
3 | 186,347 | INV-1004 | 68,731 to 195,230 |
4 | 268,847 | INV-1008 | 253,946 to 315,195 |
5 | 351,347 | INV-1011 | 348,506 to 401,280 |
6 | 433,847 | INV-1014 | 432,906 to 506,385 |
7 | 516,347 | INV-1016 | 512,511 to 550,720 |
8 | 598,847 | INV-1019 | 589,671 to 637,490 |
........
INV-1004, at $126,500, is larger than the $82,500 interval and is hit by points 2 and 3. The sample therefore contains 7 distinct invoices: INV-1002, INV-1004, INV-1008, INV-1011, INV-1014, INV-1016 and INV-1019. Their combined value is $442,335, or 67.0% of the population. The sum of the hit-count column must be 8, and the count of invoices with at least one hit must be 7.
When the misstatement evaluation is performed, the auditor works from the 8 selection points, not from 7 invoices. The double hit on INV-1004 is part of the statistical design and is kept in the evaluation workpaper even though the invoice is examined once.
··········
AI ERRORS TO CHECK IN AN AUDIT SAMPLE WORKBOOK.
The most frequent problem is a volatile random start. An assistant asked to "pick a random start" often writes RANDBETWEEN into the cell, and the whole selection changes the next time anyone edits the workbook or opens it on another machine. The reviewer cannot reproduce the sample, which defeats its purpose. The start must be a typed value with a note of how and when it was generated.
The second problem is the treatment of large items. Some generated formulas remove duplicates and report 8 distinct invoices by quietly shifting a point, and others list INV-1004 twice in the testing schedule. The correct result keeps 8 points and 7 invoices to test, with the double hit visible.
Boundary errors are the third issue. If column D is written as the previous cumulative total without adding 1, a point that lands exactly on a cumulative total can be matched to two invoices by COUNTIFS. A quick test is to type a start equal to a cumulative total, such as 18,450, and confirm that only INV-1001 is selected.
Credit balances and zero items are the fourth. A receivables population with unapplied cash or credit memos produces negative amounts, and the cumulative column then runs backward. Those lines should be extracted and tested separately, and the gross positive population used for the selection.
The last check is the population itself. An assistant will build a correct selection on a filtered range, a stale export or a range that stops one row short of the data. The sum of the selection range must agree to the ledger and trial balance before anything else is reviewed, as described in the year-end close cut-off and accrual tests.
··········
REVIEW AND DATA CONTROLS FOR AI-ASSISTED AUDIT SAMPLING.
The sampling workpaper should record the population source and total, the reconciliation to the ledger, the sample size and its basis in the methodology, the interval, the random start with its origin, and the final list. Copilot and Claude can draft this documentation from the workbook, but the preparer and reviewer sign off on it, and the sample size and tolerable misstatement remain auditor decisions.
Formula review follows the same discipline used for any AI-generated spreadsheet. Each formula the assistant writes should be traced back to the inputs, and the explanation it gives should be compared with what the cell actually calculates. Claude's cell-level citations and Copilot's explanations help with this, and the method is set out in more detail in how to audit and explain Excel formulas with Copilot, Claude or ChatGPT.
Data handling deserves a decision before the client file is opened. Anthropic states that Claude for Excel sends workbook content to Anthropic and does not inherit an organization's custom retention settings, and that its activity is not included in Enterprise audit logs, while Enterprise organizations with the Compliance API enabled do get Claude for Excel sessions through it. Microsoft documents that Copilot's availability and data protections depend on the account type and tenant settings. Firms with client confidentiality obligations should confirm which configuration applies before using either assistant on engagement data. The same controls apply when AI is used to categorize bank transactions or prepare other close workpapers.
··········
MONETARY UNIT SAMPLING WITH COPILOT OR CLAUDE: PRACTICAL CONCLUSIONS.
The selection mechanics of monetary unit sampling are fully deterministic once the population, sample size and random start are fixed, so every AI-built selection can be checked exactly. In the example, any workbook that does not return 8 points, 7 distinct invoices and $442,335 of selected value contains an error.
Copilot in Excel and Claude for Excel are both documented as able to write and explain formulas in the workbook, and both vendors state that outputs must be reviewed, with Anthropic advising against audit-critical calculations without verification. The safe division of work gives the assistant the formula building and the documentation draft, and keeps the population reconciliation, the sample size, the random start and the evaluation of misstatements with the auditor.
The judgement areas sit outside the spreadsheet: the sample size and its risk inputs, the treatment of credit balances and individually significant items, and the projection of any misstatement found. Those decisions follow the applicable sampling standard and the firm's methodology, and no prompt replaces them.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
