It is also generated a specially formatted table for operations to upload directly into their Transportation Management System (TMS). The final tool ran the model, took the results, and spit out a nicely formatted word report for management. Secondly, this was a model we needed to run frequently, and as such, it was easier for me to do it in Python so it was more automated and less hands on. My example only shows 25, each with their own custom constraints, whereas my real world project had well over 200. I did not want to pay for Solver and I enjoyed the challenge of getting my solution built in Python.
Why not just use the Excel Solver?įirst, I had too many constraints for the free version of Solver.
The goal was to optimally select which lanes to run and at what volumes to maximize equipment utilization and financial impact. We also had a limit on how many miles we could cover in a week due to a finite amount of trucks. We had other constraints that required a minimum volume per week on certain lanes to meet certain demands even if they were not a net positive on the transportation margin (see the ‘ImpactPer’ column in the data). I had already determined routes and an estimated margin impact for each of the ‘routes’ or lanes. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.The logistics example (Beer Distribution Problem) provided by the developers is a great example, however, I wanted to approach it differently.
It is not necessary to use trial and error.
For example, the path SBET has a total distance of 16. With this formulation, it becomes easy to analyze any trial solution.ġ. Total Distance equals the sumproduct of Distance and Go. For all other nodes, Excel looks in the From and To column. As a result, only cell F15, F18 or F21 can be 1 (one ingoing arc). For node T, the SUMIF function sums the values in the Go column with a "T" in the To column. As a result, only cell F4, F5 or F6 can be 1 (one outgoing arc). For node S, the SUMIF function sums the values in the Go column with an "S" in the From column.
Range NameĮxplanation: The SUMIF functions calculate the Net Flow of each node. To make the model easier to understand, create the following named ranges. What is the overall measure of performance for these decisions? The overall measure of performance is the total distance of the shortest path, so the objective is to minimize this quantity.Ģ. All other nodes should have one outgoing arc and one ingoing arc if the node is on the shortest path (Net Flow = 0) or no flow (Net Flow = 0).Ĭ. Node T should only have one ingoing arc (Net Flow = -1). Node S should only have one outgoing arc (Net Flow = 1). What are the constraints on these decisions? The Net Flow (Flow Out - Flow In) of each node should be equal to Supply/Demand. For example, if SB is part of the shortest path, cell F5 equals 1. What are the decisions to be made? For this problem, we need Excel to find out if an arc is on the shortest path or not (Yes=1, No=0). To formulate this shortest path problem, answer the following three questions.Ī.