Haulage Analysis for a fictional haulage business.
image source: google.com
all rights belongs to the orignal creator
Data Analysis using Power BI
Creating a Reports that gives the insights on a fictional haulage business covering the employee productivity, most moved product(most profitable product), revenue, expenses by product, drivers and depot and destination visited.
Including a year over year analysis.
Power Bi
Excel
Notepad
The dataset was provided by my mentor
Sulaiman Lukman
- Closed trip list: This includes more than 7 columns:
- Trip No
- Date
- Time
- Truck No
- Driver Name
- Destination
- Actual Date
- Open trip list: This includes more than 7 columns:
- Trip No
- Date
- Time
- Truck No
- Destination
- Actual Date
- Trip Rates: This includes more than 9 columns:
- Load Port
- Destination
- Product Category
- Trip Rate
- Loading Expenses
- Trip Allowance
- Union Fees
- Parking Fees
- Capacity
- Product
- Truck Available: This includes more than 7 columns:
- Truck No
- Product Type
- Capacity
- Truck Classification
- Aquilla Status
- Status(Availability)
- Depot Location
- Truck List: This includes more than 7 columns:
- Truck No
- Product Type
- Capacity
- Truck Classification
- Aquilla Status
- Status(Availability)
- Depot Location
- Registration Year
- Use column
After importing the datasets using the import method in power bi:
Then, the first task i did was to clean the data by renaming misspelt values in columns, changing data-types and splitting the date-time columns (in tables that had them) to increase performance.
Then I used excel to create a new table including just a column named total truck trip id by copy the truck trip id from both closed and open trips into a single column. This was done to create a better relationship between these two columns.
I didn't append both tables because they had little structural differences.
Then I imported this newly created table which I named destinations into power bi (note: I later used this table as my fact table. Forgive me for the wrong choice of name)
I created a primary key in all the initial five tables to enable creating relationship with the destination table easier.
Then I merged the already imported Destination tables (fact table) with other tables to create a table with more than 10 columns aside the secondary keys.
Which Includes:
- Truck Trip Id
- Total Truck No
- Trip Status (conataining value indicating if the trip is closed or opem)
- Product type
- Truck Capacity
- Truck Classification
- Depot Location
- Trip Rate
- Loading Expenses
- Trip Allowances
- Union Fees
- Destination (three columns- Destination name, Latitude and Longitude)
- Secondary Keys
I created a date table using blank query and M code.
I also disabled power bi's defualt date table by setting my created table as my date table
I enabled many to one relationship and cross filter direction from the destination table (fact table) to other tables using their respective keys.
Which contains 5 main visuals, 6 cards and 3 slicers
- The total trucks
- Trucks In Use
- Trucks Under Repair
- The three classification of trucks
- Year Slicer to filter between years
- Status Slicer to filter between available and unavailable trucks
- Classification Slicers to filter between the classification of the truck
This visual will make its easier for the decision makers to know which of their trucks are prone to breakdown and prepare for it before hands. Factors influencing breakdown include how much the trucks is being used, this brings us to the next visual.
This visual shows how many trips a truck has travelled through out the business's lifetime(2 years+).
This will enable the stakeholders know which of their trucks are the most active and prepare for replacement before hand imcase of a breakdown.
This shows trucks by their registration type.
This will enable the stakeholders know which of their trucks are available for aquilla approved states.
This shows the total trips travelled by trucks which includes a drill through from year, to quarter, month and day.
This will enable the stakeholders know the total trips travelled by their trucks and prepare before hand for breakdown.
This shows the percentage classification of all trucks
This will enable the stakeholders know the truck types they have in stock and the type of destination they can travel based on their classification and know which type of trucks will warrant more investment.
With Bridging meaning long distance trucks, West local - Trucks that travels to closer states and local meaning trucks that travels within states.
This shows the total trucks by capacity and available trucks
This will enable the stakeholders know the truck by capacity they have in stock and those that are available for jobs. When clients demands for them
Majority of the company's truck were purchased in 2020.
The top 5 trucks that travelled the most destination where purchased in year 2020 with majority being non aquilla, bridging trucks having majority capacity of 45,000litres and none of the trucks (trucks bought in 2020) are currently under repair.
Over 160 of these trucks are available for use while 290 are currently on route at the time the data was collected.
I will recommed the stakeholders to get new trucks to replace these trucks in few years to come due to the higher possiblity of breakdown due to their immense usage.
Which contains 4 main visuals, 3 cards and 3 slicers
- Total Trips
- Total Open Trips
- Total Closed Trips
- Year Slicer to filter between years
- The Truck No slicer
- The Driver's name slicer (note: all names are fictional and any similarities with real person should be taken as coincidence)
This visual will make its easier for the decision makers to know which of depot they have more presence in advert of employement and development
This shows the total trips travelled by trucks which includes a drill through from year, to quarter, month and day
This will enable the stakeholders know the total trips travelled by their trucks and prepare before hand for breakdown.
The first flow map shows the how the trucks travelled from the depot to various destination with concentration on the destination visited.
The second flow map shows how the trucks travelled from the depot to various destination with concentration on the depot.
This will enable the stakeholders know which area or route to create repair shops or strike a deal with the mechanic along the route in advert of a truck breakdown.
A total of 15K trips have being carried out by the company at the time of this report. Having a total of over 6k closed trips and 7k open trips.
Location 4 depot happens to be the most active depot, while abuja happens to be the most visited places.
I will recommed the stakeholders to make deal with mechanics and repair shop at strategic places along the most used route by its drivers incase of breakdown for immediate support and repair
Which contains 4 main visuals, 8 cards and 3 slicers
- Total No of Drivers
- Total Closed Trips
- Total Revenue
- Expenses
- Profit
- Total Number of bridging drivers
- West local drivers
- Local Drivers
- Year Slicer to filter between years
- The Classification Slicer(to filter truck classification to better undertsand the trip carried out by drivers)
- The Driver's name slicer (note: all names are fictional and any similarities with real person should be taken as coincidence)
This visual will make its easier for the decision makers to know which of their drivers are they spending the most on.
This visual will make its easier for the decision makers to know which of their drivers have travelled the most (i.e most productive).
This visual will make its easier for the decision makers to know which of their drivers have they generated the most revenue from their successful trips.
This shows the total trips travelled by trucks which includes a drill through from year, to quarter,m onth and day
This will enable the stakeholders know the total trips travelled by their trucks and prepare before hand for breakdown.
The dataset provided gave only details(name) of drivers whom have successfully completed a trip (closed trips).
In the bridging section
- Pilot 151 had the most successful number of trips. Travelling from Warri to Sapelle.
He also accounted for the most expenses in the catergory of bridging drivers which is understandable but with a lesser revenue generated (the third driver with the most revenue generated) due to the destination he his plying.
In the West Local section
- Pilot 649 had the most succeful number of trips. Travelling from Warri and few other states like Auchi.
He also accounted for the most expenses and the most revenue generated in the catergory of West Local Trucks drivers which is understandable.
In the Local section
- Pilot 658 had the most succeful number of trips. Travelling from Warri and few other states like Auchi.
He also accounted for the most expenses and the most revenue generated in the catergory of Local Trucks drivers which is understandable.
I will recommed the stakeholders to give / provide initiatives for outstanding employee to increase their productivity and a means of acknowledgement of the job well done.
Which contains 5 main visuals, 1 cards and 2 slicers
- The total product moved
- Year Slicer to filter between years
- The Status Slicer
This visual will make its easier for the decision makers to know the quantity of product on the road and those delivered.
This shows the total product moved by product types
This will enable the stakeholders know which product type they moving the most
This shows the product types by expenses
This will enable the stakeholders know which of the product types they spending the most on, in terms of transportation.
This shows the product by product moved
This will enable the stakeholders know which of the product have they moved the most.
This shows the product by capacity by product volume by total trips
This will enable the stakeholders know which product types is the most moved and of which of the capacities
The most moved product is the white product.
Lesser product are on the road compared to the successfully delivered ones
I will recommend the decision makers to make sure that trucks of capacity 45,000L are always ready to answer clients request since the are the most used which implies that trucks of such capacity will be the most demanded by clients.
Which contains 5 main visuals, 1 cards and 2 slicers
- The total product moved
- Year Slicer to filter between years
- The Status Slicer
The total revenue by product type
This visual will make its easier for the decision makers to which product gives them the most revenue
This shows the total revenue generated by closed and open destination trips
This will enable the stakeholders know which of their trips have generated the most.
This shows the total Revenue generated per years
This will enable the stakeholders know which of the years have generated the most revenue.
This shows the total Revenue generated by drivers
This will enable the stakeholders know which drivers have generated the most revenue.
This shows a breakdown of revenue by depot, destination, product type, trip status and driver's name.
This will enable the stakeholders drill through the revenue and notice the factors affecting the revenue.
The product that generate most revenue is the white product due to the fact that it the most moved product.
While revenue generated by the trip status is nearly evenly distributed with open trip 3%+ more than the revenue generated by the closed trip which is also understandable because the dataset clearly says the total open trips are more than the succesfully completed trips (closed trips).
The most revenue generated came from the year 2021 due to more bussiness active in the that year.
Road pilot 172 Generated the most Revenue which is understandable due to the type of destination he travels and the product he carries (Bridging driver).
Other drivers should be charged to work more to reduce the overhead effect on the company's revenue incase Road pilot 172 decides to leave.
Which contains 5 bookmarks, 5 main visuals, 1 multi cards and 2 slicers
- Total Expenses
- Total Loading Expenses
- Total Trip Allowance
- Total Union Expenses
- Year Slicer to filter between years
- The Status Slicer
- Expenses
- Loading Expenses
- Union Expenses
- Trip Allowance
- All Expenses
Note three bookmarks contains the same visual but the reference columns are being replaced in respective bookmarks.
- Reference columns include
- Loading Expenses
- Union Expenses
- Trip Allowance
This shows a breakdown of all expenses by depot, destination, product type, trip status and driver's name.
This will enable the stakeholders drill through the revenue and notice the factors influencing their expenses.
The expenses by product type
This visual will make its easier for the decision makers to know which product they are spending more on.
This shows the expenses by closed and open destination trips
This will enable the stakeholders know which of their trips status they spent more on.
This shows the expenses per years
This will enable the stakeholders know which of the years they have spent more in.
This shows expenses by drivers
This will enable the stakeholders know which drivers have incurred the most cost.
The product that generate most expenses incurred was on the white product due to the fact that it the most moved product.
While expenses incurred by the trip status is nearly evenly distributed with open trip 3%+ more than the expenses incured by the closed trip which is also understandable due to the dataset which clearly says the total open trips are nore than the succesfully completed trips (closed trips).
The most expenses incured came from the year 2021 due to more bussiness active in the that year.
Road pilot Austin incurred the most expenses which is understandable because he travelled the most trips
This shows a breakdown of Loading Expenses, Union Expenses, Trip Allowance (in their respective bookmarks) by depot, destination, product type, trip status and driver's name.
This will enable the stakeholders drill through the revenue and notice the factors influencing their expenses.
The Loading Expenses, Union Expenses, Trip Allowance (in their respective bookmarks) by product type
This visual will make its easier for the decision makers to which product they are spending more on.
This shows the Loading Expenses, Union Expenses, Trip Allowance (in their respective bookmarks) by closed and open destination trips
This will enable the stakeholders know which of their trips have they spent more on.
This shows theLoading Expenses, Union Expenses, Trip Allowance (in their respective bookmarks per years
This will enable the stakeholders know which of the years they have spent more in.
This shows Loading Expenses, Union Expenses, Trip Allowance (in their respective bookmarks) by drivers
This will enable the stakeholders know which drivers have incurred the most cost.
The product that generate most revenue is the white product due to the fact that it the most moved product.
While revenue generated by the trip status is nearly evenly distributed with open trip 3%+ more than the profit generated by the closed trip which is also understandable due to the dataset which clearly says the total open trips are nore than the succesfully completed trips (closed trips).
The most profit generated came from the year 2021 due to more bussiness active in the that year.
I will recommend the trip allowance of the drivers with the most trips being increased because i found out that they weren't among the highest paid drivers.
This shows a year to date, quater to date and month to date visual of alll the categories of expenses
This will enable the stakeholders understand the how the expenses incurred are performing from a year to a date level
Which contains 5 main visuals and 3 slicers
- Year Slicer to filter between years
- The Product Type
- The Driver's name
This shows the total trip rate(revenue) vs total trip rate(revenue) in the previous year, total product moved vs total product moved in the previous year, total expenses Vs total expenses in the previous year
This will enable the stakeholders compare their growth and expenses between years
The company's most active year was 2021. Which lead to a reasonable expenses incurred, net revenue generated and a profit from the bussines in that particular year.
Most Product were moved with, more focus on white product
I will advise the company on looking for ways to market other product aside white.
Which might mean expanding to the locations where other products are in demand to prevent a total fold up incase white product goes out of demand.
Also, I will recommend the company to get more of her trucks aquilla registered to benefits from the subsidy provided by the government, Without forgetting to encourage her workers.
My finally recommendation to the company is that they should offer incentives such as a reduction in trip rate for returning clients in other to retain them.
