Can Solver Save My Sanity?

M

mickyd67

Board Regular
Joined
Jul 13, 2011
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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 🙏



Solver Example.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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:

VBA Code: 
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):
VBA Code: 
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.
 
Upvote 0
@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 🙏

Solver Example.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
24 month account
Replies
5
Views
265
ExcelSwede
E
G
  • Solved
Sum a row of values and redistribute the data in groups of 10.
Replies
3
Views
319
Peter_SSs
Peter_SSs
F
  • Solved
Allocation of cost to value of savings.
Replies
4
Views
188
fayo2009
F
P
  • Question
How do I make these two pivot charts from this specific table? I can't figure it out, and this is a very common table format.
Replies
1
Views
304
awoohaw
awoohaw
M
  • Question
Dashboard
Replies
4
Views
807
awoohaw
awoohaw

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top