Dashboard – Transport Management
- Marco Antônio de Andrade
- Mar 22
- 4 min read
Context:
Logistics costs play a crucial role in the overall cost composition of a product.
A 2022 survey conducted by the National Confederation of Industry (CNI), which interviewed 2,500 business owners across the country, revealed that freight costs account for, on average, 15% of a product’s final price (Source: FIESC).
This cost can often exceed the profit margin on the sale itself. Therefore, managing it—alongside other expenses and costs—must be done with great attention to detail.
In addition to cost, another key factor to monitor in logistics is delivery performance. Delays can lead to extra expenses and strain customer relationships. When the volume of deliveries is high, keeping track becomes a complex task. Although there are systems to support this, it is common to use spreadsheets for more customized analyses.
In this context, I had the opportunity to assist a company in consolidating its delivery data. Using a fictional database, I will demonstrate how I developed this project, highlighting how it improved monitoring and the insights that were uncovered.
Understanding the Problem
The company had an Excel database containing its delivery data, similar to the one shown in the image below. The goal was to allow users to continue entering data in this sheet while creating another sheet with key delivery performance indicators.
The main columns are:
Column A | Invoice Number: the invoice identification number.
Column B | Carrier: name of the carrier responsible for the delivery.
Column C | Mode: the transportation mode used.
Column E | Scheduled Delivery Date: the date the carrier was expected to complete the delivery.
Column F | Actual Delivery Date: the date the delivery was actually completed.
Column G | City: the recipient's city.
Development
In this project, I used Excel features such as:
Power Query
The first challenge that needed to be addressed was related to the fact that the database records each product from the invoice in a separate row. That is, if the invoice contains multiple products, it will be represented by multiple rows corresponding to the same invoice. However, the information we wish to extract refers to the invoice as a whole. If we considered each row as an invoice, we would be duplicating the invoice, and the information would be incorrect.
To solve this issue, I used Power Query, which is the appropriate tool for data processing. With it, I removed duplicate data, added a column with calculations to determine the delivery status (late, on-time, or early), and performed the necessary data typing for loading into the table that will serve as the basis for the charts.
VBA
I used VBA to create two automations that make the workbook easier to use:
→ The user can enter data directly in the base worksheet, and when accessing the Dashboard tab, all information is automatically updated.
→ I added a button for the user to navigate through the Dashboard, as shown in the video above.
Pivot Tables and Charts with Data Segmentation
With Power Query handling data processing and VBA automating the updates, I developed pivot tables, which are loaded into a hidden worksheet and serve as the basis for the dashboard's charts and data segmentations.
Dashboard
As we saw, the company had a database to record deliveries, but it lacked a clear view of their performance. Therefore, the solution was to develop a dashboard to answer the business questions it needed:
What was the overall performance of the deliveries? In the donut chart, we can see that 225 deliveries were made, and we can track the status of deliveries both by number and percentage. In the simulation below, more than one-third of the deliveries were late.
What was the performance by mode? In the column chart, we can analyze the air and road transportation modes. We can see a higher volume of deliveries by road, with a higher percentage of late deliveries compared to on-time deliveries.
What was the performance by city? In the bar chart, it's easy to identify which cities had delays and the proportion of each status.
What was the performance over the period? In the line chart, we can analyze the performance over each period and track the trend of each status.
In the second part of the Dashboard, we have a detailed analysis through pivot tables:
By carrier: In the first pivot table, we can analyze the "Total General" column to see the proportion of deliveries from each carrier, and in the other columns, we can view the status of how these deliveries were made. An interesting feature of the pivot table is the ability to double-click on any value to generate a detailed worksheet. For example, we see that "Transportes E" had 28% of deliveries delayed. By double-clicking the number 28 in the pivot table, a sheet is generated with the data for "Transportes E" deliveries that were delayed, as shown in the second screenshot below.
The other two pivot tables allow us to analyze deliveries by day of the week and by day of the month. This way, it's possible to identify where the highest incidence of delayed deliveries is occurring and evaluate measures, such as rescheduling, to better distribute deliveries.
Spreadsheet generated from the pivot table:
Fonte:
Comentarios