28  Summarizing Simulation Results with Pivot Tables

28.1 Introduction

In this exercise, you will practice creating pivot tables in Excel to summarize and analyze the results of a truck routing simulation experiment. You will work with a dataset containing the results of various simulation runs, each corresponding to different methods, objective functions, service levels, and demand sizes.

28.2 Case Study

You are tasked with solving a company’s truck routing problem. The company operates a fleet of trucks that deliver goods to customers and seeks to optimize its delivery routes to minimize costs and improve customer satisfaction. To evaluate different methods, you implemented a simulation that logs the results of various configurations into a .csv file for analysis.

You developed and tested four methods to solve the truck routing problem:

  • M1 = Simulation of the current routing strategy (baseline)
  • M2 = Greedy algorithm (Nearest Neighbor Heuristic)
  • M3 = Exact algorithm (Integer Linear Programming)
  • M4 = Metaheuristic algorithm (Simulated Annealing)

Each method has trade-offs in terms of solution quality and computational efficiency. For example, the exact algorithm guarantees the optimal solution but is computationally expensive, while the greedy algorithm is faster but may yield suboptimal results.

Metrics Collected

For each simulation run, the following key performance indicators (KPIs) were collected:

  • Distance Traveled (km)
  • Average Delivery Delay (hours)
  • Number of Customers Rejected
  • Runtime (seconds)

Configurations Tested

In your simulations, you tested different configurations to find the optimal solution for the company. The configurations include:

  • Objective Functions: Three objective functions were tested to minimize costs and improve customer satisfaction:
    1. OF1 = Minimize total distance traveled (km)
    2. OF2 = Minimize average delivery delay (hours)
    3. OF3 = Minimize the number of customers rejected
  • Service Levels: Two service levels were evaluated to assess their impact on results:
    1. SL1 = Reject customers if the service is infeasible
    2. SL2 = Serve all customers (no rejections allowed)
  • Number of Customers: Two demand sizes were considered to test the scalability of the methods:
    1. 100 customers
    2. 500 customers
  • Demand Distribution: 10 random demand distributions were generated for each demand size, varying the locations and the demand needs of customers.

The fleet size was kept constant across all simulations.

Experimental Setup

To find the best solution, you ran simulations for all combinations of methods, objective functions, service levels, number of customers, and demand distributions. This resulted in 480 runs (4 methods × 3 objective functions × 2 service levels × 2 demand size × 10 distributions), each representing a different scenario of the truck routing problem.

Your algorithm has been implemented, and you have completed the simulations. The results of each simulation run are stored in a .csv file named experimental_setup_results.csv. The file contains the following columns:

  • Method: The method used for the simulation (M1, M2, M3, M4).
  • Objective Function: The objective function used for the simulation (OF1, OF2, OF3).
  • Service Level: The service level used for the simulation (SL1, SL2).
  • Number of Customers: The Number of Customers used for the simulation (100, 500).
  • Distance Traveled (km): The total distance traveled in kilometers.
  • Avg. Delivery Delay (hours): The average delivery delay in hours.
  • Number of Customers Rejected: The number of customers rejected.
  • Runtime (seconds): The runtime of the simulation in seconds.
Table 28.1: Experimental Results (top 10 rows)
Method Number of Customers Objective Function Distribution Service Level Distance Traveled (km) Avg. Delivery Delay (hours) Number of Customers Rejected Runtime (seconds)
M1 100 OF1 1 SL1 412.362036 19.507143 20 88.984550
M1 100 OF1 2 SL1 479.055047 14.458328 20 72.962445
M1 100 OF1 3 SL1 400.112583 11.428668 12 51.029225
M1 100 OF1 4 SL1 590.972956 18.324426 15 50.038938
M1 100 OF1 5 SL1 597.663468 16.174815 21 51.153121
M1 100 OF1 6 SL1 457.432398 13.998610 25 61.638567
M1 100 OF1 7 SL1 327.181930 16.183860 21 75.711722
M1 100 OF1 8 SL1 477.724371 10.464504 28 84.015377
M1 100 OF1 9 SL1 435.149776 10.132650 23 90.419867
M1 100 OF1 10 SL1 391.384131 10.976721 29 62.051273

For the following questions, you will use the simulated data stored in the experimental_setup_results.csv file to analyze the performance of the different methods and configurations (you can download the file from here). You have to decide how to structure a series of pivot tables in Excel to achieve this analysis, namely, what fields to place in the Rows, Columns, Filters, and Values areas, and how to aggregate the data (e.g., Sum, Average, Count).

28.3 Average Distance Traveled by Method, Objective Function, and Number of Customers

You decide to create a pivot table in Excel to summarize the average distance traveled for each method, objective function, and demand size to understand how these factors influence the total distance traveled.

Table 28.2: Pivot Table for Average Distance Traveled (km) by Method, Objective Function, and Number of Customers
# Method Number of Customers OF1 OF2 OF3
1 M1 100 471.92 455.38 436.84
2 M1 500 473.34 473.46 455.38
3 M2 100 359.74 361.00 326.89
4 M2 500 329.33 334.09 372.29
5 M3 100 261.10 260.90 250.68
6 M3 500 250.18 280.16 248.51
7 M4 100 313.10 284.41 318.93
8 M4 500 285.85 292.20 265.00

Which of the following alternatives correctly describes the configuration of the pivot table in Table 28.2?

  1. Rows: Number of Customers, Method / Values: Average of Distance Traveled (km) / Filters: None / Columns: Objective Function
  2. Rows: Objective Function, Method / Values: Average of Distance Traveled (km) / Filters: Number of Customers / Columns: None
  3. Rows: Method, Objective Function / Values: Distance Traveled (km) / Filters: None / Columns: Number of Customers
  4. Rows: Method, Number of Customers / Values: Average of Distance Traveled (km) / Filters: None / Columns: Objective Function

28.4 Average Delay of Customers by Objective Function

You decide to create a pivot table in Excel to summarize the average delivery delay for each objective function to identify which objective function leads to the lowest average delivery delay. In this analysis, you want to filter the results by the service level SL2 to focus on scenarios where all customers are served without rejections.

Table 28.3: Pivot Table Structure for Average Delay of Customers by Objective Function Filtered by Service Level
# Objective Function Average of Avg. Delivery Delay (hours)
1 OF1 10.95
2 OF2 10.83
3 OF3 10.96

Which of the following alternatives correctly describes the configuration of the pivot table in Table 28.3?

  1. Rows: None / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL1 / Columns: Objective Function
  2. Rows: Objective Function / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL1 / Columns: None
  3. Rows: Objective Function / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL2 / Columns: None
  4. Rows: None / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL2 / Columns: Objective Function

28.5 Total Run Time per Method

You want to analyze the total runtime of each method across all configurations to identify the most time-consuming method. You decide to create a pivot table in Excel to summarize the total runtime for each method.

Table 28.4: Pivot Table Structure for Total Runtime per Method
# Method Sum of Runtime (seconds)
1 M1 8,805.93
2 M2 4,853.98
3 M3 23,805.86
4 M4 12,517.24

Which of the following alternatives correctly describes the configuration of the pivot table in Table 28.4?

  1. Rows: None, Values: Count of Runtime (seconds), Filters: None, Columns: None
  2. Rows: Method, Values: Sum of Runtime (seconds), Filters: None, Columns: None
  3. Rows: None, Values: Sum of Runtime (seconds), Filters: None, Columns: Sum of Runtime (seconds)
  4. Rows: Method, Values: Sum of Runtime (seconds), Filters: Runtime (seconds), Columns: Sum of Runtime (seconds)