An Effective Management Reporting System Communicating a Call to Action on Over-Age Inventory

On my other, more generic, Auto Dealership Group Management blog, I wrote a post a few days ago about dealership operations reporting. That post was an attempt to work through the different types of reporting media and to distinguish the delivery system as an important part of the whole reporting strategy. In this post, I will discuss an effective implementation of a reporting system that will reduce over-age used vehicle inventory by month end. This implementation will create the report and deliver it automatically with CDK, PowerShell and Windows Task Scheduler, without any human intervention, month in and month out.

The reporting system I will be using in this project is HTML and the presentation (delivery) system will be the body of an Email. It is important to distinguish between management reporting using attachments to email (not effective) and management reporting in the body of an email.

Developing a call-to-action reporting project requires the issuance of multiple reports starting mid-month and continuing at ever increasing intervals until the final report is issued on the last day of the month. The process is to educate all concerned on the current situation (on the 15th etc.) by letting them know exactly which used vehicles will be considered over-age at the upcoming month end and how much of a write-down will be charged against gross profit if they are not sold. The objective is not to have any over-age inventory or, if there are some, used vehicle department gross will be charged with a write-down based on the auto group’s policy.

In this example, the report will calculate a charge to the dealership’s used vehicle department gross for 5% of a vehicle’s cost after 60 days, another 10% at 90 days and another 15% for each 30 days thereafter. If a manager kept a car 270 days it would be completely written off. It is a rare case (extremely high-line cars etc.) when a car is kept over 120 days.

Usually a major market dealer has little problem with a mandatory 60 day turn but small market dealerships will keep good cars longer in anticipation of the right customer.

Process Design

The entire process will be driven using the very powerful scripting language from Microsoft, PowerShell.

The dealership group I created this for is a CDK dealer which makes access to the data wonderfully easy. As I have mentioned in my previous blog posts there are three ways to get data from CDK by dealers:

  • Using RPG (RPX etc) aka Report Generator.
  • Using DDA (formerly MDA) – The daily (sometimes more often) updated PostgreSQL database mirroring the dealership(s) data.
  • Using DDRT (formerly DAP) – The API that gets you the data real time, quickly and easily.

For the data I will use a DDA instance using an PostgreSQL ODBC connection. I will write a companion post on my blog here if there is any interest (leave a comment) that will go into more of the technical details of the data access and script.


  • I have written a SQL statement to extract the used vehicle inventory from ed.inventoryvehicle_v and join it up with ed.vehiclesales_v to allow the report to show any sales in process for the units. The CDK documentation indicates that the ed.inventoryvehicle_v view can be set to update as frequent as every 5 minutes and ed.vehiclesales_v every hour.
  • Having the pending sale information in the report informs everyone that there is a pending deal and a correspondingly good chance the car will be sold by month end. I designed the report to inform the reader of the customer name, status in F&I and the date sold off to the right of the vehicle information.
  • The PowerShell script calculates a qualifying date which, if the entrydate field in the ed.inventoryvehicle_v record is less than, indicates the car will be at least 60 days old at month end. In addition to the qualifying date, the SQL statement should not extract used vehicles that would not be written down or considered over-age at the 60 day mark. For example, used Lamborghini’s sometimes sit for a year before being sold so would you write them down at the 60 day mark. Also one would want to filter out (exclude) any used vehicles with a cost under a certain threshold such as $3,500. The SQL statement filter (WHERE clause) can be as fine-tuned as it needs to be.
  • The HTML Email body will need to display well on a phone or tablet so I was careful to use HTML and CSS to make sure that happens.
  • I designed the report to be delivered 5 times in the month starting on the 15th, 20th, 25th, and 28th with the last report issued on the last day of the month. These can be set to whatever makes sense for the group. I add to the distribution list for the last report to include the Business (Office) Managers so they could record a journal entry to book the write-down amount.
  • Each day the script runs, if

    is in the list of day numbers (with a special calculation to get the day number of the last day of the month) it sends the SQL statement to DDA and creates the report.

  • Each vehicle from the returned data is analyzed and two new properties are calculated based on the write-down policy the auto group has implemented. The current month cumulative write-down amount which is then used to calculate the current month write-down amount (if the vehicle isn’t sold before month-end). In the event the vehicle had a previous write-down one has to add that back prior to calculating the current write-down.
  • The PowerShell script will need to have custom objects to hold much of the constant variables by dealership. Here is a sample of the object and the constant properties:

Report Design

The report is one continuous page of all the dealerships in the group. If a store has no inventory that is at risk of becoming over-age at month end the table simply says “No Over-Age Inventory anticipated! Great Job!”

The first report for the month looks like the following image. I obscured the logo, store and customer names but each report that goes out during the month has the following sections:

  • Email subject line “CountDown to Zero Over-Age Used Vehicles…” – This not only tells them what the email is about but also the goal “Zero Over-Age Inventory” and the urgency “Count Down”.
  • The blue writing is a message that may or may not change depending on the day the report is sent as follows:
    • 15th – We’re half way through the month. Below is your at-risk used inventory. These vehicles will be 60 days or older at month end.
    • 20th – It’s been 5 days. Below is your at-risk used inventory. These vehicles will be 60 days or older at month end.
    • 25th – It’s been 5 days. Below is your at-risk used inventory. These vehicles will be 60 days or older at month end.
    • 28th – This is the 2nd to last report for the month. [Insert name of CEO/GM here] gets a copy of the next one. Avoid the write-down.
    • Last Day of the Month – It’s the last day of the month. Hopefully your list is down to zero. Finalize the deals today to avoid a write-down.

The date changes and the red numbers change to show the days remaining.


As I mentioned above I will be writing a more technical article to go through the key parts of the script if there is any interest. Let me know in the comments if so…



Start Collecting Your Dealership’s Data So You Can Understand & Improve Your Store’s Results

A post to describe how you can maximize your store’s performance.

What I am about to explain to you will change your life if you are at all frustrated with your store’s performance?

It may sound strange but usually performance issues arise from a lack of feedback to the workgroups contained within your operations. I use the term workgroups because there are numerous groups of employees in your dealership that need feedback to perform optimally. These groups of employees (workgroups) include:

  • An Individual
  • A Team – (usually front end sales teams but this can include techs and other employees).
  • A Department
  • A Store
  • Same stores – a workgroup important for comparing Year over Year (YoY) results.
  • A Division – ( a group of stores by franchise or geographical location)
  • A Region – ( usually geographical)
  • A Country (if you have stores in different countries)

How would you like to improve the profitability of all your store or stores right now?

We will need to use your data from your DMS (Dealership Management System) extracted properly for analysis and presented in a way that ranks your workgroups. The following SharePoint Online interactive report has a tab that ranks the performance of two workgroups; stores and individual F&I Managers:


The premier business data presentation platform, SharePoint Online, is free with the Enterprise Office 365 subscriptions. With SharePoint Online you can control who is able to see each individual report based on their logon. Those who don’t have permission will not even see the report link on the site.

Feedback is extremely important if you want your employees to improve. If your employees don’t get feedback they will either:

  • Get the data on their own or ask for it or,
  • Just maintain the status quo (under-performing) or,
  • In all cases, leave your employ.

The sad part is, the good ones leave (because they are not recognized) along with the not-so-good (because they are not motivated).

We need to take advantage of a person’s or workgroup’s natural competitive instincts!

Critical to reaping the performance improvement that feedback can create is ranking the workgroups. Feedback can also be fine using objectives but that doesn’t get the competitive juices flowing. We have to rank them!

In my experience, when you rank workgroups they all rise, even the bottom individual or workgroup. That’s because everyone wants (on some level) to do good.

There are three steps to making this happen:

  1. Automate the Collection of the Data.
  2. Train your CFO and Controllers on how to use the latest tools to analyze the data efficiently
  3. Train your CFO and Controllers on how to create reports with the latest tools and publish them to SharePoint and Office365.

I have a lot of experience with CDK, InfinitiNet and DealerTrack and so that is what I will stick with here. You can access your data on these platforms but I am not so sure about Reynolds anymore.

Step 1:

To automate the collection of your data all you really need is an ODBC connection, some knowledge of the DMS data model and a subscription to Microsoft Azure. You would then ask someone like me to write a PowerShell script to run on your server to extract the data from the DMS at least daily and move it to a SQL Azure database (or on-premise).

You can skip this step if you are on CDK and you have signed up for their wonderful MDA (Managed Data Access) or DDA (Dealer Data Access) product. Those products serve up daily refreshes of data on a PostgreSQL database on your CDK server already.

Step 2:

CFOs and Controllers and even advanced Business Managers are the ones to analyze the data and create the reports. They need training and I will be setting up some courses here in Las Vegas to train them. I don’t advocate having IT people do this because they just don’t have the knowledge of how these numbers are generated; where they come from and how they inter-relate. So they need training in:

  • The DMS data model i.e., where the data resides and what transactions put it there.
  • How to use Excel 2013 and 2016 with PowerPivot and PowerQuery and other tools to create interactive reports so they refresh automatically when the underlying data is updated. You don’t want a set of gorgeous reports that require your people to manually update them. We will let SharePoint Online handle that.

Step 3:

Because this data is confidential and because we need to make sure it is correct this step also falls on the responsibility of the financial side of the business. I recommend this be handled by the CFO, Controller and/or Business Manager. In larger dealer groups the CFO or Controller can hire someone with this expertise but they need to understand car dealership transactions and accounting. There are two kinds of reporting in the dealership ( I teach all the competencies needed for both):

  1.  Permanent periodic reports. These include rankings, Over-Age Inventory,  Heat sheets, A/R Aging, CIT, Incentives, Upcoming Lease Terminations Reporting etc. These reports need to be built once and automated by reporting in their own SharePoint page with automatic refresh with proper permissions.
  2. Ad-Hoc reports. These are reports that you need to create one time or on-the-fly to do analytics prior to making a major business decision. In either case properly trained financial people need to prepare them.

I will be sending out an email announcing a seminar in the spring to lay this out for Dealers, CFOs and Controllers in detail. If you want to be on the list you can email me at jdonnelly at jdonnelly dot com or give me a call at 702.498.8777

PowerShell Cmdlets for Auto Dealerships

I am getting more and more calls for help with CDK Global, DealerTrack, and InfinitiNet data extraction and I want to post now about how I can help best:

  • I will write you a PowerShell cmdlet that will exactly meet your needs

I find the Microsoft PowerShell platform the best for folks that are not technically adept to extract data when, where and as often as they want. It helps to know how to run a PowerShell cmdlet but that is not critical. I can get you up and running with a 15 minute phone call.

The beauty of PowerShell is that I can write you a cmdlet to do whatever it is you want done. We then import those into your PowerShell ISE and then you can run it with different parameters as often as you want. The output you get back can be opened directly in Excel (if that is how you want it) by PowerShell. I can even write the cmdlet so that it emails the information around or in many other ways.

The reason I like PowerShell is because running the report is easy for those who don’t know PowerShell and yet I can make PowerShell do pretty much anything you need.

I will write your custom cmdlet quickly and for a fixed fee because, not only am I adept at writing PowerShell cmdlets and modules, but I know DMS data models very well.

I would estimate that most extraction cmdlets will be quoted in the $150 to $350 range but if you want a cmdlet to not only extract the data but send email reports or marketing it could (depending on how elaborate) cost upwards of $1,000 – $2,000.

Monthly support for the first 3 cmdlets is free and add $100 a month for each batch of 3 thereafter.

Email me now for a quote at jdonnelly at jdonnelly.com


The “Poor Man’s” CDK GLOBAL (ADP) Business Intelligence

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 (ADP) (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).

Generating ADP (CDK Global) Accounting Schedules in Excel PowerPivot

ADP (CDK Global) Technical

Some background

Last year when I was evaluating the new PostgresQL data server for ADP, I asked them after at about the 6 month mark if they had a way to query the appropriate General Ledger tables such that we could analyze accounting schedules in Excel. In particular I was very intent on utilizing new Business Intelligence add-in, PowerPivot. Sadly, there was not a one among the dev team that was an accountant and they showed little enthusiasm. I challenged them to write a SQL query that could bring into a PowerPivot data model what I needed to review 28 dealership’s accounting schedules (to make sure the books are clean you see) in one day.

I am a CPA by education (UCLA Accounting), experience (practicing California Automotive CPA for years) and training (keeping up with technology). I knew early on that to be effective in financial…

View original post 2,187 more words

ADP (CDK Global) Expertise – Your Team, Making You More Money

ADP (CDK Global) has recently announced a new data server product available to their customer base. The product is currently called Managed Data Access (MDA).

I was considering writing a series of blog posts about this exciting new offering on my main site here but then I thought that this rather extensive opportunity needs a place of its own.

I am sure you will decide to take advantage of this offering and when you do ADP (CDK Global) will perform an initial full ETL (extract, transform and load) of your dealership’s data into a PostgresQL relational database server. ADP will then keep the database current with at least daily differential updates.

It’s true that some dealership groups have been doing their own ETLs into a SQL Server database for many years utilizing the ADP DAP program. DAP (Data Access Program) basically offered a way for dealerships to access APIs (application programming interfaces) to pull data. The DAP APIs do not lend themselves to object relational mapping and hence only the simplest data extractions were possible without some programming effort. These groups that have been doing the ETLs to SQL Server have had to have some pretty sharp Database Admins on staff to build the relational data model. Attempting this process was worth it though so these dealership groups could write queries and perform the joins necessary to get relevant actionable data.

The power of the MDA product though is in the connectivity of powerful data analysis and presentation software to your ADP data, namely Microsoft Excel. Beginning with Excel 2010 and continuing with Excel 2013, Microsoft has made Excel into a premier self-service data analysis (aka business intelligence) platform. Microsoft has done so with the advent of a few key free Excel add-ins namely:

  • Power Query – Makes adding data to Excel simple from just about anywhere.
  • PowerPivot – Makes joining data from disparate sources (or the same source) effortless for truly actionable data analysis.
  • PowerView – Makes data analysis beautiful and unbelievably easy with effortless slicing, dicing and drill-down capability.

With a little help from us in the initial setup, a dealership manager (whether it be the Dealer, the GM or a Department Manager or all of the above), will have access through Excel to a level of understanding of their operation heretofore unknown in the car business.

We will post here those projects that have an immediate, profound effect on the dealership’s or dealership group’s growth and/or profitability, based on sound principles, utilizing ADP (CDK Global) Data and Microsoft Excel. Simple projects that make a big difference.

In the mean time, give us a call if you have any questions.