Part II: Working with Formulas and Functions
The EBIT formula starts with net loss in C18 and uses two VLOOKUP functions to find the
interest expense and income tax expense from the income statement. For EBITDA, the for-
mula starts with the result of the EBIT calculation and uses the same VLOOKUP technique
to add back the depreciation expense and amortization expense.
There is a benefit to using VLOOKUP rather than simply using the cell references to those
expenses. If the lines on the income statement are moved around, the EBIT and EBITDA for-
mulas won’t need to be changed.
See Chapter 14, “Using Formulas for Matching and Lookups,” for more on the VLOOKUP function.
Calculating cost of goods sold
Cost of goods sold is the amount you paid for all the goods you sold. It is a critical compo-
nent to calculating gross. If you use a perpetual inventory system, you calculate the cost of
goods sold for every sale made. For simpler systems, however, you can calculate it based on
a physical inventory at the end of the accounting period.
Figure 15.4 shows how to calculate the cost of goods sold with only the beginning and end-
ing inventory counts and the total of all of the inventory purchased in the period.
Goods Available for Sale
=SUM(C2:C3)
Cost of Goods Sold
=C4-C5
FIGURE 15.4
Calculating cost of goods sold
The goods available for sale is the beginning inventory plus all of the purchases made. It is
an intermediate calculation that shows what your ending inventory would be if you didn’t
sell anything.
The cost of goods sold calculation simply subtracts the ending inventory from the goods
available for sale. If you had the goods at the start of the period or you bought them dur-
ing the period but you don’t have them at the end of the period, then they must have been
sold.
