Cash Flow Inventory
Menu
Blog

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.

Excel purchase order with line items, discounts, tax and freight totals next to a delivery and bill matching sheet
On this page
  1. Key takeaways
  2. What a purchase order template in Excel should calculate
  3. Worked example: a purchase order with discounts, mixed tax and freight
  4. What every purchase order must say: a copy-ready checklist
  5. How to fill in and send the purchase order template, step by step
  6. The receive-and-bill match: checking delivery and invoice against the PO
  7. Decision rules for mismatches
  8. Common purchase order mistakes in Excel
  9. Edge cases the basic template does not cover
  10. Prices that include tax
  11. An order-level discount
  12. Imports, duty and freight from a different company
  13. Foreign currency
  14. Partial deliveries and substitutions
  15. Blanket orders and price breaks
  16. How to do this in Cash Flow Inventory
  17. Frequently asked questions
  18. How do I make a purchase order in Excel that calculates automatically?
  19. What should a purchase order include?
  20. What is the difference between a purchase order and an invoice?
  21. What is three-way matching in purchasing?
  22. Does this template work in Google Sheets?
  23. 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.

Download the spreadsheet

Email me this template + the next 4 guides on ordering and purchasing

Optional: the download above is free and needs no e-mail.

Optional. Before any news, we e-mail you once to confirm.

We use your e-mail only for what you ask here. Privacy notice

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.

Columns of the PO lines sheet: header, what it holds, the example in row 2 and the formula
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.

Download the spreadsheet.xlsx, 13 KB · formulas and the worked example filled in · Excel, Google Sheets, Numbers, LibreOffice

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

  1. 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.
  2. 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.
  3. 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.
  4. Enter each line’s tax rate as a decimal formatted as a percentage (0.15 shows as 15%).
  5. 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.
  6. Check that the lines total plus freight and freight tax equals the PO total.
  7. 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.
  8. 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.

Columns of the Receive and bill match sheet: header, what it holds, the example in row 2 and the formula
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.

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:

  1. 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.
  2. Open Purchasing › Purchase orders, choose New order, and pick the supplier and the location the goods go to.
  3. 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.
  4. Add cost lines such as freight or duty, and an advance if you pay part before the bill. Save.
  5. The order waits for approval unless your rule approves it. Approvers can act from the approvals centre.
  6. Use Send to supplier… in the row menu to e-mail it, or print or download the PDF.
  7. 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.
  8. 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.
  9. 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.
  10. 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.

Download the spreadsheet

Email me this template + the next 4 guides on ordering and purchasing

Optional: the download above is free and needs no e-mail.

Optional. Before any news, we e-mail you once to confirm.

We use your e-mail only for what you ask here. Privacy notice

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.

Mohammad Ali

Led by Mohammad Ali (15+ years in inventory management software), the Cash Flow Inventory Content Team empowers SMBs with clear financial strategies. We translate complex financial concepts into clear, actionable strategies through a rigorous editorial process. Our goal is to be your trusted resource for navigating SMB finance.

About the team