Can Solver Save My Sanity?

[mickyd67]

Hi all,

I am working on a process that is very painful - it's been done manually in the past and I think there must be a way Excel can help. I Google searched last night and the Solver add-in on Excel was suggested, which I've never heard of before.

Here's my challenge:

I have 28 customers

1. Each have a certain amount of stock to sell during the year
2. The stock is made across the year, in monthly buckets - some months have zero production
3. The stock is made up of boxes of 'x' quantity - in the example I'm posting it's 538
4. Some customers can accept stock every month of the year
5. Some customers can only accept stock in certain months (i.e. May / November - or May / July / Sept / Nov / Jan / March)
6. Where a customer can only accept stock on a certain month but there's no production in that month I can carry over stock from the previous month - but I can't carry stock over for 2 months
7. The aim is to have a 'smooth' supply and not to supply everything in the first month and nothing for the rest of the year (i.e. if the customer needs 100,000 and there's 10 production months each month they'd get 10k)
8. Ideally each month would balance out to zero - where stock has to be carried over due to stock acceptance constraints that won't happen, in those circumstances the 2 month period should balance out to close to zero
9. At the end of the year I should be left with no more than 538 units (1 box)

I have this across a number of products - all with different production, demand & box quantities. So my goal would be to have something I can plug the numbers into and Solver would provide a solution

The attached image shows how the data is being managed - the yellow cells are the months where a customer can accept stock.

Column B shows the sales target
Row 2 shows the monthly production numbers - note Nov & Mar have no production
Row 32 shows how much supply I have left to send to customers in that month
Row 33 shows the remaining balance of the month - this should only drop below zero if I am carrying supply over from the previous month & it balances out
Row 34 is cumulative balance, at the end of the year this should be less than 538 in this instance

Right now I'm having to populate this info manually for about 50 products, and I'm losing the will to live. Please help

 

[Kaper]

I doubt a bit if you don't go over Solver limits.
Basically there is about 30 customers in 12 months so at first glance 300+ cells to be set.
Upper limit for Solver (depending a bit on particular problem) is 200- cells.
On the other hand, you have some customers, who are accepting only twice a year or quarterly.
You could try to downsize the Solver problem and for these costomers set constant amount sent just as half or a quarter of their total each month they are accepting it.
Also customers with no order (customer 26) shall be moved out of main problem do be solved.
So may be we are within limits (if not there is also a third party add-in called OpenSolver - on some forums it is highly recommended for larger problems, but I have no personal experience with it)

Some comments:

* As for "box" - is it used during months/by customers? Lookong at rows 2 and (filled by you manually row 3) seems not. So point 3 is not clear.
* Point 9 on your list - you shall take care about it while planning the production (row2) and acquiring orders (column B). (Or in other words - I assume (may be I'm wrong), that when table is ready, column O contains only zeros.
* point 8 shall probably be expressed that way that C33 has to be less or equal D32 and analogical relation for next columns.
* I'd take point 7 (minimum of sum of standard deviations calculated for each row (excluding yellow cells) ) as the aim for the solver
* all other restrictions from points 1-9 shall be used to set constraints for solver.
* I'd probably not use constrain that the numbers in cells for solver have to be whole numbers. I'd make a vba code to round the values to whole numbers after solver dit it's job. Possible discrepancies of say 1-2 pieces could be easily corrected manually
*To make formulas much easier (as you know, excel don't handle cell color in standard formulas) it is worth considering to write in some other cells (lets say) C43:N60 0's in all cells which in original table are gray and 1's in all cells which are yellow in main table (this could be also easily done by a short VBA code)
This could be as simple as this one:

Sub Prepare_1_0_table_below()
Dim i As Long, j As Long
For i = 3 To 30
  For j = 3 To 14
    Cells(i + 40, j) = IIf(Cells(i, j).Interior.ColorIndex = 6, 1, 0)
  Next j
Next i
End Sub

* to make "solvers life" easier (also for a start point for manual filling) i'd write a simple VBA code, which spreads equally data from column B into yellow cells from columns C:N. A good starting leads to quicker finding of a solution by solver. This could be as simple as the one below (the one mentioned above has to be executed before):

Sub Initial_values_spreaded()
Dim i As Long, j As Long, partialvalue As Double
For i = 3 To 30
  If Cells(i, 2) > 0 Then
    partialvalue = Cells(i, 2) / WorksheetFunction.Sum(Cells(i + 40, 3).Resize(1, 12))
    For j = 3 To 14
      If Cells(i + 40, j) = 1 Then Cells(i, j) = partialvalue
    Next j
  End If
Next i
End Sub

Unfortunately - this is mrexcel forum so no attachments are allowed. but if you can share a link to sample worksheet we could proceed a bit.

 

[mickyd67]

@Kaper - may thanks for the above message. I made a bit of an error in my explanation & data. In this instance the box quantity is 500, not 538 - so that didn't tie in with the numbers I manually entered.

I can't edit the original message so I've just copied, pasted and corrected the below.

Let me find an example worksheet to send. Really appreciate this!

= = =

Hi all,

I am working on a process that is very painful - it's been done manually in the past and I think there must be a way Excel can help. I Google searched last night and the Solver add-in on Excel was suggested, which I've never heard of before.

Here's my challenge:

I have 28 customers

1. Each have a certain amount of stock to sell during the year
2. The stock is made across the year, in monthly buckets - some months have zero production
3. The stock is made up of boxes of 'x' quantity - in the example I'm posting it's 500
4. Some customers can accept stock every month of the year
5. Some customers can only accept stock in certain months (i.e. May / November - or May / July / Sept / Nov / Jan / March)
6. Where a customer can only accept stock on a certain month but there's no production in that month I can carry over stock from the previous month - but I can't carry stock over for 2 months
7. The aim is to have a 'smooth' supply and not to supply everything in the first month and nothing for the rest of the year (i.e. if the customer needs 100,000 and there's 10 production months each month they'd get 10k)
8. Ideally each month would balance out to zero - where stock has to be carried over due to stock acceptance constraints that won't happen, in those circumstances the 2 month period should balance out to close to zero
9. At the end of the year I should be left with no more than 500 units (1 box)

I have this across a number of products - all with different production, demand & box quantities. So my goal would be to have something I can plug the numbers into and Solver would provide a solution

The attached image shows how the data is being managed - the yellow cells are the months where a customer can accept stock.

Column B shows the sales target
Row 2 shows the monthly production numbers - note Nov & Mar have no production
Row 32 shows how much supply I have left to send to customers in that month
Row 33 shows the remaining balance of the month - this should only drop below zero if I am carrying supply over from the previous month & it balances out
Row 34 is cumulative balance, at the end of the year this should be less than 500 in this instance

Right now I'm having to populate this info manually for about 50 products, and I'm losing the will to live. Please help