Payroll Audit Analytics Workbook: Payroll Audit Template with Benford Test and Exception Register
Full description
For internal auditors, financial controllers, HR and payroll managers and general managers who want to test a payroll export. Paste up to 800 payroll lines and the employee list, and the workbook flags duplicate bank accounts and IDs, employees paid after termination, overtime outliers by z-score, round-number payments, rate changes without an approval reference, hours above a limit and paid hours above the roster. A first-digit (Benford) test reports a chi-square statistic, digit z-scores and the mean absolute deviation. Flagged lines get risk points and a priority, appear in an exception register and are tracked on a follow-up tab. The Word and PDF guide explains each test and how to follow it up.
Save with a bundle: this workbook is also in the F&B Control & Audit Pro Bundle, 25 workbooks for $59 instead of $310. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.
What is inside
- Payroll_Audit_Analytics_Workbook.xlsx: 11 tabs, 26,978 formulas, 3 native charts, 15 integrity checks, 16 method rows
- Payroll_Data and Employees tabs: paste an export of up to 800 lines and an employee list of up to 150; the sample holds 600 lines for 102 employees over 6 monthly pay periods
- Tests tab: seven line tests (shared or repeated bank account, paid after termination, overtime z-score outlier, round-number payment, rate change without approval reference, hours above limit, paid hours above roster)
- Statistics tab: overtime mean, SD and outliers by department, paid against rostered hours, and a first-digit (Benford) table with chi-square, digit z-scores and MAD
- Exceptions tab: every flagged line in payroll order with the tests triggered, risk points and High, Medium or Low priority; room for 150 lines
- Follow_Up tab: status, owner, explanation and amount at risk for each reviewed line; 22 sample entries, room for 60
- Settings tab: every limit, weight and threshold is an editable input (hours limits, z-score limit, round unit, rate tolerance, chi-square critical value, priority points)
- Dashboard: 9 KPI tiles, a model check tile, a test summary and 3 charts
- Payroll_Audit_Analytics_Guide.docx and .pdf: 12 A4 pages covering each test, the first-digit method, scoring, how to follow up, a worked example, checklist and glossary
- README.txt and LICENSE-and-Terms.txt
How it works
- Set the audit period on Settings and confirm every limit and weight. Hours and rate limits depend on law and policy, so check them locally.
- Paste your employee list on Employees and your payroll export on Payroll_Data. Use codes, not names or account numbers.
- Read the Tests and Statistics tabs. Confirm the Checks tab reads ALL OK.
- Work the Exceptions register from High priority down, using the follow-up questions in the guide.
- Record the status, owner and explanation for each reviewed line on Follow_Up and read the Dashboard.
Details
- Formats: XLSX workbook, DOCX and PDF guide
- Language: English
- Software: Excel 2016 or later (Windows or Mac), or LibreOffice Calc 7 or later
- No macros, no external links, no pivot tables
- Instant digital download
Good to know
- Sample data is illustrative and describes no real property. Replace it with your own payroll export.
- Hours limits, weights, thresholds and the chi-square critical value are examples. Verify legal limits, local requirements and your audit method.
- The first-digit test suits amounts that spread widely. It is a pointer, not proof. A flag is not a finding.
- Payroll data is personal data. Handle and store it as local law and your policy require.
- Not audit, legal, tax or accounting advice. Single-user license.
FAQ
Which tests does the workbook run?
Seven line tests: shared or repeated bank account, payment after termination, overtime outliers by z-score, round-number payments, rate changes without an approval reference, hours above a limit and paid hours above the roster. A first-digit (Benford) test runs on the payment amounts as an eighth, whole-file test.
Can I change the limits?
Yes. Hours limits, the z-score limit, the round-number unit, the rate tolerance, the roster tolerance, the chi-square critical value, the weights and the priority points are all inputs on Settings. The sample values are examples, so confirm legal and policy limits locally.
How does the first-digit test work?
It compares how often each leading digit appears in the payments with the expected share, log10(1 + 1/d). It reports a chi-square statistic against your critical value, a z-score for each digit and the mean absolute deviation. It works best when amounts spread over several orders of magnitude, and it points to where to look rather than proving a problem.
Does a flag mean fraud?
No. A flag is a reason to look. Many flagged lines have a normal explanation, which is why the Follow_Up tab records the evidence and the outcome for each line.
How many lines can I test?
The tabs hold up to 800 payroll lines and 150 employees. The Exceptions register holds up to 150 flagged lines. The sample has 600 lines.
