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.
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:
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…