The tool finds out the best schedule by brute force, that is by evaluating the 8! (=40,320) possibilities and computing the total production time for each of them. The hard work is done on the 3rd sheet.

The different combinations are evaluated by listing all permutations of the number string 01234567. These correspond to the different batches and the order of the number represents the schedule. The reason for choosing this is to facilitate its use in the OFFSET function which is used to evaluate the total production time in each combination. Note that the evaluation is done in the first column, this is to facilitate the use of the VLOOKUP function to pick up the best combinations. To find the top 3 best combinations, the statistical function SMALL is used in cell C2.

Note that since all batches have to be produced, we only have to evaluate the combination that requires the least set up time. The formula for evaluating the total production time looks complex (see cell A1 of the sheet "Calculation"), but it is actually quite easily created using Excel itself, see this tip on creating long Excel formulae using Excel.

Generating Permutations

The different permutations were generated using VBA. Efficient codes are available on the internet, see one example here.

Why restrict to 8 batches ? We could keep adding more, but after a certain point, we will quickly find that the number of permutations are so many that computing them takes an unacceptable amount of time.