This is a retail & fmcg problem we approach through our Data to Data service line.
The problem
You make a hundred or more products from a small number of raw materials. The main input is perishable and its price moves with season, supply and a quality attribute — fat content, grade, moisture — so the same volume costs differently from week to week and from supplier to supplier.
Costing is done in a spreadsheet, quarterly if you are disciplined. Between revisions, the true cost of every line drifts away from the costed figure, and nobody knows by how much until the period closes.
Pricing then happens on instinct plus whatever the competition is doing on shelf. When input prices spike, the choice is made under time pressure with no view of which lines are absorbing the increase and which have gone underwater.
Why it’s hard
One input moves and the whole catalogue re-costs. Shared raw materials mean cost changes propagate through every recipe that uses them, in different proportions. A cost model that is not built on the recipe structure cannot compute that, and a spreadsheet per product guarantees that some products get updated and others do not.
The raw material is priced on a quality attribute, not just volume. Paying for milk by fat content means the effective input cost per unit of finished product depends on the composition of what arrived, not the litres. Volume-based costing systematically misprices the entire catalogue in one direction.
Wastage and spoilage are costs that vary with time and handling. They belong in the unit cost and they are almost never recorded in a form that lets you put them there. Most manufacturers hold wastage as a variance after the fact rather than as an input to costing.
Seasonality moves supply and demand in opposite directions. Input supply peaks are rarely aligned with demand peaks, so the periods where margin is most at risk are exactly the periods where the historical average is least useful as a guide.
Pricing is a constrained problem, not an optimisation. The mathematically optimal price is frequently unavailable: trade agreements, promotional commitments, shelf price ceilings, price-point conventions and cannibalisation between your own lines all bound it. An optimiser given only an objective function will return answers the commercial team cannot use, and after the third one they will stop opening it.
The output has to be trusted by people who already have a method. A recommendation that contradicts a category manager’s judgement without showing its working gets overridden and then ignored. Explainability is the adoption mechanism, not a compliance nicety.
How we approach it
Recipes as structured data, first. Each product’s recipe is stored as structured formulas with raw material quantities and dependencies. This is the artefact everything else computes against, and building it properly is what makes the catalogue-wide re-cost possible in one pass.
Daily ingest from the systems that already know. Point-of-sale transactions, inventory logs including usage and wastage, and supplier pricing for each input, collected daily and cleansed through ETL into a central store. Cost accuracy is limited by wastage data quality more than by anything modelling can fix, so this is where the early effort goes.
Cost computation across the whole catalogue. The backend recomputes cost for every product on each data update, so cost is a current figure rather than a quarterly artefact.
Forecasting on the inputs, not on the price you want. Time-series models predict input pricing and cost trends over short and mid-term horizons using seasonality and market data, refreshed against daily feeds. Forecasting the input is tractable; forecasting your own optimal price directly is not, because it depends on decisions nobody has made yet.
Constrained non-linear optimisation for the recommendation. Price optimisation runs as a constrained problem — input price volatility, storage cost, spoilage risk and competitive position bound the solution. Scenarios rather than a single answer, so the commercial team sees the margin consequence of each.
A dashboard aimed at the decision. Recommended price per SKU, margin projection under varying cost scenarios, and seasonal and inventory-driven alerts. The reason a recommendation moved is visible next to the recommendation.
What we would not do. We would not push prices to market automatically. We would not build the optimiser before the cost model is trusted, because an optimiser on wrong costs produces confident bad advice faster than a human could. And we would not model a catalogue at product level when the economics live at recipe level.
What it takes
Recipes, in a form someone will vouch for. Usually they exist across spreadsheets, production notes and one person’s memory. Consolidating them is a project deliverable and it needs a production owner, not a data engineer.
Wastage recorded as data. If it is currently a period-end variance, expect a phase of instrumenting it. This is the most common reason a cost model disappoints.
Daily supplier pricing. Weekly or monthly input pricing means recommendations lag the market by exactly that interval.
POS and inventory access. Sales and stock movement, at SKU level, ideally daily.
A commercial owner with authority to price. The system produces recommendations. Someone has to be accountable for accepting them, and their scepticism during the first month is the most valuable input the project gets.
Where this has been done
This is a shipped reference under confidentiality. Between 2021 and 2022 we built a price forecasting and cost optimisation platform for a dairy manufacturer: recipe-level cost modelling, daily ETL from POS, inventory and supplier pricing, time-series forecasting on input prices, and constrained non-linear optimisation producing per-SKU price recommendations through a dashboard.
Three figures from that engagement: a unified pricing engine covering more than 100 SKUs, margin improvements of 5–12% across products during volatile seasons, and forecast reporting at better than 90% accuracy.
The engagement was delivered under confidentiality, which is why this page describes the work rather than the client.
The method transfers to any manufacturer whose cost base is a formula over volatile inputs: FMCG, beverages, formulation-based cosmetics and pharmaceuticals, and retail chains pricing dynamically against stock position.