Introduction
It can be actually satisfying turning a collection of rows and columns into something that has real meaning. The challenge, however, is that none of this is obvious, and many times the numbers have a few things to say before we can make sense of them.
For this assessment, I worked with a business dataset for JCars Logistics, using Microsoft Power BI to examine the company's financial and operational performance. The task was to build an executive dashboard, create detailed report pages, investigate business questions, identify meaningful patterns and provide recommendations that management could use.
As I got further into the work, I realized that creating the visuals was only one part of the assignment. The other part was learning how to look at the numbers, question what they were showing me and work out why any of it mattered to the business.
In this article, I take you through my experience, the analysis I conducted, some of the findings that stood out, and a few lessons I am carrying forward.
1. Preparing the Data
Before we can analyze data, we need to understand what we are working with. Raw business data is not always presented in a form that is immediately ready for analysis. Different tables may describe different aspects of the business, identifiers may be inconsistent, and values may require cleaning or standardization.
For this project, data preparation involved reviewing the source data, checking its structure and working towards a model that could support analysis across different business dimensions.
Cleaning involved correcting data types, replacing blanks with 0, null and N/A where necessary. For example, I learned later on when modelling and visualizing that having ‘null’ in the numeric columns created discrepancies during calculations. Because these are rows used for a lot of calculations, it is preferrable to use 0 where there are no records, or that contain unreconcilable errors. The text was trimmed and set to proper case.
In the Order ID column, which contained different prefixes such as LC,LCL, ORD required analysis and I concluded that LC and LCL were the same while ORD and CAR were totally separate orders and therefore I cleaned the column appropriately.
For the price columns i.e unit selling price, costs and discounts, as well as delivery fees, I cleaned the data according to the requirements – treating unnamed currencies as Kes. After cleaning, I used a conversion table with following rates;
Currency Rate to KES
KES 1
USD 129.3
EUR 146.1
ZAR 7.2
I proceeded to clean the rest of the text columns, reconciling differences of spelling, identifying duplicate records.
One of the important considerations was how the tables related to one another. The analysis needed to connect transactions with information about customers, vehicles and locations without introducing duplicate records or distorting the measures.
A relationship between tables might look perfectly reasonable in the model view, but if it causes a transaction to be counted more than once, the resulting visualization can be misleading.
I had to pay attention to how the tables connected and how those relationships affected the results shown in my report.
2. Building the Power BI Report
Once the data was prepared and modelled, I moved into building the report.
I approached the project using two levels of reporting: an executive overview and detailed analytical pages.
The executive dashboard provides a high-level view of business performance. It brings together the main financial indicators so that management can quickly see the overall picture without having to work through every individual transaction.
The detailed pages allow a closer look at specific areas of the business, including:
- Sales and revenue performance.
- Vehicle performance.
- Customer contribution and ratings.
- Sales representative performance.
- Branch comparisons.
- Transaction-level investigation.
The distinction between the two levels is important. An executive dashboard should help someone identify where attention may be needed. A detailed report should then help them investigate the pattern more closely.
A large number on a dashboard can tell us that something is happening. It does not necessarily tell us what is behind it.
3. Beyond the Dashboard
This was probably the most important part of the assessment for me.
Creating a visual is one thing. Interpreting it is another.
For example, suppose a branch generates the highest revenue. It might be tempting to conclude that it is the best-performing branch. But what happens when we compare its gross profit with that of another branch? What if it sells considerably more vehicles but generates less profit?
That is where the questions become more interesting.
Rather than looking at each figure in isolation, I began comparing measures across different business dimensions. I looked at revenue alongside gross profit, sales volume alongside profitability, and customer contribution alongside customer ratings.
The process helped me think more carefully about the difference between observing a pattern and explaining it.
If one branch generates more revenue than another, that is an observation. If the difference suggests that sales volume does not translate proportionately into gross profit, that is an interpretation. Explaining why the difference exists requires further investigation.
Those are three different stages of analysis, and learning not to confuse them is something I found particularly valuable.
4. What Did the Data Reveal?
Here are some of the findings that stood out during my analysis.
Overall financial performance
The executive dashboard reported the following figures:
| Measure | Reported value |
|---|---|
| Total revenue | KSh 1.8 billion |
| Total costs | KSh 1.49 billion |
| Gross profit | KSh 312 million |
These figures provided the starting point for understanding the company's overall financial performance. From there, I could investigate how revenue and gross profit were distributed across different vehicles, branches and sales activities.
Looking at the business as a whole is useful, but breaking the figures down helps reveal where the financial contribution is coming from.
Vehicle performance: Revenue is not the whole story
Toyota was the leading vehicle make by reported revenue, generating approximately KSh 200 million and KSh 123 million in gross profit.
Volkswagen generated KSh 159 million in revenue and KSh 122 million in gross profit.
What caught my attention was how close the gross profit figures were despite the difference in revenue.
Toyota generated approximately KSh 41 million more revenue, but only KSh 1 million more gross profit than Volkswagen.
Branch performance: More sales, less profit?
The branch comparison was another interesting one.
Thika generated KSh 368 million in revenue from 70 vehicles sold. Nairobi generated KSh 192 million in revenue from 24 vehicles sold.
However, Nairobi recorded KSh 113 million in gross profit, compared with Thika's KSh 102 million.
In other words, Thika sold substantially more vehicles and generated considerably more revenue, but Nairobi recorded higher gross profit.
This is a useful reminder that volume, revenue and profitability measure different things.
Sales representatives: Who contributes the most?
Grace Njeri led the reported sales representative comparison, generating KSh 316 million in revenue and KSh 140 million in gross profit.
Faith Achieng followed with KSh 277 million in revenue and KSh 89 million in gross profit.
The difference in gross profit was larger than the difference in revenue. This suggested that comparing representatives using revenue alone might miss an important part of their financial contribution.
Of course, the numbers do not automatically explain why one representative performs differently from another. Transaction values, vehicle types, costs and discounts may all influence the result.
Still, the comparison gives management a starting point for investigating the factors associated with stronger profitability and identifying practices that might be useful across the team.
Lead sources: The value of walk-in customers
Walk-in leads generated KSh 305 million in revenue and KSh 136.5 million in gross profit.
That is a substantial contribution and makes walk-ins worth examining more closely.
Customers: Revenue and experience
The customer analysis recorded 275 distinct customers.
Dealer customers generated KSh 453 million in revenue, followed by government customers at KSh 396 million.
Dealer customers also recorded an average rating of 3.83 out of 5, compared with the overall customer rating of 3.54 out of 5.
These findings raised two questions for me. First, how important are these customer categories to the business's overall revenue? Second, how does customer experience differ across the other categories?
The ratings provide a starting point, but they do not explain what customers liked or disliked. Comparing ratings across customer groups, branches and other available measures would help identify where further investigation might be useful.
5. From Findings to Recommendations
An analysis becomes more useful when the findings inform a possible course of action.
For this assessment, I focused on recommendations that followed from the patterns in the data without assuming that the underlying causes were already known.
First, investigate differences in profitability. The branch and vehicle comparisons suggest that revenue alone is not enough to understand financial performance.
Second, examine the transactions behind strong sales performance. Comparing the work of leading sales representatives at transaction level could reveal useful patterns in vehicle mix, pricing and gross profit. These patterns could inform coaching and performance evaluation.
Third, monitor customer contribution and experience together. Dealer and government customers represent substantial revenue segments. Monitoring their contribution alongside customer ratings could help management identify changes that deserve attention.
Fourth, compare lead sources using more than revenue. acquisition costs would help establish which channels offer the strongest financial contribution.
In Conclusion
This assessment gave me an opportunity to work through more than the technical side of Power BI. It challenged me to connect data preparation, modelling, visualisation and business reasoning into one process.
I am learning that the work does not end when the dashboard looks good. In many ways, that is when the more interesting part begins.
You have the figures. You have the comparisons. Now you have to work out what they mean, what they do not tell you, and what questions should come next.
As I continue developing my skills in data and technology, I want to get better at that entire process: not just presenting information, but making it useful.
One project at a time.
Tools used: Microsoft Power BI, Power Query, DAX and data modelling.
Top comments (0)