Purchase Order Log

About this template

From order to receipt on one row

Three sheets: ‘Dashboard’ · ‘Input data’ · ‘Usage guide’. ‘Input data’ is the log, one row per order. Sixteen columns run No. · PO No. · PO date · Client · Item · Spec · Qty · Unit · Unit price · Supply value · VAT · Order amount · Due date · Received on · Qty received · Status, with nine sample orders in rows 2-10.

Filling order

1. The moment the order goes out, record PO No. (column B) and PO date (column C) — the sample uses PO-2026-0118, prefix, year, serial.
2. Enter Client (column D) — what Summary by client groups on, so spell it identically every time, down to ‘Co., Ltd.’ and the spacing.
3. Enter Item, Spec, Qty (column G), Unit and Unit price (column I); the three money columns fill themselves.
4. Note the Due date (column M) and, when the goods arrive, fill in Received on (column N) and Qty received (column O) only. Leave Status alone.

How the formulas run

• Supply value =G2*I2 → VAT =ROUND(J2*0.1,0) → Order amount =J2+K2. Row 2 is 120 × 86,000 = 10,320,000, plus 1,032,000 VAT, giving 11,352,000.
• Status =IF(N2=””,”Order placed”,IF(O2>=G2,”Received”,”Partial”)) — an empty receipt date means Order placed, a quantity at or above the order means Received, less than that means Partial. The sample shows four Received, two Partial, three Order placed.
• The metric cards read column L only: 9 rows · total 76,236,600 · average 8,470,733 · max 22,440,000.
• Summary by client separates six suppliers — Seongjin Trading Co., Ltd. 2 orders, 17,556,000; Daesung Information Co., Ltd. one order at 22,440,000.

Partial receipts and tax-exempt items

Sample row 5, the aluminum profile, was ordered at 240 bars and 180 arrived, so it reads Partial. Do not split the shortfall onto a new row. Two rows under one PO number break the client totals and the reconciliation against payment alike. Update Qty received and the row flips to Received on the day the balance lands. For tax-exempt items such as books or farm produce, delete the formula in column K and enter 0.

Carrying through to payment

Only orders that have passed inspection go forward for payment. When the tax invoice arrives, check that it agrees with Order amount (column L). On a partial receipt, pay for what arrived and settle the rest on delivery. Under Korean subcontracting rules payment falls due within 60 days of receiving the goods, which makes Received on (column N) the date the clock starts from — leave it blank and the deadline cannot be worked out.

The trend table is updated by hand

‘Monthly order amount (KRW m)’ holds Dec 22 · Jan 15 · Feb 43 · Mar 19. The four value rows are typed in; only Total 99 and Average 24.75 are formulas. Carry each month’s order total across in millions of won. Orders bunched into one month press on cash planning and warehouse space alike, so a spike like February is the prompt to spread the next quarter. If Summary by client shows one supplier carrying too much, line up an alternative source.

What goes wrong

• Typing ‘done’ into Status — the formula is gone, and later corrections to Qty received change nothing.
• Writing a supplier both with and without ‘Co., Ltd.’. The summary splits into two lines.
• Deleting a canceled order. With the PO number gone the history cannot be retraced, so leave the row and set the quantity to 0. Rows where the due date has passed and Received on is still empty are the ones to chase.
• Filling in Qty received before inspection. The row flips to Received and defects go through to payment. Enter it after inspection, deduct returns, and note the reason.