Sales forecasting for multiple products

Sales forecasting for multiple products starts with consistent product rows and ends with a company total that reconciles. Forecast quantities and net prices separately, then calculate revenue for each product and period. Keep the approved target visible if it differs from what those rows support.

Set one grain for the company outlook

Bring a sales-history export, a product forecast, and the approved company outlook. Each product forecast row needs a product identifier, period, scenario, forecast version, quantity, quantity unit, currency, and net price. The actual-sales file needs matching product identifiers and period definitions.

Choose a grain such as product, month, and sales region. If the forecast is monthly, aggregate transactions into months before comparing them. A quarterly total can conceal a delayed shipment that leaves the annual figure unchanged but changes the monthly cash and delivery plan.

Decide whether the outlook measures orders, shipments, invoices, or recognized revenue. The example below measures invoiced product sales for one month. It excludes taxes and assumes net prices already reflect discounts. It does not turn invoiced sales into an accounting revenue schedule.

Keep forecast-category definitions consistent through sales forecast categories. A product forecast built on commit assumptions should not be added to another product's best-case view without identifying that mixed scenario.

Calculate product revenue from quantities and prices

A US business-services supplier sells three packaged offers. All quantities in this example represent packages invoiced during the same month, and each product has a stable identifier. The prior-period unit figures are included for context, not used as an automatic growth formula.

Place this table in A1:E4. In Excel, enter the revenue calculation in E2 and copy down:

=C2*D2

Use an independent total check:

=SUMPRODUCT(C2:C4,D2:D4)

Microsoft's SUMPRODUCT documentation describes the corresponding-range multiplication used here. The result is $330,000, the same as the sum of product revenue. All amounts in the worked example are exact whole dollars.

Do not average the three prices and multiply that average by total units. Standard and Premium have different quantities, so they contribute different weights. If management needs an average net price, divide total revenue by total compatible units: $330,000 / 1,950 = $169.23, rounded to two decimals.

Reconcile the product sum to the target

Suppose the approved company target is $350,000. The supported product forecast is $330,000, leaving a $20,000 gap:

=350000-SUM(E2:E4)

That difference should remain an explicit unallocated target gap until an owner supplies a defensible change. Raising Premium from 250 to 300 packages at $400 would add $20,000 and close the gap mathematically. It does not establish that another 50 packages can be sold or delivered.

If the change is approved, save a new version with its assumption and owner. Preserve the original version for forecast-versus-actual review. The sales forecast template supports the wider outlook, while sales forecasting reports covers how to present the approved number and its changes.

On a longer file named Forecast, use columns A through E for product, period, scenario, units, and revenue. With a month date in H2, this sums only that month's base scenario:

=SUMIFS(Forecast!$E$2:$E$1000,Forecast!$B$2:$B$1000,H2,Forecast!$C$2:$C$1000,"Base")

Microsoft documents SUMIFS as adding values that meet multiple criteria. Use identical range boundaries and confirm the export fits inside them. A subtotal must not accidentally include both the base scenario and its replacement.

Compare actual sales and explain the difference

The month closes with these quantities and realized net prices. The forecast remains the original $330,000 version.

Actual revenue is 3.7% below forecast, rounded to one decimal: $317,800 / $330,000 minus one. The product differences sum to the company difference.

Split each difference with a consistent bridge:

Quantity effect = (Actual units − Forecast units) × Forecast price

Price effect = Actual units × (Actual price − Forecast price)

For Standard, (1,050 − 1,100) × $100 gives the quantity effect; 1,050 × ($98 − $100) gives the price effect. Their sum equals actual revenue minus forecast revenue. Use the same bridge convention for every product so the total remains additive.

Where product totals go wrong

• Counting bundles twice. Include the bundle or its component sales according to the chosen reporting definition, not both.
• Adding incompatible units. Revenue can reconcile even when a companywide quantity total is meaningless.
• Ignoring substitution. Record whether a launch takes demand from another product before adding all its sales as growth.
• Mixing versions. Preserve the scenario and approval date when combining product submissions.
• Treating missing rows as zero. Confirm whether a product has no sales or an incomplete export. Investigate availability separately through forecasting lost sales.

Covirage is not affiliated with Microsoft.

Reconcile your product files in Covirage

Upload your product history, approved forecast, and company total; Covirage maps the columns, and its tools compute and check the comparisons while the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data. See sales forecast analysis for the supporting review workflow.