Collaborate on Billing Excel Sheet for Quality Assurance with Ease Using airSlate SignNow

Billing excel sheet for Quality Assurance

hello everyone welcome back to BP interview tutorials and uh today we came up with an interesting topic or else I can say interesting concept that you guys can use in your daily work and uh this is actually a report which we will be preparing today and the team leads quality uh trainers everyone can use it even managers and everyone can use it and this is just a basic so before moving to this concept so just guys I want to tell you everyone please share subscribe the channel so that you guys can get the notification click on Bell icon also and also please uh check out our playlist you will find multiple videos on uh Six Sigma certification quality analy report dashboard 7 QC tools and uh Team lead trainers interviews many more are there so please do watch and don't miss to subscribe the channel so before starting uh I'll just go with a few few things here so for an example you're working as a QA TL or trainer okay but obviously you will be having a team aligned to you okay a team is align to you in one team you may have 15 to 20 operators or else associate or more than that right now uh quality will do the audits okay QA will do audits or else trainers can also do the audits if it is a new hired batch and uh we will do the audits on the basis of that we will gauge the performance now how the performance is gazed so there will be a audit for an example there is an individual call ID there will be a certain parameters that will be get marked down there correct okay and for example there are 12 to 13 parameters and those will be marked down if you find there is no any errors we will considered that call as a good call correct or else if there is an any error or if there is a single markdown also that call is a defective call correct different different process have their are different different names some people may call it as a defective call or else call failed and some people may give some Des sets there some people some QA or trainers will give NPS or uh the the ratings will be given there on the basis of that have you ever thought one thing that is do I need to share a weekly performance report report to my team to my align team a weekly performance report so what this will give you a benefit now for an example there are two Associates who are getting repeated errors okay you guys are sharing them the feedbacks and all everything is going very smoothly at the end the people are getting know about their scores by end of the month either a team lead will show them the score with the help of an equality dashboard but if a quality or trainer would have shared a weekly performance report that would be a best thing for the associate in this particular report they will be getting multiple information like overall scores they will get uh their date wise performance okay they will also get weekly performance how the scores are going weekly they will get some Paro chart to know on which parameter they need to focus okay parameter wise performance okay and uh they would also have uh get some information about the uh insights like on which parameter they need to focus future plan of action so everything uh we will be mentioning here in one performance report and this performance report would have been shared by a quality trainer team lead on weekly basis for an example in month we may consider there are four weeks okay this report will be shared for all the week fours and it would be shared on the first week first day of the week and after sharing this report while having a feedback session the quality trainer or team lead need to have have a discussion with the associate that where the performance is trending and on which parameter they need to work and on which parameter the entire team need to work and uh uh who had done really well job the who who are there the people who are getting repeated errors everything will get gauged in this one report now if you prepare this one particular report and if you're connecting with your team it will look like you guys are doing some analysis rather than just doing audits giving feedback and leaving the office so this is an add-on activity you guys can do and which will also uh give you a good Insight at the end you can gauge your team performance you can prepare a Rao in front of your team a connection in in front of your team and at the end your manager will also get impressed because if your manager is coming and asking you what you have done for the entire month you can show this performance report by end of the month of all the four emails the V the analysis what you have done is it interesting correct so we will learn this uh report today how to do this and we will automate this report so for first week you have just added the data you prepared the report second week you just need to add the data you need to refresh the pavot table the report will get uh the report will get prepared everything will get sorted here I will also tell you how you need to paste that in the email uh with the proper insights the plan of action what you are following whom you need to keep on emails and everything I will guide you so I guess this sounds very much interesting to learn because I have seen that many quality people are there who are just doing the audits they're just giving the feedback closing their emails and they leaving the office there is no any addon activity done by them so you guys can do this and impress your boss and also gauge your team performance and U get a good score by the end of the month so let's start this topic today I'll close my uh this particular sheet this is just okay so considering this data now this is just a rough data guys uh don't consider this is related to any company or all just just a rough data guys okay here uh every process have there different different columns uh some people may also have this uh uh employee ID okay some people also have a location like either they are working for US Canada multiple countries are there and they may also have location in India so multiple columns will be that it's up to you how your data is actually looking out but at the end there would be few things that are common for all the process that is parameters right so I just randomly added 12 parameters here okay 12 parameters here and if I'm doing this particular audit okay I'm just removing this defect count I will add it again here uh post uh getting all the details okay so I'm just removing this uh uh defect count here okay and I'll tell you how I have added this done so so considering this particular audit for an example I'm doing audit for sur I got this call ID and I I have identified that uh the call opening was not done good so I'm just giving a cross mark here I'm doing a markdown basically and uh the whole procedure he exed the whole limit and tagging not done properly notes not added properly I give I have given this particular markdown now considering this particular audit I would have few questions to you my first question for this particular call can you say the the call is defective yes because it has at least one error more than error which means yes definitely it's a defective so it has at least one error which means the call is already a defective call which means there is an error if there is zero error the that is a good call got it consider this point in your mind now I will also calculate for this particular call how many markdown I have received one 2 3 4 there are four markdowns I have received so what I'll do I'll just use one formula here this is the activity you guys need to do one time okay that also when you are preparing report first time if you're continuing this for the entire year no need to do all this work again and again just you need to paste your data refresh the P table everything will everything every work will get done now I want to just calculate first how many defects I have received which means uh defect count the formula is very easy equal to count a and I want to count uh just till uh from greeting to uh correct transfer from F to Q okay and done so automatically it will give me a four this is a simple formula that is Count a you can also watch my previous video you will get to know what is the use of count a now I want to identify either my call is defective or not for that I will use if formula so what I'll do equal to if now my formula is very easy what if the defect count which means this R if my defect count is equal to zero I I will get zero otherwise I will get one which means if the defect count is zero at that time only I will get this as a zero otherwise I will get one here I'm considering zero means it's a good call one means it is a deflective call effect so same what I'll do I'll just copy paste this formula down so see here I did not got any mark down that is the reason it's showing me the defect count is zero if you copy paste the same formula down it will give me a zero here reason which means it's a good call okay this is a good call right and here there are four defects there is a markdown in call opening or there's a markdown in uh call hold there is a markdown in tagging and also there is a markdown in customer notes so I'm just mening my comments here so just I'm giving you an example here okay guys so okay so in this way so this means it's a good call which means there are zero defect and is is it a defective call no it's not that is the reason I'm giving this as a zero zero and one which means one means yes it is a defective call zero means it's not a defective call got it so I'm just copy pasting these both the formula to the end okay there are 875 audits done for this particular team okay adding one more Point here you can prepare this particular report for individual Team guys okay don't mix it with for two team leads but because it will not give you a proper insights here okay so if you are aligned to two teams prepare to different two different reports for them okay now this is done now I will add the defect count defective call also done how to do that we have done this so on the basis of this in this treatment we have 31 defects you guys can see here some so my 31 calls got a defects here out of 874 and how many markdowns are there approx 35 markdowns are there I'm just giving you an example here I'm just giving few more markdowns guys uh so that uh the data will look somehow good so see here if I do markdowns randomly I'm doing here okay you guys can do on the basis of your actual data because I'm just giving a random markdowns here okay so so don't don't uh get panic here that why I'm doing this this is just to show you thatth how this error goes and everything okay I I have done this now there are 47 shipments or else 39 shipment got impacted and uh out of 874 this is just an example here you guys would be having a comment section and the quality person who have done this particular audit okay uh this is something which is nowhere required I will remove it done okay so I just want to add one more column here see guys I'll tell you the reason behind that I want to know what is the count of the audits for that I will be adding one additional column that is audit count okay so you know how to add draw additional row and everything it's very much easy just you need to do uh click on a right click you need to click here and you need to get um you want in insert a copy cell okay you just want a few Sals here okay then it will just delete it okay the audit uh account got copied that is the reason we are getting this additional information I'm just deleting it okay uh and post doing that I will be getting my required answer okay so I'm just adding a a it says a audit count so why I'm doing here is I just want to calculate how many audits I have done here so I am just giving one to the end okay which means it will give me in P table it will help me to get the exact audit count numbers okay so that is the reason I have done this okay you can do this every time because it will help you uh in your reports in a different way now everything is done now I will start preparing my quality Dash uh uh sorry performance report so what I'll do I'll do control a insert and I will add a pavot table here okay and it will say okay done now first I'll rename the sheet I'll give this name as a raw I'll give this sheet name as a p table so see guys everything will be done with the help of P table now I hope you guys have checked my previous video about the calculate field and everything so that will surely help you now guys while preparing the reports okay uh one more thing you guys need to cross check that is a duplicates here guys always check the duplicates because if there are duplicates the uh things would have done wrong that means or else the two audits done for the same time which will not give you the exact data to check the duplicates it is very easy click on uh this entire row conditional formatting highlight cell rules with duplicates do this and just give a filter here right click and you will check filter by color if there are duplicates yes it will give in a pink color in this way you need to check in my data there is a duplicate because I'm just giving you a just a sample data here that is the reason but in your case there should be no duplicates okay now again I'll go to the P table in P table what I what I have want first I will do one thing here that is I will prepare one more sub sheet I will rename it as a weekly performance report performance report wherein I want data in this particular way I want uh Team lead uh wise performance I want this particular data now I even want ass uh associate wise performance so team lead I remove it like associate associate wise performance I want this two type of data first okay I'm just giving uh this is in some border okay B so I'm just giving some uh uh highlights here so what I'll do I'll go to P table now I want team lead wise data so first I will pick team leader in row the John is the team leader now what I'll do I want a defect count like how many defects are there I want my defective call correct I I now what I want I want the audit count also I told you write audit count here how you can calculate with the help of this so I'll go with audit count in the starting so we got this three information now what I'll do I'll just uh prepare a same table here also okay first I'll give a team lead name team lead then I want uh audit count then I want defect count then I want defect call then I want uh it's fair audit Fair audit means how many uh calls got passed okay so fair audit percentage I I do also want the accuracy accuracy of the calls okay so you may have a different terms you can use it on the basis of that you calculate okay now I want team lead I'll just do equal and I will pick John from here just I'll click on John okay now from audit count also I'll do equal I will pick audit count here okay I'll do equal I will select defect count here if I'll do equal I will select defect calls here now I want to calculate Fair AIT Fair audit is very easy uh so one minus one minus which will give me answer in a positive approach so how many defect calls are there divided by overall a this is very easy for an example there are 100 students 30 students fails which means 70% is our Fair audit in the same way I have calculated this accuracy formula is also very much easy 1 minus I'll collect the defect count how many defects are there I'll do divide then uh I'll again start bracket how many parameters are there there are 12 parameters you may have 15 20 it's up to you multiply with the total audit count now this is the formula of fair audit okay so very easy again I'll tell you fair audit it's a very easy formula like if there are 100 students 70 are pass 30 are fail which means 70% is my fair audit accuracy which is equal to 1 minus uh defect count divided by bracket how many total parameters are there in our audit sheet into total audit count and bracket close two times I will select this both the data I will give percentage here and I will just increase the space here in this way you'll get my answer I'm just giving a border all border okay so I told you right one time only you need to do this activity next time if you add more data this will get changed automatically my this information will also get changed and the calculation will also get changed automatically will get team leader wise data so copy this and paste it here okay so here it will if you direct paste it now it will copy paste it will take formulas so that is the reason what you will do just bring this one step down and uh if you go to here copy this uh Title First okay and uh do do past special of this course Okay the reason the formula will not get copied how you will do past special right click past special I just want only values from that uh and so uh what I'll do I'll just uh uh do pay special here I will just do alignment select all bring it to Center give the Border that's it and again uh give percentage here home this is the percentage sign and we just want two Z after that so now this team lead wise detail every time do contr s guys so that you can save your data okay now done now what I want next I just want is uh associate wise data again I'll go to P table I will just copy this entire P table okay entire P table I will paste two three step down give a good space between them otherwise you will get a issue of P table table overlap now what is this P table overlap check out the playlist guys the tricks videos you will get all the information there how to avoid the overlapping of the P table you will get that information okay now I want associate wise details how I will get it's very easy remove the team lead name and bring the employee name here yes that's it you got your answer so uh this is associate wise audit count associate wise defect count and Associate wise defective call okay you may you got this data now what I'll do for this associate wise I will not do uh the calculation the how we have done for the team lead wise because it uh for the associate wise there are multiple entries so simple what I'll do I'll just copy paste don't pick the title first if you pick title first here it will again come in a p table format only so first just pick the associate wise details till the grand total and paste it here same now just uh take the uh this uh uh title or else you can also pick title from the first uh the team lead device just remove team L to the employee name employee name done CR a give border okay align them to Center done everything done now what I'll do I'll just copy this Fair audit formula and I will paste it here for all this row done automatically my calculation will get done okay for individual SOS same Fair accuracy also okay this will work now I'll just give one space down I'll delete and I'll give one space down here and I'll just do sort from high to low if you don't bring one space down now what happened I'll tell you if you do sorting here now it will sort grand total also see here this grand total also got sorted we do not want grand total to get sorted right that is the reason I'll just bring one step down for few time uh for for for a while okay and I'll do now largest to smallest okay so sorting is very easy just you need to select the required data don't select from the key just select the data because we have only these entries that need to be sorted go to home sort do largest to smallest okay if you have still doubt in sorting you can watch out my playlist guys you will get a video regarding to the Sorting also now everything done I will just give a color here okay so the things will look similar okay and I'll give border control B now done the the data is also showing perfectly so cavia uh got the zero error in 28 audits his fair audit is 100% even accuracy also so this is also sorted now agent wise data is completed copy this go to your weekly performance report and paste it here now this is also done now what I want next so I want now I'll just paste this here so I want now weekly performance so guys consider here one thing so before that we will go with date wise performance first okay date wise performance okay I want date wise performance how I can do date wise again it's very easy just copy paste again this raw table from here to give few space down okay give some few otherwise again you will get this uh uh pay table overlap issue it's very easy remove team lead and paste the date here you will get the date wise performance it's easy same don't pick the title just pick the entire data and from if you want the title you just go up and pick the title from here remove from employee name it to date it's done now you got the date wise performance also it's it's very easy guys uh to do this I'm just giving borders and everything same I want Fair audit and accuracy I'll just copy paste these two CS because this is formulated Sals you guys can see here so the same formula will also work on this particular celles also see guys and to verify this see the last one 50 95.54% now here we will not do sorting because we want date wise details so I'll just give a border uh sorry color and the Border here same I'll give a color and the Border here same I'll do for this also and same I'll do for this also so now see the date wise data also got generated you can also discuss with the associate date wise performance like uh on Day first we have got we are at 97 percentage Fair audit then later on our score got dropped here also our score got dropped so in this way you guys can do discuss so for 6th May there is 95 audits we got 14 defects which means on 6th May there was something happened due to that a score got dropped our weekly report is also prepared now if you want to convert this in a form of graph you can do it for that I have already prepared a video guys you guys can cross check okay uh you will get the complete information how to prepare a date wise graph guys okay you will get that information very easily from my uh the playlist okay do check that now date wise graph is also done now I want weekly graph for weekly graph I will again I'm doing controls okay to save the details for weekly we want a week here are you able to see this guys there is no weak added here so what I'll do at the end I will just add one move row that is name of week now this data is for the May month now I'll go to May month data okay may month data see from first I just consider this first also in the uh week one reason it is just a single day that is the reason so from 1 to 8 I am considering week one because it again it it it everything comes under one week from 9th to 15th I will consider week two so this data is still 1 to8 okay the data is still 1 to8 only if you want to check you can check it here uh bring a filter here you will get to know see the data is still to8 okay so I'm just keeping everything under week one so I'm just giving here as a week one and coming to the last pasting for all the entries in this way I have just done this for the week one now just tell me one thing guys for week one you want a uh uh table here okay how you will do so again I'm copy pasting this P don't copy paste this okay it is not a p table the P table is here okay copy paste this entry and paste it here okay one thing this particular data only include data till the uh QA name doesn't have a data till week so that is the reason you will not able to find weak here correct to do that how you will do be on uh the P table P table analysis go to P table and change data source reselect all the data okay from starting audit count to last entry control shift down control shift left arrow to the weak okay don't select any black entries guys it will give you a wrong data and okay so we have done this now I will check here either week is visible yes now what I do I'll just remove the audit date and I'll keep week here so we got a week one data also so same what I'll do I'll just copy paste this entries here and I'll take these entries from here titles and I'll just mention it as a week okay I'll just do mention as a week audit count everything is done I'll copy paste Fair audit and accuracy here and here so it's done it's looking uh pretty good and I'm just copy pasting the and I'm giving the border and doing the alignment same Vin is also done done and I'm just pting my we wise data here so guys are you getting uh the details here we are doing this activity one time later on we just need to add the data refresh the pavot table and just copy paste our required data here your work will get done so now we have already got the details associate team lead wise done associate wise done uh now uh date wise done and weekly done for weekly and date you can prepare a graph guys I will show you how to prepare that graph while watching to our video video that is already available in our playlist now last we want to prepare a par and defect wise and I will also show you how to how you can paste the same in your email so that uh this data will look good so before moving to that I would also show you one thing here that is how you how you can prepare a graph a date wise and a weekly just go to our YouTube channel guys and don't miss to click on subscribe guys click on subscribe you will get a subscribe option here please click on subscribe also click on Bell icon guys so that you can get a notification go to our playlist so there are multiple videos you guys can find videos related to operation kpi escalation dashboard attendance tracker C Sigma certification Excel tricks uh CA which is related to RCA seven quality tools and quality analyst St so here we are looking for the date wise and uh the weekly uh performance report guys so you will find that video here in a playlist uh of uh the Excel tricks and I'll show you where that video actually here is um okay here we go here you guys can find the date wise uh how to design a date wise performance report okay you guys can find out here date wise performance report in a BPO company so this is this is very interesting guys uh you just watch this particular video okay don't miss it and don't show this uh data only in front of our table guys it's it is looking good but preparing uh Report with the help of uh the uh graphs it will it would look it will look more more cool guys okay so so we will we will just watch this video guys I will just add the link of this video uh in my in my description guys you guys can check it so see guys this is all about about the uh video that will show how to prepare a date wise graph okay so it's very easy you guys just watch it guys it's a 15 Minutes video do watch because it will give you a clear understanding how to do this and at the end this graph will look amazing uh amazing guys see in this way this graph will look the same you can prepare for the week wise also okay just the date will get removed and you need to just mention a gra a week there so in this way you can do this I will give you this link guys in my description so that you guys will find a easy how to do this and we do also have a videos related to guys uh I I already told you count a uh sorting P table issue as I told you guys these are very important video please do watch before going to the reports okay so this is all about uh the details of the graphs which you need to prepare and that is a homework for you guys okay please do this and uh share your views on it like either are you able to do that or not you face any issues please let me know I will definitely help you out okay now weekly done now what I'll do I'll just want a uh parameter wise defect okay parameter wise defect so how many parameter got marked down I just want to see that now so for this particular thing I have already prepared video on it that is parameter wise drill down you guys can cross check that playlist you will get information there that is very easy so how many parameters are there so there are 12 parameters I'll just copy this 12 parameters I have already shown this in my another video also you guys can cross check uh bring out a new sheet do right click pay special values and transpose transpose it will just give you a data in uh like uh from uh in this way you will get this particular data okay from like uh a row you will get a data and I'll just give a name as a parameter then I'll give a border here and uh a title okay done and I'll also give border and title now what I want I just want defect count or else markdowns right like how many markdowns are there uh this format printer you guys can use to get a same color and border to your data also now what I want I want just how many markdowns are there for Greetings how many markdowns are there for call openings and all there is an another video for it guys do watch that you will get a complete information but here what I'll do I'll just come to down to the last row I'll do equal to count a bracket open I will select the entire row till uh 2G right G2 okay selected there are no any errors for greting in this I'll just copy paste here so I got the markdowns here 47 is the count I want to verify it how I can do this see here 47 is the count it's verified yes now I'll copy this I'll just do paste special here itself reason the formula will get removed but paste special what you will do copy uh you'll do copy you will go to sheet five come here right click paste special values transpose done you got the details here okay now just uh give uh now I'll do just high to low select this entire data and do sorting largest to smallest copying this title to the last I'm giving as a grind total okay and I'll do some formula here so that I'll get to know that what are the total defect count I go to my weekly performance report I will paste this details here I want to change the color click on grand total use the format printer and you can do this here format printer actually uh will copy paste the same color and border to your respective column which you are actually selecting okay now this is also done you already got the parameter wise def count also with the help of this you can easily prepare a par chart for this also I will I have already prepared a video guys uh you can just watch it how to uh create a paror with the help of parameter it is very easy I will paste the link for the graph and everything do this and uh you will get your answers there okay guys so this is all about the performance report in the next video we will see guys uh how we can paste this detail in a email along with the graphs and everything okay guys uh so watch out this count a video guys you will understand how we can do parameter wise Paro and after that watching this video you will get to know how you can get uh the details about the parameter wise and after that watch out the video of chart guys so it will give you a clear understanding that how you how you can prepare a par chart okay let's move guys and uh let's prepare a mail body so what will be my subject it will be weekly performance report John team right John team I'll mention hi John uh pfb the uh weekly performance report right for May 2022 first will be team lead wise perform performance right I'll just give border to is I'll go to my uh table and uh I'll select this I will paste it here done next now I'll paste processor wise performance or associate wise performance agent wise performance and then I will be pasting defects right defect count defect count I will be pasting this so what I associate wise performance I'll just uh copy this associate wise performance it's done now I want defect count I'll paste this I'll go to my mail body and I'll just paste it here so this is okay grand total is coming 47 correct okay this is also done uh now what I'll do uh after this stream advice I will paste that Weekly graph which I have already prepared you guys also can prepare the same graph with the help of video I have already shown you guys uh use a playlist you will uh get the exact uh idea how to prepare this and just paste it here okay same if you have a weekly graph paste that graph also increase this size uh click on this graph you will get fit to the original and reduce some sort of size so that it would look good good okay now this is also done associate wise done defect count done okay now at last you will um uh just mention your Paro chart the chart which you will be preparing out okay so just add all that also and Below to that mention uh future plan of action future plan of action which you are going to implement like future plan of action in the sense if the scores are down you will be conducting dip check for repeated defaulters correct you will be also conducting a refresher session refresher sessions you will be also having few more activities with the team the plan of action which you are going to implement okay it's up to you what you want to do you will mentioning these points here reason the team lead will get to know that uh the QA is working uh regularly with the team and he is more focusing on the team score Improvement in this way guys you can prepare a weekly performance report and to be frank guys it look really good if you share this report so in two you will mention all the associate email IDs guys the associates who are here and uh just change it to team hi team okay and in CC you will keep your manager your team lead uh then you will keep trainers you will also keep the team lead uh your the team lead respective team lead manager operation team lead manager and you can also include your managers and uh the authorities to whom you are allowed to send the emails so you can do this and send this email and make sure you will attach the Excel sheet the on which you are actually working while attaching the sheet just hide it hide the P table even the sheet five everything just I just keep the raw data so that it look look professional while sending the report do controls save attach it and send it the email guys in this way you can prepare a weekly performance report guys and it is very easy just do it guys and get a good score and uh work on your team Improvement and the performance that's all guys from my side don't miss to subscribe and share the video guys share this channel Channel click on Bell icon and also just click on uh the thanks button guys if you are uh thinking that I'm helping you a lot in uh uh your job role please click on this thanks option also and support me with the super thanks