Hello,

I have a complicated problem which is dificult to explain easily I think.
I will try and lay out the basic concept. What I would like to know is how
can I achieve this in excel.

I have a list of products A B C for example which I will produce during a
set period of gg days on one single machine. The knowns are
- It takes xx hours to produce A, yy for B, zz for C
- If I change from one product to another it takes 4 hours to retool the
machine (from A to B, B to C etc for all changes)
- The production date that each product must be finished

Customers, (being difficult), would like me to deliver in the following
order A B C A C A C B. This presents two potential problems
- Time may be restricted
- If I do follow the customers orders exactly I need to spend a lot of time
changing over my machine. Time wasted that maybe I can use to find more
products or customers.

What would be nice is to have a tool in excel that can answer the question -
can I change the order in which I will manufacture the products and still
meet the customer delivery requirements? If so give me a production schedule.

So I have a table from which I know the products and dates the customer
wants. From the production and tool change times required I can calculate
when it will be possible to make each product. Then I would like to find
some way to get excel to produce a production schedule by optimising the
manufacturing order to reduce the amount of tool changes, (and therefore
total time spent on them), ensuring that all of my orders respect the
customer dates.

The function/method needs to group similar products together providing
- manufacture is finished on or before the customer date
- the resort process does not make any other products late (as in if the
customer asks to have A B A but I re-organise to A A B maybe the A's are
great but I have made the B late).

An important point to consider is that I have only one machine therefore it
is one product after another so the time that product 2 is finsihed is time
of day the line was started + manufacture time for product 1 + tool change
time + time to make product 2.

The function, (in my opinion), needs to iterative as basically it will
re-organise the manufacturing order, check that all orders will be finished
within date, if not re-organise and recheck etc etc.

Is this possible and if so how in terms of function design etc?

Thanks a lot. I hope its clear enough, if not please ask. I'll well stuck
in tables and tables and tables.

LiAD