Production optimization in Excel

[eibsen]

Hi,

I have an optimization problem, that I'm trying to solve in Excel, but it keeps getting too complex (too many columns/iterations), and I hope someone in the forum may be able to give some pointers.

On a given day, at a given timeslot, I have 6 production lines, each with different production capacity, and being able to produce either the same or 6 different products. Production is for direct consumption (=real time) - there is no warehouse.
Due to different constraints, production for all 6 lines will now be limited to 60%.
It is easy to predict the loss in sale - each time demand/sales has surpassed 60% of the capacity of the given line. However - if we were to optimize production lines, and let multiple lines produce the same product, and hence only the least demanded product would stop being produced - how can we limit the loss?

Take this simple example with 3 lines, with a capacity of 100, 50 and 25 respectively, now being limited to 60, 30 and 15 respectively:

[TABLE="width: 700"]
<tbody>[TR]
[TD]Example[/TD]
[TD]Line1Sales[/TD]
[TD]Line2Sales[/TD]
[TD]Line3Sales[/TD]
[TD]SimpleLoss[/TD]
[TD]Optimized loss[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]90[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]40[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]90[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]


in example 1, we would let line 2 produce the same product as line 1 to optimize. In example 2 we would let line 2 product the same product as line 1, and move product2 to line 3

is there a simple way of solving this type of iterative problem solving? (it doesn't always cascade down from line 1)

I appreciate any help you can provide.

Thanks,
Erik

 

[baitmaster]

welcome to the board

short answer, yes this is possible

only problem is, I'm not sure I fully understand your problem

My understanding suggests the 60% reduction is irrelevant and can be ignored. I think it boils down to:
- we have production capacities of 60, 30 and 15
- we have demand of 90, 20 and 25
- we can't make everything so how can we optimise production so that it most closely matches sales?
> we would choose 60 and 30 since this sums to 90 and matches line 1 sales
> we would then choose line 3 sales (being next most popular) and make 15 of these

So the algorithm becomes, to minimise the differences between [most popular demand items] and [possible combinations from production lines]

The improvement has become the change from pre-optimisation:
= (90-60) + (20-15) = 35 loss against (25-30) = 5 spare capacity on production line 2
versus post-optimisation:
= (90-60-30) + (25-15) + 20 = 30 loss and no spare capacity anywhere

Get the algorithm right, and then the iteration is easily possible

 

[eibsen]

welcome to the board

Thank you, and thanks for taking a stab at this. I will try to address your questions below.

My understanding suggests the 60% reduction is irrelevant and can be ignored.

Correct, but the optimization should be to reduce waste between a 60% reduction given the current production plan (prod1 on line1, prod2 on line2, prod3 on line 3) vs whatever combination the solution suggests.

- we have demand of 90, 20 and 25

Yes, that particular time slot, but it obviously changes. I actually have 1700 of these, so looking for an effective solution to solving so many instances of it

> we would choose 60 and 30 since this sums to 90 and matches line 1 sales

yes, in this instance it fits perfectly

> we would then choose line 3 sales (being next most popular) and make 15 of these

yes, but popularity does not have to be a criteria. Choosing the 20 or 25 would both be ok solutions - I am only looking to minimize wasted demand.

= (90-60) + (20-15) = 35 loss against (25-30) = 5 spare capacity on production line 2

Not sure I follow here. The pre-optimization is as it is stated in the table - and if we planned production the same as before the loss would be 40 (prod1 on line1, prod2 on line 2 and prod3 on line3 each with a 60% reduction in capacity). If we end up with spare capacity it doesn't matter as such, but assume it would be a low spare capacity would be equal to low waste (lost demand)

versus post-optimisation:
= (90-60-30) + (25-15) + 20 = 30 loss and no spare capacity anywhere

Agree for example 1

Does that make it clearer, or did I just confuse more?