Build one source of pricing data

Your report suggested a move on the state of your pricing data.

Every later pricing step needs one answer to a plain question: what did this customer pay for this product last quarter? This move guide builds one source that answers it in hours and ties to the ledger.

If you took the consumer check, go to the consumer section. If your products reach shoppers through retailers, go to the section for brands.

1. When it applies

Use this move guide if your report placed this practice in the lowest two stages.

If it already sits at the middle stage or higher, skip this move guide.

If your report gave the foundation moves, this is one of them. Run it in the same weeks as naming the owner.

2. What to do

Seven steps, in this order. They take four to six weeks for a first version you can use. Cleaning carries on after that.

1. Pull two to three years of invoice lines. Take one line per invoice line, by customer and product. Flag credits and returns. Table A below lists what to pull.
2. Strip the one-offs before you analyse. Take out credits, returns, promotional items, warranty, staff sales and zero-value lines. Keep a list of what you removed and why.
3. Fix the hierarchies. Give each customer one parent and each product one category.
4. Attach what sits off the invoice. Put rebates, settlement discounts and freight against the invoice lines they belong to. Where you cannot, allocate at the lowest level you can and write down the rule.
5. Reconcile to the ledger. Revenue in the source should match the profit and loss within a tolerance you state, such as 1%.
6. Log the defects. Give each one an owner. Fix the ten worst this quarter.
7. Agree it as the one source. Finance and sales both use it. Other spreadsheets stop being used for pricing decisions.

The free worksheet /worksheets/pricing-b2b/data-foundation does the recording. It holds the agreed source of each field, the allocation rules, the ten worst defects and the tests.

3. What to produce

One table, plus the worksheet above.

Table A. What to pull and what to strip

Work out profit for each invoice first, then roll it up to customer and product. Rolling up from product averages hides the small orders.

4. A worked example

Illustrative. Written for this move guide. It describes no client.

A food-service distributor sells about 6,000 products to 3,000 cafes, restaurants and institutions.

What the pull showed.

• Two years of invoices came to about four million lines.
• Credits, returns, samples and zero-value lines made up 6% of them. They came out first, and the list of removals went to finance.
• One buying group appeared under 14 account numbers. They were joined under one parent.

Off the invoice.

• Rebates were booked at head office in one sum. They were spread back to customers from the rebate agreements.
• Freight was allocated by delivery, from the routing system.

The test.

• Revenue in the source matched the ledger within 1%.
• Ten defects were logged with owners. The worst was product codes that changed halfway through the first year.

Six weeks on.

• Finance and sales each asked what the largest twenty customers paid for the top fifty products last quarter.
• They got the same answer, from the same source, on the same afternoon.

On a retake, the practice could be answered at the middle stage.

What it looked like in our work

From our own work. Redrawn.

A building products business built its profit model in stages. It did not try for the best allocation on the first pass. Each cost moved one stage at a time.

Each stage made the answer sharper than the last.

Three systems, three totals

From our own work. Figures changed.

A distributor of business supplies asked three systems for the same number: net invoice value for the year so far. It got two answers.

The two totals that matched did so by chance, for different reasons. Agree one source for each measure, write down what it covers, and reconcile the others to it.

Until the totals agreed, every later analysis started with an argument about the numbers.

Cumulative contribution by product

Illustrative.

Once the data is in one place, rank the products by margin, best first, and add up their contribution. Here, about a third of the products earn four fifths of it. The last fifth take back about a tenth of the peak.

Past the peak, a product needs a higher price, a lower cost, or a reason to stay. At the top, a lower price may win volume worth having.

5. Effort and dependencies

What you need

• Access to invoice lines, the rebate agreements and freight records.
• The general ledger for the same period, to reconcile against.

How long

• Four to six weeks for a first usable version.
• Cleaning carries on after that, ten defects at a time.

What gets in the way. We have seen these in our own work:

• Expecting speed. Our own pricing process notes found that invoice-level data took weeks to source and clean. Even then, the cleaning had only begun.
• Aiming for perfect on the first pass. The same notes say to refine the profit analysis in stages. Never try for the top level the first time.
• Rolling up from item level. Work out profit for each invoice first, then roll it up.
• Tangled hierarchies. A beverages business ran several customer hierarchies and had no single list price. Nobody could see what any customer paid.

6. How will you know it worked?

The test is whether finance and sales answer a pricing question from the same source. Retake the check after the first version is in use. This should be true when you answer:

For consumer businesses

This section is for the B2C Consumer Pricing Health Check. The job is the same, with transactions in place of invoices and a promotion flag on every line.

When it applies

Use it if your report placed this practice in the lowest two stages.

What to do

1. Pull two to three years of transactions, by product, channel and week. Flag returns.
2. Strip the one-offs. Take out staff sales, returns booked to the wrong week and zero-value lines. Keep the list.
3. Fix the hierarchies. Give each product one category, with the same item code in stores and online.
4. Attach what changes the price. Put the promotion flag, vouchers and discount codes, delivery fees and payment fees against each sale.
5. Reconcile to the ledger within a tolerance you state.
6. Build the weekly view. It shows price, units, margin and promotion status by product and channel. Finance and trading both use it.

The free worksheet does the recording: /worksheets/pricing-b2c-direct/data-foundation, for the agreed source of each field, the defects and the weekly view.

What to produce

Table B. What each weekly line carries

A worked example

Illustrative. Written for this move guide. It describes no client.

A pet supplies retailer sells through 60 stores and a website.

• Online discount codes were recorded as a lower price, with no flag. Nobody could tell a promotion from a price cut.
• The digital team carried the code ID into each sales record.
• Stores and the website used different item codes for the same product. One code list now serves both.
• After six weeks, trading and finance read margin by product and channel from one weekly view.

On a retake, the practice could be answered at the middle stage.

Effort and dependencies

• Who. The pricing owner sponsors it. One finance analyst works on it full time. The digital team carries the promotion flags.
• What you need. Transactions from every channel, the promotion calendar and cost by product.
• How long. Four to six weeks for the first weekly view.
• What gets in the way. Costs updated quarterly while prices change weekly. Agree how often cost is refreshed before you build the view.

How will you know it worked?

For brands selling through retailers

This section is for the B2C via trade Brand Pricing Health Check. For a brand, the first data job is one list of customers and one list of products that every function uses.

When it applies

Use it if your report placed this practice in the lowest two stages.

What to do

1. Count the lists you have now. Note who keeps each customer list and each product list, and how many entries each has.
2. Agree the levels. The customer hierarchy runs banner, group, store. The product hierarchy runs category, brand, pack.
3. Merge the duplicates in your top hundred customers this quarter.
4. Give each list an owner. New customers and products are added by that owner only.
5. Point every report at the agreed lists. Sales, planning and finance stop using their own.

The free worksheet does the recording: /worksheets/pricing-consumer-trade/customer-product-hierarchies, for the count, the levels, the merge and the owner.

What to produce

Table C. The lists you have now

A worked example

Illustrative. Written for this move guide. It describes no client.

A biscuit maker sells through grocery, convenience and a national distributor.

• Sales, demand planning and finance each kept a customer list. One grocery banner appeared under three names.
• The agreed hierarchy ran banner, group, store, and every report used it.
• Forty duplicates in the top hundred customers were merged in one quarter.
• The commercial analyst became owner of both lists, and new accounts came through that one person.

On a retake, the practice could be answered at the middle stage.

Effort and dependencies

• Who. The pricing owner sponsors it. A commercial analyst does the work, with IT for the systems.
• What you need. Every customer and product list in use, and the reports that draw on them.
• How long. About a quarter for the top hundred customers.
• What gets in the way. Store-level lists kept by the merchandising team. Bring them under the same banner and group levels.

How will you know it worked?

The worked examples are illustrative.

Think we've used your work without proper credit? Tell us and we'll fix it.