If you're like me, a multi-dealership executive manager, you need data. You count on your sources in the dealership to provide you data. Most often your only source is the accounting office; but let's face it, most dealership accounting offices are not the GAO. So we get it where we can.

In the single point auto dealership there is usually not the expertise to access, analyze and/or vet the data, let alone build a data warehouse. However, in larger stores and in multi-point operations we sometimes see either Data Analysts working for the Controller or Group Controller, or the Controllers themselves staying on top of advances in business intelligence data and reporting. They do this to do their jobs better and to support executive managers such as the UVD, FID, FOD, CFO, COO, & CEO.

Very rarely will one find a Business Manager (automobile dealership title for Chief Accountant) that has acceptable Microsoft Excel skills let alone the ability to use new self-service BI tools such as PowerPivot. That is not a criticism because there are many a CPAs, Group Controllers and Controllers that I have found in the same condition. It just has not been a priority.

In fact, there are many executive managers that don't see a need. They don't see a need to understand completely what goes on in their organizations and to ferret out opportunities to improve or build competitive advantage. Untrained and inexperienced executive managers often respond to needs to assess situations through data analysis by asserting that such methods would be "too complicated". In other words, if the dumbest guy in the room (them or another) can't understand it, they don't want to talk about it. Rather than bring their people up, they bring the organization down.

I have always said with enthusiasm, when the public companies get involved in our business, then the relatively unsophisticated dealership will use these methods or suffer a disadvantage. Those days are here.

I have chosen to work primarily with the CDK Global DMS (dealership management system) and currently limit my involvement to dealerships who use it. CDK Global has a very extensive data model and access to it has become, of late, very robust. There are very smart people at CDK Global who I have had the pleasure of working with over the years including Jerry Farnsworth, Bill Schwab, Mark Roman, Gary Thompson, and Mark Kryzwonos and many more who know the value of this data and are getting access in the hands of people who need it. They know and understand the importance of the dealer getting open access to his data unlike other DMS providers in the arena.

Not many dealerships or dealership groups have the budget for business intelligence (I want to meet those who do) and I am not going to argue for one. This post is about assigning a project to your Controller to setup the "Poor Man's" BI solution. Basically, you want your Controller to be able to use interactive BI reporting to answer all the operational questions in all departments. And, besides the CDK Global data model, you also want him or her to start keeping all the manually collected data in your new data warehouse as well.

If your dealership(s) are not using Office 365, SharePoint or SharePoint Online, I suggest you email me (see below). I am a Microsoft Partner and I can show how such services can solve most of your IT issues and improve the operating results (profits) of your stores. SharePoint is perfect for reporting information (ranking etc) to keep competition alive in the dealership. I will show you how.

What I am about to tell you could always be done with CDK Global (ever since the late 1980s). However, a "Flat File" repository was never very accessable using Microsoft Excel, nor was the end product very presentable. Microsoft has included with Excel 2013 three key new tools; PowerQuery (for the accessability), PowerPivot (for the analysis) and PowerView (for the presentability). Now your people can handle data in flat files as deftly as they can a full fledged database.

The idea is to create a share folder with all these flat files whether CDK Global originated or manually gathered and use this "repository" as a data source to create a data warehouse in the Excel data model. So as an executive manager here is what you want to do:

1. Tell your Controller to setup a "Flat File" repository of all the major tables available in CDK Global's data model. This includes (but is not limited to):
   1. All Car Deal Sales Data (F&I)
   2. All Vehicle Inventory Data
   3. All Service Sales Data
   4. All Parts Sales Data
   5. All Parts Inventory Data
   6. On Demand Check Data
   7. All General Ledger Transaction Data
   8. Payroll Data
2. They can do this using CDK Global's Report Generator Extract (RPX) function and setting up unattended downloads nightly (hourly?) to keep the data current. These downloaded files (csv) are kept in the repository.
3. Tell your Controller to create Excel Workbooks using PowerQuery as the ETL (extract, transform and load) agent as it specifically accepts flat files as data sources. They then create a PowerQuery query to ETL each of the flat files downloaded into the Excel data model in an Excel workbook that will be your data warehouse. (Note: They will need a new PC with 32 GB of RAM, an i7 processor with a large SSD and a large hard drive)
4. Tell your Controller then to join these tables that are now in the Excel data model so that they are no longer isolated "Flat Files" but rather tables that have relationships in your new Excel based data warehouse (tabular model).
5. Tell your Controller then to do the same ETL using PowerQuery (in the same workbook) with manually collected data (e.g., daily sales logs) and create relationships to incorporate that information in your new Excel based dealership data warehouse.
6. Tell your Controller to start exploring the data warehouse and getting familiar with the relationships so they can be your go to for business intelligence.

If you need more information, don't hesitate to contact me at jdonnelly at jdonnelly.com (turn that into an email address).