Blog · Forecast and pipeline · Finance and FP&A teams
Forecast COGS from expected sales units, product mix and the cost assigned to each unit. Follow a product-level spreadsheet example, reconcile the changes and compare the forecast with actual ledger costs.
Forecasting cost of goods sold means multiplying expected sales quantities by the cost expected to be recognized for those products. Calculate each product separately, then total the rows so volume, product mix and unit-cost changes remain visible. A single percentage of revenue can hide all three.
Use a recent closed month's product sales export and COGS detail as the baseline. Add the forecast sales quantities and the cost assumptions applicable to that forecast month. Keep product, business unit, currency and period consistent across both files.
The minimum planning columns are product code, baseline units sold, baseline unit cost, forecast units sold and forecast unit cost. Keep the source document, assumption owner and effective date beside the forecast inputs. Use the historical cost of goods sold method to establish the accounting boundary before projecting it forward.
For a distributor, include the costs that enter inventory under its policy. The IRS description of purchased merchandise cost includes invoice price after appropriate discounts and transportation or other acquisition charges. A supplier's quoted price alone may therefore be incomplete.
Forecast the cost flowing out of inventory, which can differ from the newest purchase price. If existing stock will be sold first, reflect those inventory layers in the projected unit cost. Have the controller confirm the treatment of rebates, write-downs and other adjustments outside the ordinary product cost.
A hypothetical Ohio distributor is planning its next month. It sells standard and specialty parts. The baseline contains 10,000 units; the forecast contains 12,000. All costs below are USD, and all units use the same selling unit of measure.
Put product in column A and the remaining fields in B through G. Product rows occupy rows 2 and 3; the total is row 4.
| Product | Baseline units B | Baseline unit cost C (USD) | Forecast units D | Forecast unit cost E (USD) | Baseline COGS F (USD) | Forecast COGS G (USD) |
|---|---|---|---|---|---|---|
| Standard | 8,000 | 12.00 | 8,400 | 12.50 | 96,000 | 105,000 |
| Specialty | 2,000 | 30.00 | 3,600 | 31.50 | 60,000 | 113,400 |
| Total | 10,000 | 12,000 | 156,000 | 218,400 |
Calculate the two expense columns and fill down:
F2: =B2*C2
G2: =D2*E2
F4: =SUM(F2:F3)
G4: =SUM(G2:G3)
The forecast is $218,400. Its weighted unit cost is $218,400 / 12,000 = $18.20, compared with $156,000 / 10,000 = $15.60 in the baseline. That increase does not mean every supplier raised prices by the same amount. Specialty parts also take a larger share of sales.
Use a declared bridge order. First change total volume at the baseline mix and cost. Next change the mix at baseline costs. Finally apply forecast unit costs to forecast quantities. This order assigns the interactions consistently.
| Bridge step | Calculation | COGS change (USD) |
|---|---|---|
| More total units | (12,000 - 10,000) x 15.60 | 31,200 |
| Different product mix | (8,400 x 12 + 3,600 x 30) - 12,000 x 15.60 | 21,600 |
| Higher unit costs | 8,400 x (12.50 - 12) + 3,600 x (31.50 - 30) | 9,600 |
| Total change | 31,200 + 21,600 + 9,600 | 62,400 |
The proof is $156,000 + $62,400 = $218,400. These formulas reproduce the bridge directly from the product table:
Volume: =(SUM(D2:D3)-SUM(B2:B3))*SUM(F2:F3)/SUM(B2:B3)
Mix: =SUMPRODUCT(D2:D3,C2:C3)-SUM(D2:D3)*SUM(F2:F3)/SUM(B2:B3)
Unit cost: =SUMPRODUCT(D2:D3,E2:E3)-SUMPRODUCT(D2:D3,C2:C3)
Keep the bridge order unchanged in later reviews. Reordering it changes which driver receives the interaction, even though the total movement is unchanged. For the broader effect on profit, connect these inputs to forecasting revenue and cost.
When the month closes, replace forecast quantities and unit costs with actual quantities and recognized costs. The actual export contains:
| Product | Actual units | Actual unit cost (USD) | Actual COGS (USD) |
|---|---|---|---|
| Standard | 8,200 | 12.60 | 103,320 |
| Specialty | 3,800 | 32.00 | 121,600 |
| Total | 12,000 | 224,920 |
Actual COGS exceeds forecast by $224,920 - $218,400 = $6,520. Total units match, so the volume effect is zero. Moving 200 units from standard to specialty at forecast costs adds 200 x ($31.50 - $12.50) = $3,800. Actual unit costs add 8,200 x $0.10 + 3,800 x $0.50 = $2,720. Together, $3,800 + $2,720 = $6,520.
All displayed calculations are exact to the shown cents or whole dollars; no intermediate rounding is used. Reconcile the actual product total to the ledger. List unmatched adjustment entries separately rather than forcing them into a unit rate. Procurement owns the rate explanation; sales and operations explain the quantity and mix assumptions.
Bring a sample of your own product cost plan and actual COGS export to Covirage. Covirage maps the columns, and its tools compute and check the supplied product totals and differences; 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 finance team's wider reporting work.
A stable historical percentage can provide a reasonableness check. It becomes less useful when selling prices, product mix or supplier costs change. Build the operating forecast from product quantities and costs, then compare its implied COGS percentage with history.
Only when the inventory movement and other relevant adjustments make them equal. Purchases may remain in inventory rather than become an expense immediately. Keep a separate inventory roll-forward and check which costs are expected to flow into goods sold.
Procurement should support purchase-price and inbound freight assumptions, operations should support production requirements, and finance should approve the valuation basis. Give each assumption an effective period and an owner so a forecast change can be traced to evidence.