Medication Supply Forecast Formulas

I need a reliable, easy-to-update forecast for medication supplies that tells me two things at a glance: how much stock I will need and when each batch will expire. I already have partial historical demand figures that you can build on; what I lack is a clean, formula-driven model that projects future requirements with confidence.

Please use Excel or Google Sheets—whichever you feel gives the clearest, most maintainable formulas. The sheet should automatically pull forward the remaining stock, apply average or weighted consumption rates from my partial data, flag upcoming expiries, and highlight any shortfalls before they happen. If there’s a smarter way to visualise this, feel free to include a dashboard, but the core deliverable is the working set of formulas.

Acceptance criteria:
• Inputs for monthly demand, incoming shipments, and lot-specific expiry dates
• Automatic calculation of projected on-hand quantity by period
• Visual or conditional-format alerts for items that will expire before depletion
• Clear annotation of every formula so I can maintain it myself later

Once the model passes a quick stress-test with dummy figures, we’re done.