How do I calculate an EBITDA margin using Excel?

EBITDA is an acronym that stands for earnings before interest, taxes, depreciation and amortization. To arrive at EBITDA, you start with EBIT (operating income) and add back non-cash charges and depreciation and amortization expenses.

EBITDA is a non-generally accepted accounting principles (GAAP) measure of profitability. But it is commonly used in financial analysis because it better measures the returns on a business' ongoing production, as opposed to simply using earnings (which would include many non-operational items).

By starting with EBIT, you only include actual operations of the business. So, why exclude interest and taxes? Interest is not inherent to a company's operations, the reasoning goes, but it's a result of the capital structure, which reflects the financing choices management makes. Likewise, taxes are considered non-operational, because they too can be affected by accounting options and management decisions.

For example, say you ran a lemonade stand and found $50 on the ground (presumably dropped by a customer). This $50 must be included in your net income, but no one would argue that finding lost money is part of the normal operations of a lemonade stand business. Funding the lemonade stand with equity or debt (which results in interest charges) or deferring taxes (on your accountant's recommendation) has no bearing on the actual cost of lemons or sugar which are integral production expenses. Neither does how many cups of the drink you sell, which is the key operation of the business. By removing "arbitrary" decisions, you can perform better apples-to-apples comparisons of similar businesses, and get a better sense of their operations.    

The EBITDA margin is the EBITDA divided by total revenue. This margin reflects the percentage of each dollar of revenue that remains as a result of the core operations.

Calculating this in Excel is simple.

After importing historical data and forecasting and future periods, you build up to EBITDA:

1. Take EBIT from the income statement, which is a GAAP line item.
2. Find depreciation and amortization on the statement of operating cash flows.
3. Add them together to arrive at EBITDA.
4. Calculate this period's EBITDA divided by this period's revenue to arrive at the EBITDA margin.
5. For forecasted periods, you can derive future depreciation and amortization (D&A) by taking historical D&A, then dividing it by historical revenue, and applying that ratio forward. That suggests that the total capital expenditure (CAPEX) and intangible costs have a relationship with total revenue, and you expect that relationship to stay consistent in the forecasted periods.
6. For forecasted revenue, you can apply some growth rate, typically using consensus estimates as a starting point.
7. Use the steps illustrated in the table below for other forecasted cells.

EBITDA is best used as a comparative metric for analyzing a business' performance relative to other companies in the same industry with similar business models. A strength to this approach is better apples-to-apples comparison. A weakness is that it understates the effects that capital structure and CAPEX spending have on a business.