• Energy Research

This work models a last-mile network design problem for an e-retailer with a capacitated two-echelon distribution structure - typical in e-retail last-mile distribution, catering to a market with a stochastic and dynamic daily customer demand requesting delivery within time-windows. Considering the distribution evnironment, this work formulates last-mile network design problem for this e-retailer as a dynamic-stochastic two capacitated location routing problem with time-windows. In doing so, this work splits the last-mile network design problem into its constituent strategic, tactical, and operational decisions. Here, the strategic decisions undertake long-term planning to develop a distribution structure with appropriate distribution facilities and a suitable delivery fleet to service the expected customer demand in the planning horizon. The tactical decisions pertain to medium-term day-to-day planning of last-mile delivery operations to establish efficient goods flow in this distribution structure to service the daily stochastic customer demand. And finally, operational decisions involve immediate short-term planning to fine-tune this last-mile delivery to service the requests arriving dynamically through the day. Note, the last-mile network design problem formulated as a location routing problem constitutes three subproblems encompassing facility location problem, customer allocation problem, and vehicle routing problem, each of which are NP-hard combinatorial optimization problems. To this end, this work develops an adaptive large neighborhood search meta-heuristic algorithm that searches through the neighborhood by destroying and consequently repairing the solution thereby reconfiguring large portions of the solution with specific operators that are chosen adaptively in each iteration of the algorithm, hence the name adaptive large neighborhood search. Further, considering the stochastic and dynamic nature of the delivery environment, this work develops a Monte-Carlo framework simulating each day in the planning horizon, with each day divided into 1-hr timeslots, and with each time-slot accepting customer requests for service by the end of the day. In particular, the framework assumes the e-retailer will delay route commitments until the last-feasible time-slot to accumulate customer requests and consequently assign them to an uncommitted delivery route. Note, a delivery route is committed once the e-retailer starts loading packages assigned to this delivery route onto the delivery vehicle assigned for this delivery route. At the end of every time-slot then, this framework assumes the e-retailer integrates the new customer requests by inserting these customer nodes into such uncommitted delivery routes in a manner that results in the least increase in distribution cost keeping the customer-distribution facility allocation fixed. Thus, the framework iterates through the time-slots with the e-retailer processing route commitments, accumulating customer requests, and subsequently integrating them into the delivery operations for the day. E-commerce has the potential to make urban goods flow economically viable, environmentally efficient, and socially equitable. However, as e-retailers compete with increasingly consumer-focused services, urban freight witnesses a significant increase in associated distribution costs and negative externalities particularly affecting those living close to logistics clusters. Hence, to remain competitive, e-retailers deploy alternate last-mile distribution strategies. These alternate strategies, such as those that include use of electric delivery trucks for last-mile operations, a fleet of crowdsourced drivers for last-mile delivery, consolidation facilities coupled with light-duty delivery vehicles for a multi-echelon distribution, or collection points for customer pickup, can restore sustainable urban goods flow. Thus, in this study, the authors investigate the opportunities and challenges associated with such alternate last-mile distribution strategies for an e-retailer offering expedited service with rush delivery within strict timeframes. To this end, the authors formulate a last-mile network design (LMND) problem as a dynamic-stochastic two-echelon capacitated location routing problem with time-windows (DS-2E-C-LRP-TW) addressed with an adaptive large neighborhood search (ALNS) metaheuristic. Funding provided by: California Department of TransportationCrossref Funder Registry ID: http://dx.doi.org/10.13039/100004811Award Number: 65A0686 Task Order 066Funding provided by: U.S. Department of TransportationCrossref Funder Registry ID: http://dx.doi.org/10.13039/100000140Award Number: 69A3551747114

# Sketch Planning Tool for Sustainable and Resilient Urban Goods Distribution [https://doi.org/10.5061/dryad.bk3j9kdjt](https://doi.org/10.5061/dryad.bk3j9kdjt) # Getting Started Before you can use tool, you must enable the Solver add-in in the Excel Options dialog box. Click the File tab, and then click Options below the Excel tab. In the Excel Options dialog box, click Add-Ins. In the Manage drop-down box, select Excel Add-ins, and then click Go. In the Add-Ins dialog box, select Solver Add-in, and then click OK. After you have enabled the Solver add-in, Excel will auto-install the Add-in if it is not already installed, and the Solver command will be added to the Analysis group on the Data tab in the ribbon. Further, you must establish a reference to the Solver add-in. Click the Developer tab, and then in the Code group click Visual Basic command. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select Solver under Available References. If Solver does not appear under Available References, click Browse, and then open Solver.xlam in the \\Program Files\\Microsoft Office\\Office14\\Library\\SOLVER subfolder. # Navigating the tool This section helps the user navigate the tool through the different worksheets in the Excel file. This tool contains primarily three kinds of worksheets, namely, input-based, output-based, and function-based worksheets. While the input-based worksheets are for the user to view and edit, the output-based worksheets are for view-purpose only, however, the function-based worksheets are protected for the sake of functionality and the user is strongly suggested to not edit them. ## Input-based worksheets ### input The *input* worksheet guides the user to input the necessary data and run the tool. These data inputs include service region parameter values, supply parameter values, and demand parameter values. The authors detail this user input process in the section – Running the tool. ### service region The service region worksheet enlists parameter values of the service region including the characteristics of the service region, the demographics of the population, and the emissions costs in the service region. ### vehicle parameters The vehicle parameters worksheet lists essential parameters of the vehicles deployed in last-mile distribution. ### supply The supply worksheet enlists supply-side parameters including distribution environment as well as those pertaining to the primary distribution channel (e-retailer’s distribution structure) and secondary distribution channel (outsourcing distribution structure). ### demand The demand worksheet enlists pre- and peri-/post- disruption demand parameters. ## Output-based worksheets ### output The output worksheet characterizes and plots the observed demand and service disruption. And with this, the output sheet analyzes e-retailer’s response to disruption evaluating last-mile distribution resilience in the form of Robustness, Redundancy, Resourcefulness, and Rapidity – Resilience Metrics. The tool also evaluates Operational Metrics wherein, Total Delay expresses cumulative delay in terms of number of package-days of delayed service, while the Average Delay evaluates the average number of additional packages delayed on any day, and the average number of days a package is delayed, assuming that packages are delivered on a first-come-first-served basis. Moreover, the output worksheet evaluates Economic Metrics that evaluate the Direct, Indirect, and Total Loss to the e-retailer from the disruption. Note, the Direct Loss evaluates the change in distribution cost relative to pre-disruption distribution cost, and Indirect Loss accounts for the loss from delayed service penalizing late delivery (unmet demand) at $5 per package for every day of delayed service, while the Total Loss is the sum of Direct and Indirect loss, and thereby reflects the explicit and implicit costs to the e-retailer. ## Function-based worksheets ### pre-disruption demand The pre-disruption demand worksheet employs multinomial logit model that depicts consumer choice of shopping channel to estimate total pre-disruption e-commerce demand in the service region. ### main The main worksheet enlists parameters and decision variables relevant to the different optimization models and the simulation framework in the tool. ### optimize The optimize worksheet details e-retailer’s pre- and post- disruption distribution structure optimization model. ### mincost\-w\|o The *mincost\-w\|o* worksheet details e-retailer’s cost minimization model without use of secondary (outsourcing) distribution channel. ### maxcap\-w\|o The *maxcap\-w\|o* worksheet details e-retailer’s capacity maximization model without use of secondary (outsourcing) distribution channel. ### mincost-wo The mincost-wo worksheet details e-retailer’s cost minimization model with use of secondary (outsourcing) distribution channel. ### maxcap-wo The maxcap-wo worksheet details e-retailer’s capacity maximization model with use of secondary (outsourcing) distribution channel. ### miscellaneous The miscellaneous worksheet includes some data for functional purpose. ### results The results worksheet outputs the results of the simulation including daily congestion levels, demand, unserved demand, primary distribution capacity, auxiliary distribution capacity, served demand, cost, cost per package, disruption, and level of service. # Running the tool This section details the essential steps to run the tool. **Step 0.** Start at the input worksheet. Step 0.1. Reset values **Step 1.** Set service region parameter values in the service region worksheet. Step 1.1. Set service region characteristics. Step 1.1.1. Set service region name in cell B2. Step 1.1.2. Select service region location from the dropdown list in cell B3. Step 1.1.3. Scroll through the bar to set service region size in cell B4. Step 1.1.4. Scroll through the bar to set service region population in cell B5. Step 1.1.5. Scroll through the bar to set service region congestion factor in cell B6. Step 1.1.6. Scroll through the bar to set service region facility fixed cost rate parameter in cell B7. Step 1.1.7. Scroll through the bar to set service region facility fixed cost distance parameter in cell B8. Step 1.1.8. Scroll through the bar to set service region discount rate in cell B9. Step 1.2. Set service region demographics. Step 1.2.1. Scroll through the bar to set service region gender ratio in cells B15-16. Step 1.2.2. Scroll through the bar to set service region education levels in cells B19-22. Step 1.2.3. Scroll through the bar to set service region age groups in cells B25-29. Step 1.2.4. Scroll through the bar to set service region income levels in cells B32-38. Step 1.2.5. Scroll through the bar to set service region household size in cell B41. Step 1.2.6. Scroll through the bar to set service region household number of children in cell B41. Step 1.2. Set service region emissions cost. Step 1.3.1. Scroll through the bar to set service region CO2 cost in cell B47. Step 1.3.2. Scroll through the bar to set service region CO cost in cell B48. Step 1.3.3. Scroll through the bar to set service region NOx cost in cell B49. Step 1.3.4. Scroll through the bar to set service region PM cost in cell B50. **Step 2.** Set supply parameter values in the supply worksheet. Step 2.1. Set distribution environment parameter values Step 2.1.1. Scroll through the bar to set e-retailer's planning horizon in cell B2. Step 2.1.2. Scroll through the bar to set e-retailer's working days in cell B3. Step 2.1.3. Scroll through the bar to set e-retailer's working hours in cell B4. Step 2.1.4. Scroll through the bar to set e-retailer's market share in cell B5. Step 2.1.5. Scroll through the bar to set e-retailer's planning horizon in cell B6. Step 2.2. Set primary distribution channel parameter values Step 2.2.1. Select fleet type from the drop-down list in cell B14. Step 2.3. Select outsourcing channel and its parameter values *If no outsourcing channel is deployed* Step 2.3.1. Set outsourcing channel to None from the dropdown list in cell B22. *Else if distribution is outsourced with crowdsourced delivery.* Step 2.3.1. Set outsourcing channel to crowdsourced delivery from the dropdown list in cell B22. Step 2.3.2. Select fleet type from the drop-down list in cell B28. Step 2.3.3. Set fleet size limit in cell B29. Step 2.3.4. Set tour limit in cell B30. *Else if service is outsourced via collection-points for customer pickup.* Step 2.3.2. Set number of collection-points in cell B35. Step 2.3.3. Select fleet type from the drop-down list in cell B37. Step 2.3.4. Set fleet size limit list in cell B38. Step 2.3.5. Set tour limit in cell B39. *Else if distribution is outsourced via Logistics Service Provider’s micro-hubs* Step 2.3.2. Set number of micro-hubs in cell B44. Step 2.3.3. Select fleet type from the dropdown list in cell B46. Step 2.3.4. Set fleet size limit list in cell B47. Step 2.3.5. Set tour limit in cell B48. To view/edit vehicle related parameters refer to *vehicle parameters* worksheet. **Step 3.** Set demand parameter values in the demand worksheet. Step 3.1. Set pre-disruption demand Step 3.1.1. Set pre-disruption demand in cell B2. Step 3.2. Set peri-/post- disruption demand Step 3.2.1. Either select one of the default disruption scenarios or set up a custom disruption scenario. **Step 4.** Solve Step 4.1. Simulate Step 4.2. Analyze **Step 5.** Export results Step 5.1. Export The urban goods distribution system is a critical component of modern society. However, the COVID-19 pandemic exposed significant vulnerabilities in this system, as it struggled to cope with an unforeseen surge in demand. This crisis highlighted the urgent necessity of developing a resilient and sustainable urban goods distribution system capable of efficiently recovering from high-severity disruptions. To address this challenge, our research team previously developed a novel analytical model, the Robustness, Redundancy, Resourcefulness, and Rapidity - Last-Mile Distribution - Resilience Triangle (R4-LMD-RT) framework. In line with the previous work, this work aims to create a sketch-planning tool tailored for local jurisdictions, based on the R4-LMD-RT model. This tool assists in the strategic planning of urban goods distribution systems, identifying land use requirements and proposing sustainable and resilient strategies, such as urban consolidation, micro-hubs, alternative delivery points, and zero-emission vehicles. As part of a case study, the authors validate the effectiveness of this planning tool by applying it to the city of Los Angeles for a COVID-19-like disruption. The outcome of this research paves the way for more sustainable and resilient urban goods distribution systems in the post-pandemic world.

This work models a last-mile network design problem for an e-retailer with a capacitated two-echelon distribution structure - typical in e-retail last-mile distribution, catering to a market with a stochastic and dynamic daily customer demand requesting delivery within time-windows. Considering the distribution evnironment, this work formulates last-mile network design problem for this e-retailer as a dynamic-stochastic two capacitated location routing problem with time-windows. In doing so, this work splits the last-mile network design problem into its constituent strategic, tactical, and operational decisions. Here, the strategic decisions undertake long-term planning to develop a distribution structure with appropriate distribution facilities and a suitable delivery fleet to service the expected customer demand in the planning horizon. The tactical decisions pertain to medium-term day-to-day planning of last-mile delivery operations to establish efficient goods flow in this distribution structure to service the daily stochastic customer demand. And finally, operational decisions involve immediate short-term planning to fine-tune this last-mile delivery to service the requests arriving dynamically through the day. Note, the last-mile network design problem formulated as a location routing problem constitutes three subproblems encompassing facility location problem, customer allocation problem, and vehicle routing problem, each of which are NP-hard combinatorial optimization problems. To this end, this work develops an adaptive large neighborhood search meta-heuristic algorithm that searches through the neighborhood by destroying and consequently repairing the solution thereby reconfiguring large portions of the solution with specific operators that are chosen adaptively in each iteration of the algorithm, hence the name adaptive large neighborhood search. Further, considering the stochastic and dynamic nature of the delivery environment, this work develops a Monte-Carlo framework simulating each day in the planning horizon, with each day divided into 1-hr timeslots, and with each time-slot accepting customer requests for service by the end of the day. In particular, the framework assumes the e-retailer will delay route commitments until the last-feasible time-slot to accumulate customer requests and consequently assign them to an uncommitted delivery route. Note, a delivery route is committed once the e-retailer starts loading packages assigned to this delivery route onto the delivery vehicle assigned for this delivery route. At the end of every time-slot then, this framework assumes the e-retailer integrates the new customer requests by inserting these customer nodes into such uncommitted delivery routes in a manner that results in the least increase in distribution cost keeping the customer-distribution facility allocation fixed. Thus, the framework iterates through the time-slots with the e-retailer processing route commitments, accumulating customer requests, and subsequently integrating them into the delivery operations for the day. E-commerce has the potential to make urban goods flow economically viable, environmentally efficient, and socially equitable. However, as e-retailers compete with increasingly consumer-focused services, urban freight witnesses a significant increase in associated distribution costs and negative externalities particularly affecting those living close to logistics clusters. Hence, to remain competitive, e-retailers deploy alternate last-mile distribution strategies. These alternate strategies, such as those that include use of electric delivery trucks for last-mile operations, a fleet of crowdsourced drivers for last-mile delivery, consolidation facilities coupled with light-duty delivery vehicles for a multi-echelon distribution, or collection points for customer pickup, can restore sustainable urban goods flow. Thus, in this study, the authors investigate the opportunities and challenges associated with such alternate last-mile distribution strategies for an e-retailer offering expedited service with rush delivery within strict timeframes. To this end, the authors formulate a last-mile network design (LMND) problem as a dynamic-stochastic two-echelon capacitated location routing problem with time-windows (DS-2E-C-LRP-TW) addressed with an adaptive large neighborhood search (ALNS) metaheuristic.