Data at Your Fingertips
Business Intelligence helps you manage profit.
Having real-time data supports good decisions by operations management.
It is also useful to executive management to understand financial trends.
Car Sales
The following analysis is of 233 Thousand automobile sales from a free CSV dataset of automotive sales provided by Kaggle. I used Power BI to produce these reports based on that data.
First, Please Try a Few Things to Interact with this Data:
Filter by Make and Model
Navigate to the Seller Performance tab using the button at the top.
In the Sales by Make table, select ‘Ford’ and note that the Sales by Seller table and the Sales Details update to reflect solely Ford sales.
For more specific information, click the plus (+) sign next to Ford to expand different model types. Click F-150 to see data updated for Ford F-150 sales.
Review Seller Performance
Click ‘mercedes-benz financial services’ in the Sales by Seller table (7th from the top) and see the Sales by Make table change. Click it again to deselect and see all results again.
Note that when mercedes-benz financial services is selected, we see that they financed mostly Mercedes, but also some Smart cars. Details of each sale are in the table at the bottom.
Use the Comp Sales Tool
Navigate to Comp Sales. This is designed for Sales People to price vehicles.
Assume a BMW 128i is being priced. Expand the BMW models, 1 Series and Convertible to select 128i.
The Price Gauge shows this model sold between $10K and $36K and the average price was $21K.
The bottom table gives average prices with average odometer and conditions, all helpful for the sales person to set a thoughtful price.
The Story...
What can we learn from the data? Here is what I see.
Volume and Price Trends
The company began selling in Week 51 of 2014. Sales volume grew through Week 7 of 2015, then tapered off through Week 9.
Total Sales more than doubled from $230 Million in Week 51 of 2014 to $631 Million in Week 7 of 2015 before softening by 19% to $511 Million in Week 9.
BMW, Honda and Infiniti sales showed a recovery in Week 9.
Seller Data
Nissan-Infiniti appears multiple times in these tables. There are 7,573 sales by 'nissan-infiniti lt' and another 4,404 sales by 'nissan infiniti lt' (without the hyphen '-'). These should be combined.
Seller Performance
After combining them, Nissan-Infiniti Lt is the leading seller at $201 Million and Ford Motor Credit is second at $142 Million. Because Fords are sold by several sellers, Ford is the most sold vehicle at $570 Million followed by Chevrolet at $317 Million, and Nissan in third place at $262 Million.
Other Considerations
Quality factors, including odometer readings and average vehicle condition remained relatively stable during the first 9 weeks of 2015.
There has been no significant change in mix of automatic versus manual transmissions
Seller Performance
After combining the two rows related to Nissan-Infiniti ('nissan-infiniti lt' and 'nissan infiniti lt'), we see that this seller is the largest in both volume (11,977 sales) and in sales dollars ($201 Million).
The declining volume is generally consistent across vehicle makes and sellers, although there are a few exceptions:
- Sales volumes by Nissan-Infiniti remained strong between Weeks 7 and Week 9, with a slight increase in Week 8.
- Sales by AHFC/Honda Lease Trust increased in Week 9 to Week 7 levels after a 55% drop in Week 8.
- Enterprise Veh Exchange/Rental and GM Financial recovered volume after a Week 8 drop, but not to Week 7 levels.
- Most other sellers dropped further in volume in Week 9.
Data Cleanup
This data contains 4,720 sales missing the vehicle make. As the make is a fundamental part of this analysis, these sales have been omitted.
Similarly, 11,705 sales missing vehicle condition information has been omitted from this analysis.
Sales data prior to Week 51 of 2014 has also been omitted as these sales were immaterial (less than $10K).
It is critical to understand what data is excluded. Decisions should be made while seeing the full picture, meaning the data needs to be complete. Information not included in the starting dataset, or that has been filtered out, needs to be understood and quantified to ensure decisions makers make smart decisions.
Financial data, for example, should be reconciled to the financial statements and general ledger.
Use the button below to view another example Power BI project
Companies Data
About the Author
This was created by Shane Bohlender, CPA and MBA. I have seen Business Intelligence reports like these transform how businesses are managed with tangible bottom line improvement.
Some examples that I’ve seen particularly effective:
- Sales by customer, region, product and sales person, with comparisons to budget and KPIs
- Operating expenses by department with comparisons to budget and KPIs
- Freight cost comparisons to amounts passed along to customers
- Accrual details to support research and ensure completeness and accuracy
- Cash flow tracking with forecasts
- Dashboard for executive monitoring of financial results in near real time
- Company-wide KPI management
- Combining data from multiple systems to provide the full picture