Automated Sales Performance Reporting
A scheduled automation solution that aggregates weekly sales data from SharePoint Lists, generates formatted Excel KPI reports, and distributes personalized performance summaries to regional managers — saving 4 hours of manual reporting work every week.
The Problem
Every Monday morning, a Commercial Operations analyst spent 4 hours manually compiling the previous week's sales performance report. This involved:
- Exporting data from 5 different regional SharePoint lists
- Copying data into a master Excel spreadsheet
- Creating pivot tables and charts for each region
- Formatting the report and writing summary commentary
- Emailing the correct version to each of 5 regional managers
- Manually following up when managers reported receiving wrong region data
The process was error-prone and delayed — reports were sometimes not ready until mid-Monday morning, delaying weekly planning meetings.
Solution Architecture
Scheduled Trigger
Flow runs every Monday at 6:00 AM automatically
Data Collection
Queries all 5 regional SharePoint Lists for last week's sales records
Aggregation
Applies expressions to calculate totals, averages, and performance vs. targets
Report Generation
Populates pre-built Excel template with aggregated KPIs per region
Personalized Delivery
Each regional manager receives their own report via Teams and email
Implementation Details
Power Automate — Main Reporting Flow
- Recurrence trigger: Every Monday at 6:00 AM
- Get items from each regional SharePoint list filtered by last 7 days
- Apply to each array: compute region totals, conversion rates, revenue, units sold
- Compare actuals vs. target stored in a "Targets" SharePoint list
- Calculate % achievement, highlight top/bottom performers
- Apply to each — region loop: generate and send individual report
SharePoint — Data Infrastructure
- 5 regional "Sales Activity" lists: Region (North/South/East/West/Central), Date, Rep Name, Product, Units, Revenue, Customer
- "Sales Targets" list: Monthly and weekly targets per region and product category
- "Report Archive" document library: All generated reports saved with date/region naming convention
- "Region Managers" list: Mapping of regions to manager email addresses
Excel Online — Report Template
- Pre-designed Excel template with named cells/ranges for each KPI
- Dynamic charts auto-update when data cells are populated by the flow
- Conditional formatting: Green (≥100% target), Yellow (80–99%), Red (<80%)
- Executive summary section auto-generated from calculated fields
- Each region gets its own copy of the template, populated separately
Delivery — Teams + Email
- Teams Adaptive Card sent to each manager with KPI summary inline (no need to open file)
- Card shows: Revenue vs Target %, Top Product, Top Rep, Week-over-Week trend
- Email with Excel attachment sent simultaneously for record-keeping
- Company-wide summary card posted to Commercial channel with overall performance
- Error handling: If a region has no data, manager receives a "No activity recorded" notification
Results & Impact
Report Delivery
Managers receive reports 3.5 hours earlier, enabling Monday planning meetings to start on time
Accuracy
Zero instances of wrong data delivered to wrong manager since launch
Data Freshness
Reports now reflect all data entered up to 5:59 AM Monday morning