DOI: 10.5061/dryad.bk3j9kdjt
title: Sketch planning tool for sustainable and resilient urban goods distribution

description: 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.
description: # 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 CO<sub>2</sub> 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 NO<sub>x</sub> 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