Templates

Accounts Payable Workbook

Cost per invoice and payment costs by method, a three-way match split into quantity and price variances with a tolerance test, a discount calculator against your cost of funds, days payable outstanding three ways, year-end accruals, a duplicate payment review, Forms 1099 under the $2,000 threshold with backup withholding, and a business case for AP automation: ten tabs, one fictional company, no macros.

Enter your own volumes, terms and balances. The workbook prices a cost per invoice of $11.32, splits a $360.50 invoice difference into $196.00 of quantity and $164.50 of price, shows that 2/10 net 30 is worth 37.24% a year, calculates DPO at 42.1, 40.9 or 48.0 days depending on the definition, and pays back an automation project in 5.1 months. A Checks tab ties 50 figures to the book.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 147 free models.

Accounts_Payable_Workbook.xlsx (24 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind the workbook: The Accounts Payable Handbook, PDF and paperback, with the full case, the controls and the checklists.

What is in the workbook

Inputs
every assumption of the case in one place: spend, cost of goods sold, team cost, invoice and payment volumes, payment mix and unit costs, the three-way match example, monthly match statistics, payment terms and spend by terms, the revolver rate, payables balances, accruals, the duplicate review, Form 1099 counts and the automation proposal.
Volumes
cost per invoice, invoices per team member, PO and non-PO invoice counts, payment counts and annual cost by method, the check-to-ACH saving and the virtual card rebate.
Match
the three-way match of invoice 20871 line by line, the split of the difference into quantity and price variances, the tolerance test against the lower of a percentage and an amount, and the first-pass match rate with exceptions by cause.
Discounts
the implied annual rate of any discount term, the decision against the cost of funds, discounts available, taken and lost, and the net gain from taking every discount after interest.
DPO
days payable outstanding in three versions, spend-weighted terms, the value of one day, past-due payables, and the cash released by extending terms.
Accruals
goods received not invoiced, service accruals by category, the total, and the result of the search for unrecorded liabilities.
Duplicates
the scope of the review, flags by test, confirmed duplicates by cause, the duplicate rate and recoveries.
Form 1099
forms by type under the $2,000 threshold, the comparison with the former $600 threshold, and the backup withholding calculation.
Automation
the business case: benefits, costs, net annual benefit, payback in months and cost per invoice after automation.
Checks
figures recalculated by the workbook and compared with the figures printed in the book.

Every assumption is shaded in a light color on the Inputs tab; every other cell is a formula, with no macros, no links to other files and no circular references. Every ratio is guarded against an empty divisor. Replace the inputs with your own company's figures; the Checks tab will then show differences from the book by design.

From The Accounts Payable Handbook. To read: DPO, calculated three ways · the three-way match, worked · finding duplicate payments.