Weekly reorder plan that tells you what to buy before you stock out

Every Monday at 8am, build me a supplier replenishment plan that tells me exactly what to reorder before I stock out.

Start by reading my reorder settings tab in Google Sheets with Get Values. It holds one row per SKU with these columns: SKU, Supplier Name, Lead Time Days, Minimum Order Quantity, and Safety Stock Weeks. Treat that tab as the source of truth for which SKUs to plan for and what each supplier's constraints are.

Then pull the last 60 days of ShipBob orders with Get Orders and work out each SKU's average daily sales velocity across that window. Get current stock with Get All Inventory Levels, using total sellable quantity, which is fulfillable quantity minus exception quantity, rather than raw on hand. Get everything already on the way with Get Multiple Warehouse Receiving Orders and count the units on receiving orders that have not landed yet, so we never reorder units that are already inbound.

For each SKU, calculate days of cover as on hand sellable plus inbound divided by average daily velocity. Flag a SKU when days of cover falls below its supplier lead time plus the safety stock buffer, where the buffer is Safety Stock Weeks multiplied by seven days. For each flagged SKU, suggest an order quantity that covers the projected gap through the lead time plus buffer, then round it up to that supplier's minimum order quantity.

Skip SKUs that sold only a couple of units across the whole 60 day window, so slow movers and discontinued items do not clutter the plan. Separately, in its own section at the top, call out any SKU that is already at zero sellable stock with nothing inbound, since those are already losing sales rather than merely at risk.

Append the finished plan to a Reorder Plan tab in Google Sheets with Append Values, one row per flagged SKU, including the run date, SKU, supplier, average daily velocity, on hand sellable, inbound units, days of cover, and suggested order quantity.

Finally, post a Slack Bot message to my ops channel with Send a Message. Lead with the SKUs projected to stock out soonest, then group the rest by supplier so one section equals one purchase order conversation. Keep it scannable enough that I can act on it without opening the spreadsheet, and note anywhere the numbers look unreliable, such as a SKU whose velocity is driven by a single unusually large order.