The bad and the ugly will follow in this series because in my experience spreadsheets are sometimes a blessing but can also be a curse. It pays to be able to distinguish one from another. I had a very good experience in the early part of my career that probably gave me a false sense of security in future times. I’ll take you back to the early 1990s and the days of SuperCalc, an MS-DOS programme that was a forerunner of Lotus 123 and Excel.
I had the good fortune to be working in a Spinning Mill. At certain times it felt that I was in a working museum but the company had invested in some of the latest technology and it was doing its best to transform from a bulk cotton yarn manufacturer to be a producer of modern fashion yarns. This was a massive challenge and I was employed to make sense of the impact on productivity, output rates and production costs.
All the roving and spinning machines had performance monitoring systems that provided daily records of stop and start time, changeovers and running speeds. The data was summarised in a file that was compiled overnight. My first job of the day was to access this data and add it into a spreadsheet. The calculation process managed to combine the data for machine running with the production rates for the various different yarn types. The output was a report that summarised the performance and the volume of yarn produced over the previous 24 hours from every machine. Each morning I could create a management report that could be distributed amongst the team, and also add the data to a cumulative record that I could use to analyse trends and identify issues.
Using my experience in work study I was also able to improve the spreadsheet. By examining some of the calculation steps in detail and taking relevant observations I improved the accuracy of the model. One of the by-products of the process was that I could look at the efficiencies of groups of machines in order to adjust manning levels. A spinner, for example, usually looked after around 8 machines if he or she was producing a stable cotton yarn. New types of yarn required more interventions in which case the workload had to be reduced.
The data in the original spreadsheet enabled me to create a new resource planning tool. I used that each week to calculate the most efficient way of grouping machines in order to balance the workloads, optimise machine performance and minimise labour costs. This extra step enabled me to deliver a 12.5% increase in productivity.
This was my first experience of developing new tools for operational workforce planning. Fortunately it was a good experience and it gave me a new outlook on what could be achieved by turning data into information. It also showed that an effective process can achieve significant cost benefits.