Excel 2019 Bible

Chapter 15: Using Formulas for Financial Analysis

15

Gross Margin

=SUM(D7:D8)

Revenue

=ROUND(D8/(1-SUM(C4:C7)),0)

The two variable expenses shown in Figure 15.6, cost of goods sold and selling expenses,

are calculated by multiplying the revenue figure by the percentage. The formulas from

Figure 15.6 are shown here:

Cost of Goods Sold

=ROUND(D3*C4,0)

Selling Expenses

=ROUND(D3*C7,0)

To build the break-even model in Figure 15.6, follow these steps:

1. Enter 0 into cell D18 to indicate zero net profit.


2. Enter the fixed expense amounts in column D next to their labels in column B.


3. Enter the percentage the company pays in commission in cell C7 (8% in this
   example).


4. Enter a percentage equal to 1 minus the expected gross margin in cell C4. In
   this example, the company expects a 60% gross margin percent, so 40% is entered
   in C4.


5. In cell D13, enter the formula for the operating margin shown earlier. The oper-
   ating margin must be the sum of interest expense and other income and expense.
   As shown in Figure 15.6, if we estimate the interest expense to be $465 and the
   other income and expense to be $1,368, then the operating margin must be $1,833
   for the net profit to be zero.


6. In cell D8, enter the formula for the margin net of variable expenses shown
   earlier. This calculation is operating margin plus the fixed operating expenses. It
   will drive the revenue calculation.


7. In cell D7, enter the formula for selling expenses shown earlier. We haven’t
   entered the revenue formula yet, so this will be zero for now. But once revenue is
   entered, it will show the correct value.


8. Enter the formula for cost of goods sold in cell D4. Like the selling expenses for-
   mula, this will return zero until revenue is computed.


9. Finally, enter the formula for revenue in cell D3. The revenue calculation divides
   the margin net of variable expenses by 1 minus the sum of the variable percent-
   ages. In Figure 15.6, the two variable expenses will be 48% (40% plus 8%) of
   revenue. One minus that number, 52%, is divided into the margin net of variable
   expenses to get the revenue.