Introduction to PluvioCarry¶
PluvioCarry Corporation is a bastion of paper distribution, sales, and most importantly, general packaging in the United States. The company has one distribution and packaging center that sends truckloads of materials and product from the east coast to as far away as the west coast of the United States. Inherent in having one distribution center for thousands of miles of distribution drop-offs is the search for minimizing the cost of delivering certain materials and products to certain places - and at the right speed, with the right service. Specifically, PluvioCarry wants to explore the opportunities it has in assigning its contracted distribution carriers to the least cost route possible while still attaining excellent delivery service for its products and customers. Here we have the opportunity to minimize distribution costs by assigning carriers to delivery locations based on contractually obligated truckload commitments, and the price of these deliveries.
The Model¶
The Model takes the form of an assignment optimization model. Gurobi, Python(gurobiPy), and Excel are the softwares used calculate and double-check the solution for minimizing costs. These softwares optimize using the Simplex Linear Programming Algorithm.
Data¶
The data comes in the form of the cost per mile for a carrier and the location to which it is being delivered. Shown in the Excel Model below is the spreadsheet of these costs, and the calculated total costs for a trip per one truckload for that carrier column and location row. The carrier columns on the right-hand side show the Dollar per Mile rate that a carrier charges for each location delivery. For example, the carrier IRST charges $0.88 per mile for a delivery to Atlanta Georgia. Carriers that will not deliver to a certain location are denoted by red cells and a data point of 0.
Below are the calculations in dollars for the total trip to a location for a certain carrier. For example, the carrier IRST will charge $538.56 to deliver a truckload of product to Atlanta Georgia.
For clarity, below are those calculated trip costs that we use in a more sophisticated model, and in an easier to view format. On the left-hand side are the carriers, and on the top, in columns, are the delivery locations.
#
from gurobipy import *
import pandas as pd
m = Model('WestVaco')
abct = [0,0,0,350,0,0,894,0,0,0,495.9,0]
irst = [538.56,947.16,649.8,400,2600.4,527.67,903,650.08,3536.55,650.1,495.9,651.24]
last = [703.8,927.24,350,478.75,2764.81,690.69,1284,350,0,532.8,712.5,498.96]
mrst = [532.44,955.68,324.9,367.68,0,523.38,897,325.04,2926.8,528.45,495.9,495.72]
nest = [581.4,976.2,350,363.85,3163,570.57,996,350,0,415.65,513,414.72]
psst = [642.6,933.36,380,425.13,0,630.63,996,349.52,0,674.8,746.7,631.8]
pd.DataFrame.from_dict({
'ABCT' : abct,
'IRST' : irst,
'LAST' : last,
'MRST' : mrst,
'NEST' : nest,
'PSST' : psst
}, orient = 'index', columns = ['Atlanta','Everatt','Ephrata','Riverview','Carson','Chamblee','Roseville','Hanover','Sparks',
'Parsippany','Effingham','Kearny'])
Decision Variables¶
The Decision Variables take form in the number of truckloads for each carrier, for each destination. So we have a total of 72 decision variables - 6 Carrier Columns, for 12 possible deliver locations each.
#
ABCT = [m.addVar(vtype = GRB.CONTINUOUS, name = 'ABCT' + str(i + 1)) for i in range(12)]
IRST = [m.addVar(vtype = GRB.CONTINUOUS, name = 'IRST' + str(i + 1)) for i in range(12)]
LAST = [m.addVar(vtype = GRB.CONTINUOUS, name = 'LAST' + str(i + 1)) for i in range(12)]
MRST = [m.addVar(vtype = GRB.CONTINUOUS, name = 'MRST' + str(i + 1)) for i in range(12)]
NEST = [m.addVar(vtype = GRB.CONTINUOUS, name = 'NEST' + str(i + 1)) for i in range(12)]
PSST = [m.addVar(vtype = GRB.CONTINUOUS, name = 'PSST' + str(i + 1)) for i in range(12)]
m.update()
Objective¶
The objective function is the minimization of the total costs from the distribution of deliveries to carriers and locations. Essentially, every variable is added together and then the best combination of these variables that produces the lowest cost is the solution.
#
objABCT = quicksum(abct[i]*ABCT[i] for i in range(len(ABCT)))
objIRST = quicksum(irst[i]*IRST[i] for i in range(len(ABCT)))
objLAST = quicksum(last[i]*LAST[i] for i in range(len(ABCT)))
objMRST = quicksum(mrst[i]*MRST[i] for i in range(len(ABCT)))
objNEST = quicksum(nest[i]*NEST[i] for i in range(len(ABCT)))
objPSST = quicksum(psst[i]*PSST[i] for i in range(len(ABCT)))
m.setObjective(objABCT + objIRST + objLAST + objMRST + objNEST + objPSST, GRB.MINIMIZE)
m.update()
Constraints¶
The constraints manifest in the form of contractual obligations to provide certain carriers with a minimum delivery schedule, as well as certain locations with a minimum distribution allocation. Essentially, we have to find the lowest cost while also delivering what is needed by our partner carriers to where it needs to be delivered. This way we can keep our contracts with our carriers safe, and also be confident that enough product is delivered to each location.
#
tripconstr = [4,1,3,5,1,1,1,1,2,1,5,7]
carrierminconstr = [1,7,6,0,0,4]
carriermaxconstr = [4,8,7,7,3,4]
decvars = [ABCT,IRST,LAST,MRST,NEST,PSST]
for i in range(12):
m.addConstr(ABCT[i] + IRST[i] + LAST[i] + MRST[i] + NEST[i] + PSST[i], GRB.GREATER_EQUAL, tripconstr[i])
for i in range(6):
m.addConstr(quicksum(decvars[i][j] for j in range(12)), GRB.GREATER_EQUAL, carrierminconstr[i])
for i in range(6):
m.addConstr(quicksum(decvars[i][j] for j in range(12)), GRB.LESS_EQUAL, carriermaxconstr[i])
for i in range(3):
m.addConstr(ABCT[i], GRB.EQUAL, 0)
for i in range(4,6):
m.addConstr(ABCT[i], GRB.EQUAL, 0)
for i in range(7,10):
m.addConstr(ABCT[i], GRB.EQUAL, 0)
m.addConstr(ABCT[11],GRB.EQUAL, 0)
m.addConstr(LAST[8], GRB.EQUAL, 0)
m.addConstr(MRST[4], GRB.EQUAL, 0)
m.addConstr(NEST[8], GRB.EQUAL, 0)
m.addConstr(PSST[4], GRB.EQUAL, 0)
m.addConstr(PSST[8], GRB.EQUAL, 0)
m.update()
Optimization¶
Finally we combine our data, objective function, and constraints and search for an optimal minimized cost allocation for carrier truckloads and delivery to location allocations.
m.optimize()
Results¶
Optimal Minimum Cost per Day¶
#
print('The optimal minimum cost to implement this carrier distribution is: ${:,}. '.format( round(m.objVal,2)))
Distribution of Truckloads by Carrier and Location¶
Below is the Truckload allocations for each carrier and location; The Carriers are on the left-hand side of the table, while the delivery location is on the top in column form. For example, this table would be read as there being 4 truckloads from the carrier ABCT delivered to Riverview.
#
pd.DataFrame.from_dict({
'ABCT' : [ABCT[i].X for i in range(len(ABCT))],
'IRST' : [IRST[i].X for i in range(len(ABCT))],
'LAST' : [LAST[i].X for i in range(len(ABCT))],
'MRST' : [MRST[i].X for i in range(len(ABCT))],
'NEST' : [NEST[i].X for i in range(len(ABCT))],
'PSST' : [PSST[i].X for i in range(len(ABCT))]
}, orient = 'index', columns = ['Atlanta','Everatt','Ephrata','Riverview','Carson','Chamblee','Roseville','Hanover','Sparks',
'Parsippany','Effingham','Kearny'])
Optimal Outcome¶
The outcome of optimizing for the least-cost assignment of truckloads to carriers is:
The cost of this distribution plan is $22,394.38.
Visualization of Savings¶
#
OptValTotalCost = [6718314, 33591570, 67183140]
WorstTotalCost = [9526614,47633070,95266140]
columnscosts = ['One Year', 'Five Years', 'Ten Years']
pd.DataFrame.from_dict({
'Optimized Total Cost' : OptValTotalCost,
'Non-Optimized Potential Total Cost' : WorstTotalCost
}, orient = 'index', columns = columnscosts)
The most important figures here are the optimized annual cost of $6,718,314 in comparison to an arbitrary choosing of carriers that produces an annual cost of $9,526,614 - A difference in costs (or savings) of $2,808,300 for each year for this specific case.
#
savings = []
for i in range(3):
savings1 = WorstTotalCost[i] - OptValTotalCost[i]
savings.append(savings1)
pd.DataFrame.from_dict({
'Total Savings' : savings
},orient = 'index', columns = columnscosts)
The most important figures here are the total annual savings of $2,808,300 and the savings over ten years of $28,083,000.
Recommendation¶
I recommend three immediate courses of action regarding moving forward with establishing a procedure for an optimal distribution schedule:
Course of Action 1 - implementing the plan at the worker level
The first course of action requires distilling the information from the optimization algorithm to a format that can be well recieved by the foreman who determines the distribution schedule. This allows for consistent and unarbitrary decisions about which carriers will take which truckloads of product to which clients. I recommend an exploration of an additional process that allows the information from the algorithm to be provided in paper or computer form in a schedule sheet for the foreman each day.
Course of Action 2 - Exploring Carrier Options
The second course of action is to make some decisions at the executive level about the contracts that are held with carriers. These decisions will be based on a sensitivity analysis of the room available to expand from current carriers at their current negotiated costs. This is one (the very start) example of one way to start optimizing the business solution and exploring other opportunities to reduce the cost that the algorithm optimizes. Exploring these opportunities are win-win for the company.
Course of Action 3 - Further In-Depth Analytics and Sensitivity Analysis
This is an important step for continuing into the future of reducing costs optimally and taking full advantage of the power and insight that analytics can bring to decision making at PluvioCarry. Essentially, this solution is only one very miniscule part in the constantly moving complex group of parts that keep this company running. I implore the use of this as an excellent starting point for further in-depth analytics of the many different opportunities available for this company. It has already been shown that the company could save over 28 million dollars over ten years using a very limited analysis of cost reduction opportunities. Could you imagine the saving, or gains from navigating every opportunity that is available?
Business Opportunity Highlights and Sensitivity Report¶
Sensitivity Report - An Example¶
Exploring Carrier Costs - Bidding for New Carriers or Better Contracts
An interesting analysis is the opportunity costs associated with picking one carrier to deliver to a customer over another. An example of this is when the algorithm does not choose a carrier because of the cost associated, but it would choose that carrier if the cost was slightly lower for that carrier to that destination. Through the analysis we find that there is a $2.35 opportunity cost in not picking MRST for a delivery to Ephrata, PA. This two dollar cost is how much the carrier would have to change(decrease) in the charge they give PluvioCarry before the algorithm would choose this carrier for this destination. This is important because it communicates that there may be some wiggle room in the contracts associated with the carriers - and some negotiation that could be had on the part of PluvioCarry. It gives the company a legitimate fact number to negotiate with the carrier - something like if the carrier reduces their charge by five dollars for this location PluvioCarry will use them. This gives value to the carrier by providing the a contract for this destination, but also allows PluvioCarry to reduce there costs to a margin of their negotiation.
Expanding the limits on the Number of Truckloads
It may be advantageous for PluvioCarry to review the constraints it places of the number truckloads that are allowed to be sent to a destination each day. For example the limit of truckloads to Ephrata, PA, are limited to 3 truckloads. However, through the analysis, the cost of sending 1 or even 2 more truckloads a day to this location is only $30 more per truckload. This cost is interesting to the company because this might allow a shifting of another cost like inventory costs, to the customer while PluvioCarry moves more product. Further review might uncover the benefits of then only shipping to this location 3 times a week rather than five, and overall saving more from only shipping three times a week with one or two more truckloads than the costs associated(driver,gas,time,etc) with shipping all five days. This requires further analysis but can give excellent business insight on where PluvioCarry might want to begin in cutting costs. This provides an excellent direction to start moving immediately.
Updating Route information - Accurate Costs for Miles Example¶
The reported distances between the distribution site and the customers being delivered to are innacurate. Some research suggested that the distances differ in some cases, detailed below, by as much as 600 miles. This is important because accuracy is required in forecasting costs into the fucture, and conducting good analysis of the cost-cutting opportunities. It may sound bad, but the difference of three thousand dollars more per day from our accurate calculations means the correct decisions are being made, and that decisions are not being made on some phony values. Furthermore, this gives us a starting point for genuinely taking an honest look at how we might reduce costs and improve the bottom-line.
#
reporteddist = [612,612,190,383,3063,429,600,136,2439,355,570,324]
actualdist = [531,557,250,614,2638,519,1219,201,2655,351,776,336]
milesdifference = []
for i in range(len(reporteddist)):
diff = abs(actualdist[i] - reporteddist[i])
milesdifference.append(diff)
pd.DataFrame.from_dict({
'Reported Distance to Destination' : reporteddist,
'Actual Distance to Destination' : actualdist,
'Difference in Miles' : milesdifference
},orient = 'index',columns = ['Atlanta','Everatt','Ephrata','Riverview','Carson','Chamblee','Roseville','Hanover','Sparks',
'Parsippany','Effingham','Kearny'])
#
updOptimalcost = 25296.48
print('The reason updating our miles matters is because the cost of implementing an optimal plan is not only more accurate,but provides a better sense of direction when making business decisions. The optimal Cost of the distribution plan when considering updated route information is: ${:,} per day. This is distinct from our previous optimal cost of ${:,} per day. '.format(updOptimalcost, round(m.objVal,2) ))
In Summary¶
The most important take away from analyzing the way that PluvioCarry distributes and delivers their products is that there is always a benefit from the insight provided from exploring common business issues with data. What this means is that it pays, in the realest sense of the phrase, to explore the data that encompasses the business processes - from everyday, to historical decade old data. When optimizing the business practices, it gives an important direction for decisions made at the executive level that may determine the profitability of the company in the future. Importantly this optimization saves money for the company, and sets precedent for an exploration of cost-cutting practices in the futue.
Approach and Challenges¶
The Approach¶
The approach began with a search for a full understanding of the background of the company. I found it important to understand where the data is coming from, and where my effort in analyzing this data would end up. Knowing the decision path of the company provided insight into how I can better present my finding to convey the most value to decision makers. After general research, an exploration of the data itself is necessary to sift through important variables and non-important noise. This allows for a general focus on the problem definition and a clear view to finding its solution. Next, I prototyped my model using Excel and Excel Solver, building a foundation and understanding for the variables, constraints, and formulation of a possible solution. After Excel was optimized, checked, and rechecked, I progressed to rebuilding a model in Gurobi that communicated the same optimal solution as my prototype. This step took the longest and required a discovery of the algebraic notation of a very non-algebraic business problem. Finally, optimizing the programming itself iteratively in Python (Gurobi) that provided a clear framework for the business problem. The only step remaining is effectively communicating what was found to the decision makers, and providing valuable insight for future decisions.
General Challenges¶
Programming Under Time Constraints and Deadlines¶
Programming and building this model under time-pressue was easily the highest intensity challenge associated with this project. Not only does it take time to understand the business problem, but then it takes double the time to translate and articulate this model effectively in code, and in such a way that it may be used in the future, or expanded upon. This is the top priority in future projects.
Deciding Whether or Not to Communicate a Sensitivity Report¶
I decided against including a sensitivity report for this solution because of the straightforward nature of the optimal solution. Although a sensitivity report would absolutely provide insight and value, I think that in this case - what seems like an exploration of one business process - having cold hard values to benchmark against historical data was authoritative enough to convince decision makers that they can save money using this optimization model on distribution processes. In the scope of a larger project, or a more pivotal project, a sensitivity report is instrumental in convincing decision makers of the opportunity cost and economic cost(interpretation) given by the analysis.
Over-explaining the Model¶
Over explaining the dense technical aspects of the project is always a temptation, and I found it difficult to eliminate unnecessary explanation in this project. I learned that this will probably be an issue for most projects, and I need to be vigilant with myself and my communication of the technical aspects of a project in the future.
Technical Challenges¶
Finding the Right Constraints¶
Finding the right constraints, and actually understanding what they meant, was a challenge for me in this project. The reason the constraints were challenging is because of the technical nature of their programming. I simply got stuck on articulating the constraints in Python and spent hours defining and redefining what the constraints meant and how they should be expressed.
Next Steps Moving Forward¶
I like to include a next steps, or future improvement and goals section as it provides a framework for each project that I may call back upon in future projects. With this project, I would include a sensitivity analysis that delves into the different distributions of our carrier's truckloads, what a distribution might look like over time, how changing prices of gas or mileage would factor into our calculation, and the appropriate visualizations that are attached to every factor. This just provides more established authority on which to base my claims and convince decision makers of the solution. Additionally, visualization is a good way to articulate and communicate, and a next step for this project (or ideally a beginning step) would be to include more visualization of the raw data, and any relationships that might be present(if theres a product mix). Additionally, expanding the scope of this project to transportation networks, and product mix optimization and cost minimization would be an interesting and valuable path to take for providing insight to the company's leadership.