Excel: Logistics cost optimization with constrained destination points?

[swapnil_bhrdwj]

We are trying to run a model, which will help us minimize the overall logistics cost. We have different type of vehicles available in the fleet, each with a different capacity and different tariff.

As a result of this solution, we want to reach an outcome, where not only the logistics cost incurred by me is the lowest, but at the same time, I want to ensure that most of number of vehicles used by me are touching two drop points. All drop points available can be assumed to be equidistant for the sake of simplicity. The way we have modeled this problem is not helping because solver is throwing an error that we are trying to solve a NSP (Non Smooth Problem)

Objective fn: sum(derived_cost,criteria(Combination>0))

VehicleConfiguration.png
Optimization.png

Will be grateful if someone can help.

[MrShorty]

In Excel, these kinds of problems are usually solved using Excel's built in Solver utility. Frontline, the makers of Excel's Solver, have several examples on their page for these kinds of logistics problems. https://www.solver.com/optimization-...stics-examples I would start by studying their examples and then adapt one of them to your specific problem.

[swapnil_bhrdwj]

We have already taken cues from these set of solutions. We are getting stuck at the point, where we are trying to introduce a restriction that as many vehicles should touch multiple touch points. When we are trying to model it, we are making the entire model a Non-smooth Model.

In order to understand the number of drop points a vehicle is travelling, I am using sumproduct((C4:H4)<>0*1). The not equal to sign <> is making the model non smooth.

Also, I can't use functions such as IF, SUMIF, COUNT, COUNTIF, because all of them will make the function non smooth

[MrShorty]

I am not very skilled at linear programming problems like this, so I am not sure what specific recommendations to make. Typically when I approach a problem like this, I start with something that works for the unconstrained model (without the need to hit two drop points), then look for ways to incorporate the additional constraint(s) that maintain the necessary smooth, linear nature of the problem. As you correctly note, this usually means avoiding many worksheet functions that tend to be discrete, non-smooth functions. Based only on the screenshots given, without any knowledge of a model that works for the unconstrained scenario, I have no specific recommendations to make. My general recommendation is to reconsider your use of the discrete function and see if there is a different way to incorporate the additional constraint without using a discrete function.

What Solver algorithm are you using? Some -- like the evolutionary algorithm -- are less bothered by discrete functions. If you have not tried those, it may be worth trying to see if that helps.

[Alf]

Perhaps any of these links could be of interest? So instead of distance between points you could use costs instead.

https://www.excel-easy.com/examples/...h-problem.html

https://www.youtube.com/watch?v=-E3rSoClgMI

Alf

[Alf]

And finally this link, a freebie that works with Google earth for transport planning.

https://people.bath.ac.uk/ge277/vrp-spreadsheet-solver/

you can download the solver file and the manual of the how and the why of the solver model.

Alf