Our sales tracking template series continues! Today we will show you step by step how to create an advanced sales report.
Stay with us, if you follow the blog we will share a lot of interesting information with you.
Sales Tracking Template – User Interface
We have used this when we created the free dashboard 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.
There are two things in focus, net sales and profit.
In section (1) of sales tracking template 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 of the charts, that’s why we can call this kind of Excel 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 2014, products sold in the USA by salesmen Jacob.
The net 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 net 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 net sales by salesrep.
Now let’s see the second part of the Excel dashboard 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.
This combined chart represents the accomplishment by salesrep 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 have to visualize different kinds of sales information.
The third part is a summarizing combined chart similar to the one introduced in the previous paragraph. The difference can be found in the content of the displayed information. We can find summed up information regarding the given year’s net Sales / Profit.
Important to note that these numbers contain the achievements of all salesrep of all of the countries in the given year, compared by countries.
The fourth part of our sales tracking template chart is a percentage partition exactly the same as the product level resolution 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.
Sales Tracking Template – Functions
We have talked about a lot of things already, discussed the details of the dashboard 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, than 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.
And you can start practicing; the Excel template provided with remarks can be downloaded here! We hope this sales tracking template is very useful to you.