Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "LiAD" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
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 "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "LiAD" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
Yeah sure
Where do i find your address? "RyGuy" wrote: 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-- "LiAD" wrote: 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 "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "LiAD" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
Just found it. was looking for buttons so ignored ur signature
"RyGuy" wrote: 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-- "LiAD" wrote: 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 "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "LiAD" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
ryguy7272 wrote...
What you need is Solver. . . . .... "LiAD" wrote: .... 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 . . . 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. . . . 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. .... 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 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. .... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
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" wrote: ryguy7272 wrote... What you need is Solver. . . . .... "LiAD" wrote: .... 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 . . . 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. . . . 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. .... 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 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. .... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestions needed
LiAD wrote...
.... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Suggestions needed. | Excel Discussion (Misc queries) | |||
Suggestions please! | Excel Worksheet Functions | |||
Suggestions??? | Excel Discussion (Misc queries) | |||
Need your suggestions | Excel Worksheet Functions | |||
help NEEDED URGENTLY (I HAVE TRIED SOME SUGGESTIONS BUT THEY DONT WORK UNLESS I AM DOING THEM WRONG | Excel Worksheet Functions |