Companion files
Product Costing, Overhead Allocation and What a Wrong Standard Cost Does to the Orders You Win
These are the four Excel workbooks that go with the book. Calderbrook Engineering makes precision machined components in one factory, sells 48,050,000 across four product families, and earns an operating profit of 7,291,184. Its factory overhead of 13,440,000 sits in a single pool and is absorbed on direct labor hours at 87.88 an hour. That pool is 73.7 per cent of conversion cost, and it moves with setups, production orders, machine hours, engineering changes and inspections, not one of which is a labor hour. Re-assign the identical 13,440,000 on those five drivers and 4,053,091 moves between the four families, which is 55.6 per cent of the whole operating profit. Both methods distribute the same 13,440,000 and both foot to the same gross profit of 14,011,184, so nothing is created and nothing is destroyed: the money only changes address. Every figure the book prints is reproduced here by a live formula rather than a typed constant. Change a volume, a driver count, a pool or your own avoidable share, and every dependent number moves. Each workbook ends with a Checks sheet setting the printed figure beside the computed one: 826 checks in all, every one passing on delivery. If a check ever reads FAIL, the workbook is wrong, not the book.
Free to download. No sign-up, no email address, nothing to fill in.
Everything described below is inside it, with the read-me.
Chapters 1, 2, 3, 5, 6 and 7
The company first, built from its inputs rather than asserted: revenue of 48,050,000, direct material of 15,796,500, direct labor of 4,802,316 and factory overhead of 13,440,000, giving a total factory cost of 34,038,816 and a gross profit of 14,011,184, then 6,720,000 of selling, general and administrative cost and the 7,291,184 the factory actually earns. Beside it sits the ratio that sizes the whole argument: overhead is 73.7 per cent of conversion cost, being 13,440,000 of 18,242,316.
Then the same factory is costed twice. Method A builds the labor hours family by family, 1,850,000 units at 0.030 through to 19,500 at 1.540, sums them to 152,940, and performs the division that gives 87.8776 an hour on the face of the sheet instead of typing the rate in. The four build-ups land on unit costs of 6.53, 15.50, 54.53 and 287.69, and every family clears the board’s 28.0 per cent except the brackets at 17.3 per cent. Method B runs the same pool through its five drivers and lands on 4.87, 13.95, 64.22 and 436.91, where the brackets lose 25.5 per cent. The identity sheet is what the rest of the workbook exists to support: both allocation columns total 13,440,000, both differences from the pool show as zero, the four margin shifts sum to zero, and both margin columns foot to the 14,011,184 in the accounts. The migration prints the unit costs, the shifts of -25.3, -10.0, +17.8 and +51.9 per cent and both margin percentages side by side, with the cross-subsidy of 4,053,091 and its 55.6 per cent of operating profit locked to the cell beside it, because the money quoted without the ratio means nothing.
Chapters 4 and 8
The 13,440,000 opened into the five cost centers it already resolves into in the ledger, each share computed rather than typed: 3,510,000 of setup and changeover at 26.1 per cent, 1,980,000 of order handling and scheduling, 4,620,000 of machine running at 34.4 per cent, 1,610,000 of engineering and change notes and 1,720,000 of quality and inspection. Each carries its level, unit, batch, product or facility, in a column beside it, so the distinction that decides whether a cost falls with a unit or with an order is a property of the pool rather than a remark in the text. The counts sheet holds the sixteen driver counts, the 3,900 setups, 5,330 production orders, 980 engineering changes and 16,660 inspection events, and derives the number the book turns on: a standard bushing gets 5,967.7 units out of one setup and an aerospace bracket gets 11.5.
The five divisions are performed on the face of the sheet and each rate is printed to four decimals as well as two, which is the difference between a table that foots and one that does not: 900.00 a setup, 371.48 a production order, 30.67 a machine hour, 1,642.86 an engineering change and 103.24 an inspection event. Twenty charges follow, five pools by four families, each one a count times a rate, with every column footing to its own pool and the grid footing to 13,440,000 in both directions. Capacity then runs the identical pool over two denominators: 152,940 actual hours at 87.88 against 168,000 practical hours at 80.00, a gap of 7.88 an hour, with 15,060 idle hours carrying 1,204,800, which is 9.0 per cent of the pool and is the cost of work that was not done loaded onto work that was. The machine pool is re-struck at 26.8605 against 30.6671, which moves the bracket from -25.5 per cent to -23.4, so the finding survives the correction and the workbook says so rather than leaving a reader to find it. Practical capacity is an input cell, because it is a judgment and not a measurement. The avoidable split sits here too, the five shares from 90.0 per cent of setup and changeover down to 10.0 per cent of engineering, and what they make of each family: 47.2, 55.0, 56.9 and 52.5 per cent of its own overhead, which is why a single flat share across all five pools is indefensible.
Chapters 9, 10, 11, 12 and 13
What the two costs actually decide. The batch computes what one order costs to start and reconciles it back to the annual charge: an order of aerospace brackets costs 1,069.19 before a unit is cut, 887.97 of that avoidable, against a batch-free cost of 317.38 and a contribution of 30.62 a unit. So the full-cost break-even is 34.9 units and the avoidable one is 6.1, with the average order of 8.9 on the same row and a label on each column saying which question it answers: whether a year of this order pattern is sustainable, or whether one order next week generates cash. Both are true, and a factory that refuses an order on the full-cost test while it has idle capacity has destroyed money rather than saved it. The bracket is then carried at seven order quantities, from 25 units at a loss of 3.5 per cent to 2,500 at a gain of 8.7 per cent, with the batch-free cost printed as the asymptote.
Make or buy is where the assumptions are made visible. Three quotes of 5.90, 51.00 and 280.00 arrive, all three below the absorbed cost, so the absorbed number says buy every time. Each is set against three costs: absorbed, avoidable and full driver cost. On the bushings the avoidable cost is 4.36 against a quote of 5.90, so buying is a 2,857,439 mistake that strands 958,654 of overhead; on the other two buying is right, for a reason that has nothing to do with the case, since a number that always says buy is right whenever buying is right. The five avoidable shares are blue input cells at the top of the sheet rather than constants buried in formulas, and the decision text in each row is a formula, so overtyping a share changes the words as well as the numbers. The survivor sheet then closes the loop most such analysis never draws: take all three quotes, 5,809,744 of overhead leaves, 7,630,256 stays, the 39,680 remaining labor hours absorb it at 192.29 an hour against 87.88 today, and the last family in the factory prints at 6.5 per cent.
Cost to serve prices the same 46,000 units of the same hydraulic fitting for two customers. The distributor takes 14.0 per cent off list and orders twelve times a year; the manufacturer takes 6.0 per cent, orders 196 times, expedites 84 of them and pays in 92 days. Net margins are 28.42 per cent and 14.66 per cent, on costs to serve of 23,925 and 225,723, a difference of 201,798 on the same part, and the family-wide scaling of 1,517,037 is marked on the sheet as a ceiling test rather than a measurement. The spiral runs the rounds, with overhead held fixed as volume leaves and the assumption stated rather than hidden: four rounds from 48,050,000 of revenue to none, the rate climbing from 87.88 through 137.93 and 232.69 to 447.55, and operating profit falling from 7,291,184 to -20,160,000, losing the profitable families first and keeping the loss-making one longest. The price correction asks what each family would have to sell at to reach the board’s 28.0 per cent on the driver cost: 6.77 rather than 9.20 on a bushing, 606.81 rather than 348.00 on a bracket, and -773,867 on revenue if every price moved at once, which is not the direction anybody expects a costing correction to point.
Chapters 14, 15 and 16
This one is the one to open first, and it does not reproduce the book. Its front sheet costs your part rather than Calderbrook’s. Type in your pool, split into as many of the five activities as your ledger supports, the total driver counts behind each, your part’s own counts, its volume, its material, its hours and its price, and it returns both costs, the one a labor-hour base produces and the one the drivers produce, both break-even quantities, full and avoidable, and the gap between the two costs in money a unit and across the year. Three inputs are enough for a first answer and the rest refine it, because a cost computed from four rough figures before the quotation leaves the building is worth more than a precise one reconciled after the invoice is raised.
Around the calculator sit the sensitivities. Six of them move one data input at a time and report the cross-subsidy, with the other pools absorbing the difference so that the total overhead never changes and the mix of the pool is isolated from its size. Ranked by span: 2,431,855 for the size of the overhead pool, 1,445,701 for the volume of the smallest family, 1,378,399 for the share driven by machine time, 444,868 for engineering changes, 269,192 for setups, and 0 for the direct labor rate a hour, which is a finding rather than a defect: the rate does not affect how overhead is distributed, only how large the labor line is, and it is the line most often negotiated. Three judgment sensitivities follow, and each moves a decision further than any of the six data inputs moves the money. How much overhead is avoidable on outsourcing spans 4,147,573 between 0.30 and 0.75, and two of the three sourcing decisions change sides inside that range. The batch elasticity in Case B spans 1,741,645. The price elasticity of volume spans 75.0 percentage points of the largest valve-body price rise that still pays.
Case B is the year the reports improved and the factory did not. Sales chased the volume where the absorbed margin looked fattest, which is the low-volume complex end, and revenue rose 10.3 per cent to 52,997,520 while overhead rose 23.8 per cent to 16,639,817, more than twice as fast, because the mix moved toward the parts that consume the drivers. Operating profit fell by 431,133 and the absorption rate rose from 87.88 to 96.20. The sign of that finding turns on the batch elasticity of 0.78, and it flips at about 0.65, which is inside the plausible range and is printed rather than buried.
It ships filled in with Calderbrook, so the Checks sheet can prove it reproduces the book before you trust it with anything of your own: 287.69 and 436.91 on the aerospace bracket, a gap of 149.22 a unit, 34.9 and 6.1 on the break-evens, and 17.3 per cent against -25.5 per cent on the margin. Overtype the blue cells with your own factory and those checks will fail. That is the workbook working, not breaking.
| Blue text | an input: change it and everything recomputes |
| Yellow fill | an assumption that decides the answer rather than merely feeding it: practical capacity of 168,000 hours, the five avoidable shares from 90.0 per cent down to 10.0 per cent, the volume elasticity of 1.35, the batch elasticity of 0.78 and the target gross margin of 28.0 per cent. In the fourth workbook every input cell carries the fill, because on a part not yet quoted every one of them is an assumption somebody is making |
| Black text | a formula: do not overtype these |
| Grey text | a note |
| Green text | on the Checks sheet, a link to the computed cell |
| Checks sheet | the printed figure beside the computed one, the difference, a PASS or a FAIL, and the tolerance being applied |
There are no macros, no external links, no protection and no circular references anywhere, and nothing is locked or watermarked. The files behave identically in Excel, LibreOffice and Google Sheets.
A workbook that agrees with a book proves nothing on its own, since the author wrote both. What the Checks sheets do is different: they force the model to reproduce a number that was printed before the model existed, from a formula rather than from the number itself. 826 checks across the four files, being 223, 165, 262 and 176, all passing on delivery.
On this book the discipline earns its keep on the identity the whole argument rests on. Several of the checks in the first workbook do nothing but assert that both allocation columns total 13,440,000, that the four margin shifts sum to zero, and that both margin columns foot to the gross profit of 14,011,184 in the accounts. Re-assigning a pool is easy to get subtly wrong in a way that still looks plausible, because every charge on the grid belongs where it sits; the identity is what refuses a version in which the total has quietly moved. Nothing in these files creates or destroys a dollar of overhead, and the checks are how that is enforced rather than promised.
The second thing the checks enforce is that figures which look like each other are not the same measurement. The 4,053,091 that moves between families is neither a loss nor a saving: it is the profit the reports attribute to the wrong parts, and it is 55.6 per cent of operating profit. The loss the aerospace bracket actually makes is 1,733,666 on 6,786,000 of revenue, which is 23.8 per cent of operating profit and a different number answering a different question. Both sit on the same sheet with their bases beside them, and a check holds each one to the cell it was computed from.
Every headline figure in these files is computed before the capacity correction, so every driver cost printed carries a share of the 1,204,800 that idle capacity costs, and every target price computed from one is correspondingly high. The bracket still loses money after the correction, at -23.4 per cent against -25.5, so the finding survives it. The assumptions most worth arguing with are the five avoidable shares, the 90.0 per cent of setup and changeover down to the 10.0 per cent of engineering that decide every make-or-buy in the book. Move them together from 0.30 to 0.75 and the cost of taking all three quotes moves across a span of 4,147,573, and two of the three decisions change sides. Type your own shares instead and watch which answers move and which do not.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks to update links on opening, decline, because there are none.
The other books with companion files. The full list of titles is on the author page.