Case Study – Why Did I Start Production Planning By Spreadsheets?

This is a story from Gabriele Tettamanzi. Gabriele is a new contributor to the site. In this article, he shares his experience from using the Fast Excel Development Method in his previous company.

At the beginning of the 2000s, I worked in the Automotive business. I was leading the supply chain department for a component manufacturer. We made fluid pressure and level sensors for cars and bikes applications.

I had an urgent need to improve our on-time delivery – the main customer of that plant had asked us to eliminate a regional distribution center and to switch to just-in-time, direct delivery to the plant. This was a big shift in the way we deliver to the customer.

Our planning activity used to take place on a weekly basis. The materials were planned by a traditional MRP system that was fed by sales orders and a forecast. Each and every day the supply chain people were chasing the most urgent finished goods in manufacturing and the missing materials from suppliers.

This operating mode created many inefficiencies in production. These included stoppages due to material shortages and unexpected production changeovers. It made it difficult to respect the delivery dates to the customer. The materials schedule and the productions schedule were very loosely linked and consequently prone to inconsistencies.

I started looking for a programming tool that would allow me to solve these issues: a finite capacity scheduling with a short-term materials availability check that could be interfaced with our company management system.

I investigated the market of software tools that specialized in scheduling. I certainly identified a number of them that were suitable for the function but they had to go through a long and uncertain approval process and subsequently to implementation with external consultants.

I tried to use an open-source generic linear solver interfaced with a spreadsheet for input and presentation of the results: the pilot model that I created for a single line, which was also the subject of a university thesis. It worked but the logic was completely obscure to the people working for me and it was hard for me to predict its behaviour.

In the meantime, time was passing by and I didn’t have much more available. With little hope I started looking for a spreadsheet template. In the midst of a myriad of articles that illustrated why NOT to use Excel and to buy this or that piece of software instead, I came across Production-Scheduling.com.

There was a lot of what I was looking for. Above all there was a method to develop the system on Excel quickly and effectively together with guidelines to make it solid. And also practical examples to upload: including the mighty P-S_Cycles example.

I contacted the founder, Tony Rice, who proposed to meet me in Barcelona where he would shortly be taking an introductory course, I made my trip approved and I signed up for the seminar. I didn’t receive any update about the course in Barcelona, therefore I got in touch with Tony again who informed me of the cancellation of the seminar but sent me a good number of examples and declared himself available to give me remote support if I needed it.

By following the Production Scheduling guidelines and analyzing the material received by Tony I was able to set up the scheduling system which included:

• demand management, sales orders, backlog and forecasts
• finite capacity programming of 17 assembly lines, with simple optimization of the assembly sequence to minimize changeovers
• short-term verification of the availability of materials both produced by the internal shearing and molding department and purchased from suppliers
• interface with the data of the management system both inbound (stocks, bills of materials, customer order portfolio and purchases) and outbound (the scheduling on Excel of the 17 lines worked as a master production plan for the MRP of the management system)

I didn’t need to wait long time for KPI improvement: On Time Delivery went to over 95%. At the same time we saw a reduction of inventory levels by 15%.

Even more important it was the organizational process that which led us to make the supply chain system more robust and to operate collaboratively and in an integrated way with production coworkers.

After that experience, there have been a number of situations in which I developed capacity and material programming applications on Excel, in some cases as pilot systems preparatory to specialized piece of software, in other cases as a permanent component of the supply chain system.

The flexibility of Excel allows you to develop powerful tools, it does not require spending and it is suitable for continuous improvement and adaptation to new needs.
The Production-scheduling method helps to achieve the goal in a very short time and to guarantee its continuity and scalability over time.

This is why did I started to deploy capacity and material planning systems by spreadsheets. And I’m still doing it.