Estimated gross margin: build a plan and check actuals

Estimated gross margin is expected gross profit divided by expected net revenue, built from the prices, quantities and costs in your commercial plan. Calculate it by product, then compare the actual dollars and margin on the same basis. This is a forward commercial estimate, separate from the gross profit method used to estimate inventory.

Set the basis before building the estimate

The base definition remains gross profit margin. Your estimate changes the inputs from booked results to stated assumptions:

Estimated revenue = Planned units × Planned net selling price

Estimated COGS = Planned units × Planned unit cost of goods sold

Estimated gross margin = (Total estimated revenue − Total estimated COGS) / Total estimated revenue

Use a defined month or quarter, one currency, and a cost boundary that matches the actual P&L. The SEC's guide to financial statements describes gross profit as net revenue less cost of sales. That is the subtotal your actual comparison should reconcile to.

For purchased merchandise, include acquisition costs. IRS Publication 538 describes purchased inventory cost as invoice price less appropriate discounts, plus transportation and other acquisition charges. A supplier quote can therefore be incomplete. The controller should confirm the reporting policy used in the plan.

Keep marketing, corporate salaries and financing costs outside this estimate when they sit below gross profit in your ledger. If you need a broader operating comparison, gross margin versus EBITDA margin explains the different cost boundaries.

Build the workbook from the files you have

Start with the sales plan, pricing workbook and purchasing cost file. Match them by product identifier, effective date and unit of measure. A carton price against an individual-unit sales quantity will invalidate the estimate before any margin formula runs.

Use these columns in a spreadsheet:

In H2 through M2, respectively, enter these formulas and fill down:

=B2*C2
=B2*D2
=H2-I2
=E2*F2
=E2*G2
=K2-L2

Derive actual unit price from net revenue divided by units, and actual unit cost from matched COGS divided by units. Do not substitute the latest purchase quote for booked cost. If returns are material, model net units and any unrecovered return costs explicitly; the simple example below assumes no returns or additional rebates.

Work the plan and actuals by product

A US equipment distributor plans one quarter for two product lines. These are its assumptions and actual inputs; unit prices and unit costs are in USD.

Multiplying the inputs gives:

The plan margin is $85,000 / $200,000 = 42.50%. Actual margin is $72,400 / $186,500 = 38.82%, a decline of 3.68 percentage points. Percentages are rounded to two decimal places after calculation.

For rows 2 and 3 in Excel, calculate planned and actual total margin with these respective formulas. Microsoft's SUM function documentation explains how it adds a cell range:

=SUM(J2:J3)/SUM(H2:H3)
=SUM(M2:M3)/SUM(K2:K3)

Avoid averaging product percentages. Revenue weights changed as well as individual economics.

Covirage is not affiliated with Microsoft.

Prove the gap before explaining it

Gross profit missed plan by $12,600. Use planned unit economics for quantity, then actual quantities for price and cost. These formulas calculate quantity, price and cost effects, respectively:

=(E2-B2)*(C2-D2)
=E2*(F2-C2)
=-E2*(G2-D2)

The proof is $85,000 − $5,000 − $3,500 − $4,100 = $72,400. This ordered bridge allocates interacting effects consistently; label the convention when sharing it. It combines volume and product mix in the quantity effect.

Also reconcile actual net revenue and COGS to the ledger. A bridge can be mathematically correct while explaining an incomplete sales export. Only after both checks pass should sales review the price gap and procurement review the cost gap. For repeated monitoring, keep the same basis in a gross margin dashboard.

Where it goes wrong

• Stale cost assumptions. A quote that expires before the planned sales period understates expected cost. Record its effective date and owner.
• Gross prices against net actuals. Planned discounts omitted from the estimate create an apparent execution miss that was already predictable.
• Changing the cost boundary. Adding freight to actual COGS while leaving it out of plan makes the comparison inconsistent.
• Overwriting the original plan. Preserve the approved baseline and label later revisions. Otherwise the variance disappears when assumptions are updated.

An adjusted gross margin reconciliation addresses approved exclusions from reported cost. Keep those adjustments separate from changes to commercial assumptions.

Compare your plan with your own actuals in Covirage

Bring your pricing, planned volumes, unit costs and actual sales workbook to Covirage. Covirage maps the columns, and its tools compute and check the product margins and plan-to-actual differences; 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 wider planning context.