Purchase Order Template (Excel) With Automatic Totals, Tax and Terms
A free Excel purchase order that adds itself up: line discounts, mixed tax rates, freight and advances. It includes a checklist of what every PO must say and a sheet that matches the delivery and the supplier's bill against the order.
On this page
- Key takeaways
- What a purchase order template in Excel should calculate
- Worked example: a purchase order with discounts, mixed tax and freight
- What every purchase order must say: a copy-ready checklist
- How to fill in and send the purchase order template, step by step
- The receive-and-bill match: checking delivery and invoice against the PO
- Decision rules for mismatches
- Common purchase order mistakes in Excel
- Edge cases the basic template does not cover
- Prices that include tax
- An order-level discount
- Imports, duty and freight from a different company
- Foreign currency
- Partial deliveries and substitutions
- Blanket orders and price breaks
- How to do this in Cash Flow Inventory
- Frequently asked questions
- How do I make a purchase order in Excel that calculates automatically?
- What should a purchase order include?
- What is the difference between a purchase order and an invoice?
- What is three-way matching in purchasing?
- Does this template work in Google Sheets?
- Should freight go on the purchase order?
A purchase order template in Excel is a sheet where you list each item, quantity and unit price, and formulas work out the line discount, the net amount, the tax, the freight and the order total for you. You send it to the supplier as your written commitment to buy. Later you check the delivery and the supplier’s bill against it before you pay.
On this page you get a free downloadable file with three sheets: the PO lines, the PO totals and a receive-and-bill match. Every formula is explained, so you can also build the file yourself. You also get a fully worked example with numbers that add up, and a checklist of what every purchase order must say. After that come the mistakes and edge cases that cause most supplier disputes, and how the same process runs in Cash Flow Inventory once a spreadsheet stops being enough.
Free download: Purchase order template with automatic totals
An Excel file (.xlsx, 13 KB) with the formulas already in place and the example from this guide filled in. It opens in Excel, Google Sheets (File › Import), Numbers and LibreOffice. No sign-up needed.
Key takeaways
- Every computed cell should be a formula: net = quantity × unit price × (1 − discount), tax = net × rate, total = net + tax. Never type a total by hand.
- Put the tax rate on each line, not once at the bottom, so mixed rates and freight tax come out right.
- A PO is only useful if it states the terms: delivery date, delivery terms, payment terms, units of measure, and the instruction that the invoice must quote the PO number.
- Do not pay a bill until it matches the order and what actually arrived. Quantity and price differences are where money leaks.
- When orders, partial deliveries and bills start to overlap, move to a system that makes the bill from the receipt, so the three can never drift apart.
What a purchase order template in Excel should calculate
A purchase order does two jobs. It tells the supplier exactly what you want and on what terms. It also gives you a fixed reference to check the delivery and the bill against. A good template does the arithmetic so the second job is easy.
PO lines sheet
The file has a sheet called PO lines with these columns. You type the first seven and the tax rate. The rest are formulas.
| Col | Header | What it holds | Example row 2 | Formula row 2 |
|---|---|---|---|---|
| A | Line | You type it | 1 | — |
| B | Item code | You type it | KW-101 | — |
| C | Description | You type it | Stainless saucepan 20 cm | — |
| D | Unit | You type it | each | — |
| E | Qty | You type it | 24 | — |
| F | Unit price | You type it | 18.50 | — |
| G | Discount % | You type it | 0.0% | — |
| H | Net amount | Net amount = ROUND(Qty × Unit price × (1 − Discount %), 2) |
444 | =ROUND(E2*F2*(1-G2),2) |
| I | Tax rate | You type it | 15.0% | — |
| J | Tax amount | Tax amount = ROUND(Net amount × Tax rate, 2) |
66.60 | =ROUND(H2*I2,2) |
| K | Line total | Line total = Net amount + Tax amount |
510.60 | =H2+J2 |
In Excel, row 2 reads the formulas above in column H, column J and column K. Copy the row down for every new line.
Filling down: each formula is shown for the first row of data. Copy it down the column and the spreadsheet moves the row numbers for you (B2 becomes B3, B4 …); references with $ signs stay fixed.
The PO totals sheet pulls everything together:
Goods before discounts = SUMPRODUCT(Qty, Unit price)
Line discounts = Goods before discounts − Net goods
PO total = Net goods + Freight + Tax on goods + Tax on freight
Balance to bill = PO total − Advance paid
Freight sits on the totals sheet as a separate cost line with its own tax rate. Freight is often taxed differently from the goods, and it may arrive on a different bill.
Worked example: a purchase order with discounts, mixed tax and freight
Example: a shop selling kitchenware online and in store orders from a cookware wholesaler. This is a hypothetical business. Amounts are in any currency, and the tax rates (15% standard, 5% reduced on books) are illustrative only, so use your own. The supplier gives 5% off frying pans and 10% off chopping boards.
| Line | Item | Qty | Unit price | Disc. | Net | Tax rate | Tax | Line total |
|---|---|---|---|---|---|---|---|---|
| 1 | KW-101 Stainless saucepan 20 cm | 24 | 18.50 | 0% | 444.00 | 15% | 66.60 | 510.60 |
| 2 | KW-102 Non-stick frying pan 28 cm | 36 | 22.00 | 5% | 752.40 | 15% | 112.86 | 865.26 |
| 3 | KW-205 Chef’s knife 20 cm | 20 | 31.75 | 0% | 635.00 | 15% | 95.25 | 730.25 |
| 4 | KW-310 Bamboo chopping board | 50 | 7.40 | 10% | 333.00 | 15% | 49.95 | 382.95 |
| 5 | KW-412 Silicone spatula set | 60 | 5.25 | 0% | 315.00 | 15% | 47.25 | 362.25 |
| 6 | KW-520 Recipe book | 15 | 12.00 | 0% | 180.00 | 5% | 9.00 | 189.00 |
| Lines total | 2,659.40 | 380.91 | 3,040.31 |
The totals sheet then adds freight and the advance:
| Item | Amount | How it is worked out |
|---|---|---|
| Goods before discounts | 2,736.00 | Sum of qty × unit price |
| Line discounts | 76.60 | 2,736.00 − 2,659.40 (39.60 on pans, 37.00 on boards) |
| Net goods | 2,659.40 | Sum of net amounts |
| Freight | 120.00 | Quoted by supplier |
| Tax on freight (15%) | 18.00 | 120.00 × 0.15 |
| Tax on goods | 380.91 | Sum of line tax |
| Total tax | 398.91 | 380.91 + 18.00 |
| PO total | 3,178.31 | 2,659.40 + 120.00 + 398.91 |
| Advance paid with order | 1,000.00 | Deposit agreed with supplier |
| Balance to bill | 2,178.31 | 3,178.31 − 1,000.00, if everything arrives as ordered |
A quick check: the lines total of 3,040.31 plus freight with its tax of 138.00 also gives 3,178.31. If those two routes ever disagree in your sheet, a formula has been overwritten.
What every purchase order must say: a copy-ready checklist
The numbers are the easy part. Most supplier disputes come from what the PO did not say. Check each order against this list before you send it.
- PO number and date, unique and never reused. Add a revision number (PO-1042 rev 2) if you change it after sending.
- Your legal name, billing address and tax registration number, plus the delivery address if it is different.
- Supplier’s legal name, address and tax number, as they appear on their invoices.
- For each line: your item code, the supplier’s code, a description, the unit of measure (each, case of 6, kg), quantity, unit price, discount and tax rate.
- Currency, and whether prices include or exclude tax.
- Required delivery date, and whether partial deliveries are accepted.
- Delivery terms: who pays freight and who carries the risk in transit. For cross-border orders, name an Incoterms rule from the International Chamber of Commerce and a named place.
- Payment terms (for example 30 days from invoice), and any advance or deposit.
- Invoice instruction: “Quote this PO number on every invoice and delivery note.”
- Quality and returns: what happens to damaged, short or substituted goods.
- Approval: who approved it and up to what amount.
If your orders begin as internal requests from staff, record those first as a purchase requisition, then raise the PO from the approved request. Check supplier minimums before you finalise quantities. Our guide to minimum order quantity covers how to handle MOQs that exceed what you need.
How to fill in and send the purchase order template, step by step
- Save a copy of the file under the PO number, for example PO-1042 Cookware Wholesale.xlsx. Never overwrite a PO you have already sent.
- On PO lines, delete the example rows except row 2. Enter your first line, then copy row 2 down for each further line so the formulas follow.
- Enter quantities in the supplier’s selling unit. If they sell cases of 6 and you count singles, order in cases and note the conversion in the description.
- Enter each line’s tax rate as a decimal formatted as a percentage (0.15 shows as 15%).
- On PO totals, enter freight, its tax rate and any advance. Make sure the SUM ranges cover all your lines if you added more than six.
- Check that the lines total plus freight and freight tax equals the PO total.
- Have the order approved if it is above your limit, then export it to PDF and send it. Send the PDF, not the editable file.
- File the sent PDF where the person checking bills can find it.
Quantities should come from your stock position, not a guess. If you track stock in a sheet, our inventory spreadsheet template with reorder alerts tells you what to order. The reorder point calculator tells you when to order.
The receive-and-bill match: checking delivery and invoice against the PO
The PO only protects you if you compare it with what arrived and what you were billed. This is often called a three-way match: order, goods receipt, bill. The third sheet in the file, Receive and bill match, does it line by line.
Receive and bill match sheet
Its first columns pull from the PO lines sheet. You type in three things. Formulas then show the rest.
| Col | Header | What it holds | Example row 2 | Formula row 2 |
|---|---|---|---|---|
| B–E | Item code, quantity ordered, PO unit price and discount | Pull from the PO lines sheet | B KW-101 C 24 D 18.50 E 0.0% |
B2 ='PO lines'!B2 C2 ='PO lines'!E2 D2 ='PO lines'!F2 E2 ='PO lines'!G2 |
| F | Qty received | The quantity received (from the delivery count) | 24 | — |
| G | Qty billed | The quantity on the supplier’s bill | 24 | — |
| H | Billed unit price | Unit price on the supplier’s bill | 18.50 | — |
| I | Received vs ordered | Received vs ordered = Qty received − Qty ordered |
0 | =F2-C2 |
| J | Billed vs received | Billed vs received = Qty billed − Qty received |
0 | =G2-F2 |
| K | Price difference | Price difference = Billed unit price − PO unit price |
0 | =H2-D2 |
| L | Value at issue | Value at issue = ROUND((Qty billed × Billed price − Qty received × PO price) × (1 − Discount %), 2) |
0 | =ROUND((G2*H2-F2*D2)*(1-E2),2) |
| M | Status | A status column reads “Match”, “Check quantity”, “Check price” or “Check qty and price”. | Match | =IF(AND(G2=F2,H2=D2),"Match",IF(AND(G2<>F2,H2<>D2),"Check qty and price",IF(G2<>F2,"Check quantity","Check price"))) |
Filling down: each formula is shown for the first row of data. Copy it down the column and the spreadsheet moves the row numbers for you (B2 becomes B3, B4 …); references with $ signs stay fixed.
How the sheets link: Receive and bill match column B (Item code) reads PO lines (another sheet in the file). Receive and bill match column C (Qty ordered) reads PO lines (another sheet in the file). Receive and bill match column D (PO unit price) reads PO lines (another sheet in the file). Receive and bill match column E (Discount %) reads PO lines (another sheet in the file).
Continuing the kitchenware example. The delivery arrives with 30 frying pans instead of 36. The supplier’s bill charges for all 36, and it charges 32.50 per chef’s knife instead of the agreed 31.75. Everything else matches.
| Item | Ordered | Received | Billed | PO price | Billed price | Value at issue | Status |
|---|---|---|---|---|---|---|---|
| KW-102 Frying pan | 36 | 30 | 36 | 22.00 | 22.00 | 125.40 | Check quantity |
| KW-205 Chef’s knife | 20 | 20 | 20 | 31.75 | 32.50 | 15.00 | Check price |
| Other four lines | 149 | 149 | 149 | — | — | 0.00 | Match |
| Total before tax | 140.40 | ||||||
The pan figure is 6 × 22.00 × 0.95 = 125.40. The knife figure is 20 × 0.75 = 15.00. With 15% tax on both, the overcharge is 140.40 + 21.06 = 161.46. Without the match, that money goes out with the next payment run.
Decision rules for mismatches
- Billed more than received: do not pay the extra. Ask for a credit note or a corrected invoice, or pay only for what arrived if your terms allow it.
- Received less than ordered: decide whether the rest is still coming. If it is not, close the line short in writing so nobody expects it later.
- Price differs from the PO: the PO price stands unless you agreed a change. Small differences may come from rounding. Set a tolerance in advance, for example a few units of currency per bill, so staff are not chasing pennies.
- Received more than ordered: accept it only if someone with buying authority agrees. Otherwise return the extra or hold it unpaid.
Tip from practice: count the delivery before you open the invoice. If the person receiving goods sees the billed quantities first, they tend to confirm them instead of counting. A blind count, against the PO and not the bill, catches far more short deliveries.
Common purchase order mistakes in Excel
- Typing over a formula. Someone types a “corrected” total into column K, and the sheet stops adding up. Lock formula cells with sheet protection once the layout is final.
- SUM ranges that miss new rows. Add line 7 below a SUM that stops at row 7 and it is silently left out. Insert new rows inside the range, or widen the ranges on the totals sheet.
- One tax rate for the whole order. This breaks as soon as one item or the freight is taxed differently.
- Editing a sent PO without a revision number. The supplier ships version 1 and bills version 2, and nobody can prove which was agreed.
- Ambiguous units. “10 knives” when the supplier sells in boxes of 6 leads to 60 knives and a bill to match.
- Paying from the invoice alone. The PO and the delivery count are the evidence. The invoice is only the claim.
- Rounding drift. Rounding tax on each line, then rounding the total differently, leaves a few cents’ difference against the supplier’s bill. Follow your tax authority’s rules on invoice rounding. In the UK, for example, HMRC’s guidance on VAT record keeping covers what invoices and records must show.
Edge cases the basic template does not cover
Prices that include tax
Some suppliers quote tax-inclusive prices. Convert them before entering: net unit price = inclusive price ÷ (1 + tax rate). Otherwise you will tax the tax.
An order-level discount
If the supplier gives 3% off the whole order rather than off lines, apply it before tax. Spread it across lines in proportion to their net value, so each item’s cost and tax stay right. Putting it as one negative line at the bottom understates your stock cost per item.
Imports, duty and freight from a different company
For imports, duty and import tax are usually paid to customs or through a broker, and freight is often billed by a forwarder. These belong in the true cost of the goods but not on the supplier’s PO. Track them as separate cost lines and allocate them to items, typically by value or weight.
Foreign currency
Write the PO in the supplier’s currency and note the rate you used for budgeting. The bill will be booked at the rate on its date, so expect a difference.
Partial deliveries and substitutions
A second delivery against the same PO needs its own receipt line. The match sheet then compares the cumulative received quantity with the ordered quantity. A substituted item, such as a 26 cm pan instead of 28 cm, is a new line at an agreed price, not a silent swap.
Blanket orders and price breaks
If you agree annual quantities at fixed prices and call off deliveries, each call-off is its own PO. Track what is left on the agreement in a separate sheet, or the spreadsheet will let you order past it.
How to do this in Cash Flow Inventory
A spreadsheet works for a handful of orders a month. Once deliveries arrive in parts, bills cover several receipts and more than one person approves spending, a system that links order, receipt and bill avoids the gaps. The full procedure is in the purchase orders guide. In short:
- In Settings › Purchase, set Purchase process to Order → receive → bill → payment. Choose a Purchase order approval rule: approve each order by hand, approve automatically, or approve automatically up to an amount. Under What purchase documents carry, switch on Cost lines (freight, duty and other charges), Landed cost and Advance payments if you use them.
- Open Purchasing › Purchase orders, choose New order, and pick the supplier and the location the goods go to.
- Add items and quantities. If you keep supplier prices in Purchasing › Agreements & prices, lines are priced for you, using the highest quantity break at or under the line’s quantity.
- Add cost lines such as freight or duty, and an advance if you pay part before the bill. Save.
- The order waits for approval unless your rule approves it. Approvers can act from the approvals centre.
- Use Send to supplier… in the row menu to e-mail it, or print or download the PDF.
- When goods arrive, choose Receive goods in the row menu and enter what actually came on each line. Leave a line at zero if nothing came. See receiving goods for lots, expiry dates and serials.
- Saving the receipt adds the stock and makes the supplier’s bill as a draft for what was received, plus a draft cost bill for each other cost on the order. Each line comes into stock at its landed unit cost, which takes in its share of the order discount and of the order’s other costs.
- Open Purchasing › Bills and check the draft against the supplier’s invoice before approving. If the supplier’s price is wrong, use Hold payment… while you resolve it. If your recorded price needs fixing, use Correct prices… on the bill. The bills and accounts payable guide covers both.
- If the rest of a short delivery is not coming, use Close order and choose the reason, such as Supplier delivered short.
In the kitchenware example, receiving 30 pans would make a draft bill for 30. The supplier’s invoice for 36 shows up as a difference before anything is owed. That is the receive-and-bill match done by the system rather than by a formula.
Free download: Purchase order template with automatic totals
An Excel file (.xlsx, 13 KB) with the formulas already in place and the example from this guide filled in. It opens in Excel, Google Sheets (File › Import), Numbers and LibreOffice. No sign-up needed.
Frequently asked questions
How do I make a purchase order in Excel that calculates automatically?
Give each line a net amount formula, =ROUND(Qty*Price*(1-Discount),2), then a tax formula and a line total. Sum the lines on a totals section and add freight with its own tax. The downloadable file above already has these formulas, ready to copy down.
What should a purchase order include?
At minimum: a unique PO number and date, both parties’ legal names and tax numbers, the delivery address, and each item’s code, unit, quantity, price, discount and tax rate. Add the currency, the delivery date, the delivery and payment terms, and an instruction that invoices quote the PO number. The checklist above is ready to copy.
What is the difference between a purchase order and an invoice?
A purchase order is what you send to a supplier to commit to buying. An invoice, or bill, is what the supplier sends you to ask for payment after delivering. You check the invoice against the PO and the goods received before paying.
What is three-way matching in purchasing?
It means comparing three documents before paying: the purchase order (what you agreed), the goods receipt (what arrived) and the supplier’s bill (what you are charged). Quantities and prices should agree across all three, within any tolerance you set. Differences are resolved with the supplier before payment.
Does this template work in Google Sheets?
Yes. Upload the file to Google Drive and open it with Google Sheets. The functions used (SUM, SUMPRODUCT, ROUND, IF, AND) work the same way there.
Should freight go on the purchase order?
Yes, if the supplier is charging it, because it is part of what you agreed to pay and part of the goods’ true cost. Give it its own line and tax rate. If a separate carrier bills you, note the expected amount on the PO but match it against the carrier’s own invoice.
The template will carry you a long way if you keep the formulas intact and match every bill before paying it. When you want orders, receipts and bills linked so they cannot drift apart, see how Cash Flow Inventory’s purchase order software handles approvals, partial deliveries and bills, or start a free trial and enter your next order there.



