The cost model data extract, field by field
Quick answer. A cost-to-serve or TDABC profitability model runs on twelve extracts. Four carry cost: general ledger expense lines, payroll and headcount, depreciation, and the cost-centre hierarchy. Five carry the work: invoice lines, order lines, deliveries, returns and credit notes, and service contacts. Three carry master data and capacity: the customer master, the product master, and the working calendar. All of it at line level, joinable on customer, product and document keys, covering the same months. This page lists the fields in each extract, the grain, how much history, and what we do when a field does not exist.
Every engagement reaches the same first question. Somebody asks what we actually need, and whether their systems can produce it.
This is the answer, written down as a specification rather than as a conversation: the extracts, the fields inside each one, the grain the file has to be at, how much history, and the fallback when a field does not exist. Take it to your IT team as it stands.
Why the extract decides the project
Cost models rarely fail on the accounting. They fail because the design promised a granularity the source systems could not feed, and nobody found out until the build was well under way. We wrote about that gap in from blueprint to a model that runs.
So we start with a data request rather than a workshop. The files usually confirm what everyone believed. Sometimes they show that order lines are summarised at invoice level, or that delivery drops are not recorded against the customer, and the model design changes in the first week instead of the sixth. That is a good day, even though it does not feel like one.
This page is the specification. The shorter version, the five sources a TDABC model feeds on and why each one matters, is in the data you need for a TDABC model. Read that one first if you are deciding whether to start. Forward this one to the person who will write the query.
Three rules that matter more than the field list
- Line level, not summary. One row per invoice line, order line, delivery, return or contact. A summarised extract cannot be un-summarised later, and every interesting difference between two customers lives in the detail that averaging removed.
- Keys that join. Every transaction file needs the customer key, the product key and the document number, in the same format the master data uses. Names instead of codes cost more time than every other data problem put together.
- The same period, everywhere. Cost files and transaction files have to cover the same months. A model that prices twelve months of work with nine months of cost reconciles to nothing and gets dismissed in one meeting.
The four files that carry cost
| Extract | Fields we ask for | Grain | History | If it does not exist |
|---|---|---|---|---|
| General ledger, expense side | Period, cost centre, GL account, account description, amount, currency | One row per cost centre × account × month | Same months as the transaction files | A trial balance by cost centre works to start. Account-level detail is what lets us separate people cost from space, systems and fleet. |
| Payroll and headcount | Period, cost centre, role or grade, headcount, full-time equivalent, fully loaded employment cost | One row per cost centre × role × month | Same months as the ledger | Department totals plus a headcount list will do. Individual salaries are never needed and we prefer not to receive them. |
| Fixed assets and depreciation | Asset class, cost centre, depreciation charge per period, useful life | One row per asset class × cost centre × month | Same months | Take the depreciation lines straight from the ledger. This file only adds precision for asset-heavy operations. |
| Cost-centre hierarchy | Cost centre code, description, parent, function, site | One row per cost centre | Current structure | An organisation chart and a site list, translated into the codes the ledger actually uses. |
These four produce the cost pools and the capacity cost rate. What they cannot do on their own is say who consumed them.
The five files that carry the work
| Extract | Fields we ask for | Grain | Why it matters | If it does not exist |
|---|---|---|---|---|
| Sales invoices | Invoice number, date, customer code, ship-to, product code, quantity, unit of measure, gross value, discounts, net value, currency | One row per invoice line | It is the revenue side of every profitability view and the anchor everything else joins to. | Nothing substitutes for this one. If invoice lines cannot be extracted, the scope has to change before the project starts. |
| Sales orders | Order number, order date, requested date, customer code, channel or entry method, lines per order, urgency flag, amendment count | One row per order line | Orders carry the behaviour that invoices hide: how often, how small, how urgent, how often changed. | Derive an order profile from the invoices and record that order behaviour is approximated. Order entry and amendment cost will be understated. |
| Deliveries and shipments | Delivery number, date, ship-to, route or zone, drops, weight and volume, carrier, incoterm, service level, freight charged and freight paid | One row per delivery, ideally per drop | Delivery is usually the largest and most unequal component of cost to serve. | Use the invoice ship-to plus a freight allocation by weight or zone, labelled as an approximation. Waiting time and failed drops stay invisible. |
| Returns and credit notes | Document number, date, customer code, product code, quantity, value, reason code, disposition | One row per return or credit line | Returns cause work in three places and are usually netted against revenue, so the work disappears. | Credit notes from the ledger, split by reason where a reason code exists. Without reasons, returns cost becomes a single average. |
| Service and support contacts | Contact or ticket ID, date, customer code, channel, category, severity, handling time where recorded, escalation flag | One row per contact or ticket | It separates the quiet accounts from the noisy ones, which is often the biggest single surprise in the model. | A contact count by customer for one representative month is enough to start. If nothing exists, this cost pool is spread and clearly labelled as spread. |
Line level in every one of these five. A summarised version of any of them removes exactly the difference the model exists to find.
Three files for master data and capacity
| Extract | Fields we ask for | Grain | Note |
|---|---|---|---|
| Customer master | Customer code, name, group or parent, country, channel, segment, sales representative, payment terms | One row per customer | The group or parent field is what lets a whale curve roll individual ship-to codes up to the relationship a commercial director actually manages. |
| Product or service master | Product code, description, family, brand, unit of measure, standard cost, weight and volume, storage class | One row per product | Weight, volume and storage class do more work in a cost-to-serve model than the description does. |
| Working calendar and shifts | Working days per period, shift patterns, standard hours, holiday and absence rates by function | One row per function or site | This is the capacity denominator. It is the smallest file on the list and the one that most changes the answer, as capacity costing explains. |
Some operations add a thirteenth file. Manufacturing adds work-centre confirmations, with production order, work centre, setup time, run time, quantity produced, scrap and rework. Healthcare adds the activity or procedure log with the pathway identifier. Professional and digital services add timesheets and the engagement master.
The extract is not paperwork before the project. It is the first honest conversation the project has.
What you can leave out
Data requests balloon because everyone adds the thing they might want later. Four items we routinely leave out, and one we actively refuse.
Individual salaries. Cost by cost centre and role is enough, and leaving names out avoids a data-protection conversation that helps nobody. If a payroll file arrives with names on it, we ask for it again without them.
The balance sheet. A profitability model works on the operating cost base. Balance-sheet detail matters for capital-allocation work, which is a different question, covered in capital allocation and ROIC.
Budget and forecast versions. Build the model on actuals first. Budget comparison is a reporting layer added afterwards, and mixing the two early is how a costing project quietly turns into a budgeting project.
Perfect data. Missing fields, inconsistent codes and a run of orphan rows are normal. We reconcile, quantify what is unmatched, and put that figure on the page rather than hiding it. A model that states how much revenue it could not match is trusted. A model that claims it matched everything is not.
Anything that would need a legal review to send. If a field is sensitive and not load-bearing, drop it. Almost nothing on this list is personal data once payroll is aggregated.
The reconciliation test that decides everything
CSV or Excel is fine. Parquet is better for large transaction files. What matters is that the files are raw exports rather than formatted reports: no merged cells, no subtotal rows, no thousands separators inside numeric fields, one header row, UTF-8, ISO dates.
Volume is rarely the obstacle people expect. The logistics model in our Saudi Arabia case study ran on 525,000 shipment rows, used in full with no sampling. If your invoice-line file is a few hundred thousand rows, it is an ordinary file.
On history, twelve months is the working target, because it covers a full seasonal cycle. Twenty-four lets you see whether a pattern is structural or one bad quarter, and it usually costs nothing extra once the query is written. One quarter is enough to get a first working model standing, which is the position we take in the data you need for a TDABC model.
One test decides whether the extract is usable, and it is worth running before anything else. Sum the cost files for the period and compare the total with the ledger for the resources in scope. Sum the invoice lines and compare with reported revenue for the same months. If either total drifts materially, the extract is filtered somewhere and the model would inherit the filter. Reconciliation first, modelling second. Everything in the seven-step build guide assumes this test has passed, and the plumbing behind it is covered in ETL for profitability analytics.
In a form you can forward
Copy the block below into the mail you send to whoever owns the systems. It is deliberately short, because a request that runs to four pages gets read once and answered slowly.
Format: CSV or Parquet, UTF-8, ISO dates (YYYY-MM-DD), decimal point, no thousands separator, one header row, raw export rather than a formatted report.
Cost: GL expense lines by cost centre × account × month · payroll cost and FTE by cost centre × role × month · depreciation by asset class × cost centre · cost-centre hierarchy.
Work: invoice lines · order lines · deliveries, per drop where recorded · returns and credit lines · service contacts or tickets.
Master: customer master including group or parent · product master including weight, volume and storage class · working calendar and shift patterns.
Keys: customer code, product code and document number present in every transaction file, in the same format as the master files.
Excluded: individual salaries, personal data, balance sheet, budget versions.
Missing fields: tell us rather than leaving the column blank. We have a documented fallback for every one of them.
Once the files land, the next decision is which characteristics of a transaction become terms in a time equation. That is the subject of the cost driver library, and the arithmetic behind it is in the time equations reference and in the TDABC reference.
Fair questions.
- What data do I need to build a cost-to-serve or profitability model?
- Twelve extracts. Four carry cost: general ledger expense lines by cost centre and account, payroll cost and full-time equivalents, depreciation by asset class, and the cost-centre hierarchy. Five carry the work: invoice lines, order lines, deliveries, returns and credit notes, and service contacts. Three carry master data and capacity: the customer master, the product master, and the working calendar. All of it at line level, covering the same months, joinable on customer, product and document codes.
- How much history do you need?
- Twelve months is the working target, because it covers a full seasonal cycle. Twenty-four months lets you see whether a pattern is structural or one bad quarter, and once the query is written it usually costs nothing extra to extend. One quarter is enough to get a first working model standing, but not enough to reprice an account on, because a single unusual month can carry the whole conclusion.
- Do the files have to be at line level?
- Yes, and this is the single most important requirement. A summarised extract cannot be broken back down later, and every meaningful difference between two customers lives in the detail that summarising removes: how many lines an order had, how many drops a delivery needed, how many contacts an account generated. If line-level extraction is genuinely impossible, that constrains the model design and should be known in the first week, not the sixth.
- What if we do not have the delivery or service data?
- The model still runs, with a documented approximation in place of the missing driver. Freight can be allocated by weight or zone, service cost can be spread across accounts, and both get labelled as spread rather than traced. The result is still far more informative than a company-level average, and the gaps become the roadmap for what to start capturing.
- Is this personal data, and do we need a data-protection review?
- Almost none of it is. We ask for payroll cost aggregated by cost centre and role rather than by person, and we send back any file that arrives with names or individual salaries on it. Customer records are business records. If a field is sensitive and not load-bearing for the model, leave it out.
- What format should the files be in?
- Raw exports rather than formatted reports. CSV or Parquet, UTF-8, ISO dates, a decimal point, no thousands separator inside numeric fields, one header row, no merged cells and no subtotal rows. Then run the reconciliation test before anything else: the cost files have to tie back to the ledger for the resources in scope, and the invoice lines have to tie back to reported revenue for the same months.
Can your data carry a model?
The free Profit Check takes 12 to 15 minutes and scores how ready your data and your cost mechanics actually are, before anybody exports anything.
- Duration
- 12 to 15 minutes
- You receive
- Score, 7 dimensions, sector benchmark
- Price
- Free, no email needed
Proof
A distributor in New Zealand. €1.335M of cost-to-serve made visible, then halved, and 830 loss-making customers brought down to 295.
Read the case study →Who you would be talking to
Miguel Guimarães, Founder
Cost and profitability practitioner since 2010. Lectured alongside Professor Robert S. Kaplan at the CFO conference in Amsterdam (2009).
Call +351 910 313 731
