Enhance your research and development process with sales lead management for R&D

How to create outlook signature

foreign [Music] Highway your One Stop Shop for all your Excel needs today's video I want to share with you a lead tracking tool that you can build in Excel if you're trying to improve your business and using different lead sources and you want to understand what's working what's not how long does it take etc etc you can do this by using this template um currently I'm showing you the dashboard sheet this is the output basically and this is where you can build your kpis and your slicers and really understand very easily what's working well and what's not working so well if you like this kind of contact contact please make sure to subscribe and you will continue to see my videos I'm publishing constantly the file has four sheets sheet called lists which can be used to create the drop down list right now I only have one database which is basically what builds the dashboard pivots where I have pivot tables and other options that's going to build that's the back end part of the dashboard and the dashboard itself which is the front end so let's start with the easiest one lists I have a list for lead source which is a category I'm using for filtering the data and this is a table and I've also um Define it as a range called lead okay so to do that you just need to Mark the table and give it a name in this case I gave it the name lead and this is going to be used as a drop down list database so what columns do you have here you have the blue columns which are the columns you need to manually input and the orange columns which are the formula columns which of course you can change the mechanism so lead Source this is a drop down list coming from here so whatever you change here if I add another field it will immediately appear that's what's nice about using um table with a drop down list so you have the lead Source then there's some columns that didn't fill them out just for some data like the name website URL and project description the costs in US dollar but of course any any uh currency will work then there are the dates okay for the different steps in the lead first of all the lead itself when when did you start this is a must the next step is the opportunity basically it's when you you know engage the customer or send them some information maybe had a demo whatever next stop is when you send a proposal quotation next up is when you completed the negotiation and then there are three outputs either you won the bid the lead you lost the lead or the customer went silent and as you can see some lines are in different phases and a it's important that the dates are makes sense from their order perspective you can't win a bid before you sent a proposal so make sure that you fill that out in the correct way of course you can change these to whatever whatever name matches your business okay or you can even add steps once you understand the logic you can build it in your own the next step is to understand the lead status so basically I'm looking for the max value here so from our perspective the last date inputted is this current status so I'm using index and match match for the max value and the index over here so I'm returning that position in the front row first row then I have a lot of columns to calculate the time so opportunity time that's the time between these columns if there is anything in the opportunity and this goes on for all three tables three columns the last column is the closing date in here I'm looking for the difference between um the one or lost column to the negotiation because from my perspective that's the only these are the only leads that are closed if you have a gun silent it's not shouldn't be part of the analysis cycle time of course is everything together all right then you have a lead counter I'm just going to use it for one of the charts formula is the row number one so it just has a unique value no matter how many lines you have here opportunity counter prop counter and negotiated counter and wins counter are all counters I'm using for the different phases to see if that this line went through any of them that those are going to be using the dashboard and the last column is checking if this line is closed so if there is a closing time then this line is closed if not it there is not a closed line so that's the database manual data over here formulas that you can play with and of course you can change whatever you want add more columns whatever let's go to the pivots so all the pivots are connected to the slicers over here and basically they're just simple pivot tables this one has the lead number that we saw before and the sum of the cycle time this one is going to fill this chart so this chart basically is showing you could use a date if you want I didn't want to have dates I wanted to have to be a little bit more clear chart because it's only supposed to show you the fluctuation and cl in the time to close the deal the lead sorry so you see it has the lead number which is this column right and just the sum of the cycle time only for closed leads okay so that shows you the time to close the delete then I basically built all of these are shapes that are connected to a formulas you see it's equal to a certain cell it's very useful trick that you can use to dynamically change shapes and they're all connected here to one or one of the other pivot tables so here it shows you basically the total number of opportunity time proposal time negotiation closing time you know those columns over here let's calculate the time and here is the text so I'm taking the name of the header and chart 10 gives you a space between the text and D7 which is just round number instead of the full number and that's all of those the title I have average cycle time and the summary of those and days and it this is what populates these cells it's very simple to build you just need to create a shape and in the shape go to the function and there you go once you drag it to another sheet you would need to do it again because it does not remember the location so you need to go over there and change the location so that's that part let's talk about the sales conversion area here this is over here so I'm just counting the number of leads so remember that's number that goes up and then I'm summing all those counters that I built okay it's these basically it's a one or zero for all of these and here I already have the column so I just use the count since I know it's a and it's it's an integer number and here I have the headers that I want to show and this is what you see here with the ripped this is to repeat spaces because the the the length of the name where the text is different if I don't have that I'll show you how it looks if I don't use that or you know what let's do it this way if I don't use or if I use one space then it doesn't look very nice does it you have a different location for the number but if I if I have spaces to match the length and I just played with it to see how it looks then it pretty much is on the same page which looks nice so this gives you the number of of lines that passed each step we started off 91 you ended up with 12. so your conversion from leads to wins is 13 and you see these nice Arrows with the number show you your conversion between the steps this is what you see over here very simple this is the number itself it's how much you were in the next phase divided by where you were I'm rounding that up and to visually show this in the shape I'm multiplying by 100 and adding the percentage icon okay so it's very simple and that feeds the arrows again just the shape and you reference to another location last for this one we have the project cost as you can guess it's a very simple I have another pivot table with the cost only for uh the the leads that I want and uh this is the the text itself again using the HR for a space here I'm using round uh and dividing by a thousand because I want to show this in uh k u s dollars you don't have to do that but you can if you want to so those are this these parts and the slicers these three slicers sorry this timeline and slicer you see they are connected to a lot of pivot tables so all of these will change once I change the timeline that's a very standard um you know method to use multi-pivot table slicer it's very easy to use and if I change the information changes here I built it on year and quarter and you can see everything change ing to my selection which is very nice the other two metrics that I built here are number of leads by source so this is also connected to the to the time Horizon only not to the lead Source filters so here you see it for the entire year and if I select Q4 it's going to show me what I have for Q4 and the last 30 days closed leads that is not connected to uh to the time Horizon or the lead Source it's just supposed to show you your last 30 days okay so you have the dashboard for the two charts here it is simple pivot table again uh group the date sorry let me show you there's the lead Source then there's the date once you have a date you just need to if it looks like this just click right click click on group select months and year and you can see it in this manner and I'm counting the number of leads all right for the number of leads that were closed in the last three days I'm not using a pivot table that's why everything is not connected I'm just using a simple sumifs this is today and then I took 30 days and went all the way and I'm just using a simple sumifs just for whenever um we want you have a closed lead for that date okay um so that is it if you enjoyed this content please subscribe share this like it do whatever you can to help this channel grow I would very much appreciate it and let me know in the comments what you think have a good day now