Short answer: Export units sold or shipped by SKU and destination state for the program's data year from your business system, map each SKU to its packaging configuration, multiply component weights by units, and group the results into each program's material categories. Direct shipments allocate by ship-to state; sales through distributors need a documented allocation method.
The calculation, in one line
Summed across every component and grouped by material category, that gives the quantities state programs ask for. The hard part is not the math. It is having trustworthy inputs on both sides and a clean link between them.
Step 1: Export sales the same way every time
Most business systems can export transactions with item, quantity, date and shipping address. Pull a summary of units by SKU, ship-to state and period for the data year. Before using it:
- Handle returns and credits consistently. Decide whether you report gross shipments or net of returns, and document the choice.
- Exclude internal movements. Transfers between your own warehouses or entities are not sales into a state.
- Watch samples and promotional units. They still carry packaging and may still be reportable.
- Save the export criteria. Next year's data should be pulled the same way.
Step 2: Map SKUs to packaging configurations
Many SKUs share packaging. Ten flavors in the same bottle and carton use one configuration with different labels. Build a mapping table that assigns every sold SKU to a configuration ID, and every configuration to its components. Kits, variety packs and seasonal items may combine several configurations. Retired SKUs still count if they sold during the data year.
| sku_id | packaging_config_id | units_per_case | notes |
|---|---|---|---|
| SKU-1042 | CFG-PUMP-30 | 24 | Illustrative |
| SKU-1043 | CFG-PUMP-30 | 24 | Same bottle, different label |
| KIT-2201 | CFG-KIT-GIFT | 6 | Contains two configurations |
Step 3: Allocate to states
Direct-to-consumer and direct-to-retailer shipments are straightforward: use the ship-to state. Sales to national distributors or retailers that move goods across state lines are harder, because you may not know where units end up. Options include:
- Distributor or retailer sell-through reports by state.
- Retail point-of-sale data by state, where available.
- A documented proxy, such as each state's share of your direct sales or population-based estimates, used only when better data is unavailable.
Whatever method you use, write it down, apply it consistently and note it in your assumptions log. Program rules on allocation may differ, so check current guidance for each state.
Step 4: Calculate and group
Multiply through for every component, convert to the unit each program requests, and group by each program's material categories. Keep the calculation in a form you can rerun when data changes, such as a spreadsheet with formulas rather than pasted values.
Step 5: Reconcile and keep evidence
- Reconcile total units back to the original export.
- Check that every sold SKU mapped to a configuration; list any that did not.
- Keep the export files, mapping table and calculation together with the report.
CertaRelay recommendation
Do the SKU-to-configuration mapping once, carefully, and maintain it as products launch and retire. It is the joint that every future report depends on.
How CertaRelay helps
We work from ordinary business-system exports and sales reports, with no integration project. CertaRelay builds and maintains the mapping, allocates quantities by state, documents assumptions and prepares report-ready files for your approval. See packaging data for EPR and Managed Packaging EPR Operations.