Sales account coverage dashboard: metrics and layout

A sales account coverage dashboard should show covered, overdue and untouched accounts by rep, together with the revenue behind each group. Put the accounts requiring action directly below those totals so that a manager can assign the next contact without opening another report.

Design the page around the next action

The dashboard needs a stated as-of date, a defined account population and an explicit contact rule. A high percentage has little meaning when different reps use different activity types or when dormant accounts disappear from the denominator.

Use this page layout:

Keep compensation measures in a separate sales commission dashboard. Combining payout details and coverage actions makes both harder to review. For the full metric calculation with different tiers, use the existing ten-account coverage example; the job here is arranging the result for a manager.

Prepare account, activity and invoice exports

Start with one row per active assigned account, including account ID, current owner, segment and required cadence in days. Keep missing owners visible in an exception group. Define whether the dashboard follows the current book or historical assignments before comparing periods.

The activity file needs account ID, activity date, type, completion status and a qualifying flag. This example counts completed meetings, connected calls and two-way email, under a policy approved by the sales manager. It excludes automated sends and attempted calls. The coverage by industry guide explains why a meaningful touch differs between desks.

Aggregate invoice lines to account ID before attaching revenue to the roster. Include credit lines as negative amounts. Otherwise, joining several activities to several invoices can repeat the same sales value many times.

Use the same fixed revenue window for every account. Here, value means net invoiced sales for the twelve months ending September 30, 2026. It ranks the current book by historical sales; it is not an estimate of the amount that will be lost.

Calculate status from an explicit snapshot

For a US equipment distributor, the as-of date is September 30, 2026 and every illustrated account has a 30-day cadence. Contact exactly 30 days ago still counts as covered. A blank last-touch field means no qualifying activity in the supplied history.

For Excel, name the activity table Activity, with columns AccountID, Date and Qualifying. Use Yes for qualifying rows and put the snapshot date in a named cell AsOfDate. In the account table above, put the last-touch formula in D2:

=IF(COUNTIFS(Activity[AccountID],A2,Activity[Qualifying],"Yes",Activity[Date],">0",Activity[Date],"<="&AsOfDate)=0,"",MAXIFS(Activity[Date],Activity[AccountID],A2,Activity[Qualifying],"Yes",Activity[Date],">0",Activity[Date],"<="&AsOfDate))

MAXIFS returns the maximum value meeting the criteria, so real date values yield the latest qualifying date. It returns zero when no row matches; the outer test preserves a blank instead of displaying a false historical date. Use a supported version with MAXIFS available, such as Excel 2019 or later.

Calculate days and status in E2 and F2, then fill down:

=IF(D2="","",AsOfDate-D2)
=IF(D2="","Untouched",IF(E2<=30,"Covered","Overdue"))

For varied cadences, replace 30 with that account's cadence cell. Flag missing activity dates for correction; they cannot establish contact. Exclude future-dated activities from the snapshot, and flag them separately for correction. Covirage is not affiliated with Microsoft.

Roll up count and value by rep

Using the account table in A1:F7, covered count for the rep named in H2 is:

=COUNTIFS($B$2:$B$7,H2,$F$2:$F$7,"Covered")

COUNTIFS applies all specified conditions to each row. Covered value uses the corresponding sum:

=SUMIFS($C$2:$C$7,$B$2:$B$7,H2,$F$2:$F$7,"Covered")

Change the status criterion for overdue and untouched totals. Divide covered count by assigned count, and covered value by total assigned value. Percentages here are rounded to one decimal place.

Count coverage is 3 / 6 = 50.0%; value coverage is 280,000 / 420,000 = 66.7%. The remaining $140,000 combines overdue and untouched account value. Show both components so a manager can distinguish reconnecting from establishing contact.

The action list begins with A102, A103 and B203 when ranked by historical sales. Ask Ava whether A102 needs a meeting or has an unresolved service issue. For newly assigned accounts, review time to first touch separately; a new assignment may need an earlier response than the standard cadence.

Checks and common failures

Prove that 280,000 + 80,000 + 60,000 = $420,000 and that the status counts are 3 + 1 + 2 = 6. The total must also match the invoice export for the same roster and revenue period. Explain excluded invoice accounts separately instead of forcing the assigned book to equal the whole company.

• Activity rows treated as accounts. Count the account roster after calculating status; repeated contacts must not enlarge the denominator.
• Missing history treated as neglect. Display the activity history start and investigate incomplete exports before judging a rep.
• Cadence rules changing silently. Label the policy version or a week-to-week change may reflect definitions rather than behavior.
• Contact treated as customer health. A qualifying touch establishes coverage, not satisfaction or a renewal commitment. Keep notes behind the action.

Build the review from your own account book

Upload your account roster, activity file and invoice export to Covirage; it maps the columns, and its tools compute and check coverage by owner and account value. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See account coverage for the supporting approach.