Blog · Territory, capacity and quota planning · Consumer goods
Build an FMCG planning workbook that connects consumer goods sales and gross margin with inventory, customer collections and supplier payments. Compare a worked monthly plan with actual results and a collection scenario.
An FMCG financial model connects unit sales, discounts and trade spending to profit, then connects customer balances, inventory and supplier terms to cash. FMCG means fast-moving consumer goods; US teams often call these consumer packaged goods, or CPG. The workbook is most useful when it explains why stronger sales have not produced more available cash.
Start with a monthly invoice export showing customer, product, quantity, gross sales and credits. Add the trade-spend schedule, cost of goods sold, opening and closing inventory valuations, customer receivables, supplier payables and company bank activity. Retain invoice and product identifiers so each balance has supporting rows.
Use one product unit consistently. A household cleaner sold as a bottle in one export and a case in another needs a documented conversion before any unit cost calculation. Keep inventory at cost, not selling price. For purchased merchandise, the IRS describes acquisition cost as invoice price less appropriate discounts plus transportation and other acquisition charges; use the company's approved inventory costing policy consistently.
For this fictional US household-products brand, all sales and inventory purchases are on account. Discounts and trade credits are included in customer accounts, with no separate trade liability. Operating expenses are paid within the month. There are no inventory write-offs, depreciation, capital expenditures, interest, taxes or financing flows in this simplified example.
These limits make the cash reconciliation exact. Add separate schedules when your actual files contain those movements. The broader working capital explanation covers the balance definitions; this workbook applies them to a monthly operating plan. Brands with their own plants can use a manufacturing financial model for the production-capacity assumptions.
Put row labels in A, plan in B, actual in C and a supplied collection scenario in D. Discounts and trade spending are both percentages of gross invoice sales, with separate, nonoverlapping deductions. These assumptions illustrate the method rather than a typical industry margin.
| Row and measure | Plan B (USD unless stated) | Actual C (USD unless stated) | Scenario D (USD unless stated) |
|---|---|---|---|
| 2: Units sold, bottles | 100,000 | 110,000 | 110,000 |
| 3: Gross price per bottle | 4.00 | 4.00 | 4.00 |
| 4: Invoice discount rate | 5.0% | 5.0% | 5.0% |
| 5: Trade-spend rate | 10.0% | 10.0% | 10.0% |
| 6: Product cost per bottle | 2.00 | 2.00 | 2.00 |
| 7: Gross invoice sales | 400,000 | 440,000 | 440,000 |
| 8: Invoice discounts | 20,000 | 22,000 | 22,000 |
| 9: Trade spending | 40,000 | 44,000 | 44,000 |
| 10: Net revenue | 340,000 | 374,000 | 374,000 |
| 11: Cost of goods sold | 200,000 | 220,000 | 220,000 |
| 12: Gross profit | 140,000 | 154,000 | 154,000 |
| 13: Operating expenses | 80,000 | 80,000 | 80,000 |
| 14: Operating profit | 60,000 | 74,000 | 74,000 |
Enter these Excel formulas in B7 through B12, respectively, then copy across to D:
=B2*B3
=B7*B4
=B7*B5
=B7-SUM(B8:B9)
=B2*B6
=B10-B11
Enter =B12-B13 in B14 for operating profit and copy across.
Microsoft documents SUM as adding cells or ranges, including the two deductions above. Covirage is not affiliated with Microsoft.
Gross margin is =B12/B10, copied across. It is 41.2% in every column, rounded to one decimal place. Dollar outputs are whole USD, with calculations performed before rounding. Profit increased, but that result says nothing yet about when customers paid or how much stock the brand purchased.
For a deeper analysis of customer trade rates and contribution, use the CPG financial model. The cash schedule below answers a different planning question.
Continue the same sheet with the following opening balances and supplied closing assumptions. Actual balances should tie to the inventory valuation, receivables aging and payables aging for the same cutoff date. Reconcile those reports with the ledger before interpreting a cash gap.
| Row and measure | Plan B (USD) | Actual C (USD) | Scenario D (USD) |
|---|---|---|---|
| 15: Opening customer receivables | 180,000 | 180,000 | 180,000 |
| 16: Closing customer receivables | 200,000 | 230,000 | 210,000 |
| 17: Opening inventory | 150,000 | 150,000 | 150,000 |
| 18: Closing inventory | 160,000 | 190,000 | 190,000 |
| 19: Opening supplier payables | 100,000 | 100,000 | 100,000 |
| 20: Closing supplier payables | 110,000 | 120,000 | 120,000 |
| 21: Opening cash | 250,000 | 250,000 | 250,000 |
| 22: Inventory purchases | 210,000 | 260,000 | 260,000 |
| 23: Customer collections | 320,000 | 324,000 | 344,000 |
| 24: Supplier payments | 200,000 | 240,000 | 240,000 |
| 25: Operating cash generated | 40,000 | 4,000 | 24,000 |
| 26: Closing cash | 290,000 | 254,000 | 274,000 |
| 27: Increase in trade working capital | 20,000 | 70,000 | 50,000 |
Enter these formulas in B22 through B27, respectively, then copy across:
=B11+B18-B17
=B10+B15-B16
=B22+B19-B20
=B23-SUM(B24,B13)
=B21+B25
=(B16+B18-B20)-(B15+B17-B19)
Actual purchases are $220,000 of goods sold plus the $40,000 inventory increase, or $260,000. Paying suppliers $240,000 leaves payables $20,000 higher than at the start. Actual collections are $374,000 + $180,000 - $230,000 = $324,000.
The independent check is =B14-B27-B25, copied across. Every column must return zero: operating profit less the increase in trade working capital equals operating cash generated under the stated assumptions.
Actual cash therefore reconciles as $74,000 - $50,000 of additional receivables - $40,000 of additional inventory + $20,000 of additional payables = $4,000. Compared with plan, profit improved by $14,000 while working capital absorbed an additional $50,000. Operating cash fell by $36,000.
Scenario D changes only closing receivables, from $230,000 to $210,000. Collections and closing cash improve by $20,000; revenue, gross margin and profit stay unchanged. The credit team should identify which invoices support that collection assumption. The days sales outstanding method helps assess collection timing without substituting a ratio for the invoice schedule.
Bring your supplied planning workbook, invoice export, inventory valuation and customer and supplier balances to Covirage. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the cash differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See forecast analysis for the planning context.
FMCG means fast-moving consumer goods, such as frequently purchased food, beverages and household products. US teams often use consumer packaged goods, or CPG. Define the products and units covered rather than assuming the labels establish a consistent reporting scope.
Profit can remain tied up in unpaid customer invoices or additional inventory. Supplier payments may fall due before customer receipts arrive. A cash schedule must include these balance movements as well as the period's operating result.
Start with inventory purchases and the opening and closing supplier payable balances. Cost of goods sold measures inventory consumed by sales; purchases also reflect stock building or reduction. Payment timing then depends on when those purchases become payable and are settled.