Collaborate on Sales Invoice Example for Product Quality with Ease Using airSlate SignNow

Sales invoice example for Product quality

hello everyone welcome back to my channel analytical guy today we will discuss about how to analyze sales data using excel in my previous video i discussed on how to create dashboards so i can show you the dashboard which we created in the last video if you want to refer to this video you can click on the top right screen and you'll be redirected to that video so coming back to our video so in this video i'll just show you the dummy data so in this data you can see there is zone so if if i filter this there is north one north to east south west then in second column it is region description you can see the state wise then city then distributor name then there is division in fifth column there are four divisions in short uh then there is a channel in my data there is modern trade and wholesale then invoice date uh this data is for september 2021 then the number of quantities sold what is the value and this sales column is the value in lags then what was the mrp of that quantity and this is the price point the range in which the mrp is situated all right so our agenda of this video is to answer these questions and apart from these questions we'll analyze a bit further so first question is which zone has the highest sales in india so if we have to understand if we have to get the higher sales so first you have to filter out so maybe for example in layman you will just say if i filter north one and just see the sales column the value which is coming out to be is 3049 right so this is north one so again i will go to any other zone maybe not too and i'll compare and whichever is the highest you will say that's the highest but this is not at all the right approach right so for that what we'll do is we'll just go to the pivot table first you go to insert then select pivot table then there is a dialog box and you will say okay now in this pivot table you will just select the zone and you'll just drag the sales sales in the value column so now here you can see zone wise sales so in east the sales was 32 599 similarly in all these things if i have to sort it out so i'll just go to more sort options i'll just do descending z2a from count of sales okay so now you can see the highest sales was in north one correct which is thirty nine thousand thirty nine so answer for this question is third and north one not one all right so you have answered one question second is which city contributes maximum for each division all right so each division with city contributes maximum all right all right so to answer this question we'll again go to pivot insert then pivot table select okay all right so now here we have to select all the cities that can go into the row so you have to see which city contributes maximum for each division right so now you have selected city now you have to select the sales as well so sales you can track to the value and you have to [Music] do this filtering through division right so you can add add a slicer of division so you right click to the division and add a slicer all right so if you have to understand the which city contributed maximum for let's say black coffee you just select on black coffee and you will see okay so i'll just sort it out in descending order through count of sale okay all right so if i selected black off you can see delhi is the highest contributor by uh with four three one three right if i select fox nut again delhi is there in green tea also delhi is the highest seller and roasted peanut again delhi so in all four divisions delhi is contributing the maximum so the answer to this question would be delhi now coming to the third question highest sales achieved on which date so again you go to the raw data insert pivot table and you click ok now the question read again highest sales achieved on which date so you have to add date column and sales so invoice date can go into the rows right and sales value can go into the values and again if you sort it out in descending order through count of sales you'll see which date contributed the maximum so 29th september contributed 14628 sales right so concept would be 29th 29th september all right so fourth question is which price range contributes maximum to the top line all right so again we'll just have another pivot table all right so this time i'll just select price point in rows and then sales in value all right so see top line is equivalent to revenue which is sales right so again i'll just sort it out in descending order of counter sale right so in this 180 plus price point is contributing seventy one thousand seven eighty seven out of one like twenty eight thousand thirty so this is almost what was in 56 it is contributing right so the answer for this question is 180 plus right so we have answered four questions our last question is top five distributors in terms of sales which are my top five distributors so for that again i'll just insert pivot table so you can understand the importance of pivot to analyze the data right so here i'll just get the distributor names and select sales into the value column and again i'll sort in descending order of count of sales right and from here i'll just do value filters in top 10 i'll select five right and then okay so you can see these are my top five distributors uh who are contributing to my top line so this is zero zero nine zero a a and then like these are five distributors right so these are my top five distributors all right so now we have answered all these five questions so uh there's one more thing which i want to mention in pivoting so for example if you go to this table you insert pivot table and you click ok so you get this kind of grid right so this grid is usually not recommended so you want to have another grid so where you do right click you go to pivot table options then you do display and then you pick classic pivot table layout so you want classic pivot table layout in general right this kind of layout so how can you make it as general right so if you go to the file go to options you select data and edit default layout and if you go to the pivot table options and in display you click classic pivot table layout okay and then you again click ok so this is now a default feature now so again if i go to a raw data insert pivot table now you can see the difference right so this is one of the hack which you can easily use for example now if you have to drag city in rows you can directly do that then if you have to insert the channels in the column field you can do that then if you want to add input quantities in drop value fields you can do that so it is quite easy to play with the data in this regard right all right so this is it in this video uh so if i go back to my raw data you can see you can analyze geographically you can analyze division wise you can analyze channel wise then you can analyze quantity wise and sales wise as well so if given the data you can analyze any kind of analysis all right so you have answered all these five questions now if you have any other question or you want to analyze any other data you can just comment below and i can come up with another video regarding that thank you for watching and please subscribe to this video so that you can get more such videos in future thank you