How to Calculate EBITDA in Excel
EBITDA stands for Earnings Before Interest, Taxes, Depreciation, and Amortization. It is a financial metric used to evaluate a company’s profitability and cash flow. EBITDA is often used in business valuation, debt covenants, and investment analysis. In this article, we will show you how to calculate EBITDA using Excel.
Step 1: Open Microsoft Excel and create a new workbook.
To get started, open Microsoft Excel on your computer and create a new workbook. You can do this by clicking on the ‘File’ tab and selecting ‘New’ or by using the keyboard shortcut Ctrl + N.
Step 2: Create a new worksheet and name it ‘EBITDA Calculation’.
Next, create a new worksheet in your workbook by clicking on the ‘Insert’ tab and selecting ‘Worksheet’. Then, rename the worksheet to ‘EBITDA Calculation’ by right-clicking on the tab and selecting ‘Rename’.
Step 3: Label cell A1 as ‘Revenue’.
In cell A1 of the EBITDA Calculation worksheet, type the label ‘Revenue’. This cell will be used to input the company’s total revenue for the period being analyzed.
Step 4: Label cell A2 as ‘Cost of Goods Sold’.
In cell A2 of the EBITDA Calculation worksheet, type the label ‘Cost of Goods Sold’. This cell will be used to input the company’s cost of goods sold for the period being analyzed.
Step 5: Label cell A3 as ‘Gross Profit’.
In cell A3 of the EBITDA Calculation worksheet, type the label ‘Gross Profit’. This cell will be used to calculate the company’s gross profit for the period being analyzed.
Step 6: Label cell A4 as ‘Operating Expenses’.
In cell A4 of the EBITDA Calculation worksheet, type the label ‘Operating Expenses’. This cell will be used to input the company’s total operating expenses for the period being analyzed.
Step 7: Label cell A5 as ‘EBITDA’.
In cell A5 of the EBITDA Calculation worksheet, type the label ‘EBITDA’. This cell will be used to calculate the company’s EBITDA for the period being analyzed.
Step 8: Enter the revenue figure in cell B1.
In cell B1 of the EBITDA Calculation worksheet, input the company’s total revenue for the period being analyzed. This figure should be taken from the company’s income statement for the period.
Step 9: Enter the cost of goods sold figure in cell B2.
In cell B2 of the EBITDA Calculation worksheet, input the company’s cost of goods sold for the period being analyzed. This figure should be taken from the company’s income statement for the period.
Step 10: Calculate gross profit by subtracting cost of goods sold from revenue in cell B3.
In cell B3 of the EBITDA Calculation worksheet, calculate the company’s gross profit by subtracting the cost of goods sold from the total revenue. This can be done using the formula =B1-B2.
Step 11: Enter the operating expenses figure in cell B4.
In cell B4 of the EBITDA Calculation worksheet, input the company’s total operating expenses for the period being analyzed. This figure should be taken from the company’s income statement for the period.
Step 12: Calculate EBITDA by adding back depreciation and amortization expenses to the operating income in cell B5.
In cell B5 of the EBITDA Calculation worksheet, calculate the company’s EBITDA by adding back depreciation and amortization expenses to the operating income. This can be done using the formula =B4+B6.
Step 13: Label cell A6 as ‘Depreciation and Amortization Expenses’.
In cell A6 of the EBITDA Calculation worksheet, type the label ‘Depreciation and Amortization Expenses’. This cell will be used to input the company’s total depreciation and amortization expenses for the period being analyzed.
Step 14: Enter the depreciation and amortization expenses figure in cell B6.
In cell B6 of the EBITDA Calculation worksheet, input the company’s total depreciation and amortization expenses for the period being analyzed. This figure should be taken from the company’s income statement for the period.
Step 15: Calculate operating income by subtracting operating expenses from gross profit in cell B4.
In cell B4 of the EBITDA Calculation worksheet, calculate the company’s operating income by subtracting the total operating expenses from the gross profit. This can be done using the formula =B3-B4.
Step 16: Calculate EBITDA by adding depreciation and amortization expenses to operating income in cell B5.
In cell B5 of the EBITDA Calculation worksheet, calculate the company’s EBITDA by adding back the total depreciation and amortization expenses to the operating income. This can be done using the formula =B4+B6.
Step 17: Format the cells as currency or accounting.
To make the EBITDA Calculation worksheet more user-friendly, format the cells for revenue, cost of goods sold, gross profit, operating expenses, and EBITDA as currency or accounting. You can do this by selecting the cells and clicking on the ‘Accounting’ or ‘Currency’ format in the ‘Number’ section of the ‘Home’ tab.
Step 18: Save the workbook for future use.
Once you have completed the EBITDA Calculation worksheet, save it for future use by clicking on the ‘File’ tab and selecting ‘Save As’. Give the workbook a descriptive name and save it in a location that is easy to find.
Step 19: Congratulations, you have successfully calculated EBITDA in Excel!
By following these steps, you have successfully calculated EBITDA using Excel. This skill is essential for anyone interested in financial analysis or valuation. With practice, you will become more comfortable using Excel to analyze and interpret financial data.
