Is Planning Production in Excel an Optimal Solution?

Excel experienced a popularity boom in the 1990s, becoming a well-known tool with versatile capabilities and applications. Its numerous mathematical, financial, and database functions, along with semi-automated formula replication, made everyday tasks much easier for businesses across various industries. It wasn’t limited to companies, either – the program has gained recognition among private users, e.g., for household budgeting.

Availability and simplicity are the key factors that contributed to Excel’s success, not to mention its visual appeal, user-friendly interface, and intuitive operation. Users can quickly learn to harness its power to perform rapid calculations, analyze data, and create charts that streamline daily operations across various roles and industries.

For many businesses, especially smaller or growing ones, Excel is a perfect solution due to its affordability and ease of implementation, without the need for specialized expertise. Nearly any employee can work with spreadsheets after short training.

Let’s take a step back to when manufacturing management wasn’t as advanced as it is today. At that time, all essential data existed only on paper, calculations were done manually, and planners had to sift through piles of documents and tediously write out task assignments to prepare a schedule.

It automated calculations, allowed for visual data representation, and enabled users to consolidate information in a clear and readable format. This saved planners enormous amounts of time compared to older methods, even if entering the data still required some effort. It’s no surprise that Excel was widely embraced, and planners became experts at making the most of its capabilities.

Preparing uncomplicated plans and schedules in Excel is easy. The program handles calculations automatically, enabling planners to analyze production data, track inventory, forecast demand, and configure schedules. But the question remains: is it enough for effective management of a modern factory?

Although Excel may have been groundbreaking in its time, the challenges for manufacturing companies have evolved dramatically over the years. Today’s market is dynamic, with growing customer expectations for quality, variety, and short order completion times. Meanwhile, competition is relentless.

To meet these growing challenges, modern manufacturing management methods are essential – efficient, flexible, and able to integrate all stages of the production process. Effective production planning must be based on reliable data, and companies are dealing with more data than ever before. This is where Excel starts to fall short.

Excel’s simplicity becomes a disadvantage when there is too much information. Spreadsheets packed with hundreds of macros and functions become difficult to read, manual data entry takes too long, multiple users working on the same document can cause data conflicts, and the difficulties in seamless integration with other tools hinder real-time progress tracking.

One of Excel’s biggest limitations is its lack of scalability. Small companies with minimal production and uncomplicated processes can rely on Excel for efficient production planning. However, as the company grows, its operations become more complex and the amount of processed data increases. As a result, the program becomes less efficient, leading to issues with speed and accuracy.

Like any tool, Excel relies on the information it is fed, which means that reliable output data depends on correct input data. This introduces a certain level of risk that might bring serious consequences – especially as production planning processes become more complex.

Initial data must be entered manually into spreadsheets. Even when information on customer orders, product definitions, and routing is collected in a system such as an ERP, it must be copied and pasted or even entered manually into Excel. Unfortunately, this manual data entry comes with the risk of human error. While making errors is inevitably human, the consequences may be severe.

Even a small error, not caught in time, can snowball into a huge problem, especially when it forms the basis for other calculations across multiple departments. What may seem like a trivial mistake can render hours of planning useless in an instant. This may add more work for planners, lead to unnecessary orders, cause avoidable downtimes, and create many other serious issues.

Companies that manage production in Excel often face the question: should we use one large file that contains all data, or break it down into smaller ones for specific departments or areas? Neither approach is ideal, especially in medium and large enterprises managing multiple orders simultaneously.

It might seem that one large cloud-based file is a convenient solution. Everyone has access to it and all necessary information is stored in one place. However, when several people are editing data, the file structure can easily become corrupted. Also, local downloads and edits create confusion, leading to multiple versions of the same file circulating within the company.

So maybe splitting the data into multiple files for different areas could be more beneficial? While it might help stay organized, many of these spreadsheets will share the same underlying data, requiring repeated manual entry or transfer of information between files. This way we not only waste time but also become vulnerable to errors in data entry.

Planning production is a significant mathematical challenge. For example, with just 15 orders to be processed on one machine, the number of possible arrangements is ~1,3 *10^12 = 1,3 trillion. That’s a number so large it’s difficult to comprehend, let alone manually choose the best option. So, is it even possible to manage such vast possibilities and identify the optimal sequence?

It is possible, thanks to a range of algorithms, including Linear Programming (LP), Mixed Integer Linear Programming (MILP), heuristic, e.g., genetic algorithms, as well as models derived from the Theory of Constraints and Scheduling Theory.

Sounds complicated? Because it really is. It’s easy to imagine that simple and transparent spreadsheets lack such functionalities. However, are they even necessary? Is the processing sequence really that impactful? If we care about optimal production, timeliness, profitability, and the competitiveness of our company, it can be crucial. From this perspective, schedules made in Excel are often inadequate and unoptimized because it’s simply impossible to analyze such a vast range of options or efficiently apply models based on the theory of constraints or scheduling theory.

If your company struggles with similar challenges, it may be a sign that it’s time to look for a more effective tool based on modern manufacturing management methods. The perfect solution might be the Advanced Planning and Scheduling (APS) systems, designed specifically for production planning and scheduling, using the most suitable algorithms and models. These systems can generate detailed plans and schedules in just a few seconds.

When creating plans and schedules, APS takes into account a variety of factors. Thanks to its ability to integrate with other systems, production data can be automatically exchanged in both directions with ERP and MES. Moreover, once provided, the information can be used for various purposes and continuously updated.

Professional production planning and scheduling software enables multiple users and departments to use it simultaneously. It’s also possible to assign permissions to individual employees, limiting their access only to the relevant information.

APS is also an excellent tool for business analysis, helping to estimate realistic deadlines or the profitability of a specific order, as well as selecting the best way to complete it. The system facilitates effective management of unexpected situations, such as sudden failures, allowing users to monitor the impact of changes in productivity or resource availability on fulfilling customer orders.

As mentioned above, checking and comparing different alternatives is essential for optimal planning, and APS systems are irreplaceable in this respect. The What-If functionality, or scenario analysis, utilizes and combines different algorithms to generate alternative scenarios that take various factors into account.

What makes this option so useful (and practical) is the system’s speed. For example, in discrete manufacturing encompassing several plants and a total of 35,000 orders, resulting in 80,000 operations and requiring the consideration of 20,000 components, APS can generate subsequent schedules every 10 minutes.

This example illustrates a very complex situation. For typical planning tasks, time to generate schedules is usually a few to a dozen seconds (e.g., 3 seconds for 300 orders, 600 operations, and 40 000 components). To achieve a similar performance, you only need workstation-class computers with quad-core CPU running at 4GHz or higher and 16-32 GB of RAM, depending on the complexity of the model.

Of course, the mentioned speed parameters depend on the algorithms and models implemented in the specific system. It is also important to consider not only the speed of an APS system but also the optimization efficiency, as it’s not difficult to calculate something quickly but inaccurately. Nevertheless, almost any APS system will be far more effective at supporting scenario analysis than a spreadsheet.

How should we plan production and why is the right tool key to its success? A suitably selected solution brings numerous benefits to manufacturing companies, including: 

Time Savings: Time is money, so it shouldn’t be wasted on tedious data entry or manually creating and updating schedules, which is unavoidable when using Excel for manufacturing management. APS eliminates such tasks and helps employees focus on more important issues that directly impact profits. 

Management by Facts: Accurate business decisions are based on reliable information about what’s happening in production. The more accessible, clear, and up-to-date this data is, the easier it is to develop an effective strategy, plan key actions, and make beneficial investments. With too much information, a spreadsheet becomes inefficient, while APS can create clear plans and schedules that consider many complex factors and allow for rapid updates whenever changes occur. 

Optimal Production: Sometimes even minor improvements, like changing the sequence of tasks, don’t cost much but can have spectacular effects. However, to make this possible, we need to try different options, which is not always easy. The What-If scenario analysis available in APS enables users to generate and compare alternative versions of plans and schedules, letting them select the optimal solution. 

Creating simple plans with spreadsheets is possible and can be a good idea at the start. However, as a company grows, the drawbacks of planning production in Excel are increasingly apparent, and the planning process becomes inefficient and suboptimal. This is the perfect moment to implement APS, a modern system precisely designed to streamline and optimize production planning.