Engineers-Excel.com

Batch Scheduler

Description| How to use| How it works



This application illustrates the use of brute force for integer programming in a real life example. With computing getting cheaper and faster, this technique could be used to optimize business and manufacturing processes with often involve integer programming (Integer programming refers to an optimisation problem where some or all variables are restricted to integers).

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 app 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 app, 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 app is applicable to such processes. Note however, that this app 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 app 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.

Also See:

Descriptive Statistics

Multifunction Plotter

Returns from non-periodic cash flows

Technical Stock Analysis