Tracking Sales Performance Using Excel Functions

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Every day, I receive comments and questions and many of them seem to concern one common problem. Companies or managers that have a sales force must track how their sales staff is doing. How much are they selling, how much commission are they making, what are they selling, etc. I’ve been answering several of those but given the still very strong demand, I thought I would start building a more complex spreadsheet that will help any of you that are managing such spreadsheets.

Of course, there are as many different types of spreadsheets as there are companies and very few would be able to simply take this spreadsheet and “run with it”. That is not the goal either. Rather, I hope to provide good examples of a structure or functions that can be used to easily manage a sales team. Keep in mind that depending on the complexity of your data and of the requests, the size of the actual database and many other factors, the required spreadsheet could end up looking vastly different. In fact, it could be using simple excel functions, macros or pivot tables.

I will start off with a rather simple spreadsheet, one where we have one tab that includes all of the sales data and one where we can get different types of data. I will be adding 1-2 functions every time, please be sure to let me know the types of things you’d like to see. For now, here is the data that I will be using:

The first query is quite simply to get the sum of sales and commissions for both sales guys. I will do so using the sumif function:

=SUMIF(data!A:A,info!C4,data!D:D)

The result is:

You can download the spreadsheet here!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

2 Feedbacks on "Tracking Sales Performance Using Excel Functions"

Narayan

This is nice. But could you pls helf us to determine little complex kind of commission method. Like if sales between 1-100,101-200,201-300,301-400 commission will be 1%,2%,3% and 4%. But the minimum gross profit of 10% should not detoriorate further.



seo outsourcing images

seo outsourcing images…

Tracking Sales Performance Using Excel Functions | Experiments in Finance…



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.