EBIT and EBITDA are two common financial metrics that measure a company’s operating performance. EBIT stands for Earnings Before Interest and Taxes, while EBITDA stands for Earnings Before Interest, Taxes, Depreciation, and Amortization. Both metrics exclude the effects of a company’s capital structure, tax rate, and non-cash expenses, making them useful for comparing the profitability of different companies or industries.
In this article, we will explain the basic theory behind EBIT and EBITDA, how to calculate them using excel formulas, and how to use an excel table to illustrate the calculations. We will also provide a scenario to give a detailed example with real data, and show the results of the calculations. Finally, we will discuss some other approaches or variations of EBIT and EBITDA that are sometimes used in financial analysis.
The Basic of EBIT and EBITDA
EBIT and EBITDA are derived from the income statement of a company, which shows the revenues, expenses, and profits of a business over a period of time. The income statement can be divided into three main sections: gross profit, operating profit, and net income.
Gross profit is the difference between revenues and cost of goods sold (COGS), which represents the direct costs of producing or selling the goods or services. Operating profit, also known as EBIT, is the difference between gross profit and operating expenses, which include selling, general, and administrative expenses (SG&A), research and development expenses (R&D), and depreciation and amortization expenses (D&A). Net income, also known as earnings, is the difference between operating profit and interest and tax expenses, which depend on the financing and tax decisions of the company.
EBITDA is calculated by adding back depreciation and amortization expenses to EBIT, since they are non-cash expenses that reflect the allocation of the cost of fixed assets over their useful lives. Depreciation and amortization expenses can vary depending on the accounting methods and assumptions used by different companies, so adding them back can eliminate this source of variation and make the comparison more consistent.
EBIT and EBITDA are often used as proxies for cash flow from operations, since they represent the amount of cash that a company generates from its core business activities, before paying interest, taxes, and capital expenditures. However, they are not perfect measures of cash flow, as they do not account for changes in working capital, such as accounts receivable, inventory, and accounts payable, which can also affect the cash flow of a company.
How to Calculate EBIT and EBITDA in Excel
There are two main ways to calculate EBIT and EBITDA in excel, depending on the format and availability of the income statement data. The first way is to use the bottom-up approach, which starts from the net income and adds back the interest, tax, depreciation, and amortization expenses. The second way is to use the top-down approach, which starts from the operating profit and adds back the depreciation and amortization expenses.
The formulas for calculating EBIT and EBITDA using the bottom-up approach are:
EBIT = Net Income + Interest + Taxes
EBITDA = Net Income + Interest + Taxes + Depreciation + Amortization
The formulas for calculating EBIT and EBITDA using the top-down approach are:
EBIT = Operating Profit
EBITDA = Operating Profit + Depreciation + Amortization
To use these formulas in excel, we need to enter the income statement data in separate cells, and then use the cell references to perform the calculations. For example, if we have the following income statement data in cells A1 to A6:
| Income Statement | |
|---|---|
| Revenues | 100,000 |
| COGS | -40,000 |
| Gross Profit | 60,000 |
| Operating Expenses | -30,000 |
| Operating Profit | 30,000 |
| Net Income | 20,000 |
We can calculate EBIT and EBITDA using the bottom-up approach by entering the following formulas in cells B1 to B3:
| Income Statement | Formulas |
|---|---|
| Interest | =5%*A6 |
| Taxes | =25%*A6 |
| EBIT | =A6+B1+B2 |
| EBITDA | =B3+10%*A1 |
The results are:
| Income Statement | Results |
|---|---|
| Interest | 1,000 |
| Taxes | 5,000 |
| EBIT | 26,000 |
| EBITDA | 36,000 |
We can also calculate EBIT and EBITDA using the top-down approach by entering the following formulas in cells C1 and C2:
| Income Statement | Formulas |
|---|---|
| EBIT | =A5 |
| EBITDA | =A5+10%*A1 |
The results are:
| Income Statement | Results |
|---|---|
| EBIT | 30,000 |
| EBITDA | 40,000 |
Note that the results of the bottom-up and top-down approaches are different, because we used different assumptions for the interest, tax, and depreciation and amortization rates. In reality, these rates should be obtained from the actual income statement or financial statements of the company, or from reliable sources such as industry reports or databases.
The Calculations
An excel table is a useful tool to organize and present the income statement data and the EBIT and EBITDA calculations in a clear and concise way. An excel table can also allow us to apply formatting, filtering, sorting, and other features to the data, making it easier to analyze and manipulate.
To create an excel table, we need to select the range of cells that contain the data, and then go to the Insert tab and click on the Table button. A dialog box will appear, asking us to confirm the range and whether the table has headers. We can check or uncheck the boxes as appropriate, and then click OK. A table will be created, with a default name and style.
We can rename the table by clicking on the Design tab and changing the name in the Table Name box. We can also change the style of the table by clicking on the More button in the Table Styles gallery and choosing a different style. We can also customize the table by adding or removing elements such as filters, banded rows, total row, etc.
For example, if we want to create an excel table for the income statement data and the EBIT and EBITDA calculations using the bottom-up approach, we can select the range A1:B6, and then insert a table with headers. We can rename the table as Income Statement, and choose a blue style. We can also add a total row, and use the SUM function to calculate the total revenues, expenses, and profits. The result is:
| Income Statement | Formulas |
|---|---|
| Revenues | 100,000 |
| COGS | -40,000 |
| Gross Profit | 60,000 |
| Operating Expenses | -30,000 |
| Operating Profit | 30,000 |
| Net Income | 20,000 |
| Interest | =5%*[@[Net Income]] |
| Taxes | =25%*[@[Net Income]] |
| EBIT | =[@[Net Income]]+[@Interest]+[@Taxes] |
| EBITDA | =[@EBIT]+10%*[@Revenues] |
| Total | 100,000 |
We can use the same steps to create another excel table for the income statement data and the EBIT and EBITDA calculations using the top-down approach, and compare the results. Alternatively, we can add two more columns to the existing table, and use the top-down formulas to calculate EBIT and EBITDA. The result is:
| Income Statement | Bottom-Up | Top-Down |
|---|---|---|
| Revenues | 100,000 | 100,000 |
| COGS | -40,000 | -40,000 |
| Gross Profit | 60,000 | 60,000 |
| Operating Expenses | -30,000 | -30,000 |
| Operating Profit | 30,000 | 30,000 |
| Net Income | 20,000 | 20,000 |
| Interest | 1,000 | |
| Taxes | 5,000 | |
| EBIT | 26,000 | 30,000 |
| EBITDA | 36,000 | 40,000 |
| Total | 100,000 | 100,000 |
Example
To illustrate the EBIT and EBITDA calculations with a real example, let’s use the income statement data of Apple Inc. for the fiscal year 2020, which can be obtained from the company’s annual report or from online sources such as Yahoo Finance. The income statement data are as follows:
| Income Statement | Amount (in millions) |
|---|---|
| Revenues | 274,515 |
| COGS | -169,559 |
| Gross Profit | 104,956 |
| Operating Expenses | -38,668 |
| Operating Profit | 66,288 |
| Net Income | 57,411 |
| Interest Expense | -2,873 |
| Provision for Income Taxes | -9,680 |
| Depreciation and Amortization | -11,056 |
Using the bottom-up approach, we can calculate
EBIT and EBITDA using the following formulas:
EBIT = Net Income + Interest Expense + Provision for Income Taxes
EBITDA = EBIT + Depreciation and Amortization
The results are:
| Income Statement | Amount (in millions) |
|---|---|
| Net Income | 57,411 |
| Interest Expense | -2,873 |
| Provision for Income Taxes | -9,680 |
| EBIT | 64,964 |
| Depreciation and Amortization | -11,056 |
| EBITDA | 76,020 |
Using the top-down approach, we can calculate EBIT and EBITDA using the following formulas:
EBIT = Operating Profit
EBITDA = Operating Profit + Depreciation and Amortization
The results are:
| Income Statement | Amount (in millions) |
|---|---|
| Operating Profit | 66,288 |
| Depreciation and Amortization | -11,056 |
| EBIT | 66,288 |
| EBITDA | 77,344 |
Note that the results of the bottom-up and top-down approaches are slightly different, because of the rounding errors in the income statement data. In reality, the two approaches should yield the same results, as they are mathematically equivalent.
Other Approaches or Variations of EBIT and EBITDA
EBIT and EBITDA are not the only metrics that can be used to measure a company’s operating performance. There are some other approaches or variations that are sometimes used in financial analysis, such as:
- EBITDAR: Earnings Before Interest, Taxes, Depreciation, Amortization, and Rent. This metric adds back the rent expense to EBITDA, which is relevant for companies that lease a significant portion of their assets, such as airlines or retailers.
- EBITDA Margin: EBITDA divided by Revenues. This metric expresses EBITDA as a percentage of revenues, which reflects the profitability and efficiency of a company’s operations.
- Adjusted EBITDA: EBITDA adjusted for one-time or non-recurring items, such as restructuring charges, impairment losses, gains or losses from asset sales, etc. This metric aims to capture the normalized or sustainable level of EBITDA, excluding the effects of extraordinary or unusual events.
- Free Cash Flow (FCF): EBITDA minus Capital Expenditures (CAPEX). This metric represents the amount of cash that a company generates from its operations, after investing in its fixed assets. FCF is often used to value a company or to assess its ability to pay dividends or repay debt.
These metrics can also be calculated using excel formulas, by adding or subtracting the relevant items from the income statement or the cash flow statement. However, they may not be comparable across different companies or industries, as they may have different definitions, assumptions, or adjustments. Therefore, it is important to understand the context and the purpose of using these metrics, and to check the sources and the calculations carefully.
