Production optimization in Excel

eibsen

New Member
Joined
Oct 7, 2019
Messages
2
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:

ExampleLine1SalesLine2SalesLine3SalesSimpleLossOptimized loss
19020254030
29020103015

<tbody>
</tbody>


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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,037
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

New Member
Joined
Oct 7, 2019
Messages
2
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,000
Messages
5,465,938
Members
406,453
Latest member
MarkB5432

This Week's Hot Topics

Top