Dealership KPI Report Automation

Each month I prepare a performance snapshot for 13 dealerships plus an overall group roll-up. I now want a single Excel workbook that draws fresh data from four places—CRM exports, my boss’s Google Sheet, a marketplace XLS from our reps, and an invoice file that lists VDP views—and transforms it into a clear one-month and three-month view.

The finished workbook should open on a summary sheet that shows every store on its own row, followed by a group total. For each row we need all the KPIs we already track online: Cars Online, Leads, Leads per Car, the marketplace benchmark for that ratio, VDP Views, VDP Views per Car, its benchmark, Dealership Visits, Budget and Cost per Visit. Leads per Car, Dealership Visits and Cost per Visit must stand out visually because my GMs act on them first.

I want both tables and charts so managers can glance at trends without digging. Feel free to lean on Excel tools such as Power Query, Power Pivot, pivot tables, slicers or VBA if they speed refresh. Where no API exists, a simple “paste-new-file-here and click Refresh” workflow is fine; the less manual re-formatting the better.

Acceptance criteria:
• One-click (or one-button) refresh pulls the latest four source files and updates all figures and charts.
• Separate views for the current month and rolling three-month average.
• Visual cues (colour or conditional formatting) highlight Leads/Cars Online, Dealership Visits and Cost per Visit.
• Clear documentation that lets any future analyst update source paths or add new dealerships.

If you’ve built automated Excel dashboards that marry multiple data feeds and present them cleanly, I’d love to see an example when you bid.