Blog · Board and management reporting
Compare regional sales with consistent geographic assignments and meaningful denominators. Work from an export to reconciled state totals before choosing a map or ranking.
Geographic visualization in business intelligence compares sales across locations using measures that match the decision. Start with reconciled state totals, then compare growth and revenue per covered account before deciding what belongs on the map.
A darker state can mean more customers, stronger account productivity, or simply a larger starting business. The supporting table must distinguish those explanations. The broader business intelligence definition covers how measures support decisions; this workflow focuses on the geographic comparison.
Suppose a US equipment distributor wants to decide where a regional manager should investigate account coverage. Its question is: which states have substantial sales but relatively low revenue per covered account?
Use customer billing state throughout this example. That makes the invoice totals comparable with the account roster, which uses the same assignment. A delivery-state analysis would answer a different question about where products went. Neither should silently replace the other between periods.
Choose one geographic level. A state comparison should contain states, with no city subtotal or regional total mixed into the same column. For county-level work, the Census Bureau explains that GEOIDs uniquely identify geographic areas. A stable code avoids treating counties with the same name as the same place. Preserve leading zeros when importing those identifiers.
Use an invoice export with these columns on a worksheet named Raw:
| Column | Field | Treatment |
|---|---|---|
| A | Invoice or credit identifier | Keep each accounting document once |
| B | Billing state | Use the same state codes as the roster |
| C | Posting date | Store as a spreadsheet date |
| D | Net sales (USD) | Credits negative; exclude sales tax |
| E | Account identifier | Retain for investigating individual accounts |
Use a second worksheet named Accounts, with account identifier in A and billing state in B. Keep one row per covered account, including accounts with no sales. Freeze this roster for the comparison; otherwise a changed denominator can look like changed productivity.
Set Summary!L2 to August 1, 2026, L3 to September 1, 2026, and L4 to October 1, 2026. The example compares August with September. Using the next month's start as an exclusive boundary also handles posting dates containing times.
Before aggregating, check duplicate document identifiers, missing states, credits, and the date range. Keep an explicit unassigned category if a state is unresolved. Removing those rows would make the map cleaner while understating company sales.
On Summary, place the state in A, August sales in B, September sales in C, covered accounts in D, dollar change in E, growth in F, and September sales per covered account in G.
In Microsoft Excel, enter these formulas in B2 through G2, respectively, and copy down:
=SUMIFS(Raw!$D$2:$D$5000,Raw!$B$2:$B$5000,$A2,Raw!$C$2:$C$5000,">="&$L$2,Raw!$C$2:$C$5000,"<"&$L$3)
=SUMIFS(Raw!$D$2:$D$5000,Raw!$B$2:$B$5000,$A2,Raw!$C$2:$C$5000,">="&$L$3,Raw!$C$2:$C$5000,"<"&$L$4)
=COUNTIF(Accounts!$B$2:$B$286,A2)
=C2-B2
=IF(B2=0,"",E2/B2)
=IF(D2=0,"",C2/D2)
Microsoft documents SUMIFS as summing values that meet multiple criteria. Here those criteria are the state and the posting-date boundaries. Extend the ranges to cover your entire export. Covirage is not affiliated with Microsoft.
The fictional distributor's resulting state summary is:
| State | August sales (USD) | September sales (USD) | Covered accounts | Change (USD) | Growth | September sales/account (USD) |
|---|---|---|---|---|---|---|
| CA | 120,000 | 150,000 | 100 | 30,000 | 25.0% | 1,500.00 |
| TX | 100,000 | 120,000 | 60 | 20,000 | 20.0% | 2,000.00 |
| NY | 80,000 | 84,000 | 70 | 4,000 | 5.0% | 1,200.00 |
| FL | 60,000 | 66,000 | 55 | 6,000 | 10.0% | 1,200.00 |
| Total | 360,000 | 420,000 | 285 | 60,000 | 16.7% | 1,473.68 |
Percentages are rounded to one decimal place and sales per account to cents. Keep full precision in the worksheet. Calculate total growth in F6 and total productivity in G6, respectively:
=(SUM(C2:C5)-SUM(B2:B5))/SUM(B2:B5)
=SUM(C2:C5)/SUM(D2:D5)
The state changes sum to $60,000, matching $420,000 minus $360,000. Compare each monthly total with the sales control total for the same scope. Separately confirm that the roster contains 285 unique account identifiers and that each has exactly one state assignment.
California leads revenue at $150,000, but Texas leads sales per covered account at $2,000. New York and Florida both return $1,200 per account despite different revenue totals. That supports a coverage review; it does not prove that a manager, local demand, or pricing caused the difference.
Use a map when geographic proximity matters, such as neighboring states sharing a service area. Use a sorted bar chart when the immediate job is ranking productivity. Keep the same color scale across monthly maps so a changed legend cannot manufacture improvement. Business intelligence charts explains other question-to-chart choices; data visualization tools covers choosing software for the view.
For repeated reviews, preserve these definitions with the export. Automated business intelligence discusses which recurring checks to standardize before interpreting another period.
Bring a file containing your sales export and account roster. Covirage maps the columns, and its tools compute and check the regional measures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See sales insights for the supporting business questions.
Use revenue to locate commercial exposure and growth to compare changes against each territory's starting point. Show both alongside a table when the decision needs both scale and momentum. A small territory can grow quickly while contributing little additional revenue.
Choose the location that matches the management question, such as customer billing state, delivery state, or assigned sales territory. Use the same rule in both periods. Preserve the source field and keep unmapped rows visible until someone resolves them.
Sales per covered account measures revenue against the accounts included in your roster. It does not establish market penetration unless the denominator represents the relevant addressable market. Document whether the roster contains customers, prospects, or every eligible organization.