PDA

View Full Version : Mathematical Excel Optimisation Challenge (production of units)



Nick_London
03-14-2014, 02:57 PM
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

SamT
03-14-2014, 06:51 PM
I know what you want. you want the green cells on this attachment. Unfortunately I don't know exaclty how to do it with Excel. I think you can use "Solver," But I'm not familiar with it.

I added the Blue helper columns to better visualize the sample.

I generated this solution by finding the Row (in yellow) that the total hours = 300,000, then ran some scenarios in my head. First I moved up a row (in Green) in the Factory B hours, then moved down the same hours in Factory A hours. Then compared the difference in production of moving Factory C hours and Factory A hours. It took about fifteen minutes after I figured out what I was doing.

The helper columns let me recognize that subtracting hours from Factory B, (referencing the yellow row) and adding therm to Factory A would produce the greatest benefit.

Nick_London
03-16-2014, 02:53 PM
Thanks Sam for your help.

I see how you got to the solution but when thinking about it, I had something different in mind in in order to getting to the most optimal solution. Let me explain. Currently you have selected the best combination of hours to the nearest 300 across factories that maximises output. But I was thinking along the lines of needing to perform the units produced per hour calculation that you have done across all points along the response curve including infering points where data does not exist. So for example for Factory A - one can observe from the raw data that 16,811 hours creates 99 units and 18104 creates 105. But what about 17,000 hours? If we had the calculation for every incremental 100 hours along the curve then it would be easier to say for total of 300,000 hours exactly, in which factory each 100 incremental hours should go until you get to 300,000 to maximise production. Also in line with this, factor A has data for up to 129,314 hours but it would be good to estimate the units that would be produced for say 200,000 hours based on projecting where the curve would be.


Does that make sense or am I suggesting something that mathematically is impossible to do? Else your approach is better than nothing but still need to think about a way to automate it! I've used solver before but not sure how to do it but think you would need to link all of the data across the factories into one formula to calculate production units for solver to maximise.

Thanks,

Nick

SamT
03-16-2014, 05:36 PM
Yes it makes sense and it is possible to do. I wish I knew how.

The only part I disagree with is refining the curve down to hours. At that point you are reduced to telling Bob to go home at 13:20 and Mary to leave at 14:43.

All factories experience an attendance curve, and depending on the granularity you need, you can use a simple daily average or you can go as far as Days of the week with holidays average per shift.

At first I would use a simple hours per factory daily average increment. If Factory A averages 12345 hrs/Day, then that curve should be incremented at 12345 hrs.

Imagine the added complexity of using curves for Factory A that use 1059 hrs/ monday, 1150 hrs/ friday and 1456 hrs/ tue - thur. Now throw in accounting for every individual day of the year with all holidays mixed in. Remember the 90/10 rule: If it costs $X to achieve 90% of the ideal, it will cost another $X to achieve 90% of the remaining 10%, and yet another $X to get 90% of the remaining 1.1%, etc. Zeno has nothing on percentages of perfection :D

Another thing to consider is that setup for each new line is a fixed cost that is spread over the entire run.

I think it would be valuable to increment the hourly data points by shift, because each shift will produce different numbers of the same product for the same hours, and it only triples the number of fixed data points. There is some inherent error since the setup time will be a higher percentage of a low hours shift compared to a large hours shift. This error will, however, be averaged across the product run.

A Factory where Day shift is 500 hrs, Swing - 300 hrs, and graveyard - 200 hrs


Hrs

Shift


500
D


800
S


1000
G


1500
D


1800
S



2000
G


2500
D


2800
S


3000
G




Looking at that I see that you need three tables, that start at the beginning of each shift to handle the Run starting on any shift. I guess it would be best to start with just daily increments until you get the bugs worked out. I forgot that the math for every product starts at 0 hours.

Remember,this is only a model and you will be making management decisions about the next product's start time as the current product nears it's End of Run because the model <> real life.

SamT
03-16-2014, 07:17 PM
More Thoughts

The absolute maximum hours you can get is the Number of work stations * 24/day.

If you are not already using three shifts, studies have been done that it is not cost efficient to work more that a few 60 hr weeks and gets downright dangerous to work more than a few 72 hr weeks.

Assume that Factory A has 1,293 work stations, a single shift at Factory A totals 51,720 hours in a 40hr wk. (from your original attachment)

At 7 ea 12 hr shifts, your production time is 108,612 hrs.

You can't hire another crew to work a different shift because they need to be trained, which always reduces production from normal.

If you are already using three shifts at 1,293 positions at 40 hr/wks you have a base of 155,160 hrs/wk, but can only increase to 56 hr shifts / weeks / station (24/7) for a maximum production time of 217,224 hrs.

Looking at your actual manpower, what is the maximum hours per week that one person can work. For 1 and 2 shifts a day that number is 84, (7/12s.) for a three shift factory, it is 56, (7/8s.) O maybe 7/9s and 7/8s and 7/7s.

Finally, I came up with this, (See Attachment,) chart. I used the Helper Column from Factory A in my first attachment, and reduced the production after 8 hours per day and after 5 days in a row. As you can see I stopped at a 16 hr day and think that you should stop at 28 days in a row. Also depending on the actual numbers I would not use any combo of hrs/days straight that turns up Red.

IMO, this is the kind of information you need to maximize profit per man hour.

Nick_London
03-21-2014, 10:10 AM
Hello again,

Thanks very much Sam for your additional thought and work. I guess I was saying that the curve would have to be refined to hour increments if I wanted to theoretically find the optimal combination to maximize output for any given hours as right now I am simply doing a theoretical exercise/considering the concept as opposed to factoring all the realism and constrains that you highlighted in your second post for a real world implementation.

Would be interested to know if anyone else can suggest how to do the actual mathematical optimization? Also would you go about scaling the data in Excel across the three factories so we could put the three curves on the same graph. This would then make it possible to show that for day 10,000 hours, each factory would deliver X units of production. In fact, This might even could be a necessary prerequisite in order to solve the optimisation - I don't know.

I have been reading online on about how to do this and it's suggesting it would first be necessary to find the mathematical equation that best explains the relationship in each factory between hours and units (X &Y). Then the equation can be used to estimate production of units of any given number of hours per factory. Given the non linear relationship and diminishing returns (where the curve becomes flat at higher levels of hours), It looks like estimating an "S -curve" would best represents the relationship, but again I am just guessing.

Nick