Finding High Performing Areas Within Your Department

Last Updated: Thursday, June 22, 2017 by Robb Sands
profitability leaderboard

Executive Summary

 Finding High Performance Areas Within a Department
      finding what works  
      the challenge  
      sampleco example
Approx. Skim Time: 1min 00sec ~ 100 wordsFull Read Time:4min 0sec~ 750 words

 

Performance Matters

Regardless of whether you manage salespeople, HR, finance, operations, logistics or another department within the organisation, performance matters to you.

Each department has a unique set of KPI's and each has targets for what is considered a successful period (month, quarter, year).

However, the one common trait amongst managers is that you need to know what is working and what isn't. What works is measured against segments, or categories, within your data, and can include things like:
     - Region or country
     - Team or division manager
     - Product or service type
     - Customer type, size or band

What drives success is measured against categories, or segments, within your data.  Measure it to better it.

The Problem is: How to Connect the Dots

Your data may exist in several different spreadsheets, databases or software systems, which makes connecting up everything you need a pain (unless you have Target Dashboard).

 

Example:  Sampleco

Sampleco is a company, much like any other, where one system tracks sales.

1) It has a sales order sheet that tracks which salesperson sold something, when, and for what value.

sampleco excel spreadsheet of sales


2) It has a personnel roster sheet that tracks what sales manager is in charge of which salespeople and for what countries.

sampleco excel spreadsheet of country and sales manager

 

3) It has an expenses sheet that tracks what costs were associated with each order placed.

sampleco excel spreadsheet of order costs

 

Sampleco Managers Have Some Questions

1) Which salespeople are bringing in the most revenue
2) Which salespeople are bringing in the most profit
3) How well is each country comparing to the others, in terms of revenue and profit
4) Which sales managers are bringing in the most revenue and profit

 

Question 1 - Which Salespeople are Bringing in the Most Revenue

Simply taking the first Excel sheet, importing it into Target Dashboard, then creating a bar chart, will reveal who is generating the most top line sales revenue.

  

bar chart showing salesperson revenue

 

Question 2 - Which Salespeople are Bringing in the Most Profit

Note that Dexter is producing 24% more revenue than Ian, but their profit is a dead heat, too close to call.

  

bar chart showing salesperson profit

 

Question 3 - How well is each country comparing to the others, in terms of revenue and profit

Note higher costs in England eating into profitability.


donut chart with revenue by country

donut chart with profit by country


Question 4 - Which sales managers are bringing in the most revenue, and profit

Note the higher costs in England eating into Lisa's profitability.

 

pie chart with sales by manager

pie chart with profit by manager

How to Combine the Sheets (Using Lookup in Target Dashboard)

Integrating three sheets into one master that has all data necessary to make these charts and graphs is quick and easy.

Step A - Upload all three sheets via the Excel import function in Target Dashboard

 

importing an excel sheet

 

importing an excel sheet

 

importing an excel sheet

 

importing an excel sheet

 

Step B - Edit the primary or master sheet and add lookup columns to bring in the data from the other sheets.

 

importing an excel sheet

 

importing an excel sheet

 

importing an excel sheet

 

Select the table, pick the common column, in this case the salesperson is in both tables.

Note that for the 3rd file, it is the InvoiceID that is the common field between both tables.

The idea is to repeat the process for each additional file having fields to lookup/import/link.

Pick the field to import, repeating as necessary for multiple fields. Then save and close.

 

importing an excel sheet

 

The Resulting Table Looks like:

 

importing an excel sheet

Step C - Edit the primary or master sheet and add a calculated column for profit (subtracting cost from sales revenue)

 

calculating profit

 

 

Step D - Give it a column name and use the simple formula of [field ref a] - [field ref b] and then save it. 

In this case, we're taking the Sale Amount [121685] and subtracting the Product Cost [121694] to calculate Profit.

 

calculating profit

 

The final table is pictured:

 

final spreadsheet

 

Conclusions

Performance matters, is measurable and can be improved, once thoroughly understood.


By segmenting the data, using lookup data from other sheets or sources, the picture becomes clearer and more meaningful.

Identifying the top, and bottom, performers across our key segments, we can start to draw meaningful conclusions about our next course of action.  Perhaps that means a shifting of effort toward Northern Ireland and Wales, where lower costs translated into higher profitability.  Perhaps we use Ian Johnson as a role model in producing highly profitable deals, training the other sales staff so that they can improve the margin on their new business.


Connect the dots.  Measure it.  Better it.



Where to Turn if You Need a Hand

Call us:  0141 810 2599 [UK]

Email us:  support@targetdashboard.com