Templates

Segment Reporting Workbook

The arithmetic of a segment note under ASC 280 after ASU 2023-07: aggregation margins, the three 10% tests and the 75% test, the significant expense table with other segment items, reconciliations to the consolidated statements, entity-wide tests, an interim table, a recast after a reorganization and a single-segment example: ten tabs, one fictional company, no macros.

Enter your own operating segments as your chief operating decision maker sees them. With the book's case, the workbook finds five reportable segments among six, three of them on a single test, computes 96.9% coverage for the 75% test, builds a significant expense table of $1,432.5 million across five segments with $23.5 million of other segment items, and reconciles $212.0 million of segment operating income to $71.0 million of income before income taxes. A Checks tab runs 40 checks against the book.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 191 free models.

Segment_Reporting_Workbook.xlsx (27 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind the workbook: The Segment Reporting Handbook, PDF and paperback, with the full case, the complete segment note and the checklists.

What is in the workbook

Inputs
The first table holds the seven operating segments as the chief operating decision maker sees them: external revenue, intersegment revenue, cost of revenue, selling, general and administrative expense, research and development, segment operating income, depreciation and amortization, assets and capital expenditure, and, in the last column, the reportable segment code into which each operating segment is aggregated. To aggregate two operating segments, give them the same code; to stop aggregating, give each its own. Every later tab picks up the change.
Aggregation
Computes the five-year average gross margin of each pair under consideration, the FY2026 gross margin from the inputs as a cross-check, and the gap between the averages in percentage points. A row is provided for recording the five qualitative criteria. The tab does not decide whether segments may be aggregated. It gives the numbers that the judgment in Chapter 4 relies on.
Thresholds
Groups the operating segments by their aggregation code, then runs the three 10 percent tests of Chapter 5 for each: revenue including intersegment revenue against combined revenue, the absolute amount of profit or loss against the greater of combined profits and combined losses, and assets against combined assets. A segment is flagged as reportable if it meets any one test. Below, the tab runs the 75 percent test using external revenue, and flags a structure with more than 10 reportable segments.
Disclosure
Builds the significant expense table of Chapter 7 for the reportable segments: segment revenue, cost of revenue, selling, general and administrative expense, research and development, other segment items computed as the residual, segment operating income, depreciation and amortization, assets, capital expenditure, adjusted EBITDA and operating margin, with totals, and a line for "all other".
Reconciliation
Reconciles segment revenue, operating income, assets, depreciation and amortization and capital expenditure to the consolidated amounts, line by line, as in Chapter 9.
Entity-wide
Totals revenue and long-lived assets by country, checks that the revenue total agrees with consolidated revenue, computes Germany's share of revenue and assets and the foreign share of revenue, totals revenue by product group, and tests the two largest customers against the 10 percent threshold (Chapter 10).
Interim
Produces the third-quarter significant expense table and the nine-month revenue and operating income of Chapter 12 from the annual figures and the quarterly pattern, rounding each line to one decimal and computing other segment items after rounding so that each column adds. With actual quarterly data, replace the formulas in the quarter columns with the quarterly package figures.
Recast
Moves the Aftermarket Services figures into Flow Control and Sensors & Instrumentation using the recast share, reruns the three 10 percent tests on the new structure and counts the reportable segments (Chapter 12). A check confirms that combined revenue is unchanged by the recast.
Single segment
Computes other segment items for the Larkspur Diagnostics example of Chapter 11, where the measure of segment profit or loss is net income.
Checks
Runs 40 tests that compare the workbook's results with the figures printed in the book: the threshold amounts and the result for each segment, the 75 percent test, the average gross margins, the totals of the significant expense table, the reconciled consolidated amounts, the entity-wide totals and the major customer, the third-quarter result for Flow Control, the recast and the single-segment example. With the Calderwood inputs, the result line reads ALL CHECKS OK. With your own inputs, the checks will show differences; that is expected, and the tab can be deleted or rewritten to test your own note.

Every assumption is shaded in a light color on the Inputs tab; every other cell is a formula, with no macros, no links to other files and no circular references. Every ratio is guarded against an empty divisor. Replace the inputs with your own company's figures; the Checks tab will then show differences from the book by design.

From The Segment Reporting Handbook. To read: the ASC 280 quantitative thresholds · significant segment expenses (ASU 2023-07) · single-segment disclosures under ASC 280.