Table of Contents
- 1 What are sales tracking and activities?
- 2 Dynamic Sales Tracker Template
- 3 How to visualize sales and profit by regions?
- 4 Tracking Sales Activity using Gauge Charts
- 5 Creating Cumulative Sales Dashboard
- 6 Sales Distribution Chart Template – Population Pyramid
- 7 Sales Campaign Analysis using Maps
- 8 Download Free Sales Templates
What are sales tracking and activities?
Sales tracking provides useful information about your company’s performance. Furthermore, it can dramatically improve your growing business and moving down your sales funnel.
When we talk about sales than we can approach the task from a lot of different directions. The main business goal is to track all the activities and procedures that can influence the sales. The analysis contains a review of sales activities during a specific time frame to recognize trends and compare actual performance with targeted performance.
We can create simple forecasts using sales-forecast charts. But we rather use more dynamic sales tracking templates that demonstrate the development of KPIs.
Use our comprehensive suite of free sales related excel templates and you can give an overview about sales activities to refine your sales process. It’s important to set effective and achievable business goals. You can use and easily manipulate these templates and transform them into actionable reports.
We do not like boring stuff, so hopefully this will be proven in this article too.
Dynamic Sales Tracker Template
Creating excel sales tracker we would like to repay our ‘dues’. During the past years many and different excel reports and gauge templates have been introduced, although from classic templates never can be enough.
In building of the sales template our concept was to show the sales data of seven products with the addition that even a 30-year period.
In our case the period between 1985 and 2015 can be examined.
We display values in USD and percentage also. In the next section we talk about the most important thing: design and how it works in action.
The question always remains the same: is it possible to push a huge amount of data into only one single dashboard? Our answer is yes, although there are extreme cases when we need to use tricks. We’ll show you a sales tracker that everyone understands.
Using a scrollbar in Excel, we can step year by year as you can see on the left-hand side of the picture. At one time we can display the data of 8 years dynamically in one window and in the middle of the window we can see related current data to these years.
When choosing one product the sales tracker automatically highlights the cells corresponding to the chosen product. So, our eyes can’t wander but can concentrate immediately to the key information.
With the use of the radio buttons we can choose the format of the displayed data (USD or %) simply and easily.
With this solution we have already saved up some space for instead of two boards we need only use one. You can implement key performance indicators too.
The chart is unique for we have highlighted automatically the 5 largest values with green color and the 5 lowest values with red. With this method we can send information for the leaders even faster.
How to visualize sales and profit by regions?
In this section will show you step by step how to create an advanced sales tracker. We have spent a lot of time with planning; fortunately, the previously introduced shape kit has helped us that contain predetermined interface elements.
We have used this when we created the template below.
Let’s get into the middle of it! In the process we have divided the screen into four separate parts and marked them with numbers so that you can easily understand the connections and sales activity.
In the first section there’s three drop-down lists. About the general role of the drop-down lists you have to know that they make data-grouping, organization and filtering considerably easy.
There are two things in focus, sales and profit.
In section (1) you can find the central element of the excel sales tracking template, the menu system that coordinates the data-filtering: you chose the sales year, country and salesmen here.
Obviously, any change in the current values of the drop-down list will change all the charts, that’s why we can call this kind of sales report dynamic.
Let’s see what explains chart number 1 on the top left and its numbers! In our example we have chosen sales year 2016, products sold in the USA by salesmen Jacob. The profit in this case was 82 419 USD, with profit 49 257 USD. The chart here continues to display further details.
This is a product level resolution. The chart details the accomplishment of the responsible sales representative with relation to each product. Looking at the picture we can conclude that to the sum of sales the products contributed as follows: Product 1 22%, Product 2 40%, and finally Product 3 38%.
Under the percentage we have marked the sales in USD also.
Summary: on this screen we can see the sales by sales rep.
Now let’s see the second part of the sales tracking template located on the left bottom. The value of net sales is the same as on the previously introduced chart. The main difference in this case is that with the net sales value we also portrayed the profit.
Former is displayed on a column chart, latter is on a line chart combined on one joint screen.
Net Sales and Profit Breakdown by Country
This combined chart represents the accomplishment by sales rep and profit in product level resolution. Examine closely the usage of the combined chart because it can be a great benefit for us when we must visualize different kinds of sales information using sales funnel chart.
The third part is a summarizing combined chart like the one introduced in the previous section. The difference can be found in the content of the displayed information. We can find summed up information regarding the given year’s Sales / Profit.
Important to note that these numbers contain the achievements of all sales rep of all the countries in the given year, compared by countries.
Product Level Resolution
The fourth part of our sales tracking template chart is a percentage partition the same as the product level breakdown analysis portrayed in point 1 with the addition of a pie chart we used here.
You should know about this kind of charts that the use of it is sensible in the case of maximum 3 or 4 categories, if you have more than these it becomes confusing, we recommend you keep this in mind.
Formulas and Functions
We have talked about a lot of things already, discussed the details of the sales tracker template but we haven’t talked about the formulas that help us summarize data. As usual we have placed the calculations on a sheet called ‘calculation’, now let’s review these shortly.
Here is a part of the base chart from which the demonstration was made:
The first of the Excel formulas is the SUMIFS. The essential of the formula is that it sums up values based on several conditions.
For example, in the case of Product 1 we have calculated the Net Sales value as follows.
The formula sums based on the values of currently portrayed cells in the drop-down list.
So, in the base chart it looks for dates regarding 2014 in the ‘Dates’ column, then looks for USA in the ‘Country’ column, then for the third condition looks for ‘Jacob’ in the ‘Name’ column.
If these three conditions met together (as we have mentioned SUMIFS can be used in case of several condition) then it sums up the values from the ‘Net Sales’ column. This might seem complicated at first but examine the formula can be seen on the picture.
Calculations for Product 2 and Product 3 are created in a similar manner.
To calculate profit, we also use the SUMIFS formula with only that small difference that based on the given criteria now we sum up the values in the ‘Profit / Loss’ column.
Tracking Sales Activity using Gauge Charts
Using sales activity templates, we will create a good looking sales presentation. In today’s tutorial we’ll show you how to create a complex presentation. The main objects are visualizing 4 products, 3 regions, yearly and detailed data and all of this is on one screen!
The left-side section contains a column chart. This displays the chosen product’s yearly sales evolution. On the right side there’s the breakdown by regions with the use of three gauges.
What kind of tools should we use? First and foremost, advanced Excel formulas like: MATCH, INDEX and VLOOKUP. We calculate partial results on an additional worksheet. When we’re done we display the results on the sales activity template.
Recommended structure for effective analysis
Let’s see the starting (basic) data! The data worksheet contains the starting data and all the calculations that we need. On the gauge worksheet stored is the basic information of the speedometers (minimum, maximum and actual values). On the main worksheet there are the operational elements and the charts. Let’s see in detail the tables found on the data worksheet!
In the first range you can see the matrix contains the four products and the three regions.
On the second table we didn’t use breakdown by regions, here the emphasis is on the sale values by months.
On the third table, on the dashboard worksheet the name of the chosen product appears. With this value we’ll make further calculations.
The fourth table contains the yearly sales of the chosen product in monthly breakdown. In the fifth table we calculate the actual values of the gauges that’s also depends on the chosen product.
Prepare Chart for Sales Activity Template
After this introduction let’s see all the steps of the calculations!
For the region breakdown we’ve used percentage values. This is the simplest way to unfold the numbers of yearly sales. First let’s see the example of Product A / Region I combination.
In the table the 35% value means that the share of the year’s sale was 35%. You can interpret the other elements of the matrix based on this logic easily. We display the values of the second table on a column chart! These are fixed values we don’t have to make any calculations.
And now the trick comes!
In the fourth table (calculation for monthly data charts) we have to display the yearly data for the chosen product. On the picture you can see that this is now Product “B”. The value of K2 cell is 3. With the help of the VLOOKUP formula we’ve calculated the values belonging to this product.
The fifth table is interesting! Excel offers two solutions for lookup type searches. The first one is the old, but widely used VLOOKUP. The other is the combination of the INDEX and MATCH formulas.
Advanced users prefer to use the latter solution. With the help of the MATCH formula we calculate the relative position of the chosen product. The INDEX will give the precise position of the searched value.
Finally, we link the gauge charts and the gotten values. The interactive sales template will become truly spectacular with the use of the gauge charts! After download check the calculations! We have built the formulas, so beginner users can understand all the steps.
Creating Cumulative Sales Dashboard
When creating executive sales tracking templates, we endeavored that it will be no trouble for even beginners to understand the operation of it. We hopeful that after reading this tutorial you will handle it easily, whether you use it for work or study. Let’s take a closer look at the figure below.
We have designed a simple list that allows us to be able choose between the months and we highlighted the drop-down list with green color.
In column “B” there are the names of the locations that we will make the comparison of. In the horizontal direction we have the given city’s income data for the given month.
Because the data will be summarized in column “P”, that’s way if you are picking the month of July, then the period from January to July will be accounted for. Accordingly, if you are choosing December, in that case you will have the whole year’s income data available.
Does this sound simple and easy?
Let’s look behind the curtains now and we will show you what kind of gears we have used to create cumulative sales report.
The OFFSET and MATCH formulas will help your work again. The combination of the two formulas will give the result we want. We will use these formulas subsequently, they are very useful in Excel, so we can improve our template.
We place the sales dashboard under the main chart, it is visible that upon choosing a month from the list it is changing dynamically. Of course you can shape this the way you want (colors, patterns, shades, etc. using ’Shape Fill’, ’Shape Outline’ or shape effects tool.)
Sales Distribution Chart Template – Population Pyramid
In this lesson we will show you how to create a great looking sales distribution chart in Excel to establish the distribution of customers by age and product type.
Bad news… Excel doesn’t have this chart style in the Chart’s ribbon. However, we’ll create your own sales distribution chart by using pyramid chart.
Another option: for example, you can use the pyramid chart to show a community’s age structure. Our opinion an everyday bar chart is very weak solution to show you the distribution of products over group buckets.
Just a few words about pyramid chart. Population pyramids are an effective method to analyze male and female populations over a range of ages.
Sales Campaign Analysis using Maps
How to create excel maps to track sales performance? From now it’s simple, you need only 2 minutes to finish the procedure.
Over the past few days we’ve been hard at work in the prototyping phase of Excel UK and US Dashboard. In various business sectors mapping play a valuable role, it is for sales tracking, product analysis, market analysis, risk management or decision making in top administration.
Download Free Sales Templates
Thank you for being us!
You can download the sales tracker template collection using this link.