Let’s start with the simple answer to that question. As with anything, as long as it is structured and configured correctly, and you do the right things with the right elements it will be effective.
While an effective e2e Supply Planning process is crucial to achieving the operational and financial business objectives, using excel as a platform for the Supply Chain planning tools is no different to any other solutions. And, due to limited specialist operational planning functionality available from some ERP solutions and the cost of some holistic advanced planning systems, using excel demand forecasting and planning spreadsheets is far from uncommon.
Data Integrity, Integration and Automation.
System integration and data integrity are commonly cited as the main drawbacks of using spreadsheets, however with an ODBC (Open Database Connectivity) connection, master and transactional data can be extracted from an ERP solution directly into spreadsheets with a simple mouse click and no copy/paste required. Equally, Excel has MS Query built in that allows data to be extracted directly from other spreadsheets or databases.
The benefits of using ODBC and MS Query to extract data directly is that data integrity is maintained, and any range names built into the receiving spreadsheet expand or contract according to the changes made in the data set, thereby ensuring no extracted data is omitted from the excel planning sheet.
Although automation does require some coding skills to create user forms, selection options and reporting functions the same is true of off-the-shelf systems. With spreadsheets, as they are already common place in business and most people have a basic knowledge of formula and configuring spreadsheets to specific needs, increasing the skill levels provides the ability to develop more advanced, integrated, and automated spreadsheet planning techniques.
Supply Chain planning tools.
Supply Chain planning is more than just simply balancing inventory, it goes without saying that holding the right amount of inventory is crucial to maintaining customer service level and cash flow objectives however, maintaining a robust and collaborative demand forecast is the starting point and essential to ensuring the right amount of inventory is available.
ERP solutions normally provide simple demand forecasting functionality, but this is typically limited to some basic options like using last year’s sales volumes or applying some simple growth metrics but what about changing promotional activity or event volumes?
Although without advanced coding skills an excel demand forecasting solution will not create a forward demand forecast as generated by a sophisticated algorithm, however, excel forecasting formula are available that analyse historic sales profiles to generate a seasonally adjusted demand forecast. This provides the basis for a collaborative demand review where overlay volumes can be applied from other functions to create the business demand forecast.
Depending on the business model, Inventory planning spreadsheets will normally take the form of a Master Products Schedule (MPS) - if manufacturing goods - and Material Requirements Planning (MRP) functionality to manage the bought in goods or materials, both of which are typically standard functionality in an ERP solution.
For a manufacturing business, managing the supply of the bought materials is dependent on the production schedule which needs to be capacity balanced. Generating a finished goods production plan using MRP functionality creates a capacity unconstrained supply plan and subsequently an unbalanced supply of materials risking out of stocks or unnecessary excess material inventory.
The MPS functionality available in ERP solutions is typically a suitable method of maintaining a capacity balance production plan however it does require the routing data and manufacturing calendars to be populated and maintained which can become complex, especially where some resources are shared between lines.
An MPS supply planning spreadsheet, sometimes referred to as an excel planning board, although requiring the same routing and line availability data is usually easier to compile and maintain.
Replicating MPS functionality from an ERP solution in an excel planning sheet can easily be achieved only requiring spreadsheet formula to collate the data into the right format. Any coding is limited to generating data sets for reporting and to simplify some repetitive tasks if required. Another benefit of using an excel planning sheet is that detail and aggregate inventory levels for warehouse capacities and financial liabilities can be monitored with real time change to the plan, something not typically available from an ERP solution.
Using the Material Requirements Planning (MRP) functionality available in an ERP solution is typically considered best practice and is the simplest of the planning systems. Essentially MRP creates purchase recommendations in-line with agreed supply criteria i.e. MOQ/EOQ volumes, order multiples, lead times etc. to maintain a predefined safety stock level or stock cover, and just need the supply parameters to be populated and maintained.
As MRP methodology is relatively simple and, for the most part, doesn’t need to consider capacity constraints, replicating the MRP functionality and reporting available from an ERP system in planning spreadsheets can also easily be achieved.
Rough Cut Capacity Planning (RCCP) is an analysis and reporting tool used to review capacity constraints and availability within a manufacturing environment. By aggregating item level demand by their unique manufacturing throughput rates to a line level report and applying the relevant production line efficiencies, down times etc. RCCP provides a holistic view of manufacturing capacities and inventory levels over the defined horizon and chronological periods. Although RCCP is usually an available option in ERP solutions it is rarely used, partly due to similar reasons as the MPS, and is typically either reviewed using specialist software or in spreadsheets.
Dependant on the level of detail required by the manufacturing operation Production Line Sequence Plans can also be developed which aren’t normally an option available from an ERP solution, these are specialist solutions. Production Line Sequence Plans show item start and end times, account for break and change over times can be developed to be dynamic so as manufacture of each item is completed start and end times of subsequent items are automatically adjusted. The benefit of dynamic sequence plans is that they provide a real time view of total plan giving advance notice where manufacturing is getting ahead or behind and time to take mitigating action. Maintaining Production Line Sequence Plans also supports management reporting i.e. OEE, Kg/Hr, man hour costs etc.
In addition to the main supply planning tools discussed, other tasks are also required to manage an effective supply planning process. Safety Stock levels should be reviewed periodically to ensure the correct buffer stock is maintained to support customer service level and financial targets, and an Economic Batch Quantity (EBQ) to identify the optimum manufacturing batch size accounting for operational efficiencies and warehousing storage costs.
To go back to the original question ‘Are Excel Planning Tools an Effective Option?’. It’s not so much the platform or solution used that determines the effectiveness, it’s more about ensuring all elements are managed correctly, and given some of the limited functionality available from ERP solutions and the cost of some holistic specialist solutions excel can be an effective option.
Utilising the ERP functionality available would always be considered best practice, and using specialist Demand Forecasting, Inventory Planning or Capacity Planning solutions would provide more optimum results but with both of these options data is exporting to excel for bespoke analysis and reporting so excel will always have it’s place in business operations.
Comments