Car Dealer, Financial Model, Retail

Controlling, Customer Relationship Management (CRM), Dashboard, Employee Performance, Excel, Google Sheet, KPIs (Key Performance Indicators), Management, Performance Tracking, Personal Finance, Sales Pipeline, Sales Tracking, Team management

Video Tutorial:

This template is built in Excel, but there is also a Google sheet version (requires a Gmail account, and the link is on the “instruction” tab) to use. the Excel version requires Office 365 and enrollment in the ‘insiders’ program because it uses filter functions.

The style of this workbook is simple. You have a ‘data’ tab where new leads are entered. On that tab, there is a column with the ‘status’ type, and based on that, various other tabs will populate relevant data to help leads be tracked no matter where they are in the sales process.

The 4 stages (which the names of can be changed) are: Called, Appointment Set, Follow Up, and Long Term. Anything marked as ‘Sold’ or ‘Lost’ will not show up in the pipeline.

Any lead marked as ‘Appointment Set’ will populate on the ‘Upcoming Appointments’ tab dynamically as you move through time based on any lead with a date within the current week and that pipeline status.

The monthly stats tabs will show 24 months at a time by month. The 24-month total will also display. Stats by month include: wins / losses / $sold / $commissions / $lost / $commission lost. Everything auto-updates as data is entered, including the charts/visuals for those stats.

There is an aggregate monthly stats tab, as well as 4 that can be filtered by car make, 1 that is for filtering by car type, and 1 for car type + make. The by ‘make’ and by ‘car type’ tabs can be duplicated by right-clicking the tab and copying it if you want reports that are more than what is there (a dropdown is at the top to select a different make/type).

Also, the start month can be changed to any month. Make sure the month entered is always the first day of that month.

Finally, there is a pipeline snapshot with some cool visuals that show the total count of leads and the $ value of those leads in each pipeline status based on what has been entered in the ‘data’ tab. This visual took a LONG time to get right.