S&OP forecasting: reconcile demand, supply and revenue

S&OP forecasting reconciles expected demand, feasible supply and financial assumptions for the same product families and months. Start with the exports each team already uses, preserve their original versions, and calculate a bridge from demand value to constrained sales value and then to budget.

The useful output is a decision list. A revenue gap caused by insufficient supply needs a different owner from a gap caused by weak demand or an outdated price assumption.

Set the comparison before opening the files

Sales and operations planning is a cross-functional process. IBM's supply chain planning explanation describes S&OP as aligning sales, marketing and production around common demand forecasts and supply constraints. The calculation below is the reconciliation for that conversation, using product-family totals rather than detailed production schedules.

Choose a planning month, an as-of date, a product-family mapping and a common unit of measure. Save the demand version separately from the supply version. A demand file revised after the meeting cannot serve as evidence of what the team knew before it.

Bring these fields:

Use net prices after expected discounts for the same units being planned. Separate tax and freight treatments if the financial plan does. Where products within a family have different prices, value their detailed rows first and then aggregate; a family average price can conceal a change in mix.

The distinction between a fixed financial target and an updated outlook belongs in budget versus forecast. Keep both columns here so the reconciliation does not erase that difference.

Calculate feasible volume from the exports

For an illustrative US industrial distributor, the December planning file contains the following assumptions. All stock and demand quantities are individual units; the example assumes goods are interchangeable within each family and receipts arrive before they are needed.

Treat the target closing stock as protected for this scenario. If management decides to consume it, create a separate scenario with the changed target.

Available-to-sell units = MAX(0, opening units + receipt units - target closing units)

Feasible sales units = MIN(demand units, available-to-sell units)

In Excel, put the table headers in row 1 and the families in rows 2 through 4, with family in A, demand in B, opening in C, receipts in D, target in E, price in F and budget in G. Add these formulas and fill them down:

H2 = MAX(0,C2+D2-E2)
I2 = MIN(B2,H2)
J2 = B2*F2
K2 = I2*F2
L2 = J2-K2
M2 = G2-K2

H is available volume, I is feasible volume, J is demand value, K is constrained value, L is the supply-related value gap, and M is the total gap to budget. All values below are exact dollars; no rounding is needed.

The valves family has spare supply, but it cannot satisfy demand for pumps or filters. Calculate the minimum on each family before summing feasible volumes.

Prove the financial bridge

The $250,000 budget exceeds demand value by $20,000. Supply constraints then remove another $19,000, leaving a constrained planning value of $211,000:

250,000 - 20,000 - 19,000 = 211,000

The same $39,000 budget gap must equal the sum of the family budget gaps. That check distinguishes a reconciliation from a list of disconnected totals.

For an independent value check, Excel's SUMPRODUCT multiplies corresponding array entries and sums the products, as Microsoft documents:

=SUMPRODUCT(I2:I4,F2:F4)
=SUM(G2:G4)-SUM(K2:K4)-SUM(M2:M4)

The first formula must return $211,000. The second must return zero. Covirage is not affiliated with Microsoft or IBM.

Also reconcile the stock balance. Pumps finish with 200 + 800 - 900 = 100 units; valves with 400 + 1,900 - 2,000 = 300; filters with 300 + 1,000 - 1,150 = 150. Total closing stock is 550 units, which agrees with 900 opening + 3,700 receipts - 4,050 feasible sales.

Turn the gap into an owned decision

Suppose operations confirms another 100 pumps arriving before demand is due. Change only the pumps receipt assumption from 800 to 900. Feasible pump sales rise from 900 to 1,000, adding 100 × $120 = $12,000 to the planning value.

The constrained total becomes $223,000 and the budget gap becomes $27,000. The remaining gap consists of $20,000 between budget and demand value plus $7,000 of constrained filter demand. Extra pump supply cannot close either part.

Record the proposed receipt change, evidence, approver and decision date. Sales should investigate the commercial gap; operations should investigate filter availability; finance should confirm whether price or mix assumptions explain part of the budget difference. Use S&OP KPI metrics to monitor the resulting process, and SKU and site forecast bias when persistent demand errors require deeper diagnosis.

Where it goes wrong

• Mixed versions. Comparing this week's demand with last month's supply creates gaps nobody can reproduce.
• Monthly receipts treated as always available. A delivery after the customer's due date can leave a shortage despite a balanced month. Review dated exceptions separately.
• Incompatible units or families. Cases, individual units and unlike products cannot share an unchecked total.
• Budget substituted for demand. A target is not evidence that customers will buy the volume required to reach it.
• Stock counted twice. Opening stock must exclude receipts already included in the same opening balance; future receipts belong only in the receipt column.

Reconcile your own S&OP exports in Covirage

Bring a sample of your own demand, inventory, supply and financial planning files to Covirage; it maps the columns, and its tools compute and check the comparisons, with the external AI model explaining every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your files.

See forecast analysis for the supporting planning context.