Introduction
JCars Logistics is a company that imports, sells, and delivers vehicles to customers across different regions in Kenya. The company generates data from various business activities, including sales transactions, vehicles, customers, branches, sales representatives, payments, deliveries, logistics costs, returns, cancellations, and customer experiences. This project focused on transforming the company’s raw flat dataset into a reliable and interactive Power BI business intelligence solution. The data was first examined to identify quality issues such as missing information, inconsistencies, unusual values, and formatting problems. The cleaned data was then prepared and modeled to support meaningful analysis, calculations, visualizations, and management reporting. The final solution provided insights into business performance and areas requiring attention, supporting evidence-based decision-making by JCars Logistics management.
Data cleaning
JCars data was imported into Power Query by transforming data. The column headers were then promoted, and the original column order was retained.
Cleaning text values
Text fields were cleaned by trimming the columns to remove spaces , invalid entries such as blank values, N/A, NA, null, missing, unknown and were converted to null values. Find and Replace was used to correct inconsistent spellings. For example, variations such as cental were replaced to Central, Cost to Coast, Nbi or Nrb to Nairobi, and Westen to Western. Similar corrections were made to counties, cities, branches, sales representatives, lead sources, vehicle makes, and vehicle models. Customer types were standardized so that different terms referring to the same category were grouped together. For example, person, retail, and individual were standardized as Individual, while corp and company were standardized as Corporate. Similar standardization was applied to payment methods, payment statuses and delivery statuses. Vehicle related fields were cleaned to ensure consistent naming. Different spellings and abbreviations were converted into standard vehicle makes and models. For example, Toyta and Totoya were standardized to Toyota, Nisan to Nissan, Subru to Subaru, and Corrola and Corola to Corolla. Vehicle types, fuel types, transmission types, and colours were also standardized. Missing county and region values were filled using information already available in the dataset, Where the county was missing, it was obtained from the corresponding branch. For example, Kisumu Yard was mapped to Kisumu County. Missing region values were then obtained from the county, such as Kisumu to Nyanza and Mombasa to Coast.
Cleaning numerical data
Numerical fields were converted into appropriate numeric data types and checked against reasonable ranges. Customer age was restricted to be from 18 to 100 years, vehicle year was 1990 to 2026and units sold also , Values outside these ranges were treated as invalid. Discount values were converted into a consistent percentage format using power BI after closing and applying from power query using the column tools. Values containing % or the word percent were interpreted as percentages. Entries that were written as texts were converted into numerical format . Unrealistic values were treated as null. Customer ratings were standardized to numerical values between 1 and 5The Order Date and Delivery Date columns were converted into proper date formats. some different date formats were handled manually, formats such as 26-Mar-25and Aug 29, 2025. Monetary values were examined to identify the currency used. The cleaning process recognized KES, USD, EUR, and ZAR values and removed currency symbols and unnecessary text from the amounts. Values containing an M suffix were also interpreted as millions.
Converting foreign currencies to Kenyan Shillings
To make all financial values comparable, foreign currencies were converted into Kenyan Shillings (KES). The exchange rates used were 1 USD = 130 KES, 1 EUR = 150 KES, and 1 ZAR = 7.5 KES, while KES values remained unchanged. The conversion was applied to Unit Selling Price, Unit Cost, Delivery Fee, Logistics Cost, and Revenue Recorded.DAX formula was used to convert other currencies to kenyan shillings in POWER BI
Amount in KES =SWITCH('JCars Data'[Currency],"KES", 'JCars Data'[Amount],"USD", 'JCars Data'[Amount] * 130,"EUR", 'JCars Data'[Amount] * 150,"ZAR", 'JCars Data'[Amount] * 7.5,BLANK())
categorical fields were stored as text, numerical fields numbers, discounts as percentages in power BI, dates as date values using locale in power query, and monetary fields as currency values in power query.
Data modelling
This step was done in power query to establish a one to many relationships between the facts and dimensional tables. The four dimensional tables that used to form a star schema model view were;
dim_location(Branch, city ,county ,region and Location ID)dim_customers(Customer age, customer type, customer name, Customer ID)dim_salesrep(Sales rep ,sales rep ID)dim_vehicle(Car make, car model, colour, transmission ,fuel type, vehicle ID, Vehicle type ,vehicle year)
The IDs were generated by adding an index column from 1 after removing duplicates in order to obtain primary keys that were necessary for the facts table to create the required relationship. To create dimensional tables the jcars data table was referenced four times and renamed to a relatable name, Unnecessary columns were removed to obtain the required columns in each dim table. The original table was replaced to be the facts table. The facts tables was merged to the dimensional tables in order to obtain primary keys that were the IDs from the dimensional table. The facts table contained all the transactional columns ,primary keys and every other column that was not in the dimensional table
Dashboard building
The dashboard was built to anwser insightful bussinees questions through visualizations and some DAX functions .DAX functions were used to generate gross profit, gross profit margin and net profit by adding a new measure .The gross profit ,gross profit margin and net profit were geneerated by the following DAX functions in order to obtain KPIs:
gross profit =(([units sold] * [unit selling price]) * (1 - [discount] )) - ([units sold] * [unit cost])
gross profit margin=[Gross Profit] / (([units sold] * [unit selling price]) * (1 - [discount))
Total net profit = [Total gross profit]-sum('Jcars_data facts table'[Logistics Cost])
Business insights and reccommendations
Overall business performance:
The business recorded a total revenue of KSh 1.39 billion from 255 cars sold. It generated a total net profit of approximately KSh 362 million and a gross profit of KSh 387.63 million, indicating strong overall sales and profitability.Car make performance:
Toyota recorded the highest total revenue among the car makes shown on the dashboard. This indicates that Toyota vehicles contributed significantly to the overall business revenue compared with the other brands.Branch performance:
Nakuru recorded the highest revenue among the branches displayed. This suggests that Nakuru was an important contributor to the company's overall sales performance, while other branches recorded comparatively lower revenue.City profitability:
Thika recorded the highest gross profit, followed by Nairobi. This shows that the cities differed not only in sales revenue but also in the amount of profit generated from their sales.Fuel type performance:
Petrol vehicles accounted for approximately 81.66% of the total revenue, making petrol the dominant fuel type in the dataset. Diesel and hybrid vehicles contributed considerably smaller shares of revenue.Transmission performance:
Manual vehicles generated approximately KSh 770 million in revenue, compared with approximately KSh 620 million from automatic vehicles. This indicates that manual vehicles generated a larger share of the recorded revenue.
Business Recommendations
Focus on high performing car makes:
The business should maintain adequate stock of high-performing brands such as Toyota because they contributed significantly to total revenue. Sales and profit margins for other brands should also be monitored to guide future inventory decisions.Learn from high performing branches:
Management should examine the factors contributing to the strong performance of Nakuru and Thika, such as customer demand, pricing, vehicle availability and marketing strategies. Successful practices can then be considered in other branches.Improve lower performing branches:
Branches recording lower revenue should be analyzed to identify possible challenges such as low customer demand, inappropriate vehicle selection or pricing issues. Targeted marketing and improved inventory selection could help improve their performance.Maintain appropriate fuel type inventory:
Since petrol vehicles generated the largest share of revenue, the business should continue meeting demand for petrol vehicles while monitoring the growing demand for diesel and hybrid vehicles to ensure that inventory reflects customer preferences.Monitor profitability alongside revenue:
Management should not rely on revenue alone when evaluating performance. Gross profit and net profit should also be analyzed for each car make, city and branch to identify areas that generate the highest financial returns.Monitor transmission ;
Since manual vehicles generated more revenue than automatic vehicles, the business should ensure sufficient availability of manual vehicles while continuously monitoring automatic vehicle demand for changes in customer preferences.
Conclusion
The JCars dashboard provided a clear overview of the company’s sales performance, revenue, and profitability. The analysis showed that the business recorded KSh 1.39 billion in revenue, 255 orders, 415 cars sold, and approximately KSh 362 million in net profit, with Toyota, Nakuru, petrol vehicles, and manual transmission contributing significantly to the recorded performance.
Overall, the dashboard provided useful insights that can support better inventory planning, branch performance improvement, and profitability management. By monitoring high-performing and lower-performing areas, JCars can make informed decisions aimed at improving sales and maintaining sustainable business performance.
Top comments (0)