If you’ve ever spent a Friday afternoon updating reorder points in a spreadsheet—only to find out Monday morning that a “safe” item is now backordered and production is stalled—you already know the punchline: **spreadsheet reorder points** don’t understand **vendor lead time**, and they definitely don’t understand lead-time *variability*. The result is predictable: stockouts, late orders, and “just this once” rush shipments that somehow happen every week.
This post shows how to build a **vendor lead-time + reorder alert app in Power Platform** that gives SMB manufacturers and distributors “good enough MRP” control **without an ERP migration**. We’ll cover the real root causes, a practical Dataverse data model (items, suppliers, policies, consumption), and Power Automate workflows for risk-based alerts and approvals in Teams/email—so buyers can act earlier, with better context, and fewer expedites.
—
## Chapter 1: The Real Problem—Spreadsheet Reorder Points vs. Actual Lead Times (and Why Expedite Costs Explode)
The key insight: **reorder points fail when they’re static**, but supply conditions aren’t. Spreadsheets typically store a min/max or “reorder at” number that was set once (maybe during a calmer year), then kept alive through habit. Meanwhile, supplier lead times stretch, MOQs change, partials ship, and one late container can eat your safety stock in a single cycle.
This is where it gets interesting: even if your spreadsheet math is “correct,” it’s often correct for the wrong assumptions. The real question isn’t “What’s the reorder point?” it’s **“Are we at risk of stocking out before the next likely receipt date?”** That requires lead time *per supplier*, plus some way to reflect uncertainty.
Operationally, this mismatch shows up as what many teams experience as chaos, but is really a known planning problem. McKinsey describes “inventory distortion” as a persistent issue where inventory levels and placement don’t match real demand and supply conditions—driving both stockouts and excess inventory—and points to visibility and planning as core levers to reduce it (according to McKinsey’s analysis of inventory distortion).
### Here’s what that looks like in practice…
– Item A has a “reorder at 200” value based on a 2-week lead time.
– Supplier lead time is now 4–6 weeks (and sometimes 8).
– You reorder at 200, but consumption drains to 0 by week 4.
– To keep production moving, you pay expedite fees (and likely overtime + rescheduling costs you don’t neatly tag as “expedite”).
**Practical takeaway:** Stop treating reorder point as a single number. Treat it as a *decision* based on projected depletion date vs. probable receipt date—then automate the decision support so it happens consistently.
—
## Chapter 2: Root Causes—Lead-Time Variability, Supplier Constraints, Manual Reviews, and Missing Risk Signals
Most businesses get this wrong by assuming the biggest issue is “people not checking the spreadsheet often enough.” Frequency helps—but it doesn’t solve the structural gaps. The failures usually come from four root causes.
### Lead-time variability (not just lead time)
Average lead time is a trap. A supplier with a “30-day lead time” might actually deliver in 21 days half the time and 55 days a quarter of the time. Planning to the average guarantees periodic emergencies.
This isn’t theoretical. Ongoing supply volatility—and especially longer and less predictable lead times—continues to disrupt manufacturing planning and service levels (as noted in Deloitte’s 2024 manufacturing industry outlook). If volatility is the operating reality, your reorder logic needs a volatility input.
### Supplier constraints don’t fit in a cell
Spreadsheets struggle to model the stuff buyers actually deal with:
– Approved suppliers per item (and alternates)
– MOQs and order multiples
– Capacity constraints or allocation
– Holidays/shutdowns by vendor region
– “We can ship partials” vs. “all-or-nothing”
You can add columns—but you can’t make the spreadsheet behave like a governed system of record.
### Manual reviews don’t scale
When the process relies on one planner’s experience (“I just know which items are risky”), you don’t have a process—you have tribal knowledge. It works until someone is out, growth accelerates, or vendor performance changes.
### Missing risk signals = late action
In a spreadsheet process, alerts are usually time-based (“review every Tuesday”) instead of risk-based (“this item will stock out before the next receipt if lead time slips”). Gartner notes that volatility is pushing leaders to improve planning speed and decision-making (according to Gartner’s 2024 supply chain priorities summary). SMBs feel that same pressure—just with smaller teams and less room for heroics.
**Practical takeaway:** You don’t need a full ERP to add risk signals. You need (1) structured data for lead time and policy and (2) event-driven automation that nags you only when it should.
—
## Chapter 3: Solution Blueprint—A “Good Enough MRP” Pattern in Power Platform (Dataverse + Power Apps + Power Automate)
The key insight: “good enough MRP” is less about perfect forecasting and more about **consistent, explainable replenishment decisions** with **fast exceptions handling**.
Power Platform fits this pattern well because it combines:
– **Dataverse** as the relational system of record for items, vendors, and policies
– **Power Apps** as the buyer/planner interface (review, override, approve)
– **Power Automate** for event-driven triggers, approvals, and notifications
Microsoft positions Dataverse as a managed data platform for securely storing and modeling business data with relationships, auditing, and role-based security (according to Microsoft’s Dataverse overview). That matters because “inventory planning in Excel” fails partly due to governance and auditability issues—version control, hidden errors, and weak controls (as discussed in Harvard Business Review’s guidance on spreadsheet errors).
### The pattern (simple, but powerful)
1. **Capture policy and supply reality**: min/max, safety stock intent, supplier lead times (and variability).
2. **Capture consumption signals**: usage rate, sales orders, production pulls—or even a simple “daily average usage” if that’s all you have.
3. **Calculate risk**: “days of cover” vs. “days to replenish.”
4. **Trigger actions**:
– Low risk: create a suggested reorder
– Medium risk: notify buyer
– High risk: route for approval + escalate in Teams/email
Here’s what that looks like in practice: instead of buyers scanning 400 SKUs for problems, they get **10 alerts that actually matter**, each with the “why” and the recommended action.
**Practical takeaway:** Design the system to be *boring* on good days and *loud* on risky days. That’s how you stop rush shipping from becoming routine.
—
## Chapter 4: Dataverse Data Model—Items, Vendors, Approved Suppliers, Lead Times, Min/Max Policies, and Consumption Signals
The key insight: Your automation is only as good as your data model. If you model lead time as a single field on the Item, you’ll recreate the spreadsheet problem—just with nicer buttons.
Below is a pragmatic Dataverse model that supports multi-supplier items, policy-based replenishment, and risk scoring.
### Core tables (minimum viable model)
**Item**
– Item number, description, UOM
– Item status (active/obsolete)
– Planner/buyer owner
– Default site/warehouse (if applicable)
**Vendor**
– Vendor name, terms, preferred contact
– Region/time zone (optional but useful for expectations)
**Approved Supplier (Item-Vendor bridge)**
– Item (lookup)
– Vendor (lookup)
– Preference rank (1,2,3…)
– MOQ, order multiple, standard pack
– Is preferred (yes/no)
– Last price (optional)
**Lead Time History (or Lead Time Profile)**
– Approved Supplier (lookup)
– Lead time days (actual)
– Date received or PO close date
– Notes (late reason codes if you have them)
**Lead Time Policy (derived or maintained)**
– Approved Supplier (lookup)
– Lead time typical (days)
– Lead time pessimistic (days) or “buffer days”
– Confidence score (optional)
You can calculate “typical/pessimistic” from history (simple percentile approach) or store a maintained value and adjust it as performance changes.
**Reorder Policy**
– Item (lookup) and optionally warehouse
– Policy type: Min/Max, Reorder Point, Days-of-supply
– Min qty, max qty, safety stock intent
– Review cadence (daily/weekly) if you want scheduled checks
**Inventory Position (lightweight)**
– Item + location
– On-hand
– On-order
– Allocated/backordered (if you can capture it)
– Last updated timestamp
**Consumption Signal**
– Item + location
– Daily average usage (DAU) or weekly usage
– Method (calculated/imported/manual)
– Last updated timestamp
### Simple framework: the “Cover vs. Replenish” model
You don’t need perfect demand planning to do this well. You need two numbers:
– **Days of Cover** = (On-hand + On-order – Allocated) ÷ Daily usage
– **Days to Replenish** = Supplier lead time (typical or pessimistic)
Then:
– If Cover < Replenish (typical): **Risk = Medium**
- If Cover < Replenish (pessimistic): **Risk = High**
- If Cover is comfortably above: **Risk = Low**
**Practical takeaway:** Store lead time *per approved supplier*, and store at least one “pessimistic” value (or buffer). That single decision is what makes alerts meaningful instead of noisy.
---
## Chapter 5: Automation Design—Reorder Threshold Logic, Lead-Time Risk Conditions, Approval Routing, and Teams/Email Notifications
The key insight: Automation shouldn’t spam people with “FYI” messages. It should **create a short list of decisions**, attach evidence, and route approvals only when the risk is real.
Power Automate can create and track approvals and integrate with Teams/email connectors (according to Microsoft’s Power Automate approvals documentation). That’s the backbone for turning reorder signals into governed action.
### Trigger options (choose one to start)
1. **Scheduled (daily morning run):** Evaluate all active items with policies.
2. **Event-driven:** Run when On-hand, On-order, or usage rate changes.
3. **Hybrid:** Scheduled for baseline + event-driven for critical items.
Most SMBs start scheduled and move to hybrid once data feeds mature.
### Reorder suggestion logic (practical, not academic)
A workable starting point for Min/Max:
– If (Inventory Position) < Min → Suggest reorder qty = Max – Inventory Position
Then apply constraints:
- Round up to order multiple
- Enforce MOQ
- Choose supplier based on preference + lead-time confidence
For days-of-supply:
- Target days = e.g., 30
- Target qty = Target days × Daily usage
- Suggest reorder = Target qty – Inventory Position
### Lead-time risk conditions (what makes this “good enough MRP”)
Add risk tiers using typical vs. pessimistic lead time:
- **Low risk:** Cover ≥ typical lead time + buffer
- **Medium risk:** Cover < typical lead time + buffer
- **High risk:** Cover < pessimistic lead time OR supplier confidence low OR demand spike detected
Demand spike can be as simple as “current DAU is 30% above 60-day average” if you store both.
### Approval routing (keep it simple)
- Low risk: auto-create “Planned Reorder” record (no approval)
- Medium risk: notify buyer in Teams with an “Approve/Adjust” link
- High risk: approval required (buyer manager or finance, depending on $ threshold)
Include the why in every alert:
- Item, location, supplier
- Cover days, lead time (typical/pessimistic)
- Suggested qty + MOQ/multiple adjustments
- Projected stockout date (even if estimated)
### Notifications: Teams first, email second
Teams is better for quick action; email is better for audit trails and external forwarding. Many SMBs use both: Teams for the primary alert and email for the approval record.
**Practical takeaway:** Make the workflow *evidence-based*. If the alert can’t explain itself in two lines, it won’t get trusted—and people will go back to “I’ll just order extra.”
---
## Chapter 6: Implementation Considerations—Security Roles, Data Quality, UX for Planners/Buyers, and Integrating Receipts/Usage (Optional)
The key insight: Low-code doesn’t mean low-stakes. If the app is making reorder recommendations, you need governance, roles, and data stewardship.
Gartner flags that scaling low-code/citizen development without governance increases operational risk; success depends on guardrails and shared IT-business ownership (as noted in Gartner’s guidance on citizen development governance). You don’t need bureaucracy—you need clarity.
### Security roles (minimum set)
– **Planner/Buyer:** view items, edit policies for assigned category, create/submit reorders
– **Approver:** approve/deny high-risk reorders, view history
– **Data Steward:** maintain vendor and lead-time policies, manage exceptions
– **Read-only Ops/Leadership:** dashboards, KPIs, audit trail
Turn on auditing for key tables (policies, lead-time values, approvals) so you can answer “who changed what” without detective work.
### Data quality (where success actually comes from)
Start by choosing which fields must be trustworthy:
– On-hand and on-order (even if imported daily)
– Approved supplier list
– Lead time typical + pessimistic (or buffer)
– Usage rate (even if estimated)
If consumption is messy, use a staged approach:
1. Begin with manual DAU maintained by planner for top 50 items.
2. Add imports from your accounting/WMS/3PL later.
3. Improve from averages to actual issue/ship history over time.
### UX: design for fast review, not data entry
Your Power App should have:
– A prioritized “Risk Workbench” view (High → Medium → Low)
– One-click drill-down: item → supplier options → history → policy
– A clear “override with reason” action (and store that reason)
Before diving into solutions, let’s understand the problem: buyers ignore tools that slow them down. If reviewing an alert takes longer than placing the PO, adoption dies.
### Optional integrations (nice, not required)
– Receipts from WMS/3PL
– PO open quantities from accounting system
– Production issues/scrap (to improve consumption signals)
**Practical takeaway:** Build the workflow around a small set of trusted fields, and expand integrations only after the app is producing credible recommendations.
—
## Chapter 7: Pitfalls + Measuring ROI—Alert Fatigue, Bad Defaults, Exception Handling, and Tracking Stockouts Avoided and Expedite Fees Reduced
The key insight: If you can’t measure impact, the app becomes “just another system.” Tie it to outcomes your CFO and ops lead already care about: expedites, service levels, and schedule stability.
### Common Mistakes (and how to avoid them)
– **Alert fatigue:** If everything is urgent, nothing is. Start with high-risk alerts only; add medium-risk after tuning.
– **Bad defaults:** Defaulting every item to the same buffer days guarantees nonsense. Use categories (A/B/C) or supplier reliability tiers.
– **No exception path:** Backorders, substitutions, and allocation happen. Add statuses like “Supplier constrained,” “Alternate sourced,” “Accept stockout,” with notes.
– **Ignoring MOQ/order multiple realities:** A “reorder 37 units” suggestion that becomes 200 units in real life breaks trust. Show the adjusted quantity and why.
– **No ownership:** If nobody owns lead-time policy updates, your model quietly rots.
### What Good Looks Like (brief example)
A 12-person manufacturer tracks 800 components. They start with the top 120 by downtime risk:
– Lead times captured per approved supplier
– Daily run generates 6–15 actionable alerts
– High-risk alerts require approval only above a dollar threshold
– Within two months, expedite shipments drop because orders are placed earlier—based on risk, not calendar reminders
Industry estimates suggest most gains come from preventing a small number of high-impact stockouts, not from optimizing every SKU.
### Measuring ROI (keep it simple)
Track four metrics in Dataverse (and show them in a Power BI dashboard later if you want):
– **Expedite spend** (monthly) and count of expedited POs/shipments
– **Stockout incidents** (count + duration) for governed items
– **Approval cycle time** (how long high-risk reorders sit)
– **Planner time** spent reviewing exceptions (self-reported at first is fine)
Also track one leading indicator:
– **Alerts tuned out** (dismissed without action). High dismissal means logic needs adjustment.
**Practical takeaway:** Don’t promise “perfect inventory.” Promise fewer preventable emergencies—and prove it with expedite and stockout trend lines.
—
## Signs You Need This
– You have reorder points in spreadsheets, but you still get surprised by stockouts
– Expedite fees are “occasionally necessary”… and also a recurring line item
– Different buyers use different “buffer logic” in their heads
– You can’t easily answer “which supplier lead times are getting worse?”
– A single late vendor shipment causes schedule reshuffling for days
## Questions to Ask (before you build)
– Which 50–150 items cause the most downtime or customer pain when they stock out?
– Do we have approved suppliers per item, or is that knowledge stuck in email threads?
– Can we get *any* reliable on-hand/on-order feed daily (even if it’s imperfect)?
– Who will own lead-time policy updates and exception codes?
– What should require approval: dollar threshold, risk level, or both?
—
## Closing
Spreadsheets aren’t evil—but they’re a brittle place to run replenishment decisions when supplier lead times are volatile. The “good enough MRP” approach is to (1) model **approved suppliers and lead times** in a real data structure, (2) compute **cover vs. replenish risk**, and (3) use **Power Automate** to turn that risk into the right mix of suggestions, approvals, and Teams/email alerts.
If you want to pressure-test this in your operation, take 10 minutes to list your top 5 stockouts from the last quarter and ask one question for each: **“Did we reorder late because we assumed the wrong lead time?”** That self-assessment usually makes the next steps obvious—and keeps the conversation grounded in real pain instead of abstract tooling.
