Sales mix forecast: product shares, revenue and margin

A sales mix forecast projects the share of sales each product will contribute and converts those shares into revenue and gross profit. Calculate both unit mix and revenue mix: a product can represent a small share of units but a large share of dollars. Reconcile the product totals before using the forecast to choose promotions, purchasing quantities or capacity.

Start with product rows and a defined denominator

Bring a sales-line export containing period, product code, product family, quantity, net revenue and cost of goods sold. Add a separate assumption table containing forecast quantities, expected net selling prices and expected unit costs for the same products and period. Use a stable product code to match the two files.

Net selling price should reflect expected discounts, rebates and returns. The IRS discussion of gross profit describes net receipts after returns and allowances and subtracts cost of goods sold to arrive at gross profit. That basic relationship is useful here; use your company's consistent accounting policy for the costs assigned to each product.

Choose quantities that can reasonably be added. Different filter modules sold by the piece can share a unit denominator. Consulting hours and physical modules should have separate unit-mix views, even though both can contribute to a revenue-mix total.

Unit mix = Product units / Total comparable units

Revenue mix = Product net revenue / Total net revenue

The forecast below changes composition explicitly. It does not estimate total market demand; a top-down sales forecast addresses that separate question.

Build the forecast in a spreadsheet

A fictional Ohio industrial supplier forecasts three families of replacement filter modules for November 2026. All prices and costs are USD per module. Quantities, prices and costs are management's assumptions, rather than observed results.

Put the columns in A:G and the products in rows 2:4. Enter these Excel formulas in E2:G2 and fill down:

=B2*C2
=B2*D2
=E2-F2

In H2, calculate unit mix. In I2, calculate revenue mix. In J2, calculate product gross margin:

=B2/SUM($B$2:$B$4)
=E2/SUM($E$2:$E$4)
=G2/E2

Percentages are displayed to one decimal place; formulas use full precision. Standard accounts for half the units but only 29.4% of revenue because its price is lower. Total gross margin is $138,000 / $340,000 = 40.6%, rounded. It is not the simple average of product margins.

If assumptions start as shares, put the total unit forecast in L1 and the product shares in H2:H4. Calculate each product's units with =$L$1*H2. Resolve any whole-unit rounding difference explicitly rather than silently increasing the total.

Prove that the forecast holds together

The following checks should return zero, zero and the same gross margin as the total above:

=SUM(H2:H4)-1
=SUM(E2:E4)-SUM(F2:F4)-SUM(G2:G4)
=SUM(G2:G4)/SUM(E2:E4)

Check total forecast revenue independently by multiplying quantities by prices and summing:

=SUMPRODUCT(B2:B4,C2:C4)

Microsoft documents SUMPRODUCT as summing the products of corresponding ranges. This returns $340,000. Covirage is not affiliated with Microsoft.

For actuals, aggregate every invoice and credit line once into the product-period table. Product revenue must tie to the sales ledger and product COGS to the same accounting period. Investigate unmatched codes before calculating shares; leaving them out changes every denominator. The gross profit margin guide covers cost definitions in more detail.

Compare actual mix without confusing price and volume

Suppose actual units total the same 2,000 modules, but customers buy more Standard modules. In this controlled comparison, selling prices and unit costs remain exactly as forecast. Only the quantity composition changes.

Revenue falls by $30,000 and gross profit falls by $17,000 even though total units do not change. The gross-profit bridge is visible by product: Standard adds 200 × $30 = $6,000; Premium loses 100 × $80 = $8,000; Specialty loses 100 × $150 = $15,000. The net change is $6,000 - $8,000 - $15,000 = -$17,000.

Actual margin is $121,000 / $310,000 = 39.0%, rounded. Using unrounded margins, the decline is 1.6 percentage points, rounded. In a real export, price and cost may move too; use price-volume-mix analysis to separate those effects before attributing the entire gap to composition.

The business choice is whether to restore demand for Premium and Specialty, change the promotion, or accept the lower profit in exchange for another commercial objective. Sales must establish why customers shifted, and operations must confirm whether higher-margin products were available. A mix percentage alone cannot distinguish preference from a stockout.

Where sales mix forecasts go wrong

• Mixing quantity units. Cases, pieces and service hours create a misleading combined unit share. Convert to a consistent unit or split the analysis.
• Applying unit shares to revenue. A 20.0% unit share does not imply a 20.0% revenue share when prices differ.
• Averaging margins. Calculate total gross profit divided by total revenue. Product margins need revenue weights.
• Ignoring returns and missing codes. Both alter the totals and the product composition. Reconcile the full export before reading the shares.
• Calling every gap a mix effect. Actual discounts, cost changes and total-volume differences need separate explanations. Review material assumptions in the sales forecast meeting.

Check your product forecast in Covirage

Bring your product assumption workbook and actual sales export with quantities, net revenue and costs. Covirage maps the columns, and its tools compute and check product shares, revenue and margin; the external AI model explains the comparison 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 Covirage forecast analysis for the supporting workflow.