Power BI – ABC Automotive Ltd. (Part 1 – Access Database)

Having got the hang of #POWERBI I have decided to combine my knowledge of the Automotive sector with my newly acquired knowledge of Microsoft’s Power BI.

However, before I could start to develop reports I needed to have a source of meaningful data for Power Bi to report on.  This data could not be “live” data from an Automotive Company so I had to develop my own representative “demo” database for a fictitious company, ABC Automotive Ltd., based on my understanding of the Automotive sector.

To this end, I have developed a MS Access 2016 database that can dynamically generate a portfolio of 100,000+ Automotive Finance agreements.  The database is very comprehensive and can be weighted to mirror the finance portfolio of any Automotive Finance company.

The “demo” portfolio can be weighted according to the following criteria:

1. Vehicle Manufacturer (Audi, BMW, Honda etc.)
2. Vehicle Type (Car, Bike, Van etc.)
3. Vehicle Status (New, Used)
4. Vehicle Fuel Type (Petrol, Diesel, Other)
5. Vehicle Categorisation/Segmentation (Private/B SUPERMINI 5DR Hatch etc.)
6. Vehicle Registration Date (to cater for the two annual peaks in the UK) 
7. Finance Product Type (HP, PCP, LP)
8. Financial Data (vehicle cost, deposit, monthly payments, arrears, residual values)
9. Agreement Status (Financed, Closed, Litigation, Arrears etc.)
10. Agreement Start Date (to cater for the two annual peaks in the UK) 
11. Geographic data (Sales Networks, Customers, Assets etc.)
12. Customer Demographics (Age, Family Status, Gender, House Owner etc.)
….. and other criteria

For example, it would be possible to create a “demo” portfolio for a particular Manufacturer Finance Company that contains 95% of agreements for their own vehicles and 5% for other manufacturers vehicles with 5% of agreements being in arrears and a defined Finance Product split.

Alternatively, it would be possible to create a “demo” portfolio for a non-Manufacturer Finance Company that contains a more even distribution of agreements for the cars of all manufacturers with 15% of agreements being in arrears and a majority of Hire Purchase agreements. 

The version that I will be using to create the Power BI reports that will be documented in later blogs will contain an distribution of manufacturer vehicles that match recent registration statistics, with 80% PCP products, 10% arrears and an even distribution of Vehicle Registration and Agreement Start dates.

The “demo” database will continue to be added to with the next major addition being the generation of cash flows ….. not going to be easy as it will depend on randomising payments (but not too much) for randomised agreements 🙂

It would have been possible to create most of the database using the ETL functionality and the embedded “M” language in Power BI Desktop but it was quicker for me to create it in MS Access 2016 (decades of experience) and not in the desktop (one years’ experience). 

ABC Automotive - MS Access Database
Image 1. MS Access creation of the “demo” database

This blog entry will be followed, in the very near future, by the following:

Part 2 – Power BI reports for the “Financial Services Division” of ABC Automotive Ltd.
Part 3 – Power BI reports for the “Financial Services Division” of ABC Automotive Ltd. (focussing on geographical information for Dealer Network, Customers & Assets)

Part 4 – Power BI reports for the “Sales Division” of ABC Automotive Ltd.

Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com



Leave a Reply

Your email address will not be published. Required fields are marked *