Hours Needed per Product Per Month - Solver

[talmrubin]

Hi. I need some help on this solver problem. I am trying to understand how many hours I need to run my machines to meet my demand per month per run mode.

Below are the TONS per month required along with the product name:

Sales Part Number	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
7010	200	185	143	93	77	4	20	29	-	108	82	194
7022	-	-	-	33	-	12	-	-	-	-	-	-
7023	173	65	173	216	216	43	206	206	170	195	195	65
7410	431	249	385	295	249	1,133	453	408	453	274	387	453
7420	272	91	91	181	317	159	330	272	317	340	272	249
7840	53	123	65	89	149	77	43	92	48	93	80	49
7941	1,765	1,946	1,853	1,822	1,759	1,455	1,326	1,603	1,252	1,399	1,187	1,324
7988	407	476	452	452	163	116	183	91	319	295	44	109

Below are my different run modes listed 1-6 (on top) and my Tons per Hour for each run mode. Please note that some run modes produce more than one product:

Product	1	2	3	4	5	6	Ratio
7988		1.25		1.25	1.25		100%
7989			1.25				100%
7410					9		50%
7840					9		50%
7010					6		63%
7023					3.5		37%
7022					7.5		68%
7023					3.5		32%
7941	13	9.5	9.5				100%
7420				9.5			100%

How do I set up solver to solve for hours needed per product per month for each run mode?

 

[J.Ty.]

This is much simpler than it seems to be.

Products 740, 7840, 7010, 7023, 7022 can be produced using process 5 and no one else. So you must run it to get all of them, and in each case for maximum over all demand/production quotients for those products. Next, you must run process 4 to produce all required product 7420. Both processes produce some amount of 7988. Finally, you must satisfy demand for the remaining quantities of 7988 and 7941.

So, in fact, you have no freedom concerning 7 products, because each of them is made in one process, only. Yu have some freedom for the remaining two products. This part has many solutions and you should define how to measure their quality in order to choose an optimal one.


J.Ty.

 

[talmrubin]

This is much simpler than it seems to be.

Products 740, 7840, 7010, 7023, 7022 can be produced using process 5 and no one else. So you must run it to get all of them, and in each case for maximum over all demand/production quotients for those products. Next, you must run process 4 to produce all required product 7420. Both processes produce some amount of 7988. Finally, you must satisfy demand for the remaining quantities of 7988 and 7941.

So, in fact, you have no freedom concerning 7 products, because each of them is made in one process, only. Yu have some freedom for the remaining two products. This part has many solutions and you should define how to measure their quality in order to choose an optimal one.


J.Ty.

Thanks but this is a small subset of data and i was trying to figure out a model to expand to all products and run modes

 

[J.Ty.]

I see.

However, no matter how large and complex the whole problem is, you must give a criterion of quality of solutions, to choose the best one.

J.Ty.