JCARS LOGISTICS SALES AND PERFORMANCE ANALYSIS

Lynne Chanzu — Wed, 30 Sep 2026 18:36:13 +0000

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

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"),

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

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

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

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.

UNDERSTANDING THE GIT WORKFLOW

Lynne Chanzu — Fri, 21 Aug 2026 21:07:10 +0000

Git is a version control system. Version control, also known as source control, is the practice of tracking and managing changes to software code. Version control systems are software tools that help software teams manage changes to source code over time.
Git is used for:

Tracking code changes
Tracking who made changes
Coding collaboration

Setting up a new Repository

A Git repository is a folder that Git tracks for changes.
The repository stores all your project's history and versions.
Add files to the folder.
The following describes how to set up a new repository:

Git Init

Initializes git
user@localhost $ git init
This creates a hidden folder called .git inside your project. This is where Git stores all the information it needs to track your files and history.

To see which files are in your project folder, use the ls command:
user@localhost $ ls

To Check if Git is tracking your new files:
user@localhost $ git status
The files here could either be tracked or untracked:-

Untracked Files

Files you've created or copied into the folder, but haven't told Git to watch.

Tracked Files

Files that Git is watching for changes.
To make a file tracked, you need to add it to the staging area.

Git Staging

Tells Git exactly which files you want to include in your next commit.
user@localhost $ git add .

Common Commands

git add .
Stages all new, modified, and deleted files in the current directory and its subdirectories.
git add <file>
Stages a specific file.
git add -A (or --all)
Stages all changes across the entire repository, regardless of your current folder location.
git add -u
Stages modifications and deletions of already-tracked files, ignoring completely new (untracked) files.
git add *.txt
Stages all files matching a specific pattern (e.g., all text files).

Git Commit

A commit is like a save point in your project.
It records a snapshot of your files at a certain time, with a message describing what changed.
user@localhost $ git commit -m " Describe your changes"

Pushing Changes

After you commit, your changes are only in your local repository.
Pushing sends your commits to a remote repository like GitHub.
user@localhost $ git push

Over 70% of developers use Git! Git does not store a separate copy of every file in every commit, but keeps track of changes made in each commit! For best use case, commit often and write clear commit messages.

DEV Community: Lynne Chanzu