Part II: Working with Formulas and Functions
Average Customer Margin
=F4/AVERAGE(C4,C10)
Customer Lifetime Value
=F6/C7
FIGURE 15.9
Customer lifetime value calculation
To calculate CLV, follow these steps:
- Calculate the gross margin.
- Calculate the average customer margin by dividing the gross margin by the
average number of customers for the month. Because the gross margin was
earned over the month, you have to divide by the average number of customers
instead of either the beginning or ending customer count.
- Calculate the CLV by dividing the average customer margin by the churn rate.
In this example, each customer will contribute an estimated $828.97 over their lifetime.
Calculating employee turnover
Employee turnover is a measure of how well an organization is hiring and retaining talent.
A high turnover rate indicates that the organization is not hiring the right people or not
retaining people, possibly because of inadequate benefits or below-average pay. Separations
commonly include both voluntary and involuntary terminations.
Figure 15.10 shows the employment changes of an organization over a 12-month period.
New hires are added to and separations are subtracted from the number of employees at the
beginning of the month to get the ending employee count.
Average Monthly Employment
=AVERAGE(F3:F14)
Separations
=SUM(E3:E14)
Employee Turnover
=F17/F16
