How to build a sales tracker

This post will teach you how to build a basic sales tracker.

Now let's say that I'm a salesman. I make a bonus if I make $30000 in sales in one month. In the beginning of the month, I am unwinding from the hectic end of month, so my sales are usually a bit lower, I know this about myself. I also know that by week 2 I have completely forgotten how many sales I've made, and it's a panicked nail biter selling as much as I can for the last 2 weeks hoping that I do make quota.

In order to help with this stress, I'm going to create a spreadsheet. This way I can be aware of all my figures at a glance at any point in the month. I also want my spreadsheet to automatically track if I am meeting quota so far in the month, or if I am missing quota.

First step, I'm going to need to define my headers. Since I know that I want at least one "Quota Tracker" cell, I'm going to start the headers on the second row, so that I can use the top row for my tracker. Here is the model I came up with:

By the end of this build, I will have a spreadsheet that tracks all of the sales I made. Tells me when I sold it, to who, what was sold, and for how much. The top row will also tell me if I am meeting the quota I've set for myself to this day in the month, and how many dollars I need to meet the month's quota. I also want to build in a cell that will inform me how many working days are left in the month.

First I need to decide what I want my daily quotas to be. To do this, my first step is to go to Sheet 2 and to do an autofill of the date. I want to start with the 1st of the month, and autofill down to the last day of the month, and delete weekends out of the list. When you are done, your Sheet 2 should look like this:

Now I need to figure out what I want my daily quotas to be. As I said, I know that my sales are lower in the beginning of the month than the end. There are 23 workdays in this month. Because of this, I'll say I can do 1 third of my sales, $10000 between 5/1 and 5/15. Then I'll sell $20000 between 5/16 and 5/31. Since there are 11 days in the first date range needing I need to sell about $910 a day to meet that quota. Likewise, I'm selling $20000 in the days between 5/16 and 5/31 meaning I'll need to sell about $1667 a day to reach my month end quota.

Add a header for "Sales needed" and enter the amount of sales you need each day to meet your quota in that column. Just to double check your math, it helps to run a sum formula at the bottom of the column to make sure that your total is $30000 (or whatever your personal quota is). Your spreadsheet should now be looking something like this:

Now I want this spreadsheet to update daily, and keep track of my daily progress. To do this, I'm going to need to make the spreadsheet automatically calculate how much I should have sold this point in the month. The first step for this is to add another cell that will reflect today's date. To do this use the =TODAY() function like this:

 

Now we are going to build a formula for column C that will leave the cell blank if that date is in the future, but that will populate with that days quota so that it can be summed and we will have a cell that will tell us how much we should have sold so far this month. To do this, enter the following nested =IF function into cell C2:

=IF(A2=$E$2,B2,IF(A2<$E$2,B2,""))

 This formula is saying "If the date on this row matches the cell with today's date in it, populate with the daily quota, if not, check if the date listed on this row is before today's date, if it is populate with the daily quota, if not, remain blank". Make sure to use the dollar signs. If you leave out the dollar signs, or use too many your formula will not autofill correctly down to cell C24.

Autofill the formula down from C24 and you should see something like this:

 

I also added in the cell that actually totals how many sales I need so far in this month to be on track to meet my monthly quota.

So now let's go back to sheet 1 and start building up some of that page now that we have some of our functional formulas set up on sheet 2.

First thing I am going to need on Sheet 1 is a field that calculates my sales totals. In order to do this, I'm going to create some dummy data, fill it into the spreadsheet for testing. I'll also add some boarders because it's easier on my eyes. After doing the boarders and dummy data, I want to create another cell with a simple sum function to tell me how much I have sold this month. Your results should look something like:

 

Now we have all of the components built to start writing the formulas for our cells. Next, I am going to complete my "Dollars to meet quota" cell. To do this, I simply need to write a formula that subtracts my "Total sales this month" (D27) from my "Sales needed so far" (Sheet2!D25). Input this formula into B1:

=Sheet2!D25-D27

 With your new formula installed, your spreadsheet will look like this:

So it appears that I am currently $11,242.25 behind in sales this month. Looks like I'm not getting that bonus this month. But I want a clearer view of what I need to actually sell in the next week and a half to get that bonus. Que "Dollars to meet Month Quota". This is just like the last cell we did, just different cell references. I now need to subtract "Total sales this month" (D27) from "Total Monthly sales" (Sheet2!B25). To do this, enter the following formula into D1:

=Sheet2!B25-D27

This will make your spreadsheet look like this:

 

 Last step, how many days are left in this month? For this I will go back to Sheet 2. I already have column C set up so that it populates with a number if the date on that row is in the past. That means if the cell in that column is blank, that row counts as a day in the future. We can rig up a new formula to put into column D (entered into D2 and autofilled down to D24):

 =IF(C2="",1,"")

 This formula is telling the cell that if the C column is blank, populate the D cell as 1 to raise a flag. If there is information populating in this row's C cell, just keep D blank. Last step, anywhere that you like, create a cell that sums D2:D24 (D2 to D24, this is how arrays are referenced in Excel). Hopefully at this point, your Sheet 2 is looking something like this:

 

Since we have already created a cell that sums days left in the month, just create a cell reference on Sheet 1 in cell F1:

 =Sheet2!D26

You just completed building the functionality of your new production tracker!:

  

And to add a cherry on top, I just got a massive fictitious sale! It looked like I was definitely not going to make quota, but the sale is so massive that I have already surpassed my monthly quota! It put me in such a good fictitious mood that I formatted the spreadsheet some more to make it easier to read:

 

And there you have it, a completed spreadsheet that records the sales you make, and also automatically tracks your progress throughout the month and reports to you if you are ahead or behind on your sales on any given day!