ABC Automotive (UK) Ltd. – Demo Database
Based on my recently acquired detailed knowledge of Microsoft Power BI I decided to combine my knowledge of the software with my knowledge of the Automotive and Financial Services sectors.
However, before I could start to develop reports I needed to have a source of meaningful data for Microsoft 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 (UK) Ltd., based on my understanding of the Automotive sector.
To this end, I have developed a Microsoft Access database that can dynamically generate a portfolio of 100,000+ Automotive Sales & 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 Microsoft 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.
It would have been possible to create most of the database using the ETL functionality and the embedded “M” language in Microsoft Power BI but it was quicker for me to create it in Microsoft Access (decades of experience) and not in the Microsoft Power BI Desktop (one years’ experience).
Image 1. Microsoft Access creation of the “demo” database
Here’s hoping that this was of interest. If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com
