rotes.blogg.se - Excel solver examples optimization

#EXCEL SOLVER EXAMPLES OPTIMIZATION CODE#
#EXCEL SOLVER EXAMPLES OPTIMIZATION FREE#

In the above we see something akin to a regression equation. But if you look closely, its basically a text version of something you would setup in the Solver Add-in in Excel. You will notice a new file in the working directory suffixed with ‘.lp’ after running your script.Īt first glance it might just looks like a bunch of random stuff. It is essentially a specially formatted text file.

#EXCEL SOLVER EXAMPLES OPTIMIZATION CODE#

Status: Optimal Total Impact: 4661.99 What’s Going On?Īll that code is nifty and accomplishes the optimization, but what is it doing? Well, the main thing is setting up the ‘prob‘ variable which is what PuLP uses to actually do the optimization. Remember, my volume constraints are by week so I am putting in mileage constraints by week. I know that given our fleet, they can run 20,000 to 22,000 miles per week. Some were losers but we were made whole by the parent company on those because they had to have the internal fleet move them for various reasons.įirst, we import our modules, set our high-level constraints, and import the data itself.

Min/Max Vol: These are the weekly volume constraints the business identified for each of the lanes.

But in our situation we were a fleet for a large company that manufactured things and we had to get our trucks back somehow to the plants, hopefully while making an additional profit. You might be thinking Why on earth would you move something for a net loss? And you would be right to think that. Conversely, on Lane 25 we would see a net loss of $47.50 every time we hauled a load. But for example, on Lane 0 we would have a net profit of $349.50 each time we hauled a load there. That was generated by another system (and is out of scope for this).

ImpactPer was a net impact in terms of dollars of running a truck on that route.

Distance was just the road miles between the two points.So in my case, each lane represented a City, State to a City, State. LaneId is just a route identifier, a pair consisting of an origin location and a destination.

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.

#EXCEL SOLVER EXAMPLES OPTIMIZATION FREE#

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. The logistics example (Beer Distribution Problem) provided by the developers is a great example, however, I wanted to approach it differently.