Collaborate on Client Payment Tracker Excel for Construction Industry with Ease Using airSlate SignNow

Client payment tracker excel for Construction Industry

are you looking to build a CRM in Excel well if that's the case then you've come to the right place because today I'm going to be walking you through exactly what you need to include in your CRM and how to do it now before I delve into this if you are short for time then there is a link in the description below that you can visit to purchase this CRM and it will save you all of the time that we are going to now spend going through this so I just thought I would mention that from the outset but if you do want to build it and you do have a little bit of time then this is exactly what I would suggest that you do now the first thing we're going to do is going to rename the first sheet and we're going to call this opportunities and the second tab we are going to call dashboard and we're going to be building this dashboard out once we have built our CRM tool in this first tab now the thing with a CRM is that you can change the formatting depending on your needs or how you like the the data to appear I'm actually just going to change this to Aerial and I'm going to change this to 12 for now we can always update this going forward now the I'm going to suggest to you some example columns again this list is not exhaustive you may want to include further columns or you may not need all of these but these are the recommended recommended data points that you are that you should collect so the first one we're going to put in is company name and again we're going to be formatting as we go so I'll put this in bold and we can kind of adjust this as we go on the next field I'd recommend that you create or column is date created because you want going to want to capture when you add to your opportunities tab the next one would be owner so as you can imagine this is a name it's the the owner or assignee of the opportunity the next one is opportunity status next we have opportunity stage I will do formatting once I've put all of the columns in and then we're going to do some drop downs as well so make sure you keep watching for that the next one is value I'm actually going to left click here and double click here and that basically expands the column to the required size of the text that's in there we're going to put website street address or you could double click there and it will do the same thing for that column only City state now of course depending on where you're watching this from some of these may not apply state is obviously American or if you're in the United States you may want to change this to County as an example if you're in the UK so just thought I'd mentioned that we have zip code again that could be postcode if you're in the UK primary contact so that's an individual's name and what I'll do as well is once we've finished building these columns out and changing the formatting and and working through some of the drop downs I will also put a couple of examples in here so you can understand what kind of data you need to collect we can have an email address we are going to have a phone number this could be a mobile or it could be more of a you know a business business number and we're gonna have a section for comments or notes so what I like to do at this stage is I like to select all of these I've probably should have done this before I did the first one that's when I'm going to select aerial so we'll do it now I'm gonna click bold we'll double make sure they're selected double click there and I'm also going to just differentiate the column headings by putting a light gray in here for some reason that didn't update so I've just gone in here and I've pressed enter so that's now number font size 12. and that is the basis of our CRM and the main data we're going to collect now what we're going to want to do here is I'm going to add some options some drop down options so that when you go in here you don't have to type it out every time and that you can select based on some predefined values so for opportunity status there's going to be three different statuses that we set up now to do this we're going to left click on the column we're going to click data and we're going to click on data validation so here under the data tools select this we're going to click data validation now we are going to on the in the settings in the allow we're going to select list and here you have a couple of options for Simplicity I'm going to suggest you do it this way so we're going to have open closed comma 1 and by putting the comma in your basically differentiating the different options so I'm going to press ok now you'll notice if I kind of hover over one of these cells we have those options selected you'll also notice that we've got it on the column header and that's because I did the whole column so what you I would suggest you then do is Select D1 click data validation again and we're going to click data validation and we're going to put the value and press ok so that's removed it from this heading but it remains in every cell that follows beneath now for opportunity stage we just need to do the same thing so data validation data validation and we're going to allow a list these this time these are the options that I recommend so we can have unqualified as an unqualified lead qualified follow-up we could do it like this we could do like this follow-up demo negotiation so again these are some recommendations you may want to use if I can spell correctly negotiation now you may want to one and loss you may want to have slightly different ones than this but these are the ones that I recommend again you also might want to want to change the order that you put these in so just bear that in mind press ok I spelled negotiation wrong haven't I of course I have data validation let's go back in here nego creation negotiation I think that's correct um so here then I click on here data validation if it's not then you can correct that obviously in your in your in your CRM so if a data validation and we want this removed I've selected just the opportunity opportunity stage header we're going to take any value and press ok so now it's just in the below this column here I'd suggest so select value and what we're going to do here is we're going to click on home and we're going to change the number to currency and therefore when you start putting numbers in it's going to update it now as you can see this is in UK pounds you might be able to tell from my accent I'm based in the UK so my accounting is set to pounds by default you may want that in dollars depending on where you are I'm actually going to change this one back to General the column header because we only want it for everything underneath we don't want it for this one the heading so that's the basis of the CRM and obviously you can start adding data so let's put in ABC company obviously this is completely fictitious data doesn't make any sense whatsoever and you can change this column so here to date we can change here we go date created and then we might want to take this one off again so you can put the dates in here owner that could be me so I'll just put that in for now the opportunity status this one is open the opportunity stage this is um we're following up on this one the value is one thousand the website is abc.com you could actually change this to a hyperlink so that goes directly to the website if that is something you wanted to do you would right click and you click link and then you'd enter the link in there Teresa address this could be one two three ABC Street as an example the city we could put I'm going to put London in of course it depends on where you're based obviously what this data would be state which is not applicable for us zip code that's going to be a postcode in my context so that could be something like I think a a postcode in London um can't think of one for now but you get the idea primary contact could be gym at abc.com no sorry that would be in the email address in here this could be Jim quick as an example that's his name phone number one two three four five six and comments could be you could talk about the opportunity stage against the status or something like that so we first contacted Jim Jim scheduled follow-up on in you know on May the 1st or something like that you get the idea you could then wrap this text so that it kind of drops below or you can kind of expand it here so that was that button there the wrap text uh if you don't wrap it then it's obviously going to spill over to the next cell so that's why I like to wrap it so this is the basis of the CRM and we've put some dummy data in um next let's build a quick dashboard to provide summaries of all of your opportunities so what I like to do in this section is I'd like to kind of break down the different opportunities that you have so you could have total opportunities you could have um open opportunities and then we have closed opportunities so let's put this just down here closed opportunities and this is where we're going to use some formulas to summarize the data so as an example in the open opportunities let's bring these down actually um let's bring this across Let's uh let's bold this we're going to bold all of these actually and we could also put some gray shading just to imply that this is a different like table so we'll do that so what I like to do to count the total opportunities is actually we're going to go back into the opportunities Tab and I'm going to right click here we're going to click insert and I'm going to type in opportunity oh I've got some capitals on opportunity number so this gives us a unique identifier so we're going to put format painter there and obviously this will be number one and any subsequent one will be number two three and if I select all of these and drag these down then it's going to obviously give us our number so this gives us a potential 25 rows let's wrap this and let's bring this across a little bit so uh why is that not wrapped there we go let's do it like that so then when we go in here if we go total opportunities we can go equals count Open Bracket and we can just select all of these here so it's 25. so then if we delete these it will only count when there is a value in there so there should be one correct okay so open opportunities now what we can do here is we can add the different opportunity statuses so we have uh or all stages should I say so we have um we need to basically add all of these to this section so we've got unqualified unqualified I need to spell it correctly it won't work follow-up and make sure that if you're using the dash or something like that is exact as it appears here actually what we could probably do actually to save a little bit of time is if I go data and I go data validation data valid uh data what's that not working I think my uh I've got a little bit of lag and it's causing issues data validation it's not working for some reason I'll just have to type them out for some reason it's not working sorry so if you go qualified qualified and then we go um demo was another option sorry this is probably a bit more laborious than it needs to be um negotiation and then we go one lost so what we're doing here is we're just splitting it out so here we could do um equals count and we could do this and then we could go one close brackets because that is a status in the opportunity status field now here we could do equals count if this range comma is this oh close brackets and then if I drag this down what this is basically saying is look for this field in this column and if it sees it it's going to count it so at the moment we can see that we have one follow-up so we've got one here so the open opportunities um I'd put that in wrong sorry I'll put it as one by mistake that should be uh if I take that out and put uh open case sensitive then there we go for some reason that's not sorry this is me being an idiot right equals so if this comma is open I didn't need that comma in there I've done wrong here so equals count if count if uh this one is open there we go let's put that just there we go so here we go so head that that's this section and then what we could do at the bottom here is we could say something like potential no Revenue in open opportunities so let's bring this out so what we can do here is go equals sum if so the range would be here we then put open comma Now the sum range would be the value field close bracket and then you can see we've got a thousand we can change this to currency so then we can just do a little bit further on this here so we could do lost and then we could do Revenue in Lost opportunities so equals so this would be equals count if this is lost close brackets now I believe that's an option in here lost excellent so if I put lost in there we should have that go to one correct let's put it back as open uh sorry I'm qualified for now now Revenue in Lost opportunities so this time it would be equals sum if the range would be if this is lost comma count this close brackets so just so you can have a look at that there so what we're saying here is if this anything in this column has lost it's going to count this or sum this sorry I should say and then we put that to currency and then one opportunity is we can do the same thing so control C control B that would be one and we could literally just copy this if we wanted to instead of building it out again and just change this to one and we could do the same thing here we could literally control C ush enter there click in here Ctrl V one and it's going to do the same thing and then what we could do so we'll change this to number so this isn't particularly fancy you know we can change this we can make this you know go in something like that we'll do the same here you can obviously make this look really really great with your formatting I don't want this video to go on for too long but you get the idea but then what we could then start doing is we could start go we could start using things like um we can start visualizing this data in charts and pies and things like that so as an example we could do something like uh we could do a a 2d column we could start obviously we could do let's have a look at the open opportunities we could then do something like show me uh recommended charts so we could go like this one okay and then we could do something like um I don't know open opportunity status so you get the idea you just build this out if I just put some random data in here because we want the numbers to update so if I start doing this in the opportunities then you this should start building out and as you'll see here you can then obviously do things like change the um or change chart type but you can start adding things like the you could change the or start adding the axis and things like that so we could format axis and we could do stuff do things like um start adding these as it has filled at the bottom but you get the idea but that's how you build a basic CRM in Excel and that's how you build a corresponding dashboard I'm going to clean all this up and make it kind of really really visually appealing and then that will be available as a option to purchase in the link below um if you did want to just kind of buy this instead of building it out but chances are if you're at this stage of the video you would have built this out yourself but I hope this video was useful if it was please do hit the like button that tells me I should continue recording videos like this and do consider subscribing to the channel if you head over to my channel I've got a whole playlist on building different templates I've got videos on Project methodologies and different project management tools as well so do check that out so with all that said I hope you have an excellent day