Possible for Excel?

[Supurbub]

Hello-

I am fairly strong on Excel formulas and have very lightly dabbled with Power Query/Pivot. 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 (Umbrellas) that we cut parts out of. 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.

The image below is contains some made up umbrella and part #s. We have over 75 parts and 20 different part numbers but I would imagine the solution would be the same conceptually.

My demand would look something like this:

The goal is to populate the ? with the minimum amount of umbrellas needed to satisfy the monthly quota. Leftovers can be used the next month (in other words, if you build 8 of 234-4 in May, you can use the last one to support the 1 demand in June)
I wouldn't be opposed to using PowerQuery, formulas, etc. but my work does disable Macros. My ability with them is very weak so I may need extra help if that is the best route (Sorry and thank you in advance!)

Thank you for any help that you can give!

 

[felixstraube]

Here is the updated file and I tested it for every month:

Umbrella 2 - TestedForAllMonth.zip

Here the results only:

# Required by Month	May	June	July	August	September	October	November	December
234-1	5	11	2	7	3	6	10	7
234-2	3	1	6	3	5	5	2	5
234-4	7	1	4	6	9	4	2	3
555-1	9	-	5	3	7	3	4	1
446-3	2	6	4	5	9	7	4	2
777-8	3	4	4	7	3	6	5	7
Umbrella Demand	May	June	July	August	September	October	November	December
ABC-1	-	1	1	1	1	-	2	3
ABC-2	2	5	-	3	1	-	4	2
ABC-3	-	-	-	-	-	-	-	-
ABC-4	2	-	1	1	2	1	-	-
ABC-5	-	1	-	1	-	-	1	1
ABC-6	3	-	4	-	3	6	-	-

For September there is another solution just as good as the previous one:

September
-
-
-
2
-
5

Let me know what you think