Hello-
I am fairly strong on Excel formulas and have very lightly dabbled with Power Query/Pivot and solver. My work has been making a manual schedule for quite a while and I am trying to get it automated. Our ERP does not have the capability so my best bet is to try to get this to work in Excel... unless there is another suggestion?
To oversimplify- we have giant sheets of material that we cut parts out of. The sheets are all the same material and make a variety of different parts. These parts go to different companies. Some umbrellas may make 10 of the same part or may make 7 different parts or any combination of.
Our demand for parts fluctuates by month based on customer needs. The goal is to figure out the most efficient way (least # of umbrellas used) to satisfy customer demand.
We have over 75 parts and 20 different umbrellas but I would imagine the solution would be the same conceptually.
The file below shows the umbrellas and what parts/quantities of those parts that they create.
The second table shows our quantity by part required to meet customer needs.
The blank table with the ? is what I need to populate as efficiently as possible.
Efficiency is defined as least umbrellas. By that I mean we may end up with excess parts but the least amount of sheets of material (umbrellas) possible is what reduces our cost the most. Also, parts can carry over to reduce the next months demand.
Thank you for any help you can give!
FYI- this is a repost from yesterday as I just got the XL2BB to work.
I am fairly strong on Excel formulas and have very lightly dabbled with Power Query/Pivot and solver. My work has been making a manual schedule for quite a while and I am trying to get it automated. Our ERP does not have the capability so my best bet is to try to get this to work in Excel... unless there is another suggestion?
To oversimplify- we have giant sheets of material that we cut parts out of. The sheets are all the same material and make a variety of different parts. These parts go to different companies. Some umbrellas may make 10 of the same part or may make 7 different parts or any combination of.
Our demand for parts fluctuates by month based on customer needs. The goal is to figure out the most efficient way (least # of umbrellas used) to satisfy customer demand.
We have over 75 parts and 20 different umbrellas but I would imagine the solution would be the same conceptually.
The file below shows the umbrellas and what parts/quantities of those parts that they create.
The second table shows our quantity by part required to meet customer needs.
The blank table with the ? is what I need to populate as efficiently as possible.
Efficiency is defined as least umbrellas. By that I mean we may end up with excess parts but the least amount of sheets of material (umbrellas) possible is what reduces our cost the most. Also, parts can carry over to reduce the next months demand.
Thank you for any help you can give!
| Umbrella Demand.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Umbrella | Creates: | Quantity | # Required by Month | May | June | July | August | September | October | November | December | ||||
| 2 | ABC-1 | 234-1 | 1 | 234-1 | 5 | 11 | 2 | 7 | 3 | 6 | 10 | 7 | ||||
| 3 | 234-2 | 3 | 234-2 | 3 | 1 | 6 | 3 | 5 | 5 | 2 | 5 | |||||
| 4 | 234-4 | 1 | 234-4 | 7 | 1 | 4 | 6 | 9 | 4 | 2 | 3 | |||||
| 5 | ABC-2 | 555-1 | 3 | 555-1 | 9 | 0 | 5 | 3 | 7 | 3 | 4 | 1 | ||||
| 6 | 234-1 | 2 | 446-3 | 2 | 6 | 4 | 5 | 9 | 7 | 4 | 2 | |||||
| 7 | 446-3 | 2 | 777-8 | 3 | 4 | 4 | 7 | 3 | 6 | 5 | 7 | |||||
| 8 | ABC-3 | 555-1 | 7 | |||||||||||||
| 9 | ABC-4 | 234-4 | 5 | |||||||||||||
| 10 | 446-3 | 1 | Umbrella Demand | May | June | July | August | September | October | November | December | |||||
| 11 | ABC-5 | 777-8 | 9 | ABC-1 | ? | |||||||||||
| 12 | ABC-6 | 777-8 | 1 | ABC-2 | ? | |||||||||||
| 13 | 555-1 | 2 | ABC-3 | ? | ||||||||||||
| 14 | 234-2 | 1 | ABC-4 | ? | ||||||||||||
| 15 | 234-1 | 1 | ABC-5 | ? | ||||||||||||
| 16 | 446-3 | 1 | ABC-6 | ? | ||||||||||||
Umbrella Part #s | ||||||||||||||||
FYI- this is a repost from yesterday as I just got the XL2BB to work.
