Excel Solver Find Solution but Didnt Change Anything
Answer
Hi. Most likely it's a logic error in your model.
Solver cannot find global optimization, and hence the solution will really depend on the starting values (using GRG)
However, how your model is set up is very questionable because your solutions are all integers. You have no constraints that limit the changing variables to integers!
Also, you are most likely using a discontinuous function in the counting of how many trucks you are using per week.
In addition, most likely S18 is using a Max function, which is also a discontinuous function that does not work well in Solver.
Once Solver sees that changing some cells without a change in a Max function, the program will usually give up quickly, which seems to be happening.
> "G18 <= 225"
Most likely using a Max() function on the "Left" column.
Again, this is generally not a good idea.
I don't know what you have, but I would remove this constraint, and add:
G6:G15 <= 225
etc...
For week 3, you are taking a random real number 390 (that has a fractional part)
and somehow rounding up the fixed cost to 300 (multiples of 150). The equation for arriving at a used value of 220 might also be another issue.
So, from what littler I see, I think you need to change the logic of the model.
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Answer
Hi. I dragged a copy over to that location. I "think" you should have it.
A little background. I grouped the 3 clinics together, with clinic 3 on top, then clinic 2, then clinic 1.
One of the reasons your program stopped quickly is that, for example the first line, is that as deliveries changed from 101, 123, 167, ...etc, the cost of shipping remained that same. Solver did not know you used a Ceiling function. The cost of 250 was not changing. If delivery of 101 cost 250, and 201 cost 500, then what is the middle value?
Also note for example, clinic 3. You needed to ship 820. This was going to require 5 trucks, who's cost was going to remain the same no matter which week you used. Also, a one unit increase in a storage location caused a 0.10 increase in costs, but when another location decreased by 1, the total costs remained the same (its a fixed storage cost)
For week1, we had 3 different clinics that could take 0,1,2,or3 trucks. There were 27 different combinations on week 1 alone. Then we looked at the total. This was causing Solver to actually run thru millions of combinations. That's why my first run was never going to finish.
Finding a fast solution is more of an art than a science. What you want to do is have an "integer" number of trucks to each location, and have Solver figure out the remaining limits. Also, by grouping them together, it made it easier to only use 5 constraints. Each one pointing to a large group of cells.
> ... your tentative suggestion about 200*Tr1-Trial>=0, but it looks like you're assuming there would be 200 delivered in every truckload
What I mean here is that you want Solver to keep track of the constraints, not us, and keep the number of constraints to a minimum. If Solver assigns 2 trucks, you have a limit on how many can be delivered... which is linear... 200*(# Trucks)
We can enter that Delivery & Trucks are integers with 1 entry.
So, we just need 2 more constraints.
Left >= 0
and
Delivery <= 200 * Trucks.
However, we can add both constraints with 1 entry if we rearrange the equation:
Left >= 0
200*Trucks - Trial (delivery) >= 0 '<- this is the 'Limit' column
Also, notice that we need units in groups of 10.
We are searching every integer 1,2,...127, 163,164, 580 ..etc
To drastically cut down on the unnecessary integer searches, we use integers, but just multiply by 10 and use this in our calculations. So, for example, when Solver picks 10 for delivery, we use 10*10 =100 as the actual size.
So, by now making it linear, we can use Simplex, and arrive at a solution in under 2 seconds.
Anyway, I hope this helps.
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Excel Solver Find Solution but Didnt Change Anything
Source: https://answers.microsoft.com/en-us/msoffice/forum/all/why-doesnt-solver-work-for-me/e0efd02d-c032-4769-b4a4-d1fefe1ff3e7