Create Sales Chart To Evaluate Team Performance

Many businesses track sales, but few truly understand the nuances of their sales force performance. 

We start with a fundamental question: 

Can you name your top-performing sales associates right away? 

If not, it's time to leverage your sales data.

Our video demonstrates this process using a clean sales performance data set for "bite-sized munchies." For those looking to follow along, you can download this exact data set for free by joining our newsletter. This also gives you access to a wealth of other data sets to enhance your analytical skills.

The first step in analyzing sales force performance is organizing your data. We group sales data to prepare it for charting. 

By using formulas like UNIQUE and SORT, we can easily list all unique salespeople and arrange them alphabetically, preparing the data for effective visualization.

Once your data is organized, the next step is to calculate the "total sales" for each employee. 

The SUMIF formula is perfect for this. It allows us to sum up all sales amounts associated with each salesperson, providing a clear initial view of individual revenue generation.

After calculating total sales, we create a basic bar chart. This chart visually represents the total sales for each employee.

While helpful, this initial sales chart offers only one dimension of sales force performance. It doesn't tell us, for example, if high sales are due to a few large deals or many small ones.

This limitation highlights why a more comprehensive "sales performance analysis" is essential for truly understanding "how to measure sales performance" and effectively conduct an "appraisal of sales force performance."

To deepen our understanding of sales force performance, we add another critical metric: "total deals closed." This gives context to the total sales figures. An employee might have high total sales but close very few deals, indicating large individual transactions. Conversely, an employee might close many deals, but their total sales are low, suggesting smaller average order values.

We calculate "total deals closed" using the COUNTIFS formula. This formula counts the number of deals each employee has closed.

With this new data point, we can create a more informative chart. Our video demonstrates how to create a combo chart, displaying both "total sales" (as bars) and "total deals closed" (as a line on a secondary axis).

This "sales performance chart" helps us understand different sales patterns. For instance, some individuals like Shane and Sophia might close many low-value deals, while Martin closes fewer but higher-value deals.

This combined view is vital for a thorough "evaluation of sales force performance."

To achieve the most comprehensive understanding of sales force performance, we introduce a third metric: "average order value." This metric reveals the typical value of deals closed by each salesperson.

By combining total sales, total deals closed, and average order value, we get a holistic view.

The AVERAGEIFS formula helps us calculate the average order value for each employee. With all three metrics ready, we move to create a bubble chart. A bubble chart (also known as a scatter plot) is a powerful tool for visualizing three dimensions of data at once.

In our bubble chart, "total deals closed" forms the X-axis, "total sales" forms the Y-axis, and the "average order value" determines the size of each bubble.

Each bubble represents a salesperson.

We optimize the chart's appearance by adjusting the vertical axis maximum, setting it to $2,000,000, naming the axes clearly (e.g., "Total Revenue in US dollars"), and setting a descriptive title like "Total Sales Performance."

We also adjust bubble opacity to 40% and borders to black for visual clarity, creating a polished and insightful "sales performance chart."

This advanced "sales performance chart" provides powerful insights into your "sales force performance." For example, you might observe that:

• Shane Walsh appears to be underperforming. His small bubble size suggests a low average sales deal compared to his peers, even if he closes a decent number of deals. This points to an area needing attention.

• Zeus shows significant potential. He has high average order values, meaning his deals are generally large. If he increases the number of deals he closes, his total revenue could grow substantially.

• Cindy seems to be stagnant in the middle ground, indicating a need for strategies to boost her performance in both deal volume and average value.

• Zed needs to increase his average order value to align with top performers like Martin.

This visual representation allows you to quickly identify strengths, weaknesses, and growth opportunities within your sales team. It's a key tool for "how to track sales team performance" and conduct a meaningful "sales force performance appraisal."

Ready to create these powerful sales performance charts yourself? Here’s a breakdown of the steps shown in our video:

1. Prepare Your Data

• Gather Data: Ensure your sales data is clean and includes at least salesperson names and sales amounts.

• Add "Z Employee" Column (Unique Salespeople): In a new column, use the formula =SORT(UNIQUE(A2:A)) (assuming salesperson names are in column A, starting from A2) to get a sorted list of unique employees.

2. Calculate "Total Sales"

• Add "Total Sales" Column: In a new column next to your "Z Employee" list, use the formula =SUMIF(A:A,E2,B:B) (assuming salesperson names are in column A, sales amounts in column B, and your first unique employee is in E2).

• Autofill: Drag the formula down to apply it to all employees.

3. Create the Initial Bar Chart

• Select Data: Select your "Z Employee" names and "Total Sales" data.

• Insert Chart: Go to "Insert" > "Chart".

• Choose Chart Type: Select a bar chart (or column chart).

• Aggregate Data: Ensure the chart aggregates the data by summing the totals.

• Customize (Optional): Add a chart title (e.g., "Salesperson Total Sales") and move it to its own sheet for better viewing.

4. Calculate "Total Deals Closed"

• Add "Total Deals Closed" Column: Back in your main data set, add a new column. Use the formula =COUNTIFS(A:A,A2) (assuming salesperson names are in column A and the first row of data is row 2).

• Autofill: Drag the formula down for all rows.

5. Create the Combo Chart (Total Sales & Total Deals Closed)

• Select Data: Select your "Z Employee" names, "Total Sales," and "Total Deals Closed" data.

• Insert Chart: Go to "Insert" > "Chart".

• Choose Chart Type: Select a combo chart.

• Configure Series: For "Total Deals Closed" in the series options, change the type to "Line" and set the axis to "Right".

• Customize (Optional): Adjust titles and move to a new sheet.

6. Calculate "Average Order Value"

• Add "Average Order Value" Column: In your main data set, add another new column. Use the formula =AVERAGEIFS(B:B,A:A,A2) (assuming sales amounts are in column B, salesperson names in column A, and the first row of data is row 2).

• Autofill: Drag the formula down.

7. Create the Bubble Chart (The Survivor Analyst Chart)

• Select Data: Select all relevant columns: "Z Employee" names, "Total Sales," "Total Deals Closed," and "Average Order Value".

• Insert Chart: Go to "Insert" > "Chart".

• Choose Chart Type: Select a "Bubble chart".

• Configure Data:

  • • ID: Set to "Z Employee" (or salesperson name column).

    • X-axis: Set to "Total Deals Closed".

    • Y-axis: Set to "Total Sales".

    • Size: Set to "Average Order Value".

    • Series: Set to "Z Employee" (or salesperson name column).

    • Remove ID from Aggregation: Make sure the ID is not aggregated so you have individual bubbles.

• Customize Chart:

  • • Vertical Axis: Adjust the "Max" value to ensure all bubbles are visible (e.g., $2,000,000).

    • Chart & Axis Titles: Rename the X-axis to "Total Deals Closed" and the Y-axis to "Total Revenue in US dollars". Set the main chart title to "Total Sales Performance".

    • Legend: Change the position to "Top".

    • Bubble Options: Set "Opacity" to 40% and "Border Color" to Black.

• Move to New Sheet: Publish the chart to its own sheet for a clearer view.

• Move beyond basic reporting to conduct a meaningful evaluation of sales force performance.

• Combine key metrics like total sales, total deals closed, and average order value for a holistic view.

• Use multi-dimensional charts to gain powerful insights that a single metric cannot provide.

• Identify strengths and weaknesses to create targeted strategies for your sales team.

• Transform data into action to drive business results and maximize ROI.