Multi-Plant Capacity Planning Model

I need a robust, forward-looking capacity planning model that lets me see—at a glance—whether each plant in our CPG network can meet the next 12 months of demand. The demand side will come from an existing time-series forecast; the supply side must be calculated from our current run rates, the staffing we actually have on each shift, and the working hours available.

What matters most is a clear comparison that balances supply and demand month by month, highlighting any shortfalls or excesses so we can take action before problems surface. I also want the flexibility to adjust assumptions (for example, add weekend shifts or tweak run rates) and watch the projected capacity line update instantly.

Deliverables
• A dynamic model (Excel, Python notebook, Power BI, or a similar transparent tool) that accepts:
 – the 12-month time-series forecast as an input file
 – plant-level run rates, staffing levels, and standard hours
• Automated calculations that roll these inputs into monthly capacity by plant and aggregate network view
• Visual dashboards or charts showing demand vs. capacity, with color-coded gaps
• Simple “what-if” toggles or input fields for scenario testing
• Clear documentation so my team can refresh the data and keep the model alive after hand-off

Acceptance criteria
1. The model reconciles to our test data within ±1 % for both demand and capacity lines.
2. Scenario changes recalculate in under 10 seconds on a standard laptop.
3. All formulas, scripts, and queries are unlocked and annotated.

If you have experience blending supply-chain analytics with time-series forecasting and can deliver a user-friendly, future-proof model, let’s get this built.