Empower Your Business with Pipeline Tracking Spreadsheet in Canada

How to create outlook signature

nine o'clock on tuesday morning so i don't know what i'm gonna do today and i gotta tell you doing these every week is a lot more work than i'm used to doing when you add into my whole schedule um it's kind of crazy but i'm definitely getting a lot more stuff out there like in january i ended up doing two more templates which normally i only do one every month and a half so i did the driving range one recently that was the not last stream of the stream before last the then the first one i did was the sas uh what was it oh cancellation what sas cancellation which was just if you're gonna upon the point of cancellation at what price can you reduce the price down to and still make it make sense um that was there was a lot going on in that one um and all these will be on youtube all these videos and streams and everything uh there you are uh so we did sas driving range last week or two weeks ago then last week we did inventory update restocking planner and that one was really good i felt like that that's something that needed to be done so i'm pretty happy about that one um and i actually still need to update some vendor sites with that new version i've got it up obviously ready on if through my site but um e financial models and eloquent i gotta gotta get it on there um so and those were my three streams so far um i'm not doing a full model right now i haven't had any ideas for one and i'm honestly it's it's really exhaustive it takes me about a full week like of time to get it done over you know not eight hours a day but um like it takes a while i think i just finished the driving range one uploading everywhere on uh on friday of last week and i started on tuesday so it's is time consuming no i started it sorry i started it the week before that so this one was actually two weeks uh now question is what am i doing today and i don't know i'm open to comment suggestions um you could put comments in the youtube videos too here um and it could be a customization to a past model and if i think it's worth it i'll do it or it could be a brand new model that i never thought of i'm really interested in building things with unique revenue logic and this you know fairly complex um generally what i like to do is take things that are kind of chaotic at first when you look at it and then try to structure into financial model so that kind of order out of chaos is where i've thrived oh let's see here let me uh check a couple things out let me post on twitter here live on twitch what's my twitch thing here what's my twitch thing here what's my twitch thing here oh shoot it's looping oh shoot okay so well that's good to know so it does show whatever screen i'm on i didn't know that i thought it just stayed in the excel document um all right well let's let's brainstorm here so do i have anything i've been itching to do is my question to myself i don't know um what could be cool or what interesting things one interesting thing i was trying to figure out is so i have excel uh through office 365 and i'm in the insider's program which anybody can do for free and i did this like last year to try and open up array formulas so let's say you've got a set of stuff here let's say dog cat bird and i yes no yes let's say i only want to see things from that range and then attribute one whatever some data on that i only want to filter and see anything that says yes so let's see if this works and i don't know you guys can try this on your own excel sheets um i don't know if they've included it in the main version of excel now or not if that's been updated i know i used it once on one client project a long time ago because usually if anything involves array formulas on data i usually just build it in google sheets but adding it in excel makes it a little bit better although i did know they made it excel made it really hard to filter on multiple criteria but i think it was possible um let's see here include if in this range equals yes okay that's cool so that works let's say put no here or let's say i reference a cell to do that instead of yes or no or i could just do f one i put that to no okay great now what about if i have yes bird can i filter on a second criteria i don't know let's try it i think you do times filter and now i want to check also if b is bird no that did not work uh okay do i have to put it in brackets or something nope let's take away the parentheses now let's take away all of this just do control shift what's the rate oh ctrl shift enter nope that didn't work so see i knew they made it i don't know why they should just do filter this range if a2 is this and then you should just be able to do well let's try it if a2 is this and then times because excel's weird doesn't just allow you to do multiple statements and if b2 to b22 equals bird can't change part of an array man see excel why why don't you just make it easy do filter arrange if a is f1 and b is whatever or you know just do as many as you want i know i think it's possible i can't remember how though i did it i was messing around just like a year ago hey look at that we've got two viewers what's up guys any suggestions on models or something you'd like to see me do here i'm chilling for about another 50 minutes or so excel's frozen what are you doing i don't know why i lose oh it it's reading cancel there we go so i don't know if we're going to figure this out but at least we know we can do the filter with one criteria for sure and it can be a reference you'd see whatever populate there now usually what this is used for is if you just a client's got a whole bunch of data and they want to see instead of going up to the top and filtering by like the drop down thing they want to see quickly put something or select a few things and then just see the data relative to that that's usually what that's for um so uh i'm trying to think of maybe a a simple tool that might be useful to build here this morning hmm done a lot of stuff with sales sales been tracking lately and this is once you get to a point where you've done all the main models and stuff it's about now finding good ideas that are still scalable meaning a lot of people probably need the tool so what to do what to do also people like uh math to be done for them when they have to make quick decisions and they just have a data set and they need to just click a couple things and then they don't have to spend time doing math and they might have to change things over time uh one interesting thing i was thinking of is car quotes like if your salesman is giving you quotes and you're trying to negotiate um and i don't know the last time i went and did this was a bit ago but they were like line iteming every single thing with the car and then if i try to negotiate they had to go back and change things and i bet if they had some if they had to excel up and they had the data in there it would be easier for them to do the numbers but the problem is i don't really know enough about all of the line items that are needed because i've never been a car salesman however now that i think about it i bet you a car salesman stats tracker could be interesting to build because you know a lot of salesmen they're like kind of on their own and they're trying to track their own stuff like i remember the one shop i was in uh the salesman would have the amount of cars that they've sold for the month they'd have that many like toy cars on their little window thing but i bet you [Music] there could be some cool stuff in excel that can be done specifically related to car sales like let's say you've got some data here let's say obviously uh well you know a car they they probably want to track leads too because i'm sure salesmen have pipelines you know what i think this is this is definitely going to be the next mod i don't know how long it's going to take to build but i know salesmen have leads and follow-ups and everything else and having a nice nice excel sheet where they can keep track of everything that's going on but it's probably helpful it'll help them report on their results as well as see what is coming down the pipe or or potential and then they can just add as as they have people that can add it and this is one of the great uses of excel because they're not one salesman is not going to have because usually the the problem with excel in google sheets is when you're trying to do data work with data that's really big for for mid to large size companies they just have too many you just there's too many rows to do anything with it and it's just too much but a single salesman that needs to track things for himself excuse me i got a sneeze hold on there this is perfect for that because you know maximum amount of rows let's say you know over 15 years is probably i don't know probably less than 50 000 um but also you can you can refresh every few years for like old outdated data or maybe every five years or you might just keep it forever for follow-ups let's just say you do you have 10 data and data rows a day right and you do five days a week or let's say six 365 that's oh that's kind of a lot 21 000 that's 10 leads a day six days a week oh sorry hold on i mess it up 52 weeks in a year so three thousand a year yeah you go out ten years that's only thirty thousand that's still manageable um the problem is when you get into the point where you've got companies with hundreds of thousands possibly millions of rows and usually unless you i mean because your excel will work if you've got uh a good enough hardware i mean it could do anything but you're gonna have to have a lot of space in ram to run calculations on hundreds of and millions of rows thousands of rows in tens of thousands of rows is okay though and this is perfect for for that use case um you know salesmen can sit there and the leads track everything so let's think about what data a sales guy is going to want to see and hold on a second whoops okay i'm just i'm still getting used to how all this works with twitch and stuff i don't know if that's visible okay let's think about what what is a car salesman is going to want to track so the date that the lead is entered let's freeze the top pro now i'm telling you right now i'm in no rush to get this done um fast like this probably might take me a month but i'm gonna work on a little bit here and there so let's say you just met with a client they're talking they want to buy a car or they're looking at new cars so let's do let's say today's date another thing i like about google sheets better than excel is you can format this to be a date and just double click the cell and it'll give you a calendar i don't think we can do that in excel wait wait hold on date but i don't want to see between it i just want to know is it a date can it be popped up in the calendar see it don't know so you have to manually put in the date now usually we'll put alternating colors and remove the grid lines but everyone's different with their preferences let's see so and actually i wonder what am i doing alternating colors am i not seeing this also what's the data bar add a color data bar to represent the value in a cell oh that's cool never use that before color scale if i lost that i do this all the time i don't understand i thought i'd just go to conditional formatting it's not that i don't want to make it a table let me see if i can minimize or or here we go so i could do this and still see that on the side yeah okay that's just sharing this so i can see that over there if you guys say anything all right so i want to make this into i want to remove grid lines and then make this into like a a white and gray alternating color thing and i don't know if i've lost it do you have to do a table to do that because i thought you could just do alternating colors maybe not okay so man i don't like that actually all right let's let's put the gridlines back in for now until i figure out what i'm doing so to date you want to know what what day the client came in uh what other information you probably want to know their name their phone number maybe a second phone uh an email contact um just make this bold it's a light grey top date name gym number email and then the the important information so you probably want to know because this is going to depend on how how your data gets all split up in slice didn't go through the pipeline well i don't know if we want to say car make and model probably i'm actually really excited about this because there's a lot of things that make sense to do this in excel so make and then what you'd have here is you know validation you have all the makes that exist all the models now i guess one thing we could do there's ways to make it so that like let's say this is a drop down i'm actually gonna do this so let's say you choose a make say there's you know whatever put like 50 but there could be any number let's put um you know ford i don't even know if i got these backwards is the make like ford and then like a fusion or whatever ford fusion uh whatever you can put all your all your makes in one row and all the models in the other and it will give you it will only select the models that are related to this the make that's selected here so if i go forward and then i go to this one shoot maybe not it can work for just a single situation but i don't know if it can work for a an array of like you pick to make and then only those models from that make show up in the next one is what i'm trying to do and it's an indirect formula i can't remember if you go indirect f1 like make the data validation be let's see custom equals indirect and that can just be like an array f2 to f i i don't know hmm let's see what if i just did that ford unlike ford what's another ford car um explorer [Music] uh let's just do test test one test test two so ideally you want to select this and you know what we want this to be unique though so and it's just for ease of use really could just honestly put a make model there and you just select you know data i might have to look this up to figure it out later but lists did i do down to 50. and they just had to yes as the person you just have to pick the right make and model now i know i'm going to look up if there's a way to do you know based on whatever you select here only pick the the models that are related so if i select forward it's only going to show me a drop down of these two if i select test it's only going to show me dropped onto these two also it's probably best to have this i give you a lot of different cars it's probably best to have your data set here so ford all those types because the model should be unique because there shouldn't be the same model name for two different makes i don't think so what we want here is you have all the makes and models like maybe it's export list or something you can put in here and then the make is gonna be just an index formula of whatever the model is so index this based on b2 matching this range now uh hold on that doesn't really do because i want a unique list unique a2 does it do a now or i mean unique a 2 to a ah you have to list though so this is another thing about excel it doesn't really like doing a2 to a you have to put in a number so whatever let's just put in a thousand unique a2a only that column whatever okay so that's cool the unique formula now works in excel it's nice um it's actually really interesting that to know this because i'm going to use this more now hold on hold on a second why is it doing all of these return unique rose false no all right excel what are you doing here what's what if we just type in unique ah perfect and it's going to show zero whatever i guess it reads blanks as zero that's another difference in excel in google sheet is blanks and zeros are kind of treated differently depending on how you set up the formula however this is cool so what i want to do i don't want any of this actually it doesn't matter we want this to be unique of h so let's say you've got all your data over there and h all your your makes this will list them uniquely here and then the model would be let's see i want it to be able to go so you can select this drop down so this is still the right thing and actually you probably don't need oh this is another good thing is you can just highlight the whole column so data validation and let's say we only really need to go down let's just do like 15. that can be adjusted as needed but i just want to be able to not have it too much at once so you pick the ford now i want this drop down to only give you options or remove this this okay so this so i want to click forward and then when i go to this model i only want to see options related to ford so fusion and explorer in this case so how do i do that is the question that's the question really how in the heck do we figure this out so and and i've set this up you can manually hard code over these formulas if you want but this is going to be set up so you can put in all makes and models here this is going to give you your unique list of makes and that's the going to be used for the validation but the trick is i only want to see based on whatever selected here i only want to see based on this list what is available for ford and then when i go to this next one you know i hate when this bar starts down there too i don't know why it does that sometimes i just start in the middle or down um here test now this is a different one i want to see when i select this drop down a different selection based on what's what's available for test i don't know how to do this so i think honestly the the way it would work is you have to have like a column a list of arrays for every single one of these and then this drop down would dynamically reference the column but that's not really scalable because you know i don't want to drag this over you know thousands of columns so try to figure out if there's an easier way to do this um but i guess we can come back to that um so you enter the make the model the person is interested in or you could you could do um a vehicle type let's say they don't know what maker model but they have a list of types so let's say you could do cars mid-size cars trucks whatever the heck you want to classify things as that makes sense for you is fine so put another data validation around that this one i mean i do like what 15 types you can change that later though to any amount so i can say maybe there's no for this one they haven't set a specific type but they they just you know they're interested in trucks or whatever uh so you got date day phone email model for now since i don't really know how to do this we're going to have to do the model is just going to be the same list or you know it's just going to reference this around you know 50 or so so you have to put in the four or the make in the model manually now if you do does this do no so in google sheets it will search for you if you just start typing the letter it'll search through the drop down excel doesn't do that natively i think you have to put in some different stuff to make that happen that's not super easy all right so make model vehicle type because then you could say if you want down the road to analyze this analyze your data as a salesman you can say well how many people do i have that are looking for cars how many people do i have they're looking for trucks or whatever you can really get a sense of what in the world you're looking for uh yeah this is gonna be a good model i can already tell this this will be useful for a lot of people i don't know how many car salesmen there are out there in the world but i'm guessing a lot it's not too niche really so it's pretty cool um all right so vehicle type make model what else do we want to do obviously we're going to put a status over here over here more to the left so this is going to be the status of the row and you only want one row for for each person uh for each potential lead i guess you could call them and we got a status here let's think about the statuses that they could potentially be just make this slight yellow um so we want to go from every single stage of like the process of the sales process so if i go to buy a car you know i'm gonna say well column at that point it's a a call i don't know if you need to track calls call could mean hold on a second call could mean they've called and they actually did not set an appointment yet they're just kind this basically the the broadest lead you can have somebody called write down some info about them get their name and number um and then they haven't followed up yet another let's put these over here call called appointment set because we're also going to want to have a filter tab here that says okay what are your appointments you have coming up for the week and you come in there monday morning and look at them all so we want to say so this another data point we need is let's first of all let's give this a date of validation range and we're going to have like probably i don't know not that many types of statuses let's do one two three four five six i guess sounds good so status could be you know they've called or let's say the appointment is set so if that's the case you're going to want to know the date of the appointment i'm trying to make this as easy as possible so this is not anything clear all let's say appointment set for you know 2 14 20 21 so then when we go into our filter later this this date will matter because you're going to look it'll pop up this whatever this row is all the data in this row like we'll have a tab here called you know upcoming appointments and what you can do is type you know appointments in next x days and let's say you want that to be in this case seven well you could change it i'm trying to think i mean you could so you could automate the timing of it so no matter what it shows you the current week only or you could have it show you the next seven days where the status is still still set to appointment but then see as you go through the week what a tuesday wednesday you'd have to change this to like four three two one days left um i think it's actually better to do appointments for the week upcoming appointments current week all right and it's always going to be you know so we use the today function and then based on today's date i'm pretty sure we can figure out the first and last day of the week let's see date let me think about this returns the number from 107 is by the day of the week of a date so we want to say weekday of this and we'll say i don't like we'll say 2 because i like 1 being a monday so 2 9 is the same as day 2. however i need to see when is monday so let's see serial number well let's just go over to our handy dandy google here and figure out monday of a given date ah i knew it was a weekday formula so that's the date good so no matter what the date or whatever says it is going to give you the monday of that um the monday and then the end date see we can put this over here today's that day you don't need this start and and actually we could put this into the formula the filter formula but i'm not entirely sure let's zoom in here i want to put all that like oh no because the filter can only have one criteria which is annoying with especially with this so we're gonna have to say we are gonna have to do helper columns here this check if all the different things like it's gonna have to check if it says appointment it's gonna have to check if the date is greater than the monday less than the tuesday and all all that has to be checked and if the final column adds up to all of those points are checked it'll return a one and then we just put the filter in here saying filter all of our data if whatever column is our check equals the total if every single thing has a one in it so uh we'll just put the date over here so here's the last day i'm assuming to get the last day you do seven let's see what's today's last date 14 yeah so 8 9 10 11 12 13 14 7 days so today um start week start weekend there you go so this tab is just going to be called upcoming appointments and it'll be cool some i'm sure we can do some visuals with this too um and i might just continue on here if i don't have any other um great ideas and i don't finish this which i probably definitely will not next tuesday i'll just continue on with this um but upcoming appointments based on your date ranges here let's make these like dotted there we go so you know today we start weekend so now it's cool i can and there's a lot of so i'll get into all the checks in how these automatically will populate later let's just freeze this i don't necessarily know if we need an entry date for when the lead was entered i guess yeah because you could say how many leads you're getting per day um you also are going to want a win rate like how many leads were won uh in a period how many were lost so let's see there's a whole there's so much stuff we can do here this is going to be really cool all right so where do we leave off on status type so we've got call we've got appointment set we've got uh we've got sorry about that uh called appointment set um so let's say the appointment set you have the appointment then you change it to i mean follow up so follow-up would mean they've come in they haven't signed anything they haven't purchased anything yet but they say well i'm gonna check some other places but um and but they haven't said no um so follow up let's say they do purchase right on the spot then you're gonna say sold and the other one you could say is lost and then so here we've got the appointment date another the other date that's going to be important that we have to separate is the date sold or lost that way you know in a given month how many things did you sell how many things how many leads were lost and they didn't buy anything definitively and when you mark something as lost um you could still have you're gonna still have it that data in here you could always follow back up but that doesn't mean you can't follow up but it just means that the lead is there they basically said i've bought a car from somebody else or um or you know what i'm not gonna buy a car in the end if they say that then the lead is lost but you still have their data you can you know call them but i don't know how all that marketing stuff the legalities of it but um so that's that and in a week you could say well i sold 10 i lost five what's my win rate well out of 15 completed leads and complete i'm just saying it's either sold or lost in a month you've won 10 out of 15. or yeah out of 15 you 110 lost five so what's that uh divided by five two thirds 66 percent win rate in that case um lost so called they've called might not have set an appointment yet but they haven't been lost yet either appointment set second status meaning obviously they're ready follow-up means they didn't buy but they they haven't said definitively no yet sold means well you just sold them a car lost means they're done okay see all these coming here sold lost and i'll just leave a couple spaces extra see we did six so there's five here now but you could have as many as you want here just matter updating the range and the data validation uh all right i find my date date sold lost so it's only so your upcoming appointments is only going to pull if this column is set to appointment set so as soon as that changes they'll leave the data will not populate in here um sold or lost you can track we can have a tab that tracks everything that's sold everything that's lost um we can have a tab that has everything marked as called that's probably going gonna be super useful just so you can see like where your hot leads at um and something could be marked as called that could be like a longer-term lead too like you're working on you're working on them they haven't come for an appointment but they're still looking you're trying to offer them some sales or deals or whatever these could be more long-term leads to uh so appointment date date lost so lost uh make model vehicle type um i don't think that it makes sense to put a potential sale amount i mean i guess you could put um like let's say somebody comes in they're looking at a truck for 35 000 and they they decline i guess you could put in their potential sale amount if you want and that way you can then also see how much you've potentially lost so whatever this will be uh dollars in this column 35 whatever it is oh potential sale and then you could also put um actual sale for what it was actually sold for um now if this if the potential sale is not relevant then just don't put anything in for that one these are all currency two so potential actual what else maybe if you want to track your commissions or money earned just for your own personal goals commission that's another one will be a currency and you could put some formulas in there to say well i get you know x percent of all sales but if it's complicated then you could just manually input the commission uh no we going for an hour i think this is pretty cool i'm gonna end the stream and i'll be back next tuesday i'll probably continue on this or if i have another thing to look at i'll start on something else and this will just be in my pipeline for stuff to build later but i think this is actually really good salesman uh a car car salesman tracker i don't know what i'll label it's something about selling cars and individual sales people can track their pipelines and everything all righty hope everyone has a great tuesday