PROJECT BACKGROUND
This project analyses automotive sales data to understand sales performance, profitability, vehicle performance, and operational efficiency. The data was cleaned and transformed using Power BI before being analysed and presented through interactive dashboards.
BUSINESS OBJECTIVE
The objective is to provide management with clear insights into revenue, profitability, vehicle performance, logistics costs, payments, deliveries, and returns to support data-driven decision-making.
DATASET AND GRAIN
The dataset contains automotive sales transactions, including customer, vehicle, sales, payment, delivery, and cost information. The grain is one row per sales transaction/order, with each record representing a vehicle sale and its associated details.
DATA QUALITY ISSUES DISCOVERED
The dataset contained:
- Missing and null values
- Inconsistent categorical labels
- Invalid and inconsistent age values
- Mixed date formats and invalid dates
- Multiple currencies in financial fields
- Inconsistent payment and delivery status labels
- Inconsistent discount formats
- Duplicate-looking transaction IDs
DATA CLEANING APPROACH
1. Categorical fields
Missing, null, and invalid values were standardised as “Unknown” to retain the records while clearly identifying unavailable information.
2. Dates
Different date formats were standardised and invalid dates were identified. A cleaned date field was created for analysis.
Code used for both order and delivery date
v = Text.Trim(Text.From([Order Date])),
isSerial =
Text.Select(v, {"0".."9"}) = v
and Text.Length(v) >= 5,
result =
if isSerial then
try Date.AddDays(#date(1899, 12, 30), Number.From(v))
otherwise null
else if Text.Contains(v, "/") then
let
parts = Text.Split(v, "/"),
p1 = try Number.FromText(parts{0}) otherwise null,
p2 = try Number.FromText(parts{1}) otherwise null,
p3 = try Number.FromText(parts{2}) otherwise null
in
if p1 = null or p2 = null or p3 = null then
null
else if p1 > 12 and p2 <= 12 then
try #date(p3, p2, p1) otherwise null
else if p2 > 12 and p1 <= 12 then
try #date(p3, p1, p2) otherwise null
else
null
else
try Date.FromText(
Text.Replace(v, "Sept", "Sep"),
"en-US"
)
otherwise null
Enter fullscreen mode Exit fullscreen mode
3. Currency conversion
Due to inconsistencies and missing values in the Order Date field, current exchange rates were used to standardize foreign-currency transactions to KES. This approach ensured that currency conversion could be applied consistently across the dataset without introducing assumptions about missing transaction dates.
Used (26/09/2026) transaction rates to convert currencies to KES
USD to KES = 129.54
EUR to KES = 147.71
ZAR to KES = 7.95
Financial values were separated into amount and currency, then converted to KES using the applicable exchange rate. For example, the Unit Selling Price column was cleaned by extracting the numeric amount, identifying the currency, applying the exchange rate, and creating a standardised Unit Selling in KES field.
To obtain Currency Column:
v = Text.Trim(Text.From([Unit Cost]))
in
if Text.StartsWith(v, "USD") then "USD"
else if Text.StartsWith(v, "EUR") then "EUR"
else if Text.StartsWith(v, "ZAR") then "ZAR"
else if Text.StartsWith(v, "R ") then "ZAR"
else if Text.StartsWith(v, "$") then "USD"
else "KES"),
Enter fullscreen mode Exit fullscreen mode
To obtain Amount Column
v = Text.Trim(Text.From([Unit Cost])),
noCurrency =
if Text.StartsWith(v, "USD") then Text.Trim(Text.AfterDelimiter(v, "USD"))
else if Text.StartsWith(v, "EUR") then Text.Trim(Text.AfterDelimiter(v, "EUR"))
else if Text.StartsWith(v, "ZAR") then Text.Trim(Text.AfterDelimiter(v, "ZAR"))
else if Text.StartsWith(v, "R ") then Text.Trim(Text.AfterDelimiter(v, "R"))
else if Text.StartsWith(v, "$") then Text.Trim(Text.AfterDelimiter(v, "$"))
else v,
noCommas = Text.Replace(noCurrency, ",", "")
in
if Text.EndsWith(noCommas, "M") then
Number.From(Text.BeforeDelimiter(noCommas, "M")) * 1000000
else
Number.From(noCommas)),
Enter fullscreen mode Exit fullscreen mode
Replaced the currencies with the appropriate rates and multiplied by the “Amount” Column to obtain “Unit Selling in KES”. The same was applied across other monetary columns.
4. Discounts
Mixed discount formats, including percentages and decimal values, were standardized into a consistent percentage format.
Code used
v = Text.Trim(Text.From([Discount])),
hasPercent = Text.EndsWith(v, "%"),
cleanValue = Text.Replace(v, "%", ""),
n = Number.From(cleanValue)
in
if hasPercent then
n / 100
else if n >= 1 then
n / 100
else
n),
Enter fullscreen mode Exit fullscreen mode
5. Financial calculations
Due to inconsistencies in Revenue Values, cleaned revenue and cost fields were used to create calculated measures for revenue, gross profit, and gross profit margin.
Revenue = (Unit Selling in KES * Units Sold) * (1 – Discount Clean) + Delivery Fee
Gross Profit = Revenue – (Unit Cost in KES * Units Sold) - Logistics Cost
Gross Profit Margin = Gross Profit/ Revenue
Enter fullscreen mode Exit fullscreen mode
6. Order IDs
IDs were trimmed and standardized to a consistent format, while duplicate-looking IDs were retained for further investigation rather than automatically removed.
ASSUMPTIONS
1. Revenue
Calculated revenue was used instead of the original revenue field because the source values were inconsistent.
2. Currency
Financial values were converted to KES using the applicable exchange rates available during the analysis.
3. Missing values
Missing or unusable categorical values were classified as “Unknown” rather than removing the records.
4. Transactions
Duplicate-looking Order IDs were retained for further investigation rather than automatically removed.
5. Profitability
Gross profit was calculated as revenue less Units Cost and logistics costs.
DASHBOARD DESIGN
The solution was structured into three interactive dashboards:
- Management Dashboard: Provides an overview of sales, revenue, profitability, trends, geography, sales representatives, and lead sources.
- Vehicle Performance Dashboard: Analyses vehicle and model performance using revenue, units sold, profitability, and logistics cost relative to revenue.
- Operations & Customer Performance Dashboard: Examines payment, delivery, returns, customer type, branch, and regional performance.
Interactive slicers were included to allow management to filter and investigate the data by relevant business dimensions.
KEY FINDINGS
1. Overall performance
The business sold 452 vehicles, generating approximately KES 1.94 billion in revenue and KES 532.11 million in gross profit, resulting in a 27.44% gross profit margin.
2. Declining performance over time
Revenue and gross profit show an overall downward trend from January 2025 to January 2026, indicating an area requiring further investigation.
3. Geographic performance
Central and Rift Valley were the leading regions, while Nyanza, Western, and Nairobi also performed strongly. Mt. Kenya and Mombasa recorded comparatively weaker performance.
4. Vehicle and profitability performance
Toyota generated the highest revenue and gross profit. Volkswagen recorded the highest gross profit margin despite only 17 units sold, while Toyota maintained a strong margin across 137 units. Isuzu recorded a negative gross profit margin of -9.35%, with several models, including C*R-V, Axio, Corolla, N-Series, and Navara*, also recording negative margins.
5. Sales channels
Website and walk-in channels generated the strongest lead performance.
6. Delivery and payment performance
47.57% of vehicles were delivered, while 14.60% were in transit, 12.17% were at yard, and 13.05% were cancelled. Only 51.33% of transactions had completed payment. M-Pesa was the most-used payment method, followed by banker’s cheque and cash.
RECOMMENDATIONS
1. Improve delivery and payment follow-up
Monitor vehicles in transit, at yard, and cancelled transactions, while strengthening follow-up on incomplete payments.
2. Strengthen high-performing sales channels
Continue investing in website and walk-in channels while analysing their conversion and profitability.
3. Review loss-making vehicle models
Investigate the pricing, cost structure, and payment status of Axio, CR-V, Corolla, Navara, and N-Series, which recorded negative profit margins. These models also had 35.42% of transactions pending payment and only 33.33% completed, suggesting that payment follow-up should be prioritised alongside profitability review.
4. Review regional performance
Investigate the factors behind weaker performance in Mt. Kenya and Mombasa and identify opportunities to improve sales in these regions.
5. Investigate the declining trend
Review the factors contributing to the decline in revenue and profit from January 2025 to January 2026, including sales volume, pricing, discounts, and costs.
CHALLENGES
1. Data quality
The dataset contained missing values, inconsistent categories, invalid dates, unusual values, and duplicate-looking transaction IDs.
2. Currency inconsistencies
Financial fields contained multiple currencies and inconsistent formats, requiring conversion and standardization to KES.
3. Date inconsistencies
Dates were stored in different formats, including invalid dates, making time-based analysis more challenging.
4. Financial inconsistencies
The source revenue values did not consistently align with the underlying sales, discount, and cost fields, requiring calculated revenue and profitability measures.