4  Pivot Table Example

4.1 Summarizing Simulation Results (Background)

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 4.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).

4.2 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 4.2: 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 4.2?

  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

4.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 4.3: 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 4.3?

  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

4.4 Avg Customers Rejected by Objective Function

You want to analyze the average number of customers rejected by each objective function. You decide to create a pivot table in Excel to summarize the average number of customers rejected for each objective function, so you can identify which objective function leads to the highest number of rejections. You filter the service level to SL1 to focus on scenarios where customers can be rejected.

Table 4.4: Pivot Table Structure for Total Customers Rejected by Objective Function Filtered by Service Level
# Objective Function Average of Number of Customers Rejected
1 OF1 10.93
2 OF2 11.31
3 OF3 10.82

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

  1. Rows: None / Values: Average of Customers Rejected / Filters: Service Level = SL1 / Columns: Objective Function
  2. Rows: Objective Function / Values: Average of Customers Rejected / Filters: Service Level = SL2 / Columns: None
  3. Rows: None / Values: Average of Customers Rejected / Filters: Service Level = SL2 / Columns: Objective Function
  4. Rows: Objective Function / Values: Average of Customers Rejected / Filters: Service Level = SL1 / Columns: None