Blog · Board and management reporting · Sales teams
Copy a sales-specific Excel workbook layout for actuals, targets and next-period pipeline. Follow the formulas, worked rep and region totals, and checks before presenting the dashboard.
A sales dashboard XLS template needs separate source tabs for actuals, targets and pipeline, plus a review sheet that ties rep and region totals together. The layout below is copyable into your own workbook; this page does not provide an XLS download.
Use a current supported version of Excel for the formulas below. If your recipient specifically needs the older XLS format, review compatibility when saving. Microsoft's worksheet compatibility guidance explains that saving to Excel 97-2003 format can lose functionality or formatting; changing the extension is not a compatibility check. Covirage is not affiliated with Microsoft.
Create these tabs and keep the first row for column headings:
| Tab | Columns or cells | What belongs there |
|---|---|---|
| Setup | B2 review start; B3 next start; B4 following start; B5 snapshot | Real dates used by every formula |
| Actuals | A document ID; B line ID; C sales date; D rep; E region; F net sales (USD) | One invoice or credit line per row |
| Targets | A month start; B rep; C region; D target (USD) | One approved target per rep and month |
| Pipeline | A opportunity ID; B rep; C region; D expected close; E status; F amount (USD) | One opportunity at the snapshot |
| Review | A rep; B region; C actual; D target; E next-period pipeline; F next target; G variance; H attainment; I coverage | Calculated output only |
For this example, Setup B2 is September 1, 2026; B3 is October 1, 2026; B4 is November 1, 2026; and B5 is September 30, 2026. All currency fields are USD. The Actuals tab contains September net invoiced sales. The pipeline file is an export taken at the snapshot, with open opportunities expected to close in October.
Keep targets out of the transaction tab. A target repeated beside each invoice will be counted repeatedly. Keep document-line identifiers, account identifiers and any supporting product columns in the source file even when the Review sheet does not display them.
The following input totals describe a US equipment distributor. Each pipeline amount is the sum of that rep's open October opportunities in the September 30 export. Its approved October targets happen to equal September's; the workbook still stores them as separate monthly rows.
| Rep | Region | September actual (USD) | September target (USD) | October pipeline (USD) | October target (USD) |
|---|---|---|---|---|---|
| Ava | East | 198,000 | 200,000 | 260,000 | 200,000 |
| Ben | East | 126,000 | 150,000 | 180,000 | 150,000 |
| Cora | West | 192,000 | 180,000 | 200,000 | 180,000 |
| Diego | West | 144,000 | 150,000 | 160,000 | 150,000 |
| Total | 660,000 | 680,000 | 800,000 | 680,000 |
Put the reps in Review A2:A5 and regions in B2:B5. Actual sales in C2 use an inclusive start and exclusive next-month boundary:
=SUMIFS(Actuals!$F$2:$F$10000,Actuals!$D$2:$D$10000,$A2,Actuals!$C$2:$C$10000,">="&Setup!$B$2,Actuals!$C$2:$C$10000,"<"&Setup!$B$3)
Target in D2 comes from the approved schedule:
=SUMIFS(Targets!$D$2:$D$1000,Targets!$B$2:$B$1000,$A2,Targets!$A$2:$A$1000,Setup!$B$2)
SUMIFS adds amounts meeting multiple criteria. These bounded ranges include space for additional rows; extend all matching ranges together when necessary.
October pipeline in E2 uses the separate snapshot file:
=SUMIFS(Pipeline!$F$2:$F$10000,Pipeline!$B$2:$B$10000,$A2,Pipeline!$E$2:$E$10000,"Open",Pipeline!$D$2:$D$10000,">="&Setup!$B$3,Pipeline!$D$2:$D$10000,"<"&Setup!$B$4)
For F2, repeat the D2 target formula using Setup!$B$3 as the month criterion. Fill C2:F2 down through row 5. Confirm that the export uses Open as a standardized status; map the source's open stages to that label before calculating.
Calculate G2:I2 and fill down:
Variance: =C2-D2
Attainment: =IF(D2=0,"",C2/D2)
Next-period coverage: =IF(F2=0,"",E2/F2)
Use percentage formatting for attainment and a multiple for pipeline coverage. Displayed percentages are rounded to one decimal place and coverage to two decimals; calculations retain their full precision.
| Rep | September variance (USD) | Attainment | October pipeline / October target |
|---|---|---|---|
| Ava | -2,000 | 99.0% | 1.30x |
| Ben | -24,000 | 84.0% | 1.20x |
| Cora | 12,000 | 106.7% | 1.11x |
| Diego | -6,000 | 96.0% | 1.07x |
| Total | -20,000 | 97.1% | 1.18x |
In row 6, sum the money columns and calculate H6 as =IF(D6=0,"",C6/D6) and I6 as =IF(F6=0,"",E6/F6). The company figures are 660,000 / 680,000 = 97.1% and 800,000 / 680,000 = 1.18x. They are ratios of totals, rather than averages of rep ratios.
The pipeline multiple describes open opportunity value relative to next month's target. It does not establish the likelihood of winning or the timing of invoicing. If the target uses invoiced sales, a signed October deal that invoices later may not contribute to that month's actuals.
Put actual sales, target and dollar variance across the top. Place the rep comparison below, followed by region totals and an exception list. Use sales dashboard examples to choose the reader's view and Excel dashboard examples for chart construction; this layout focuses on the source and reconciliation structure.
East actuals are 198,000 + 126,000 = $324,000 against targets of $350,000, a $26,000 shortfall. West actuals are 192,000 + 144,000 = $336,000 against $330,000, a $6,000 surplus. Their variances reconcile: -26,000 + 6,000 = -$20,000.
Require these checks before sharing:
For broader review questions, use the CFO's sales-data checklist. When the workbook also connects demand to supply, keep that work in a separate sales and operations planning dashboard.
Upload your actuals, approved targets and dated pipeline workbook to Covirage; it maps the columns, and its tools compute and check comparisons by rep and region. 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 Excel analysis for the supporting workflow.
No downloadable sales workbook is provided here. Copy the layouts and formulas into your own workbook, then replace the illustrated rows with your exports. The source tabs, checks and calculated review table specify how the workbook should fit together.
Yes. The business layout works in a modern workbook format too. If you must save as XLS, check compatibility before sharing. The file extension alone does not ensure that every formula, chart or formatting choice works in an older application.
Keep them separate. Actual sales describe completed activity under the chosen sales definition; open pipeline describes possible future business at a dated snapshot. Compare each with the target for its own period rather than presenting their sum as achieved revenue.