Chapter 15: Using Formulas for Financial Analysis
15
FIGURE 15.10
Monthly employment changes over one year
Employee turnover is simply the ratio of separations to average monthly employment.
The AVERAGE function is used to calculate the average ending count of employees over
the months. Separations are summed using SUM and are divided by the average monthly
employments.
The result can be compared to industry averages or companies in the same industry.
Different industries experience different turnover rates, so comparing them can lead to
poor decisions. You don’t have to calculate turnover for a 12-month period, but doing so
removes seasonal employment variations that can skew results.
Leveraging Excel’s Financial Functions
It’s a safe bet that the most common use of Excel is to perform calculations involving
money. Every day, people make hundreds of thousands of financial decisions based on the
numbers that are calculated in a spreadsheet. These decisions range from simple (“Can I
afford to buy a new car?”) to complex (“Will purchasing XYZ Corporation result in a positive
cash flow in the next 18 months?”). This section discusses basic financial calculations that
you can perform with the assistance of Excel.
Converting interest rates
Two common methods for quoting interest rates are the nominal rate and the effective rate.
Nominal Rate This is the stated rate, and it is usually paired with a compounding period,
for example, 3.75% APR compounded monthly. In this example, 3.75% is the nominal rate,
APR is short for annual percentage rate (meaning that the rate is applied on an annual
basis), and one month is the compounding period.