Client Context
Industry: Automotive Retail
Size: Multi-location dealership group (20+ locations)
Department: Executive Leadership, Finance, Operations
Stakeholders: CEO, CFO, Business Unit Directors, Financial Controllers
The Challenge
Leadership was flying blind between monthly board meetings.
The executive team was managing a complex, multi-location business unit spanning over multiple countries—but had no real-time visibility into how the business was actually performing. Strategic decisions were being made in the dark, with weeks of delay between events and awareness.
The core problems:
1. No Centralized Source of Truth
Business performance data was scattered across three completely separate systems:
- MIS (Management Integration System): Operational data (vehicles sold, service hours, customer transactions)
- IFRS accounting system: Financial data (revenue, COGS, OPEX, margins)
- Excel files on SharePoint: Budget forecasts, market data, and ad-hoc analysis
To answer a simple question like “Are we on track to hit our Q2 targets?” required:
- Manually extracting data from each system
- Copying/pasting into Excel
- Reconciling inconsistencies
- Building custom calculations
- Distributing the report via email
This process took 2-3 days and was only done monthly—meaning leadership was always looking at 4-6 week old information.
2. Zero “Live” Performance Feedback
Between monthly board meetings, executives had no way to know:
- Whether revenue was tracking to plan
- If margins were deteriorating
- How individual locations were performing
- Whether interventions were working
Questions like “How did we do yesterday?” or “Are we having a good month so far?” had no answer. Leadership was reactive, not proactive—learning about problems only after they’d compounded for weeks.
3. Inability to Compare and Contextualize
Even when monthly reports were compiled:
- No year-over-year trend analysis
- No comparison to budget or forecast
- No moving averages to smooth out seasonality
- No ability to drill down from business unit → country → location → department
Strategic planning happened in a vacuum, without historical context or performance benchmarking.
The Business Impact:
- Delayed decision-making: By the time leadership identified a problem, the month was over and corrective action had to wait
- Missed revenue opportunities: Strong performance in one area couldn’t be replicated elsewhere because patterns weren’t visible
- Inefficient resource allocation: Budget and staffing decisions were made based on outdated or incomplete information
- Executive frustration: Leadership meetings devolved into “wait for the finance report” instead of strategic discussions
- Wasted finance team capacity: Controllers spent 2-3 days/month compiling reports instead of analyzing and advising
The Solution
A unified, real-time executive performance dashboard
I built a comprehensive Power BI solution that integrated three disparate data sources into a single, live view of business unit performance—giving leadership the clarity and confidence to make data-driven decisions daily, not monthly.
What We Built:
1. Automated Multi-Source Data Integration
This was the technical foundation that made everything else possible:
Data Sources Connected:
- MIS system: Operational metrics (vehicles sold, service hours, customer counts)
- IFRS accounting system: Financial metrics (revenue, COGS, OPEX, PBT, EBIT, EBITDA)
- SharePoint Excel files: Budget/forecast data, automatically refreshed as files were updated
Data Processing Architecture:
- Built Power BI dataflows in the cloud to extract and transform data from each source
- Created aggregation dataflows that reduced raw transaction tables (10M+ rows) down to pre-summarized tables (~300K rows)
- This optimization reduced report load time from 45+ seconds to under 3 seconds—making the solution actually usable for executives
Refresh Schedule:
- Automated daily refresh at 6 AM—ensuring data was current when leadership started their day
- SharePoint files monitored for updates and pulled in automatically
2. Executive Performance Dashboard
Designed for C-suite and business unit directors to answer: “How are we performing right now?”
Primary KPIs displayed:
- Revenue (actual vs. plan vs. prior year)
- COGS and gross margin %
- OPEX and operating margin %
- PBT (Profit Before Tax), EBIT, EBITDA
- Vehicles sold (new, used, fleet)
- Service hours sold (warranty, retail, internal)
Key Features:
- Traffic light indicators: Green/yellow/red status for each KPI vs. plan
- Variance analysis: Automatic calculation of “we’re +5% vs. plan” or “we’re -12% vs. last year”
- Drill-down hierarchy: Business unit → country → location → department—click to investigate
- Trend sparklines: Quick visual of whether KPIs are improving or declining over past 3-6 months
3. Time Intelligence & Comparative Analysis
Built sophisticated date calculations to provide context:
Year-over-Year Comparison:
- Current month vs. same month last year
- YTD vs. prior year YTD
- Growth rates automatically calculated and visualized
Plan/Forecast Tracking:
- Actual vs. budget for each KPI
- Forecast attainment % (are we on track to hit annual targets?)
- Variance alerts when actuals deviate >10% from plan
Moving Averages:
- 3-month and 6-month moving averages to smooth seasonality
- Enabled leadership to distinguish “normal fluctuation” from “concerning trend”
Custom Time Periods:
- Last 7 days, last 30 days, last quarter, last 12 months
- Dynamic date filters so executives could explore any time range
4. Location & Performance Benchmarking
Created comparative views showing:
- Ranking tables: Which locations were top/bottom performers for each KPI
- Peer comparison: How each location compared to network average
- Contribution analysis: Which locations drove the most revenue, profit, volume
This enabled healthy internal competition and identification of best practices to replicate.
5. Self-Service Insights
Every visualization was designed to eliminate interpretation:
Instead of: “Revenue by Month (Bar Chart)”
I wrote: “Revenue Up 8% YoY But Tracking 5% Behind Plan—Action Needed in Q3”
Instead of: “EBITDA Margin % (Line Chart)”
I wrote: “EBITDA Margin Declining for 3 Consecutive Months—OPEX Growing Faster Than Revenue”
Executives could glance at any page and immediately understand the situation and whether action was required.
The Results
From monthly reporting delays to daily strategic agility
Immediate Operational Wins:
- 2-3 days/month saved for finance team (eliminated manual report compilation)
- Daily visibility into all critical KPIs (vs. 4-6 week lag)
- 3-second load time for reports (vs. 45+ seconds with raw data)—executives actually used it
- Single source of truth—eliminated conflicting numbers and version control issues
Strategic Business Impact:
- Faster course correction: Problems identified within days instead of weeks—leadership could intervene while there was still time to recover the month
- Data-driven resource allocation: Budget adjustments and staffing decisions made with current performance data instead of outdated assumptions
- Replication of best practices: High-performing locations’ strategies identified and deployed across network
- Improved forecast accuracy: Historical trends and moving averages enabled better prediction of seasonal fluctuations
Financial Impact:
- Margin preservation: Early detection of OPEX creep allowed for cost control measures before margins deteriorated significantly
- Revenue optimization: Identified underperforming segments and locations early enough to implement corrective pricing/marketing strategies
- Better capital planning: Real-time profitability data improved accuracy of cash flow forecasts and investment decisions
Cultural Shift:
The solution fundamentally changed how leadership operated:
Before:
- Monthly board meetings: “Here’s what happened 3-4 weeks ago”
- Leadership operating on intuition and anecdotes
- Finance team as report compilers
After:
- Daily/weekly data check-ins: “Here’s where we are today—what do we need to do?”
- Leadership operating on real-time data and trends
- Finance team as strategic advisors (freed from manual reporting)
The CEO started each week reviewing the dashboard. Business unit directors began using it in their Monday morning team calls. Controllers shifted from “preparing reports” to “analyzing variances and recommending actions.”
Leadership meetings transformed from “what happened?” discussions into “what should we do about it?” strategic sessions.
Technical Innovation Highlight
The Performance Optimization Challenge:
The original dataset contained 20M+ rows of transaction-level data. Loading this directly into Power BI created a 2GB+ file that:
- Took 30+ seconds to load visual
- Crashed on fairly great laptops
- Was unusable for executives who needed fast answers
The Solution:
We built a two-tier dataflow architecture:
Tier 1—Raw Data Extraction:
- Dataflows that pulled complete transaction history from source systems
- Stored in Power BI cloud in optimized format
Tier 2—Aggregation Dataflows:
- Pre-summarized data at monthly level
- Reduced from 10M rows to ~300K rows
- Lost zero analytical capability (all required KPIs still calculable)
Result:
- Report file size: <200MB (90% reduction)
- Load time: <3 seconds (93% improvement)
- Executive adoption: 100% (vs. ~20% when it was slow)
This technical work was invisible to users but critical to solution success — no matter how good the insights, if the tool is slow, it won’t be used.
