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₂ 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_x 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