Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
The problem can be sove with programming but you need some additional
information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
To take ur points one at a time. Assume that
- inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
I don't have any idea on the quantities or time to make each item so it is
hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
So from one input table I would need to create every possible output
combination then select the one thats best. Seems workable. What function or type of macro is best to use that would to generate all the tables automatically? Thanks "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
Just made a quick calculation to see if this is possible.
My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
It would be a recursive algorithm. I have a bunch of them that I mdofiy as
required. You would need to re-run the program as new order are placed like every night or once a week becaue plans always change. Here is the recursive code that will put the combination on sheet 1 so you can see the results. Put a small number in becasue the combinations are large and may exceed the number of rows you can have in a worksheet. You willnot be saving all the combinations so the size of the worksheet is not a factor. Public Orders() Public combo Public RowCount As Long Sub permuations() NumberofOrders = InputBox("Enter Number of orders") ReDim Orders(NumberofOrders) For i = 1 To NumberofOrders Orders(i) = i Next i ReDim combo(NumberofOrders) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(Orders) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & Orders(combo(j)) Next j Sheets("Sheet1").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "LiAD" wrote: So from one input table I would need to create every possible output combination then select the one thats best. Seems workable. What function or type of macro is best to use that would to generate all the tables automatically? Thanks "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
I've laid out the data as follows on sheet 1.
Col A - product A B C A B C Col B - time to produce in days (0,5, 0,4, 0,3) Col C - delivery date Col D - calculation for eithers todays date + prod time for first item, or date finished last product + time to produce next one Cell F1 - to days date When I run the macro Iìm getting a complile error as a whole list of things are not allowed as public memebers of object modules. "joel" wrote: It would be a recursive algorithm. I have a bunch of them that I mdofiy as required. You would need to re-run the program as new order are placed like every night or once a week becaue plans always change. Here is the recursive code that will put the combination on sheet 1 so you can see the results. Put a small number in becasue the combinations are large and may exceed the number of rows you can have in a worksheet. You willnot be saving all the combinations so the size of the worksheet is not a factor. Public Orders() Public combo Public RowCount As Long Sub permuations() NumberofOrders = InputBox("Enter Number of orders") ReDim Orders(NumberofOrders) For i = 1 To NumberofOrders Orders(i) = i Next i ReDim combo(NumberofOrders) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(Orders) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & Orders(combo(j)) Next j Sheets("Sheet1").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "LiAD" wrote: So from one input table I would need to create every possible output combination then select the one thats best. Seems workable. What function or type of macro is best to use that would to generate all the tables automatically? Thanks "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
That is why a recommended a modeling approach. You randomly try 10,000 (or a
fixed number) different approaches from the 80! and choose the best. Running a PC overnight is a reasonable solution. When you get the code running test measure how long it takes to run one approach and that will determine how many trials you run. This is an artificial inteligence problem. You can try things like like sorting the orders by products and then not running more than 10 orders of the same product before switching products. I've taken Graduate Level modeling courses in college and understand the complexity of this problem. What yo will see if you run 10,000 combinations 4 or 5 different times you won't see a significant difference in the results. This will give you a confidence level that the random solution is a godd solution. "LiAD" wrote: Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
LD,
The best approach to this problem is to use a bit of common sense, and to work backwards from required date/times. It is way too complicated to program in a simple macro. For each of your unique products, find the earliest due date. Then work from the latest unique product backwards, like in this made up example: Say you need 40 A on April 4, 50 B by April 5, and 60 C by April 6, and all must be shipped by Noon on the due date. And then you need 30 A on April 9, 45 B by April 10, and 90 C by April 11. Start at Noon on April 6, and calculate when you need to start work on the 60 Cs to finish by then, including change-over time. If that start time is later than the B's are due, leave the time between the two unassigned, and then do the same for the 50 Bs. Again, if B's start time is after the As are due, leave that time unassigned, and do the same for the 40 As. That gives you your absolute latest start time for each product. Then, you need to decide what to do with the intermediate times. You can move up or expand production of A to include the next 30 units, in the time before A needs to start, or into the time after A is due and before B needs to start. You can move up production of the first 40 A, keeping the production the same, and expand Bs production to include the extra 45 B, and so on. But the decision on how to move things around will depend on a lot of things, especially future volumes. If the slack time in the first cycle would allow you to produce all of the A needed in a future cycle, then move that production of A up, and you will save the switch-over time associated with that batch. Basically, you are trying to fit blocks of production into the schedule in such a way that you remove the need for the change-over, and that requires judgment, something that Excel is famously bad at. HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
Bernie: This isn't an excel problem. It is a general programming problem.
There are software packages commercially available that is desgned to solve these type problems. But no software can solve this problem because the BEST solution is deterministic. The program is only as good as its inputs. A simplistic solution can be programmed in Basic to solve the problem. That is why I'm recommending the random approach. "Bernie Deitrick" wrote: LD, The best approach to this problem is to use a bit of common sense, and to work backwards from required date/times. It is way too complicated to program in a simple macro. For each of your unique products, find the earliest due date. Then work from the latest unique product backwards, like in this made up example: Say you need 40 A on April 4, 50 B by April 5, and 60 C by April 6, and all must be shipped by Noon on the due date. And then you need 30 A on April 9, 45 B by April 10, and 90 C by April 11. Start at Noon on April 6, and calculate when you need to start work on the 60 Cs to finish by then, including change-over time. If that start time is later than the B's are due, leave the time between the two unassigned, and then do the same for the 50 Bs. Again, if B's start time is after the As are due, leave that time unassigned, and do the same for the 40 As. That gives you your absolute latest start time for each product. Then, you need to decide what to do with the intermediate times. You can move up or expand production of A to include the next 30 units, in the time before A needs to start, or into the time after A is due and before B needs to start. You can move up production of the first 40 A, keeping the production the same, and expand Bs production to include the extra 45 B, and so on. But the decision on how to move things around will depend on a lot of things, especially future volumes. If the slack time in the first cycle would allow you to produce all of the A needed in a future cycle, then move that production of A up, and you will save the switch-over time associated with that batch. Basically, you are trying to fit blocks of production into the schedule in such a way that you remove the need for the change-over, and that requires judgment, something that Excel is famously bad at. HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
I can do the working backwards bit. Its producing the final improved version
that I am getting stuck on - apparently its not easy either! At least that re-assures me a bit. If producing the final schedule cannot be automated on excel then such is life. I will try the random approach and see what that yields in the meantime - something close might be better than the nothing I have now. Thanks "Bernie Deitrick" wrote: LD, The best approach to this problem is to use a bit of common sense, and to work backwards from required date/times. It is way too complicated to program in a simple macro. For each of your unique products, find the earliest due date. Then work from the latest unique product backwards, like in this made up example: Say you need 40 A on April 4, 50 B by April 5, and 60 C by April 6, and all must be shipped by Noon on the due date. And then you need 30 A on April 9, 45 B by April 10, and 90 C by April 11. Start at Noon on April 6, and calculate when you need to start work on the 60 Cs to finish by then, including change-over time. If that start time is later than the B's are due, leave the time between the two unassigned, and then do the same for the 50 Bs. Again, if B's start time is after the As are due, leave that time unassigned, and do the same for the 40 As. That gives you your absolute latest start time for each product. Then, you need to decide what to do with the intermediate times. You can move up or expand production of A to include the next 30 units, in the time before A needs to start, or into the time after A is due and before B needs to start. You can move up production of the first 40 A, keeping the production the same, and expand Bs production to include the extra 45 B, and so on. But the decision on how to move things around will depend on a lot of things, especially future volumes. If the slack time in the first cycle would allow you to produce all of the A needed in a future cycle, then move that production of A up, and you will save the switch-over time associated with that batch. Basically, you are trying to fit blocks of production into the schedule in such a way that you remove the need for the change-over, and that requires judgment, something that Excel is famously bad at. HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
Thanks Joel,
is it possible you could give me an idiots guide on how i should lay things out to run this macro. If i understand correctly I will get a lot of tables out from which I then pick the best? Thanks again "joel" wrote: Bernie: This isn't an excel problem. It is a general programming problem. There are software packages commercially available that is desgned to solve these type problems. But no software can solve this problem because the BEST solution is deterministic. The program is only as good as its inputs. A simplistic solution can be programmed in Basic to solve the problem. That is why I'm recommending the random approach. "Bernie Deitrick" wrote: LD, The best approach to this problem is to use a bit of common sense, and to work backwards from required date/times. It is way too complicated to program in a simple macro. For each of your unique products, find the earliest due date. Then work from the latest unique product backwards, like in this made up example: Say you need 40 A on April 4, 50 B by April 5, and 60 C by April 6, and all must be shipped by Noon on the due date. And then you need 30 A on April 9, 45 B by April 10, and 90 C by April 11. Start at Noon on April 6, and calculate when you need to start work on the 60 Cs to finish by then, including change-over time. If that start time is later than the B's are due, leave the time between the two unassigned, and then do the same for the 50 Bs. Again, if B's start time is after the As are due, leave that time unassigned, and do the same for the 40 As. That gives you your absolute latest start time for each product. Then, you need to decide what to do with the intermediate times. You can move up or expand production of A to include the next 30 units, in the time before A needs to start, or into the time after A is due and before B needs to start. You can move up production of the first 40 A, keeping the production the same, and expand Bs production to include the extra 45 B, and so on. But the decision on how to move things around will depend on a lot of things, especially future volumes. If the slack time in the first cycle would allow you to produce all of the A needed in a future cycle, then move that production of A up, and you will save the switch-over time associated with that batch. Basically, you are trying to fit blocks of production into the schedule in such a way that you remove the need for the change-over, and that requires judgment, something that Excel is famously bad at. HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
You don't have to save a lot of history/results. Just enough so at the end
you can pick the solution you want to use. No matter which way you get the answer you need to determine the acceptable criteria. I would start by figuring out your criteria for an acceptable solution. I was watching a National Geogrphic show on cable last night. they showed a modeling package to determine how animal move. to generate a simulation of the animals they fed into a computer the sizes of the bones and the muscles that pulled the bones. the computer tries every method possible to get the ideal solution. they show when the computer starts the model runs and the animal falls down then final solution after 72,000 tries the animal can move a 28 miles per hour. They showed the compuer simulations for Apes, and Horses. the optimqal solution was the one where the animal moved the quickest. The show was really about Dinosaurs. The scientist were trying to understand the speed and movements of animals that were dead for 70 million years. The point is that trying to caculate the solution isn't always possible. Trying different solutions is sometimes the best method. "LiAD" wrote: Thanks Joel, is it possible you could give me an idiots guide on how i should lay things out to run this macro. If i understand correctly I will get a lot of tables out from which I then pick the best? Thanks again "joel" wrote: Bernie: This isn't an excel problem. It is a general programming problem. There are software packages commercially available that is desgned to solve these type problems. But no software can solve this problem because the BEST solution is deterministic. The program is only as good as its inputs. A simplistic solution can be programmed in Basic to solve the problem. That is why I'm recommending the random approach. "Bernie Deitrick" wrote: LD, The best approach to this problem is to use a bit of common sense, and to work backwards from required date/times. It is way too complicated to program in a simple macro. For each of your unique products, find the earliest due date. Then work from the latest unique product backwards, like in this made up example: Say you need 40 A on April 4, 50 B by April 5, and 60 C by April 6, and all must be shipped by Noon on the due date. And then you need 30 A on April 9, 45 B by April 10, and 90 C by April 11. Start at Noon on April 6, and calculate when you need to start work on the 60 Cs to finish by then, including change-over time. If that start time is later than the B's are due, leave the time between the two unassigned, and then do the same for the 50 Bs. Again, if B's start time is after the As are due, leave that time unassigned, and do the same for the 40 As. That gives you your absolute latest start time for each product. Then, you need to decide what to do with the intermediate times. You can move up or expand production of A to include the next 30 units, in the time before A needs to start, or into the time after A is due and before B needs to start. You can move up production of the first 40 A, keeping the production the same, and expand Bs production to include the extra 45 B, and so on. But the decision on how to move things around will depend on a lot of things, especially future volumes. If the slack time in the first cycle would allow you to produce all of the A needed in a future cycle, then move that production of A up, and you will save the switch-over time associated with that batch. Basically, you are trying to fit blocks of production into the schedule in such a way that you remove the need for the change-over, and that requires judgment, something that Excel is famously bad at. HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
I'm think a more structure apporach would work better. I've been thinking about the optimum
'algortihm' that you would use - start with the first full cycle: A1 B1 A2 B2 C1 A3 C2 B3 the first "cycle" would be A1 B1. So start with B1, and work backward to A1. Then ask - can I fit in all of A2, A3, ... Ax in the slack time? If so, do pick the largest of the A2 to Ax lots that will fit, and do so. If not, can you fit in any of B2, B3, .... Bx? and so on. But if none of the whole lots fit (the answer to all of those questions are "No"), then move both A1 and B1 to the earliset times, and then go onto the next cycle A2, B2, and C1, and apply the same logic, with the exception that the first question is: can I fit in one of the B2... Bx first, since the machine is set up for B already, without affecting A? HTH, Bernie MS Excel MVP "LiAD" wrote in message ... I can do the working backwards bit. Its producing the final improved version that I am getting stuck on - apparently its not easy either! At least that re-assures me a bit. If producing the final schedule cannot be automated on excel then such is life. I will try the random approach and see what that yields in the meantime - something close might be better than the nothing I have now. Thanks "Bernie Deitrick" wrote: LD, The best approach to this problem is to use a bit of common sense, and to work backwards from required date/times. It is way too complicated to program in a simple macro. For each of your unique products, find the earliest due date. Then work from the latest unique product backwards, like in this made up example: Say you need 40 A on April 4, 50 B by April 5, and 60 C by April 6, and all must be shipped by Noon on the due date. And then you need 30 A on April 9, 45 B by April 10, and 90 C by April 11. Start at Noon on April 6, and calculate when you need to start work on the 60 Cs to finish by then, including change-over time. If that start time is later than the B's are due, leave the time between the two unassigned, and then do the same for the 50 Bs. Again, if B's start time is after the As are due, leave that time unassigned, and do the same for the 40 As. That gives you your absolute latest start time for each product. Then, you need to decide what to do with the intermediate times. You can move up or expand production of A to include the next 30 units, in the time before A needs to start, or into the time after A is due and before B needs to start. You can move up production of the first 40 A, keeping the production the same, and expand Bs production to include the extra 45 B, and so on. But the decision on how to move things around will depend on a lot of things, especially future volumes. If the slack time in the first cycle would allow you to produce all of the A needed in a future cycle, then move that production of A up, and you will save the switch-over time associated with that batch. Basically, you are trying to fit blocks of production into the schedule in such a way that you remove the need for the change-over, and that requires judgment, something that Excel is famously bad at. HTH, Bernie MS Excel MVP "LiAD" wrote in message ... Just made a quick calculation to see if this is possible. My production schedule will be 40-80 lines long. 80! = a huge number, a lot more than the 65000 lines thats in excel meaning I would not have space to generate every possible version of the table. Would there be a way of excel just outputting an optimised solution? Thanks LD "joel" wrote: I don't have any idea on the quantities or time to make each item so it is hard to get to a correct solution. A JIT approach would require finding the latest date to start an order and the time required to make the item. to try evvery combination would requir e chaing the which orderr you build first, then 2nd and thrid going though every combination or build sequencies. if you had 10 orders then try each combination Below is a table of the orders and the sequence built. the 1st row is building the order in sequence. The 2nd row is the same except building order 10 before order 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 1, 2, 3, 4, 5, 6, 7, 8, 10, 9 1, 2, 3, 4, 5, 6, 7, 9, 8, 10 1, 2, 3, 4, 5, 6, 7, 9, 10, 8 1, 2, 3, 4, 5, 6, 7, 10, 8, 9 1, 2, 3, 4, 5, 6, 7, 10, 9, 8 1, 2, 3, 4, 5, 6, 8, 7, 9, 10 1, 2, 3, 4, 5, 6, 8, 7, 10, 9 and so on the solution would be to try every combination and determine how many orders were late, the number of times you had to retool, and the number of days you shipped before the due date. "LiAD" wrote: To take ur points one at a time. Assume that - inventory is zero it all needs to be manufactured. - manufacturing can start at today() - prod is 24 hours mon-fri - in reality the product range is massive so its more or less custom orders. reoccuring orders will be dealt with another way. This tool does not need to take care of those orders. - Rather than picking a number of days between retools I would rather than excel told me when i had too. When I have to could be based on your JIT approach, perfect. Dates will decide when products will be scheduled. - no penalties or cost analysis needed just balance what we need to produce against optimising machine running time. I could start with the most urgent product. Ask excel to find all products that are the same as the urgent product, schedule them all together, then just before the next product will be late schedule a change and so it continues. Nice approach and i think that would work for this case. In terms of developing a macro to run this - at the moment thats beyond my knowledge. What do you mean by keep and history and the best results? Lots of schedules are produced and you just pick the best one? I know details are a bit vague for now, but once i see if the approach may work then I can put some fat on the bones. Thanks a lot for your help LiAD "joel" wrote: The problem can be sove with programming but you need some additional information to get started. First how much inventory do you have and does your delivery dates start immediatel or do you have some lead time? Also how many hours a day are you working and is there any penaties for working over time? The solution for the problem is to set up some criteria(s) whre you are going to retool and then compare the results to determine the best solution. The best solution would be to minimze being late and to build up as much inventory as possible. One questtion you didn't specify is if each order is a custom order or are there standard products. Each product could come in 100 different colors so you wouldn't be able to build up inventory, every order is a custom order. One soilution could be to change products every two days. Another solution would be to continue making one product until you wold become late on one of the other two products and switch so you are making a product "Just-In-time". This problem seems like a modeling solution is the best approach. In modeling your run a 1000 (or any fixed number) of attempts. Set criteria X = number of days until you retool (make number 1 to 10) and Y when you retool which product you change to (could be the same product. then keep a history of the results keeping only the best results. for i = 1 to 1000 X= int(10*rnd())+1 Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3 for Days = 1 to 100 'run your production line and vaidate if you met your criteria next Days next i 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Major help needed - Iterative Functions
Hello,
Would it also be reasonable to produce more items than ordered during periods of low order volume (based on historical seasonal information about past orders and taking in account storage space, budget, risk of getting out of date with regards to technical features or fashion, etc.)? Is the task a homework exercise (aiming to get students to read Donald Knuth's Art of Computer Programming, for example) or a real production challenge? Which budget (time and money) could be invested for an acceptable solution? Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Major help needed | Excel Discussion (Misc queries) | |||
Major help needed!!! | Excel Programming | |||
Major Help Needed Asap | Excel Worksheet Functions | |||
Range - Offset (Major help needed) | Excel Programming | |||
Major help needed | Excel Discussion (Misc queries) |