Product Details

Inventory
Supply Chain
Operations
Click a product to see its stock, demand, open purchase orders and a suggested reorder quantity in Python for Excel.

Decide whether to reorder a product without pulling its numbers together by hand. Select a row in the Products table and the task pane shows that product’s stock by warehouse, open purchase orders (with late ones flagged), weekly sales for the last 12 months, weeks of cover and a suggested order quantity with the reasoning in one line.

Result preview

When the workbook opens, the Products table is on the first sheet with a Below reorder point list beside it: nine of the 30 products are under their reorder point, such as Resistance Band Set (out of stock, 120 units on order) and Stand Mixer (7 on hand against a reorder point of 22). The task pane shows the first product, labelled “Showing Garden Hose 25 ft — select a product row to see another”. Select Potting Soil and the pane shows 110 on hand, 180 on order with one purchase order past its expected date, and 1.9 weeks of cover.

What this template does

  • Reads five Excel tables: Products, Stock (three warehouses), Sales (52 weeks per product), Suppliers and Purchase Orders.
  • Shows the selected product’s supplier facts, stock by warehouse, open orders with expected dates, a weekly sales chart, average weekly demand, weeks of cover and a suggested order quantity.
  • Says so plainly when a product has no sales history, instead of failing.
  • Publishes the products below their reorder point, with suggested quantities, back to the sheet. That list does not change with the selection.

Why Python

Joining five tables for one product, averaging a sales window and rounding to an order multiple takes lookups, helper columns and manual updates in formulas, and the result still cannot follow a clicked row. Python in Excel joins the tables by value, follows the selection, and updates when stock, sales or orders change.

Click a product row to see its details. If you select several rows, the pane follows the row the cursor is on. Lower a product’s On Hand on the Stock Levels sheet and it joins the reorder list.

Operating workflow

Author: A technical maintainer owns workbook.py, notebook.py, deterministic scenarios and the generated package, and edits the # --- Edit these rules --- section to change the demand window, safety weeks and rounding.

Workbook user: A purchasing or inventory planner selects products, edits stock, reorder points and purchase orders, and reads the pane and the reorder list without maintaining the Python source.

The template can also open in App mode, which changes presentation rather than permissions, source access or workbook trust.

Download the Excel template

Inputs and assumptions

Input Location Default Description
Products Product List!A4:G34 (table Products) 30 products SKU, Name, Category, Supplier ID, Unit Cost, Reorder Point, Order Multiple
Stock Stock Levels (table Stock) 90 rows On Hand by SKU and warehouse (Main DC, East Hub, West Hub)
Sales Weekly Sales (table Sales) 1,508 rows Units per SKU for 52 weeks (29 products; the new product has none)
Suppliers Suppliers (table Suppliers) 6 suppliers Lead time in weeks and contact
Purchase Orders Purchase Orders (table PurchaseOrders) 20 open orders Quantity and Expected Date per SKU
As-of date notebook.py rules (AS_OF) 2026-10-05 Orders expected before this date are flagged late
Demand window notebook.py rules 12 weeks Weeks of recent sales averaged into weekly demand
Safety stock notebook.py rules 2 weeks Weeks of demand held on top of the supplier lead time
Rounding notebook.py rules up Suggested quantity rounded up to the order multiple (or to the nearest multiple, or not at all)

The sample includes a product with no stock (Resistance Band Set), a new product with no sales history (Solar Garden Spotlight), a slow mover with 60 weeks of cover (Fountain Pen Gift Set) and two purchase orders past their expected dates (PO-4402 and PO-4414).

Notebook implementation

The notebook reads the five tables and the selected Products rows in one bf.inputs(...) call, then builds one table of measures per product:

inputs = bf.inputs(
    products=bf.ref("Products", headers=True),
    stock=bf.ref("Stock", headers=True),
    sales=bf.ref("Sales", headers=True),
    suppliers=bf.ref("Suppliers", headers=True),
    orders=bf.ref("PurchaseOrders", headers=True),
    picked=bf.selection("Products"),
)

The as-of date is AS_OF in the notebook’s rules, not a cell.

The pane shows the product on the active cell (the first product when nothing is selected). The list of products below their reorder point is published with bf.publish(outputs={"reorder": reorder}), so the sheet never depends on the selection.

How the calculation/model works

  1. Average weekly demand: the mean of the last 12 weeks of sales for the product.
  2. Weeks of cover: units on hand divided by average weekly demand.
  3. Target stock: the higher of the reorder point and average weekly demand times (supplier lead time plus safety weeks).
  4. Suggested order: target stock less units on hand and on order, never below zero, rounded to the order multiple.
  5. Below reorder point: units on hand across warehouses are lower than the reorder point. Open orders reduce the suggested quantity, so a product already covered by incoming orders can appear in the list with a suggestion of 0.

Architecture

  • Selection decoupling: Product row selection drives the task pane display only. The published reorder spill (Product List!I4:K34) reflects catalog-wide inventory thresholds and never writes selection-dependent state to worksheet cells.
  • Fixed spill and row limits: The reorder output range accommodates up to 30 products; expanding catalog capacity requires synchronized updates to output_block and outputAnchors in workbook.py and the padding logic in notebook.py. The Weekly Sales sheet explicitly sets rowCount: 2000 to accommodate 52 weekly rows per product.
  • Cross-source date coupling: workbook.py regex-parses AS_OF directly from notebook.py source text to align generated seasonal sales history and open purchase order delivery dates with the notebook’s evaluation date.

Validation / expected results

Check Expected outcome
Products below their reorder point 9
First product in the list SKU-1001 Garden Hose 25 ft
Resistance Band Set suggested order 80
Pane with nothing selected Garden Hose 25 ft, labelled as the default
Chef Knife 8 in weeks of cover 6.4
Potting Soil weeks of cover 1.9, with PO-4402 flagged late
Fountain Pen Gift Set weeks of cover 60.0
Solar Garden Spotlight (no sales) A message instead of cover and demand
Several rows selected, active cell on Stand Mixer The pane shows Stand Mixer, with 10 units suggested

Lowering Patio Umbrella’s Main DC On Hand from 45 to 20 takes the product to 60 on hand against a reorder point of 75, so it joins the list (10 products) with a suggested order of 5. Moving the as-of date in the notebook rules back to 2026-09-01 clears the late flag on PO-4402.

Limitations

  • Demand is a simple average of recent weeks; there is no forecast model, trend or seasonality adjustment.
  • Weeks of cover count units on hand only, not incoming orders.
  • The template suggests quantities but does not create purchase orders.
  • Costs are shown in one currency.

When to use this approach

Use this approach when a planner needs a product’s full position on one screen to decide on a reorder, and the supporting data lives in separate tables. For demand with long gaps between sales, see the intermittent demand template.