Suggestions needed

[LiAD]

Hi,

I have a complicated list to sort which will be quite difficult to explain.
I do not know how to approach it so I will give a simplified version.

Imagine I have a list of something to make in a factory, products A,B,C and
D. I know the date the customer wants to have it and how long it takes to
manufacture, therefore what date i should start. I want to give the workshop
a list of what products to make and when. To make it easy I would like to
make all the A's, B's etc at the same time, PROVIDING they are within the
date and re-arranging the order will not cause any other orders to be late.
Imagine I have 3 days between the 1 and 4 Mar to make the following list
(times to produce beside).

Time to make Date due
A 10 hours 1 Mar
B 20 hours 2 Mar
C 3 hours 2 Mar
A 10 hours 3 Mar
C 3 hours 4 Mar
A 10 hours 4 Mar

In between each item takes 2 hours to change the machine from A to B, B to C
etc. so if I regroup I gain time.

Suppose today is the 30 Apr and I have to decide what to make when for the
next four days. My machine must start with product A.

I would like excel to minimise the number of times the machine changes from
one product to another, keeping all the products within the dates requested.
I need some way for excel to group similar products, check the final
production time verses the delivery time, if its within date re-arrange if
not take the next most urgent product etc etc.

So for example if we group all the A's and B's the first batch of product C
will not be delivered on time however with the list shown we could do A B C C
without affecting things.

What suggestions does anyone have for how best to approach this? Seems like
an iterative approach is needed, try, check dates, retry etc. Maybe not
though, maybe some rules could be set that index formulas can run off.

Anyone got any ideas

 

[ryguy7272]

What you need is Solver. You may have to install it, if it is not running
now. Tools > Solver. If it is not there, Tools > Add-ins > Solver Add-In >
OK. Then look at this:
http://www.solver.com/stepbystep2.htm

I don't have time to figure out the logic, and you know it better than me
anyway, so just use that as a guideline. Start simple, with one or two
criteria, and you will have your result in no time at all.

Good luck,
Ryan---

 

[LiAD]

Thanks for your answer.

I'm playing with the solver but I can't get it to reorganise the vertical
rows (product order list) to minimise the set-ups.

I have set-up a simple table with 6 columns

Col 1 - Product name (ABCACACB)
Col 2- time reqd to produce (A 0,75 days, B 0,5 days and C 0,25 days).
Using an
index function to match product to time required.
Col 3- delivery date required
Col 4- first row is todays date plus 0,75 days to produce A = todyas date
second row is date to produce first product + time to produce
second
product, or today + 0,75 + 0,5 = 1/4/09
Col 5- if function, so if product changes from A to B etc it adds 0,1 days
for a set-up
at the bottom in cell H17 is a sum function to calculate the total
set-up time.
Col 6 - another if function, if the date in col 4 is less than or equal to
the date in col
3 (in the same row) then it puts a if its not then a 0. In cell
I17 I then sum
all the 1's and zero's for the deliveries.

I would like solver to maximise cell I17 by minimising cell H17 and chaning
the order of the products in col 1.

Do I need to set this up differently to get it to work?

Thanks

 

[RyGuy]

Can you email the file to me or it is confidential? If I see the file I can
probably figure it out. I don't think I can understand what you wrote here.

Thanks,
Ryan--
(e-mail address removed)

 

[LiAD]

Yeah sure

Where do i find your address?

Can you email the file to me or it is confidential? If I see the file I can
probably figure it out. I don't think I can understand what you wrote here.

Thanks,
Ryan--
(e-mail address removed)

 

[LiAD]

Just found it. was looking for buttons so ignored ur signature

 

[Harlan Grove]

What you need is Solver. . . . ....
"LiAD" wrote: ....

Then the due dates in the table above are March dates in the
subsequent year, so you have 11 months to manufacture these parts? Or
should the dates in the table be in May? I'll assume so.
....

You don't mention times in the table above, so are the due dates all
as of 1 second after midnight on the given dates? I'll assume so along
with assuming the machine can be operated indefinitely and that you
have a full 24 hour work day.

That so, this problem could be solved by inspection. You need 10 hours
of A in 24 hours, 20 hours of B and 3 hours of C in 48 hours, another
10 hours of A in 72 hours, another 3 hours of C and another 10 hours
of A in 96 hours.

The most obvious efficiency is running a single 6 hour production run
for C ending at 03:00 on 2 May, which means you'd have 3 hours of C at
00:00 on 2 May. So you'd need to begin C production at 21:00 on 1 May,
so end B's production run at 19:00 on 1 May, so begin product B's
production run at 23:00 on 30 April, so end product A's first
production run at 21:00 on 30 April, so you'd have 21 hours of product
A production at 00:00 on 1 May.

Anyway, the most efficient production run would be

30 Apr 00:00 Begin Product A production run
30 Apr 21:00 End Product A production run and retool for product B
30 Apr 23:00 Begin Product B production run
1 May 19:00 End Product B production run and retool for product C
1 May 21:00 Begin Product C production run
2 May 03:00 End Product C production run and retool for product A
2 May 05:00 Begin Product A production run
2 May 14:00 End Product A production run

30 hours total product A in 2 separate 21 hour and 9 hour runs
20 hours product B in a single 20 hour run
6 hours product C in a single 6 hour run
6 hours total retooling time
....

I don't think Solver can handle this. The constraint is that you need
sufficient inventory of given products at order due dates/times. That
means running Solver to generate a production run table with
constraints derived from an inventory table that reflects additions
from production and reductions from filling orders.

If you use COUNTIF or SUMPRODUCT functions in cell formulas to count
the number of products with negative units in inventory and set Solver
constraints that those cells must equal 0, Solver will fail because
such formulas are too nonlinear. OTOH, unless you need 20 or fewer
separate production runs, Solver can't handle individual constraints
for each product at each order date.

This is more of a database problem than a spreadsheet one. This really
requires tracking inventory given additions to inventory from
production and reductions to inventory from filling orders, so ideally
involving a production run table (what you need to generate), an order
table (the given), an inventory transaction table (additions from
production and reductions from orders), and an inventory table (stock
on hand, essentially accumulating the inventory transactions at every
point in time).

The constraint is that you should always have nonnegative (>= 0)
inventory in all products at all times. The objective is minimizing
the ending date/time of the last production run.

The initial production run's start time would be a given. Easiest just
to cycle through products A, B, C, A, B, C, etc. Then start times for
subsequent runs would be 2 hours after the prior run's end time *IF*
the prior run's end time was later than its start time, or just the
previous run's start time. That is, products can have zero run times.
This way production run end times are the only variables.

That said, I still don't believe Solver can handle realistic size
problems of this kind.

 

[LiAD]

Thanks a lot for your detailed answer.

Do you think this type of problem can be handled in another way in excel?
What approach would you take in order to produce the most efficient
production schedule that respects the delivery times?

Thanks

 

[Harlan Grove]

Do you think this type of problem can be handled in another way in excel?  
What approach would you take in order to produce the most efficient
production schedule that respects the delivery times?

....

Depends. If you have fewer than, say, 40 separate production runs to
schedule, you'd be better off doing this by hand, possibly using Excel
as a convenient list manipulation tool. If you have hundreds of
separate production runs to schedule and the savings from efficient
scheduling would exceed US$10,000, there's specialized production
control software which would probably be a much better idea than
kludging this in Excel.

Excel is definitely *NOT* the right tool for this particular task.