In the world of business, especially customer-facing organizations, customer satisfaction is key. It’s absolutely essential to stand out from the crowd. This helps set the stage for long-term success. It also ensures that you deliver the best possible service at any given point.
Fortunately, this satisfaction can be measured, studied, and quantified. And thanks to Microsoft Excel, it’s very easy. You can use statistical measurements like derived importance to do exactly that. It only takes a few quick steps. Let’s learn how to calculate derived importance in Microsoft Excel.
How to Calculate Derived Importance in Excel
Before we learn how to calculate derived importance in Microsoft Excel, it helps to understand the terminology. More importantly, you need to know how derived importance can help your business. Derived importance, first off, is essentially a rather basic correlation analysis. It’s fueled by survey research conducted in your business. Let’s consider an example.
Imagine that you operate a business like a restaurant. You want to conduct a survey of your customers to determine how likely they are to recommend your restaurant to other people. This is done on a basic sliding scale of 1 to 10, with higher satisfaction levels represented by higher numbers.
As a standalone measure, this provides a good snapshot of how customers generally perceive your business. But alone, it’s hard to use as a tool to improve the overall levels of satisfaction. Let’s consider why.
Imagine, for example, that you survey 100 people, and the average score lands at 7.0. This is a positive rating, but there is upside room for improvement. You need to know which areas of your restaurant’s business to target to help improve the score. This is derived importance, and calculating it begins by expanding your survey research.
Imagine now an expanded survey. Your initial question will (and should) remain – the top-level satisfaction score. But to find derived importance, you need more data. These could be questions, or statements like the ones below, rated once again on a 1-10 scale:
- To me, the pricing of food is most important when I choose a restaurant.
- The availability of outdoor dining is a major factor when I choose a restaurant.
- I avoid restaurants with limited menu offerings.
It’s worth noting that the 1-10 scale doesn’t have to be used. You could measure satisfaction and other factors on 1-5, 1-100, or any other scale that you devise. Simply choose one and consistently apply it in your survey.
Once you’ve gathered your data, input it in an Excel spreadsheet like the one below. Column A represents the initial question (overall satisfaction and likelihood that you’ll be recommended). The other column or columns represent your followup questions.
Derived importance simply means recognizing which factors (such as pricing, outdoor dining availability, and more) have the greatest impact on the overall, top-level data. To do this, perform a quick correlation analysis in Excel. Click into an empty cell, and input the CORREL formula. In the example of this data, your formula reads:
=CORREL(A2:A11,B2:B11)
Hit Enter on your keyboard with the formula entered, and Excel returns the correlation coefficient, which here is 0.947. Remember, a correlation coefficient of 1 is a perfect positive correlation; -1 is a perfect negative correlation. In this example, the coefficient is a higher value, indicating strong positive correlation.
This, then, is the derived importance calculation complete. Think of what it’s telling you. In essence, customers are more likely to recommend your restaurant if they believe pricing is the most important factor.
Carrying it to the next level, this helps you guide business decisions. You need to be careful about implementing any price increases, as this will negatively impact how others perceive you. The derived importance calculation proves that this will hurt your chances of being recommended to other potential customers.
As you can see, it’s easy to calculate derived importance in Microsoft Excel.