Excel 2019 Bible

Chapter 15: Using Formulas for Financial Analysis

15

By marking up the cost of the product 32%, you achieve a 24% gross margin. If you want to
mark up a product to get a 32% margin (as shown in column E of Figure 15.2), use the fol-
lowing formula:

=1/(1-E9)-1

Using this formula, you would need to mark up this product 47% if you want your income
statement to show a 32% gross margin.

Calculating EBIT and EBITDA

Earnings before interest and taxes (EBIT) and earnings before interest, taxes, depreciation, and
amortization (EBITDA) are common calculations for evaluating the results of a business.
Both are computed by adding back certain expenses to earnings, also known as net profit.

Figure 15.3 shows an income statement and the results of the EBIT and EBITDA calculations
below it.

EBIT

=C18+VLOOKUP("Interest Expense",$B$2:$C$18,2,FALSE)+VLOOKUP("Income

Tax Expense",$B$2:$C$18,2,FALSE)

EBITDA

=C20+VLOOKUP("Depreciation Expense",$B$2:$C$18,2,FALSE)+VLOOKUP

("Amortization Expense",$B$2:$C$18,2,FALSE)

FIGURE 15.3

An income statement with EBIT and EBITDA calculations