Summarizing Simulation Results with Pivot Tables

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.

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 (.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 1: Experimental Results (top 10 rows)
A B C D E F G H I
1 Method Number of Customers Objective Function Distribution Service Level Distance Traveled (km) Avg. Delivery Delay (hours) Number of Customers Rejected Runtime (seconds)
2 M1 100 OF1 1 SL1 412.36 19.51 20 88.98
3 M1 100 OF1 2 SL1 479.06 14.46 20 72.96
4 M1 100 OF1 3 SL1 400.11 11.43 12 51.03
5 M1 100 OF1 4 SL1 590.97 18.32 15 50.04
6 M1 100 OF1 5 SL1 597.66 16.17 21 51.15
7 M1 100 OF1 6 SL1 457.43 14.00 25 61.64
8 M1 100 OF1 7 SL1 327.18 16.18 21 75.71
9 M1 100 OF1 8 SL1 477.72 10.46 28 84.02
10 M1 100 OF1 9 SL1 435.15 10.13 23 90.42
11 M1 100 OF1 10 SL1 391.38 10.98 29 62.05

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

M1: 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 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 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

M2: Number of Values Aggregated in Pivot Table Cell

Consider the pivot table shown in Table 3, which summarizes the average distance traveled by Method, Objective Function, and Number of Customers.

Table 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

How many values are aggregated in the pivot table shown in Table 3? For example, the average 471.92, which corresponds to the average distance traveled for the first row (Method = M1, Number of Customers = 100) and Objective Function = OF1, aggregates how many values?

  1. 8
  2. 10
  3. 16
  4. 20

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

  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

M4: 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 5: 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 5?

  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

M5: Average Metrics by Method and Objective Function

You are interested in comparing the average performance metrics (distance traveled, delivery delay, and customers rejected) across different methods and objective functions. You decide to create a pivot table in Excel to summarize the average metrics for each method and objective function to identify the best-performing combinations.

Table 6: Pivot Table Structure for Average Metrics by Method and Objective Function
# Method Objective Function Average of Delivery Delay Average of Distance Traveled Average of Customers Rejected
1 M1 OF1 14.68 472.63 19.07
2 M1 OF2 15.57 464.42 20.45
3 M1 OF3 15.39 446.11 19.20
4 M2 OF1 11.27 344.53 12.22
5 M2 OF2 11.09 347.54 11.62
6 M2 OF3 11.93 349.59 12.45
7 M3 OF1 7.47 255.64 1.82
8 M3 OF2 7.43 270.53 1.95
9 M3 OF3 7.59 249.59 2.35
10 M4 OF1 9.67 299.48 9.50
11 M4 OF2 9.56 288.30 9.95
12 M4 OF3 9.51 291.96 8.62

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

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

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

  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)