Spreadsheet
Normalized Earnings Worksheet
A price to earnings ratio has two parts and only one of them is a fact. This rebuilds the other one.
Download the worksheet
Opens in Microsoft Excel, Google Sheets, Apple Numbers, and LibreOffice Calc. To use it in Google Sheets, upload the file to Drive and open it with Sheets.
The price is observed. The earnings are an opinion.
Everybody checks the numerator. The price is on the screen, it is precise, and it is genuinely what you pay. Then it gets divided by a number that moves violently with the cycle and with whatever one-time items happened to land in the period.
The consequence is that a multiple is at its most flattering exactly when a business is at its most exposed. A cyclical at peak margins screens cheap. A company that booked a large one-time gain screens cheap. Neither is.
This worksheet rebuilds the denominator so you can see both numbers side by side: the multiple the screen shows, and the multiple you are actually paying.
What it does
Mid-cycle margin from ten years of history. You type revenue and operating margin off the filings. It computes the average and the median, and shows the peak-to-trough spread, because the wider that spread is the less any single year tells you.
One-time items, stripped in both directions. List whatever flattered or depressed the latest period, mark each as recurring or not, and only the non-recurring ones come out. Costs and gains are treated the same way, which is the part most people skip.
The gap, in one number. Reported multiple, normalized multiple, and the percentage difference between them. Then a fair value at whatever multiple of normalized earnings you would actually pay.
The example that ships in the file
A business with a ten-year average operating margin of 11.2%, currently earning 11.8%, with two one-time gains in the latest year
- Reported earnings per share
- $9.69
- Reported multiple
- 14.4x
- Normalized earnings per share
- $7.51
- Normalized multiple
- 18.6x
- What you are actually paying
- 29% more
Nothing about the business changed between those two lines. The company is not doing anything wrong and the accounting is correct. The screen is answering a question about last year.
It works on real companies, and it changes answers
We ran this on Nike. A one-time recovery of previously paid tariffs was worth 72 percent of the fourth quarter, taking reported earnings of $0.72 down to $0.20 once removed. Across the full year the multiple moved from 18.3 times to 21.7 times, which is 19 percent higher than the screen showed. That single adjustment is what turned an initial lean toward a BUY into a HOLD.
It also works in reverse, which is the half nobody uses. A cyclical at the bottom of its cycle can show a terrifying multiple on depressed earnings while being priced perfectly fairly against what it earns in a normal year. The screen has the sign backwards at both ends.
What is inside
- Ten Year History, where you enter revenue and margin and get the mid-cycle figure, the median, and the peak-to-trough spread
- One-Time Items, with a recurring or not column, an effective tax rate, and the three-year test written into the sheet
- The Multiple, showing reported and normalized side by side plus an implied fair value
- Cheat Sheet, listing exactly where in a filing each input comes from, because the hardest part of this is not the arithmetic
- Every output is a live formula. Change one input and the whole file moves. Nothing is pasted in.
The discipline the file is built around
Normalize first, then value. Never the reverse. If you decide what a company is worth and then choose which items to strip, you will find exactly the adjustments that support the conclusion you started with, and you will be able to defend every one of them individually. That is the failure mode this worksheet exists to prevent, and it catches nobody who is not looking for it.
Strip in both directions. A company that excludes one-time costs while quietly keeping one-time gains is not normalizing, it is selecting. The test applies to you too.
Use the three-year rule. If the same category of item appears three years running, it is not a one-time item. Restructuring charges every year for five years are operating costs with a better name. The sheet has a Maybe option for exactly this, and Maybe items stay in the numbers.
What this worksheet cannot tell you
It makes the denominator honest. It does not make it right. If the business has structurally changed, if a competitor has taken the market, or if the last decade included conditions that will not repeat, then the mid-cycle margin describes a company that no longer exists. The arithmetic cannot detect that. You have to.
It also says nothing about growth, capital intensity, or the returns a company earns on reinvested capital, all of which matter as much as the multiple. Two businesses on identical normalized multiples can be worth very different amounts depending on what they earn on new money.
Found an error, or think a calculation should work differently? Tell me. Corrections get published with a date attached, the same as they do for the research reports.