This not a generic tool like the other tools on this site. It is a sample application intended to serve as an "idea generator" for similar applications.

Download

.xls file (3.32 MB) or .zip file (0.76 MB)

Background

I began my career in a paints company where paints where manufactured by a batch process in paint mixers. After the batch was completed, the mixer would be cleaned and made ready for the next batch. Different batches would take different amounts of time to process and depending on which shade was processed subsequently, the cleaning time would also vary. The challenge was to produce the maximum number of batches in a given amount of time.

There are 2 options - reduce the processing and set up times or schedule the batches optimally. The former option requires typically expensive engineering modifications while the latter requires difficult computation.

This tool takes the example of paint production to show how spreadsheets can be used for the latter option, that is, to schedule the batches so as to optimize the production.

At the outset, it must be noted that this is a very difficult problem. The batches can take only discrete values, meaning only one batch can run at a time on the machine. Optimizing such variables is called Integer Programming, and they require complex algorithms to solve them. In this tool, we use the simplest approach - Brute Force to optimize 8 batches. (8 batches may not look like much, but there are a whopping 40,320 ways of arranging them !!)

Notes

Obviously, this situation occurs in many other batch processes too. This tool is applicable to such processes. Note however, that this tool does not take into account other constraints such as material availability, maintenance schedules or availability of multiple machines for the same job. It can be modified to include some of these, but modelling all real life processing constraints is quite challenging !

Acknowledgements

Originally, the idea for this tool came while thinking about a course project during my Masters course in NUS. I wish to thank the course instructior, Prof. I.A. Karimi, for his kind permission to reproduce a modified version of the same on this website.