About this lesson
Audit checks: tying out a sample and flagging exceptions in Google Sheets
In Google Sheets, tie a sample to its source with a check cell that should be zero: =SUM(B5:B14)-B2. Work out each difference (=C5-B5), then flag it against the threshold with =IF(ABS(D5)>$B$1,"Exception","OK"). Count and total the exceptions with COUNTIF and SUMIF.
Using Excel? Do this lesson in Excel.
=SUM(B5:B14)What you'll practise
Test a sample of Wollemi Engineering's sales invoices: tie the sample to the listing, compare the ledger with each invoice, and flag differences above the clearly trivial threshold.
4 steps · 40 points · the cells outlined in gold below
- tie-outs
- check cells
- materiality threshold
- exceptions
- ABS
- COUNTIF and SUMIF
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Clearly trivial threshold ($) | $500.00 | |||
| 2 | Sample total per listing ($) | $412,418.60 | |||
| 3 | |||||
| 4 | Invoice | Per ledger ($) | Per invoice ($) | Difference ($) | Result |
| 5 | INV-20417 | $18,640.00 | $18,640.00 | ||
| 6 | INV-20452 | $42,915.50 | $42,915.50 | ||
| 7 | INV-20488 | $7,380.20 | $7,392.60 | ||
| 8 | INV-20531 | $63,200.00 | $58,200.00 | ||
| 9 | INV-20577 | $29,874.40 | $29,874.40 | ||
| 10 | INV-20603 | $11,260.00 | $11,260.00 | ||
| 11 | INV-20649 | $85,430.00 | $84,618.90 | ||
| 12 | INV-20690 | $5,126.75 | $5,126.75 | ||
| 13 | INV-20734 | $104,395.25 | $104,395.25 | ||
| 14 | INV-20781 | $44,196.50 | $44,962.10 | ||
| 15 | Sample total per ledger | ||||
| 16 | Check: sample less listing | ||||
| 17 | Number of exceptions | ||||
| 18 | Total of exceptions ($) |