Quality of Earnings Report Template: Free Excel Layout With EBITDA Adjustments and Net Working Capital

A quality of earnings (QoE) report tests whether a company's reported EBITDA reflects earnings that will keep recurring. It does this by walking from reported figures to adjusted EBITDA one add-back at a time, and it sets a normalized net working capital (NWC) peg for the closing price adjustment. A good quality of earnings report template puts both analyses in one Excel workbook, so buyers, sellers and lenders can trace every adjustment back to the general ledger.

This guide lays out the tabs of a free-format Excel template, explains how to build the EBITDA adjustments bridge step by step, and shows how to set a working capital peg that will hold up after closing. It also covers the benchmarks that tell you how hard buyers and lenders will push back on your numbers.

What a Quality of Earnings Report Template Has to Answer

A QoE answers two questions that set the price. The first is what the business really earns on a run-rate basis. The second is how much working capital it needs to produce those earnings. Deals are priced as a multiple of adjusted EBITDA, so every dollar of add-back that survives diligence is worth eight to twelve dollars of enterprise value in a typical mid-market deal. Every dollar that gets thrown out costs the same amount.

Public data on add-backs explains why buyers and lenders test them so hard. S&P Global Ratings' sixth annual EBITDA add-back study, published March 27, 2024, found that add-backs still made up a median 30% of management-adjusted EBITDA when deals were launched. The same study found that actual leverage came in a median 2.3 turns above forecast in year one and 2.7 turns above in year two. S&P also found that larger add-backs at launch went with larger misses later. If one in three dollars of marketed EBITDA is an adjustment, the workbook that supports those adjustments decides the deal.

Public-company practice sets the tone too. In 2017, SEC staff told Dave & Buster's to remove an Adjusted EBITDA add-back that sped up revenue recognition, because it replaced the method GAAP requires. In 2024, staff asked Spectrum Brands to describe "the specific nature" of each add-back, from ERP transformation costs to a product recall. Private-deal buyers ask the same thing, just less formally. Each adjustment needs a clear description, a source document, and a reason it will not recur.

Next step: Before you open Excel, write a one-line test for every add-back you plan to claim: "This cost will not recur after closing because ___." If you cannot fill in the blank, leave it off the bridge.

The Quality of Earnings Report Template: Excel Layout Tab by Tab

A usable QoE spreadsheet model runs from raw data to conclusions, and every number on an output tab links back to an input tab. This is the layout used by most transaction advisory teams, with seven tabs:

Cover and Index. Deal name, review period (usually the last two fiscal years plus TTM), data cut-off date, version number and a list of tabs.
TB_Data. Monthly trial balances for the full review period, pasted as values. Every other tab draws from here through SUMIFS on account number. Do not type over numbers on this tab.
Mapping. A table that maps each GL account to a line in the income statement, balance sheet and working capital. When the seller's chart of accounts changes mid-period, fix it here once.
EBITDA_Bridge. Reported net income down to reported EBITDA, then management adjustments, then diligence adjustments, ending in adjusted EBITDA by year and TTM.
Adj_Support. One block per adjustment: description, amount by period, source document reference, status (accepted, revised or rejected) and reviewer notes.
NWC. Monthly working capital for the review period, the agreed definition, adjustments, the trailing averages and the proposed peg.
Net_Debt. Cash, funded debt and debt-like items such as deferred revenue, accrued bonuses, unpaid taxes and deferred capex.

Two formatting rules prevent most review comments. Inputs go in blue font and formulas in black. Each output tab carries a check row that ties back to the trial balance and shows "OK" or the size of the variance. If the bridge's reported EBITDA does not equal the income statement built from TB_Data, the check row will show it before the buyer's advisor does.

Next step: Build TB_Data and Mapping first and get the check rows to zero. Do not start adjustments until reported EBITDA ties to the monthly trial balances for every period.

Building the EBITDA Adjustments Bridge Step by Step

The bridge is the page everyone reads. Keep it to one screen with a column per period, and push detail into Adj_Support.

Step 1: Get to reported EBITDA

Start from GAAP net income and add back interest, income taxes, depreciation and amortization. The SEC's 2016 non-GAAP guidance says EBITDA used as a performance measure should reconcile to net income, and private buyers expect the same starting point. Starting from operating income hides below-the-line items you may need later.

Step 2: Group adjustments into categories

Owner and related-party items: owner pay above or below market, personal expenses run through the business, and related-party rent marked to market.
Non-recurring items: litigation settlements, one-off advisory fees, and the costs of a single event such as a facility move.
Accounting corrections: cut-off errors, unrecorded accruals, revenue recognized in the wrong period, and inventory reserve true-ups.
Pro forma items: full-year effect of a price increase, a new hire or a closed location. Show these below the line as "pro forma adjusted EBITDA" so they stay apart from historical adjustments.

Step 3: Put a status on every line

For each adjustment, record the management amount, the diligence amount and the difference. A rejected add-back stays on the bridge at zero with a note. Deleting it only invites the question again in the next draft.

Step 4: Run an illustrative example

Take a hypothetical distributor with TTM reported EBITDA of $6.0 million. Management proposes $1.4 million of add-backs, which is 19% of adjusted EBITDA:

Owner salary of $650k against a market rate of $300k: +$350k, accepted.
Lawsuit settlement: +$400k, accepted with support from the settlement agreement.
"Recurring one-time" consulting fees booked in all three years: +$300k, rejected because they recur.
ERP implementation: +$350k, revised to +$200k after $150k proved to be ongoing licence fees.

Diligence also finds an unrecorded $120k bonus accrual, which is a negative adjustment. Adjusted EBITDA comes out at $6.83 million against management's $7.4 million. At an 8x multiple, that $570k gap is $4.56 million of enterprise value, and the bridge shows exactly where it went.

Step 5: Test the pattern as well as the lines

Divide total add-backs by adjusted EBITDA for each period. A ratio that climbs year after year, or "non-recurring" costs that show up every year, is the pattern S&P links to projection misses. Fix it before a buyer finds it.

Next step: Rank your add-backs by size and rate how well each is documented. Build the support file for the top five first, since they usually make up most of the dollars.

Net Working Capital and the Peg in Your Excel Template

The working capital peg is the target level of NWC the buyer expects at closing. If actual closing NWC comes in above the peg, the seller gets paid more. If it comes in below, the price drops. These mechanics are close to universal. SRS Acquiom's 2025 Working Capital Purchase Price Adjustment Study, covering more than 1,200 private-target acquisitions worth about $298 billion, found purchase price adjustments in "well over 90%" of private deals, up from about half a decade earlier. DealLawyers.com's January 31, 2025 summary of the study reports that the median separate adjustment escrow was about 1% of transaction value, the average initial buyer claim was 0.9% of deal value, and 24% of claims went above 1%. On a $100 million deal, a sloppy peg can easily cost seven figures.

How to set the peg

Agree the definition. Start with current assets minus current liabilities. Then exclude cash, debt, the current portion of debt, income taxes and transaction accruals, since these are settled through the net debt calculation.
Build 24 months of balances. Use month-end balances from TB_Data through Mapping, so the same accounts feed every month.
Apply the same normalizing logic as EBITDA. If you added back a bonus accrual error in EBITDA, fix the accrued liabilities balance in the same months. A bridge and a peg that disagree are an easy target for the other side.
Check for seasonality. Chart NWC as a percentage of TTM revenue by month. If the business peaks in Q4, a TTM average is the fair measure. Comparing a closing date in a peak month to a year-end balance will skew the result.
Propose the peg. The trailing twelve-month average is the market default. Show the 3-month and 6-month averages next to it so negotiators can see the range.

Watch the debt-like items

Deferred revenue, customer deposits, accrued but unpaid bonuses, and aged payables stretched before a sale belong on the Net_Debt tab or need a specific adjustment in NWC. A seller who stops paying vendors in the last 60 days inflates cash and depresses NWC at the same time. The monthly trend on the NWC tab should flag any days-payable figure that jumps away from its historical range.

Next step: Write the NWC definition in plain English at the top of the tab, using the same wording you expect in the purchase agreement. Then have counsel compare the two before signing.

How to Use the Quality of Earnings Template in a Live Deal

The workbook's value depends on when you build it. Sell-side QoEs built three to six months before going to market give the seller time to fix accrual habits and gather support. They also let the seller cut weak add-backs before a buyer's team turns them into a price cut. On the buy side, the same template turns into a diligence tracker. You load the seller's bridge into the management column and fill in the diligence column as support arrives.

Proof of cash: Reconcile monthly revenue to bank deposits for at least the TTM. Revenue that does not turn into cash is the first thing lenders look for.
Version control: Save a new file for each draft sent outside the team and log changes on the Index tab. Disputes over "which bridge we agreed" are common and easy to prevent.
Lender package: Lenders size debt off adjusted EBITDA, and S&P's leverage misses of 2.3 to 2.7 turns explain why credit teams cut aggressive add-backs. Give them the Adj_Support tab without being asked.
Post-closing: Keep the NWC tab live. The same formulas calculate the closing statement, which cuts the time spent arguing over methodology during the true-up.

Next step: If you are selling within 12 months, run the template on your last two fiscal years now. Treat the rejected-add-back column as your to-do list.

Start From a Template, Not a Blank Sheet

The quality of earnings report template decides how much of your EBITDA turns into price. The public data shows the stakes: add-backs at a median 30% of marketed EBITDA, adjustment escrows around 1% of deal value, and buyer claims that go above that line in about a quarter of deals. The work itself is mechanical and repeatable. Tie the trial balance, map accounts once, give every adjustment a status and a source, and run working capital through the same logic as EBITDA. A ready-made workbook with the tab structure, check rows and peg calculations already built lets you spend your hours on judgment calls, like which add-backs will survive, rather than on rebuilding SUMIFS formulas. Load your trial balances, get the checks to zero and build the bridge before the other side builds it for you.