Hi,
I have data on hours worked and the number of units produced across different factories.
I would like to know if say I only had 300K of hours and wanted to decided how to allocate/split those hours of production accross the factories (am using two in the sample data) in order to maximise unit production, how could I do that in Excel or get to the optimal mathematical solution?
In the attachment I am only showing the data for two factories as an example. But it will be clear that the relationship between hours worked and production is non linear so that at low levels of hours worked the incremental units produced is higher than at higher levels of hours worked. This is because there issues around staff getting tired and other constraints to the rate at which proudction increases falls as the number of hours increases!
Also the hour worked scale is different in the two factories as some factories have more staff than others.
In terms of solutions I am open - either VBA, non VBA or a combination.
Looking forward to peoples' thoughts and suggestions.
Thanks,
Nick