Materials ordering in Google Sheets: Bill of Materials, reorder points and auto‑emailing suppliers

Category: Business & Operations • Niche: procurement, Google Sheets, inventory, backorders

Contents

• Quick answer
• Who this is for
• What you’ll build
• Step 1 — Set up a Bill of Materials in Google Sheets
• Step 2 — Calculate reorder points (with UK‑friendly formulas)
• Step 3 — Auto‑email suppliers when stock dips below ROP
• Optional — Periodic review ordering (weekly cycle)
• Flow diagram
• Tips, pitfalls and UK supplier reality
• FAQs

Quick answer

• A simple, robust reorder point (ROP) you can trust: ROP = average daily usage × lead time (days) + safety stock. See variants below.
• Build one Sheet with columns: SKU, Description, On Hand, On Order, Lead Time (days), Avg Daily Usage, Safety Stock, Reorder Point, Supplier, Email, Case Size.
• Add a formula to set Reorder Point, flag lines that hit it, and calculate a sensible Reorder Qty.
• Use Apps Script or Make/Zapier to send a tidy purchase email to your supplier when an item hits ROP.

Who this is for

Micro to mid‑size UK trades teams that:

• Lose time ringing around merchants for routine items
• Get caught out by backorders on PIR, timber, fixings and consumables
• Want a light‑touch system your office or foreman can keep current without new software

What you’ll build

• A single Google Sheet that holds your Bill of Materials (BOM) and live stock levels
• Reorder point and reorder quantity formulas that highlight what to buy, when
• An automation that turns the highlighted rows into a pre‑formatted supplier email

Step 1 — Set up a Bill of Materials in Google Sheets

Suggested columns:

• SKU
• Description
• On Hand
• On Order
• Lead Time (days)
• Avg Daily Usage
• Safety Stock
• Reorder Point
• Reorder Flag
• Reorder Qty
• Supplier
• Supplier Email
• Case Size

Example formulas (paste into row 2 and copy down):

• Reorder Point (column H):

=ROUNDUP(F2*E2 + G2, 0)

• Flag to reorder (column I):

=IF(C2 + D2 <= H2, "Reorder", "OK")

• Reorder Qty to reach target stock for review period + lead time (store your review period days in Settings!B2):

=MAX(0, ROUNDUP((E2 + Settings!$B$2)*F2 + G2 - (C2 + D2), 0))

• Avg Daily Usage (column F) from 30 days of history in a Sales sheet (optional):

=LET(rng, FILTER(Sales!C:C, Sales!B:B=A2, Sales!A:A>TODAY()-30), IFERROR(AVERAGE(rng), 0))

Step 2 — Calculate reorder points (with UK‑friendly formulas)

Two practical ways, both recognised in UK inventory teaching and trade ops:

• Service level method (demand varies; pick a service level in Settings!B3, e.g., 0.95):

=ROUNDUP(NORM.S.INV(Settings!$B$3) * STDEV.S(Demands!C2:C31) * SQRT(E2), 0)

Use this for Safety Stock (column G). Then ROP is Avg Daily Usage × Lead Time + Safety Stock.

• Simple max–avg approximation if you don’t have stdevs:

=ROUNDUP((MAX(Demands!C2:C31) - AVERAGE(Demands!C2:C31)) * E2, 0)

Further reading: practical treatments of reorder point and safety stock in UK supply‑chain blogs and guides such as Wikipedia — Reorder point and ShipBob UK guide.

Step 3 — Auto‑email suppliers when stock dips below ROP

Option A — Google Apps Script (keeps it all in Sheets)

• Extensions → Apps Script → paste the function below, update the sheet name and column letters to match yours. Set a time‑driven trigger (every hour or daily at 7:00) to run it.

function emailSupplierOrders() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('BOM');
  const data = sh.getDataRange().getValues();
  const headers = data.shift();
  const idx = Object.fromEntries(headers.map((h,i)=>[h,i]));

  const rowsToOrder = data.filter(r => r[idx['Reorder Flag']] === 'Reorder' && r[idx['Supplier Email']]);
  const grouped = {};
  rowsToOrder.forEach(r => {
    const supplier = r[idx['Supplier Email']];
    if (!grouped[supplier]) grouped[supplier] = [];
    grouped[supplier].push({
      sku: r[idx['SKU']],
      desc: r[idx['Description']],
      qty: r[idx['Reorder Qty']],
      caseSize: r[idx['Case Size']],
    });
  });

  Object.entries(grouped).forEach(([email, lines]) => {
    const html = HtmlService.createHtmlOutputFromFile('po_template').getContent()
      .replace('{{LINES}}', lines.map(l => `<tr><td>${l.sku}</td><td>${l.desc}</td><td>${l.qty}</td><td>${l.caseSize||''}</td></tr>`).join(''));
    GmailApp.sendEmail(email, 'Purchase request from {{YOUR COMPANY}}', 'HTML only', {htmlBody: html, name: '{{YOUR COMPANY}} Procurement'});
  });
}

• Create an HTML file in Apps Script called po_template with a simple table and your footer terms.

Option B — Make or Zapier (easier to maintain, good for approvals)

• Trigger when a row changes to Reorder
• Filter to only send if Reorder Qty > 0
• Build the email body with the line items and send to Supplier Email
• Optional: route via an approval step in Slack/Email before sending

Optional — Periodic review ordering (weekly cycle)

If you order on a fixed day each week, adjust Reorder Qty to cover Lead Time + Review Period instead of just Lead Time. The formula in Step 1 already does this when you populate Settings!B2 with your review period days.

Flow diagram

Tips, pitfalls and UK supplier reality

• Lead times move. Keep your Lead Time (days) column realistic with what you see in your area. CLC updates are handy for context: Material Supply Chain Group statements.
• Backorder hotspots in 2025: PIR insulation, aircrete/insulation blocks, CLS/carcassing timber. Build 1–2 weeks of buffer for these where they are critical path.
• Use case sizes. Round your Reorder Qty to pack sizes to avoid odd leftover stock: =MROUND(<qty>, CaseSize).
• Track substitutions. Add an Alternate column for acceptable swaps (e.g., mineral wool vs PIR, equivalent fixings). Note in the email so merchants can offer what’s available.
• Name one owner. Make someone responsible for keeping On Hand and On Order up to date. 10 minutes daily beats a crisis on Friday.
• Evidence for clients. When you generate purchase emails, CC your jobs inbox so you can prove delays are supplier side if dates slip.

FAQs

• What’s the best reorder point formula for trades? Use ROP = Avg Daily Usage × Lead Time + Safety Stock. If you’re learning toward AAT-style exam questions, a common textbook variant is Max daily usage × Max lead time, which bakes in a buffer. Both are acceptable with clear definitions.

• How do I estimate Safety Stock fast if I don’t have months of data? Use the simple max–avg method shown above. Tighten later once you’ve got 30–60 days of daily usage.

• Can I attach a proper PO instead of an email? Yes. Generate a PDF from a Google Docs template with Apps Script and attach it to the email. Or move to Xero/QuickBooks + ApprovalMax for multi‑step approvals.

• Will this work if I’m often offline on site? Yes. Capture counts in the Google Sheets mobile app or a quick Google Form that writes into the Sheet, then sync when you’re back on signal.

• Any good videos to learn basics? This short tutorial aligns well with what we’re doing: Create a bill of materials in Google Sheets by Pizzey Technology.

Want to slash training times and increase revenue per Engineer? Join our Waitlist: https://trainar.ai/waitlist