Hi all,
I have to develope an Excel spreadsheet to answer these two questions:
Now let me explain you briefly the problem:
There is a factory with 6 machines that make more than 30 different products. Customers place their order saying what they want (product and qty) and when they want it.
Restrictions:
- Work Calendar
- Not every product can be made in every machine
- Some products production vary depending on which machine they are.
- Minimize machine changes
Preparing and sorting data
With the customers demand I have created a pivot table were columns are total products (A,B,C,...) demand and rows are the due date when it has to be delivered:
<tbody>
</tbody>
I have also created a tab with the work calendar where I say if it is a labour day or not and if it is, how many cycles per day each machine runs (we are in an overloaded scenario so if it is a labour day each machine runs at its full capacity which means 3 shifts x 10 cycles per shift = 30 cycles per day)
I have created also another tab where I say the relationship among product - machine - qty per cycle
<tbody>
</tbody>
For example: Product A can be made in machines 1,2,3 and we get 12 products A in one cycle.
At this point I think I have all information I need but I do not know how to continue.
Capacity depends on machine configuration but configuration should be changed as less as possible but as necessary it takes to meet the demand.
Should I use solver tool for this? How should I use it? Because usually the demand we have is for the following 30 days but there is not a unique machine configuration that can last from day 1 to day 30 unchanged, it has to be changed maybe 3 times each per week... Can I get a solution like "use this configuration from day 1 to day 7, use this cofiguration from day 8 to day 14, ..." and so on?
Are there more options for this? or is there a easiest approach to this problem?
Any help would be really appreciated
Thanks
I have to develope an Excel spreadsheet to answer these two questions:
- Can I deliver everything on time?
- Which should be the machine configuration to meet the demand?
Now let me explain you briefly the problem:
There is a factory with 6 machines that make more than 30 different products. Customers place their order saying what they want (product and qty) and when they want it.
Restrictions:
- Work Calendar
- Not every product can be made in every machine
- Some products production vary depending on which machine they are.
- Minimize machine changes
-------------------------------- THIS IS HOW I STARTED --------------------------------
Preparing and sorting data
With the customers demand I have created a pivot table were columns are total products (A,B,C,...) demand and rows are the due date when it has to be delivered:
A | B | C | D | E | |
01/01/2016 | 5 | 10 | 7 | ||
02/01/2016 | 20 | 3 | |||
03/01/2016 | 5 | 1 | 2 | ||
04/01/2016 | 10 | 12 | |||
05/01/2016 | 10 | 3 |
<tbody>
</tbody>
I have also created a tab with the work calendar where I say if it is a labour day or not and if it is, how many cycles per day each machine runs (we are in an overloaded scenario so if it is a labour day each machine runs at its full capacity which means 3 shifts x 10 cycles per shift = 30 cycles per day)
I have created also another tab where I say the relationship among product - machine - qty per cycle
M1 | M2 | M3 | M4 | M5 | M6 | |
A | 12 | 12 | 12 | |||
B | 12 | 12 | ||||
C | 12 | 12 | ||||
D | 16 | 16 | 10 | |||
E | 10 | 10 |
<tbody>
</tbody>
For example: Product A can be made in machines 1,2,3 and we get 12 products A in one cycle.
At this point I think I have all information I need but I do not know how to continue.
Capacity depends on machine configuration but configuration should be changed as less as possible but as necessary it takes to meet the demand.
Should I use solver tool for this? How should I use it? Because usually the demand we have is for the following 30 days but there is not a unique machine configuration that can last from day 1 to day 30 unchanged, it has to be changed maybe 3 times each per week... Can I get a solution like "use this configuration from day 1 to day 7, use this cofiguration from day 8 to day 14, ..." and so on?
Are there more options for this? or is there a easiest approach to this problem?
Any help would be really appreciated
Thanks