Automated Excel Reports For Dealerships

Here, you will find a collection of automated templates that are designed to do several things, but mainly, to save you time!

For every template, there is an example you can open/download for review. Just click on the thumbnail images to the right.  When opening the example spreadsheets, make sure to enable macros to see all the intended functionality*.

 Receivables Analysis Template

All your receivables, aged in one report with the underlying schedules attached – total run time about 3 or 4 seconds per schedule - pulls selected schedules using AGRS, ARS and an AGRB.

Typical Usage – run periodically for a quick look at your receivables – use the projection to see what your aging looks like currently, or what it will be at the end of the month – can be run for current month, or post-ahead.

 Vehicle Inventory Analysis Template

All your vehicle inventories and underlying schedules for both new and used - calculations of aging by dollars and counts, model year, etc. - also packs, reserves, flooring, lien payoffs – total run time about 15 to 20 seconds – pulls schedules using AGRS and an AGRB.

Typical Usage – run periodically to see how your agings looks – review your pack, reserve calculations, etc. – use the projection to see where you will be at month end – staff can use to review the attached schedules for payoffs, un-floored, corrections to be made, etc. - can be run current or post-ahead.

Summary Schedule Template

Any summary schedules that you choose to see at any time – total run time 3 or 4 seconds per schedule - pulls schedules using AGRS, and a AGRB and AGRM.

Typical Usage – review any schedules of your choosing – staff can use regularly to pull certain schedules that they are responsible for – use at month end as an “archive” and get all the schedules you want in Excel format, along with an AGRB and AGRM - run as current or post-ahead.

Payroll Analysis Template

See your wage and salary expense account detailed out by account number and employee – total run time about 30 – 45 seconds – pulls AGRI detail from your selected payroll expense accounts, an AGRM, and a listing of employees.

Typical Usage – review to see who is being posted to what payroll expense accounts – see total pay for every employee in the dealership – also, groups by position description to give you total pay and employee count by those positions.

Daily Service Report Template

Results of service activity by day – total run time about 30 seconds for each day run – pulls daily and MTD RAPs, ROR, RWP and RBE.

Typical Usage – see selected results by day and MTD from your RAPs – advisor and counter person detail is also shown – open ROs by advisor, day and aged – includes daily charting.

Daily Management Template

Really, a super-charged DOC – everything you could see on an MIS, but with more stuff – run time from 5 to 10 seconds - can pull from an MIS you already have, or any kind of detailed trial balance.

Typical Usage – everything you use your regular MIS DOCs for now and more – includes daily charting, comparisons to other periods along with tools for estimating where you are for the month, and where you are going to end up.

Checking Account Analysis Template

An easy way to review all checking account disbursements – run time about a minute or less – pulls from AGRI detail of your checking account, REG and AP check register.

Typical Usage - brings information from the various ADP reports together for easier analysis and review – can also be combined with an employee list for easily identifying checks written to employees.

Vehicle Sales Analysis Template

All your finalized and booked car deals organized for review – run time about 20 seconds – pulls from your ADP Gross Book reports.

Typical Usage – can be run by Salesperson, Sales Manager, or Finance Manager for any period you wish - see how everyone stacks up by front gross, F&I gross, or total gross – can review personnel individually.

Used Sold Analysis Template

Review of what used vehicles you have sold, and how you did by model and year by any time frame you want – run time about 10 seconds – pulls from your Gross Book report.

Typical Usage – shows your best and worst results by used car model, and can also drill down to model year – see by retail and wholesale deals.

Trial Balance Tool

Ever need a trial balance out of ADP?  What you probably do is use AC Capture to get an AGRB, do the same for an AGRM, and then "copy and paste" to put the two together - this tool does it for you automatically in just seconds - Note: this tool is fully-functional and works "out of the box," so if you want to try one of these tools, download this one! *