In our recent webinar (replay here) , we explored how Python can extend Excel’s capabilities for production planning and scheduling.
For those who rely on Excel as their primary business tool, Python gives you new possibilities without abandoning the familiar spreadsheet environment. This post summarizes the key takeaways from the webinar on leveraging Python within Excel for advanced planning applications.
Why Combine Excel with Python?
Before jumping in to the technology and the “How” of using Excel and Python, we should ask “Why?” It is not enough to just replicate what we can already do in Excel.
I believe these are the best reasons to extend Excel with Python:
- Capabilities beyond Excel’s reach – particularly for iterative processes, optimization problems, and advanced statistical modeling.
- Access to a vast ecosystem of open-source libraries – professional-grade tools for optimization, forecasting, and simulation that would be impractical to build from scratch.
- Ways to share and scale the results – creating interactive dashboards and web applications from your planning data.
The key insight is not to replace Excel, but to complement it – using each tool for what it does best while maintaining Excel’s visibility and accessibility advantages.
Integration Options Explored
We demonstrated several approaches to Excel-Python integration:
- Locally installed Python – Everything running on your computer. Most flexible and direct, but more to learn.
- Microsoft 365, Python in Excel – The new built-in feature in Microsoft 365 that allows Python functions directly in spreadsheet cells.
- Other integrations with Excel and Python – including Anaconda Toolbox for Excel and PyXLL.
- API connections – Linking Excel to connect to Python services running on remote servers.
- Web applications – Creating browser-based interfaces for Python models with Excel data import/export capabilities.
Each approach offers different tradeoffs between ease of implementation, computing power, and deployment flexibility.
Practical Applications Demonstrated
Inventory Simulation with Monte Carlo Methods
We built a complete inventory simulation model using Python in Excel. Unlike traditional deterministic inventory models, this Monte Carlo approach incorporates uncertainty in both demand and lead times to give a more realistic view of inventory risks.
The simulation generates multiple scenarios (1,000 in our example) to show possible inventory trajectories over time, highlighting critical metrics like:
- The probability of stockouts
- Minimum inventory levels across scenarios
- Average inventory holdings
- Replenishment patterns under different scenarios
This approach provides a much richer understanding of inventory risk than single-path calculations typically done in Excel alone.
Here is a detailed follow-on article on the concepts and principles of doing inventory simulation.
Production Scheduling Optimization
We demonstrated how Python’s optimization capabilities can address complex scheduling problems that go beyond what we can solve with the Fast Excel Development Method
- Handling non-hierarchical work centers; where the same machine can belong to multiple work centers
- Working with flexible jobshop routing where the allocation of items to machines can be dependent on capacity
- Optimizing schedules across multiple constraints (machines, tooling, labour)
- Visualizing the optimized schedule in interactive Gantt charts
Using an open source optimization tool, we showed how a production schedule with 36 jobs can be optimized in seconds – a problem with 36! = 3.7 x 10^41 combinations than could ever be calculated through brute force.
Time Series Forecasting
Our final example showed how to create a forecasting system that:
- Applies exponential smoothing with trend and seasonality components
- Generates confidence intervals around forecasts
- Validates forecast accuracy through rolling cross-validation
- Makes the forecasting engine available either through a web interface or directly from Excel
Getting Started Without Coding Expertise
The Fast Excel Method puts emphasis on No-Code and Low-Code capability. We want to get started without needing extensive python coding expertise. Right from the Excel Macro recorder, through Power Query and now with Generative AI LLM models, we have used coding assistance to help us learn by doing. We showed a quick example of building the Inventory Simulation using a text prompt and taking the code to run it inside Excel.
Even Excel users with minimal Python experience can begin to leverage these powerful capabilities, gradually building their understanding while immediately benefiting from the enhanced functionality.
Next Steps
The integration of Python with Excel opens exciting possibilities for production planners looking to enhance their analytical capabilities. Whether you’re interested in better inventory management, more optimized production schedules, or improved forecasting, Python can help take your Excel-based systems to the next level.
Watch the complete webinar replay for step-by-step demonstrations and downloadable examples to get started with Python in your Excel workflow.
Download the files that were used as part of the webinar.
We want to develop more examples of using Python and Excel to solve supply chain planning problems. Please help by choosing between these three examples: Inventory Simulation, Production Scheduling with Optimization and Demand Forecasting.