Haulage-Analysis

Introduction

Haulage Analysis for a fictional haulage business.

Project Name: Haulage Analysis (Deriving insights From Data)

homepage
image source: google.com
all rights belongs to the orignal creator

Problem Objective

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.

Tools used in actualizing the project

Power Bi
Excel
Notepad

Data Sourcing

The dataset was provided by my mentor
Sulaiman Lukman

The dataset includes 5 excel sheets;

Data Transformation & Modeling

Data Cleaning & Transformation

- Step 1

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.

applied steps 1

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.

Note

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)

- Step 2

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:

- Step 3

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

Data Modeling

I enabled many to one relationship and cross filter direction from the destination table (fact table) to other tables using their respective keys.

model

Findings and Recommendations

1. Starting From the Truck Section

Which contains 5 main visuals, 6 cards and 3 slicers

The cards shows:

The slicers shows:

Now to the Visuals

Findings

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.

Recommendation

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.

Trucks Section

2. The Trip Section

Which contains 4 main visuals, 3 cards and 3 slicers

The cards shows:

The slicers shows:

The Visuals

Findings

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.

Recommendation

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

Trips Section

3. The Drivers Section

Which contains 4 main visuals, 8 cards and 3 slicers

The cards shows:

The slicers shows:

The Visuals

Findings

The dataset provided gave only details(name) of drivers whom have successfully completed a trip (closed trips).
In the bridging section

In the West Local section

In the Local section

Recommendation

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.

Drivers Section

4. The Product Section

Which contains 5 main visuals, 1 cards and 2 slicers

The cards shows

The slicers includes:

The Visuals

Findings

The most moved product is the white product.
Lesser product are on the road compared to the successfully delivered ones

Recommendation

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.

Product Section

5. The Revenue Section

Which contains 5 main visuals, 1 cards and 2 slicers

The cards shows

The slicers includes:

The Visuals

Findings

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

Recommendation

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.

Revenue Section

6. The Expenses Section

Which contains 5 bookmarks, 5 main visuals, 1 multi cards and 2 slicers

The multi-cards shows

The slicers includes:

The Bookmarks

- The Visuals for Bookmark(2)

Findings

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

- The Visuals for Bookmark(2,3,4)

Findings

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.

Recommendation

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.

- The Visuals for Bookmark 5

7. The Yearly Analysis Section

Which contains 5 main visuals and 3 slicers

The slicers includes:

The Visuals

Findings

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

Recommendation

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.

yearly analysis

Link to live Report