Track Maintenance Plan Churn Rate % in Excel and Identify Retention Issues Early

How to Calculate Maintenance Plan Churn Rate % in Excel

1. Set Up the Core Data Fields

Start by making sure your dataset includes the key fields needed for the analysis:

• A month or snapshot date
• A customer ID
• A plan field
• A flag for whether the plan was active at the beginning of the month
• A flag for whether the customer canceled

The video uses binary fields, so active plans and cancellations are marked with values that can be counted or summed.

2. Create a Month-End Date Column

To standardize each monthly snapshot:

• Create a new column for the end of the month
• Use the EOMONTH function to convert each snapshot date into a month-end date
• Use this new column as the monthly grouping field for the rest of the analysis

This gives you one consistent date for each month in the dataset.

3. Generate a Unique List of Months

Next, create the reporting table:

• Use the UNIQUE function on the month-end column
• Make sure the months are in the correct sequence
• Format the dates clearly
• Center the values if needed for presentation

This becomes the list of months used in the summary table and chart.

4. Calculate Active Members by Month

To count how many maintenance members were active at the beginning of each month:

• Use COUNTIFS to count records where the plan was active
• Match the count to each month in your unique month list
• Copy the calculation down for all months

The video also notes that because the field is binary, you could use a sum-based approach as well, but the example starts with COUNTIFS.

5. Calculate Churned Members by Month

To count how many customers canceled in each month:

• Use SUMIFS on the cancel flag column
• Match the cancellations to each month in the month list
• Copy the calculation down for the full period

Because the cancel field is binary, summing the cancel flags gives you the number of churned members.

6. Calculate the Monthly Churn Percentage

Once you have active members and churned members:

• Divide the monthly churn count by the monthly active member count
• Format the result as a percentage
• Add a decimal place if needed for readability

This gives you the monthly Maintenance Plan Churn Rate %.

7. Add a Target Line

To compare actual churn against a goal:

• Create a target value in a separate cell
• Reference that value down the full target column
• Use this as the benchmark line in the chart

The video uses a fixed monthly target so you can quickly see which months are above or below goal.

8. Build the Combo Chart

To visualize the KPI:

• Highlight the month column
• Highlight the active members column
• Hold Control and also select the actual churn rate and target rate columns
• Go to Insert
• Choose Recommended Charts
• Then switch to All Charts
• Select Combo

In the combo chart setup:

• Set Active Members as columns
• Set Actual Churn Rate % as a line
• Set Target Rate as a line
• Place both percentage lines on the secondary axis

This creates the chart structure shown in the video.

9. Format the Chart for Clarity

After the chart is created:

• Adjust the line colors so the actual rate and target rate are easy to distinguish
• Make the target line thinner so it stays visible without taking over the chart
• Test different colors to improve contrast
• Update the target cell to instantly move the target line up or down

This makes the chart much easier to interpret during reviews.

10. Add Only the Most Useful Data Labels

Instead of labeling every point:

• Click the line series
• Select only the highest point and add a label
• Select only the lowest point and add a label
• Optionally format the labels with a background so they are easier to read

This keeps the chart clean while still highlighting the most important moments.

11. Review the Trend Over Time

The final step in the video is to step back and review the pattern:

• Look at whether active members are growing
• Compare actual churn against the target each month
• Check whether small monthly churn percentages add up to a much larger annual problem
• Use the monthly view to spot periods where churn begins to rise

The chart is designed to help you see whether the maintenance plan base is actually expanding or quietly shrinking over time.