Sales tracking provides useful information about your company’s performance. Furthermore, it can dramatically improve your growing business and moving down your sales funnel.
What are sales tracking and activities?
When we talk about sales, then 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 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 instead use more dynamic sales tracking templates that demonstrate the development of KPIs.
Use our comprehensive suite of sales-related excel templates, and you can give an overview of 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
We want to create an Excel sales tracker to repay our ‘dues.’ During the past years, many different excel reports and gauge templates have been introduced, although classic templates never can be enough.
In building the sales template, our concept was to show the sales data of seven products with even 30 years.
In our case, the template can examine the period between 1985 and 2015.
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: is it possible to push a massive amount of data into only one dashboard? Our answer is yes, although there are extreme cases when we need to use tricks. First, we’ll show you a sales tracker that everyone understands.
Using a scrollbar
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 on the key information.
With the radio buttons, we can easily choose the displayed data format (USD or %) simply and easily. With this solution, we have already saved up some space for instead of two boards. So we need only use one. You can implement key performance indicators too. The chart is unique, for we have highlighted the five largest values automatically with green color and the five lowest values with red. With this method, we can send information to the leaders even faster.
How to visualize sales and profit by regions?
This section will show you step by step how to create an advanced sales tracker. We have spent a lot of time planning; fortunately, the previously introduced shape kit has helped us contain predetermined interface elements.
We had 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 to easily understand the connections and sales activity. In the first section, there are three drop-down lists. The general role of the drop-down lists is 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 salespeople here.
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 to 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 a profit of 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. Again, 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.
The former is displayed on a column chart; the 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 the usage of the combined chart closely because it can be a great benefit for us when we must visualize different kinds of sales information using a sales funnel chart.
The third part summarizes the data using a combined chart. But, again, 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 reps of all the countries in the given year, compared by country.
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 a pie chart we used here.
You should know about this kind of chart that its use is sensible in a maximum of 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 discussed many things already and discussed the details of the sales tracker template, but we haven’t discussed 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 made the demonstration.
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 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, the base chart looks for dates regarding 2014 in the ‘Dates’ column, then looks for the USA in the ‘Country’ column, then for the third condition looks for ‘Jacob’ in the ‘Name’ column.
If these three conditions are met together (as we have mentioned, SUMIFS can be used in the case of several conditions), then it sums up the values from the ‘Net Sales’ column. This might seem not very easy at first but examine the formula can be seen in the picture.
Calculations for Product 2 and Product 3 are created similarly.
To calculate profit, we also use the SUMIFS formula with only that small difference based on the given criteria now. Then, 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. Finally, on the right side, there’s the breakdown by regions using three gauges.
What kind of tools should we use? First and foremost, advanced Excel formulas like MATCH, INDEX, and VLOOKUP. Next, we calculate partial results on an additional worksheet. Finally, when we’re done, we display the results on the sales activity template.
Recommended structure for effective analysis
Let’s see the initial data set! 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. Finally, 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. In 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 the monthly breakdown. Finally, in the fifth table, we calculate the actual values of the gauges that also depend on the selected 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. It is the simplest way to unfold the numbers of yearly sales. But, first, let’s see the example of the Product A / Region I combination.
In the table, the 35% value means that the share of the year’s sales was 35%. You can interpret the other elements of the matrix based on this logic quickly. 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 the 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. First, with the help of the MATCH formula, we calculate the relative position of the chosen product. Then, 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 that beginner users can understand all the steps.
Creating Cumulative Sales Dashboard
When creating executive sales tracking templates, we endeavored to be no trouble for even beginners to understand its operation. We are 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 choose between the months, and we highlighted the drop-down list in green.
In column “B,” there are the names of the locations that we will make the comparison of. Then, we have the given city’s income data for the given month in the horizontal direction.
Because column “P” will summarize the data, that’s why if you pick the month of July, it will account for the period from January to July. 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 reports.
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 handy in Excel so that we can improve our template.
We place the sales dashboard under the main chart, and it is visible that it is changing dynamically upon choosing a month from the list. Of course, you can shape this the way you want (colors, patterns, shades, etc., using ’Shape Fill,’’ Shape Outline,’ or a shape effects tool.)
Sales Distribution Chart Template – Population Pyramid
This lesson 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 a pyramid chart.
Another option: for example, you can use the pyramid chart to show a community’s age structure. In our opinion, a simple bar chart is a fragile solution to show you the distribution of products over group buckets.
The population pyramid charts 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 plays a valuable role in sales tracking, product analysis, market analysis, risk management, or decision-making in top administration.
Thank you for being us!
You can download the sales tracker template collection using this link.