Forecasting revenue and cost with shared business drivers

Forecasting revenue and cost works best when both sides use the same volume, price and activity assumptions. Calculate revenue, COGS and operating expenses by business unit and period, then add the rows into a projected income statement. A scenario should change the relevant drivers and recompute every affected line.

Begin with the files and define the boundary

Use sales detail with units and net revenue, product-level COGS, and an operating-expense export by cost center. Add a planning sheet with expected units, net selling prices, unit costs, variable operating costs and committed fixed expenses. Include period, business unit, scenario version and assumption owner on each planning row.

Keep net selling price consistent with the ledger. Returns, allowances and rebates affect realized revenue: the IRS gross-profit guidance deducts returns and allowances from gross receipts before subtracting COGS. Document their forecast treatment instead of applying list prices to every unit.

This example stops at operating profit. The SEC's income-statement guide distinguishes gross profit after cost of sales from operating profit after operating expenses, before interest and income tax. Ensure depreciation and other relevant operating costs appear once in the appropriate expense line.

For the sales assumptions themselves, use the separate sales projection method. The task here is connecting those assumptions to costs and profit, with a common period and organizational scope.

Build a business-unit driver table

A hypothetical US distributor plans one month for its North and South business units. Its fixed operating expenses include all remaining operating costs, including depreciation; there are no additional operating lines in this example. The assumed unit costs already follow its inventory valuation policy.

Place business unit in A and the five input columns in B through F, with the units in rows 2 and 3:

The variable operating costs are selling and distribution expenses outside COGS under this company's policy. Hold that classification constant. If a business incurs delivery cost per shipment rather than per unit, use shipments as the driver for that expense and show the units-to-shipments assumption separately.

Calculate the connected income statement

Add output columns G through M using these formulas, then fill down:

G2 revenue: =B2*C2
H2 COGS: =B2*D2
I2 gross profit: =G2-H2
J2 variable operating cost: =B2*E2
K2 fixed operating cost: =F2
L2 operating profit: =I2-J2-K2
M2 operating margin: =IF(G2=0,"",L2/G2)

The same volume cell feeds revenue and both unit-based costs. A change to North's units therefore changes all three outputs. Extend the COGS inputs through forecasting cost of goods sold when product mix requires more detail.

Sum each dollar column. Total operating margin is $89,000 / $620,000 = 14.35%, rounded to two decimal places. Divide total profit by total revenue rather than averaging business-unit margins. The same aggregation principle applies to gross profit margin.

Change one driver and measure the effect

Test demand at 90% of base volume in both units. Keep net prices, unit costs and fixed operating expenses unchanged; assume capacity and supplier terms support those constant rates. North sells 3,600 units and South sells 2,700.

Revenue falls by $62,000 and operating profit by $23,900. The profit bridge proves the result: $89,000 - $62,000 + $33,300 of lower COGS + $4,800 of lower variable operating cost = $65,100. Fixed costs provide no offset.

Operating margin becomes $65,100 / $558,000 = 11.67%, rounded to two decimal places. That is a practical reason to connect cost behavior to revenue assumptions: a demand reduction does not automatically reduce every expense. Management can now discuss whether to accept the lower profit, change spending commitments or revise the demand assumption with evidence.

Reconcile before using the forecast

For every unit and scenario, revenue minus COGS must equal gross profit, and gross profit minus operating expenses must equal operating profit. The sum of unit outputs must equal the company view. Use =G2-H2-J2-K2-L2 as a row check; it must return zero.

Retain the approved scenario when actuals arrive. Map the ledger to the same units and cost classifications, then compare actual revenue and expense lines with that version. FP&A owns the reconciliation; commercial teams own volume and pricing, operations and procurement own unit costs, and cost-center managers own fixed commitments. Keep a dated explanation for each revised assumption, as described in budget versus forecast.

Where the forecast goes wrong

• Different volume assumptions across tabs. Revenue changes while the cost sheet still uses the old sales quantity. Reference a shared input.
• Every cost treated as variable. Rent, committed salaries and capacity steps need their own assumptions and effective dates.
• Costs included twice. A freight or labor line already in COGS should not also appear in operating expenses.
• Profit confused with cash. Collection timing, inventory and supplier payment terms require separate schedules before anyone draws a cash conclusion.

Check your planning workbook in Covirage

Bring a sample of your own planning workbook and actual revenue and cost export to Covirage. Covirage maps the columns, and its tools compute and check supplied scenario totals, differences and margins; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your own file.

See FP&A reporting for the team's broader planning and reporting work.