Optimize your logistics sales process analysis with airSlate SignNow

How to create outlook signature

let's look at this logistic sales data and see that it can get kind of complicated even to answer a simple question like what's the average sales per day need to figure out what does that mean average sales per day so I'm going to start Excel and I have a the version 2010 and I'm going to get some data from text in this case and it's on the desktop and it's this Logistics sales data and I need to import it it's I'm not going to use fixed width I'm going to delimit it or I know it's delimited and it's delimited by tabs uh not by spaces if I do spaces you'll kind of see that things don't look quite right the area is this the Channel's butter no it's distribution Channel Area North material description butter so that's the right way uh to go I'll import it so the data looks like this and it's sorted uh relatively nicely so that I can look at it easily if I do end in down and to find the in key on this computer so in down arrow goes to the bottom and in to the right it goes over to the right and end up so it's a nice way to go through big chunks of data now the data is already sorted pretty nicely that is all the butter all the material is together and then by area and the days are sorted within round so it's round then day then area then butter so that's okay now these are records of sales so there were two sales in round one on day 10 of butter in the North Region and there were three sales of butter in the north region in round three and what I'm interested in is the average sales per day so what does that mean well it could mean that you include zeros if there were no sales do you include that in the average or do you say I want the average if there was a sale what's the average sale so already it can get kind of confusing now the way that I would do this uh and work with this data is to insert a pivot table and to do that it you follow those clicks that I just did and it'll automatically select the data as long as you have the cell clicked inside the contiguous chunk of data and what I'm interested in is the quantity sold and I'm interested in the quantity sold for each material in each area so here's the total quantity sold of butter in the North Region so one thing I could do is take the average of that and that gets me pretty close so the average meaning that there's 10 days and three rounds that's 30 i' take 752 divid by 30 and that would give the average uh per day for butter I can also add a simulation day and it puts it into my values because it's a number it's like no I want it as a label so there's the sales per day uh for butter and in each area and and then I could look at this and say well it looks like on maybe I'm looking for seasonality or something like that there's not as many sold on day three or something like that although you'd have you'd want to check that well one way you could check that is to put simulation days a column so now I've got the data displayed this way and I can kind of look down and and see consistently on on day three it looks like there's um this many sold of butter in all the regions and on day six maybe there's more variants so you can kind of check out that away so that's a pivot if I'm interested in the um average per day overall the rounds I can put the rounds in the columns and for example on day six I had three sales in round three and it and it adds them all together it sums them all together there's the sum and so I can work with this data to come come up with averages so I could take the average of 58 and 74 and that would be 66 so I could calculate that in Excel so that's the general idea now if I wanted to calculate the the average for these three days then i' take 58 and 74 and divide by three so there's there's different ideas of what an average can be now if I wanted to take an average I'll show you that Excel doesn't particularly like these pivot charts work when you work with these formulas so if I take the average of let's say those three cells and hit enter the average is 109 you would expect that there is 36 that all looks good there average is 66 well that's working out all right