Demand & Inventory Planner

Combine operational spreadsheet inputs with a reactive Python planning model. SKU assumptions feed a service-level safety stock calculation, purchase recommendations are constrained to a cash budget, and custom Python formulas are callable directly from Excel cells.

How to interact with this live demo

1. Switch sheets: In the spreadsheet bottom bar, toggle between Dashboard (results) and SKU Inputs (raw operational data).
2. Adjust planning controls: In the Python notebook, change the Service level dropdown (e.g. 98%) or adjust the Planning horizon slider.
3. Inspect custom Excel functions: Click cell F6 on the Dashboard sheet to see =BF.FUNCTION("safety_stock", D6, E6) recalculate live with Python.

Need more space? Click Open in separate tab on the workbook header to test in a dedicated browser window.

Live interactive workbook

Demand & Inventory Planner interactive Python workbook

Loading Python workbook…

How the planning architecture works

This operational workflow demonstrates how to handle multi-sheet inputs, constrained decision algorithms, and reusable custom functions:

1. Multi-sheet input binding

Operational planning models frequently separate raw inputs from report presentation. Boardflare handles cross-sheet cell references cleanly:

inputs = bf.inputs(
    skus=bf.ref("SKU Inputs!A4:H14", headers=True),
    controls=bf.ref("SKU Inputs!J4:K6", headers=True),
)

Planners can update demand variability or open purchase orders in the SKU Inputs sheet without modifying any notebook code.

2. Probabilistic modeling with NumPy and SciPy

The notebook calculates safety stock and dynamic reorder points based on target service z-scores:

z_value = float(service_level.value)
frame["Safety stock"] = z_value * frame["Demand std"] * np.sqrt(frame["Lead time (weeks)"])
frame["Reorder point"] = (
    frame["Weekly demand"] * frame["Lead time (weeks)"] + frame["Safety stock"]
)

3. Constrained optimization & budget ranking

Instead of unconstrained ordering, Python ranks SKU priority and allocates purchase quantities subject to the workbook's cash budget:

ranked = frame.sort_values("Priority", ascending=False).copy()
remaining_budget = cash_budget
for _, row in ranked.iterrows():
    affordable_units = np.floor(remaining_budget / row["Unit cost ($)"] / 10) * 10
    funded_units = max(0.0, min(row["Order quantity"], affordable_units))
    decisions[str(row["SKU"])] = funded_units
    remaining_budget -= funded_units * row["Unit cost ($)"]

4. Publishing both data tables and callable functions

The notebook publishes structured summary/detail tables, plus a lightweight custom calculation function:

def safety_stock(demand_std, lead_time, service_z=1.65):
    return float(service_z) * float(demand_std) * np.sqrt(float(lead_time))

bf.publish(
    outputs={"summary": summary, "detail": detail},
    functions={"safety_stock": safety_stock},
)

In Excel, the Dashboard sheet displays:

• =BF.OUTPUT("summary") for aggregate KPIs (budget utilization, stockout risks).
• =BF.OUTPUT("detail") for the prioritized SKU allocation table.
• =BF.FUNCTION("safety_stock", D6, E6) for ad-hoc formula evaluations in any cell.

---

What to try next

• Explore Sales Scenario Analysis for a beginner starter tutorial.
• Explore Curve Fitting for SciPy nonlinear least squares and confidence bands.
• Learn more about custom Python formulas in Working with Excel.