Pricing dashboard: realized prices, discounts and margin

A pricing dashboard compares the price a company actually realizes with its applicable list price, then shows the discount exceptions and margin behind the result. Build it from the same sales rows, price periods and cost basis so that a change in list price does not masquerade as a change in discounting.

Start with one controlled sales population

Take one US distributor's September 2026 sales of Seal-10, a product sold in identical units. Use posted invoice lines net of discounts and linked credits, excluding sales tax. Keep the invoice-line identifier, transaction date, SKU, customer segment, quantity and net sales. The segment is the business classification applicable when the sale occurred.

For this example, quantities are positive and there are no returns. Real exports need a separate review of credits without quantities and full reversals; the treatment of credit notes and returns determines whether a unit-price calculation remains meaningful.

Use approved pricing records and the cost assigned to the sale. Here the unit COGS records represent the historical cost basis for these transactions, not today's replacement cost. Keep the full accounting definition consistent with gross profit margin.

Match the effective price and cost first

Put this reference table on a sheet named PriceCost, in A1:E3. Start and end dates are inclusive, stored as spreadsheet dates. Currency is USD throughout.

Price and cost share boundaries only to keep this example small. If their effective dates differ in your files, check each reference table separately and require one applicable record from each. Include currency, unit of measure and any contract price key in the match when those vary.

Put the sales inputs on Sales, in A1:G5:

Add H for unit list price, I for unit COGS and J for match count. Enter these formulas in row 2 and fill down. Microsoft documents COUNTIFS as counting rows that meet all the specified criteria.

J2 =COUNTIFS(PriceCost!$A$2:$A$3,C2,PriceCost!$B$2:$B$3,"<="&B2,PriceCost!$C$2:$C$3,">="&B2)
H2 =IF(J2=1,SUMIFS(PriceCost!$D$2:$D$3,PriceCost!$A$2:$A$3,C2,PriceCost!$B$2:$B$3,"<="&B2,PriceCost!$C$2:$C$3,">="&B2),NA())
I2 =IF(J2=1,SUMIFS(PriceCost!$E$2:$E$3,PriceCost!$A$2:$A$3,C2,PriceCost!$B$2:$B$3,"<="&B2,PriceCost!$C$2:$C$3,">="&B2),NA())

A missing match or overlapping period produces an error instead of a plausible zero. Microsoft's SUMIFS reference explains the multiple-criteria sums used here. Covirage is not affiliated with Microsoft.

Calculate price, discount and margin on each row

Add K through Q with these headers and formulas:

K2 =E2*H2
L2 =E2*I2
M2 =F2-L2
N2 =IF(E2>0,F2/E2,NA())
O2 =IF(K2>0,1-F2/K2,NA())
P2 =IF(ROUND(F2,2)<ROUND(K2*(1-G2),2),"Review","Within limit")
Q2 =MAX(0,ROUND(K2*(1-G2)-F2,2))

K is list sales, L is COGS, M is gross profit, N is realized unit price, O is discount, P is the exception flag and Q is the amount below the permitted price floor. Rounding the exception comparison to cents avoids a floating-point artifact at the exact limit.

For S2, the approved floor is 20,000 × (1 − 10%) = $18,000. Net sales of $17,000 are $1,000 below it. Ask the pricing owner to check approval and contract terms before treating that amount as collectible.

Build the segment view from sums

For a segment name in Summary!A2, these Excel formulas return realized unit price, discount and gross margin respectively:

=SUMIFS(Sales!$F$2:$F$5,Sales!$D$2:$D$5,$A2)/SUMIFS(Sales!$E$2:$E$5,Sales!$D$2:$D$5,$A2)
=1-SUMIFS(Sales!$F$2:$F$5,Sales!$D$2:$D$5,$A2)/SUMIFS(Sales!$K$2:$K$5,Sales!$D$2:$D$5,$A2)
=SUMIFS(Sales!$M$2:$M$5,Sales!$D$2:$D$5,$A2)/SUMIFS(Sales!$F$2:$F$5,Sales!$D$2:$D$5,$A2)

Display prices to cents and summary percentages to two decimals; retain full precision in calculations. Total discount is (63,000 − 56,750) / 63,000, while total margin is 19,550 / 56,750. Their denominators differ.

The Contract segment has the lower realized price, but its greater volume produces more gross profit dollars. Use segmented pricing strategy to evaluate a proposed segment change; these historical differences alone do not establish demand response. For account-level detail, use price realization by customer.

Check the dashboard before acting

Require all four match counts to equal one. Reconcile $56,750 net sales and $37,200 COGS to the same included ledger population. Prove the pricing bridge: $63,000 list sales minus $6,250 concessions equals $56,750 net sales. Prove margin separately: $56,750 minus $37,200 equals $19,550.

Keep the exception rows visible beside the totals. A total discount below the 10% policy limit still contains S2's 15% exception; aggregation conceals it.

Where it goes wrong

• Current list prices applied backward. Historical sales receive the wrong discount baseline.
• Mismatched costs. A replacement-cost scenario is presented as historical gross margin.
• Unlike units grouped together. A unit-price average across different package sizes or products has no stable interpretation.
• Credits or missing references treated as zero. Both can create artificial prices and margins.
• Exceptions called lost revenue. An approval or contractual allowance can explain the apparent gap.

Do this on your own file in Covirage

Bring your sales export, applicable price table and matching cost records, including dates, units and approval limits. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See revenue driver analysis for the supporting business context.