6 hours 3 minutes
okay for a labs off model five. Now, you will be given one very big file
with quite a lot of sales data.
And you're supposed to analyze this data.
Let's go just to the file so you can see what you have to do. So they're quite a lot. There are 60 entries in this file. Let's zoom.
And just for a reminder, if you wantto un's oum, you press control and then you scroll, you're
wheel on your mouth if you have one.
I mean, if you're using mouse
and I highly recommend that you use mouse been working in Excel because working with anything else in Excel is pretty much a nightmare. So we have this file
and we have invoice numbers.
And then, uh, we have the date of these invoices. We have a customer name.
Then we'll have a list of product codes on that invoice
and total amount for each
item. So let's zoom it in
S o. You can see what's there,
and we want to analyze this data so that first we
I want to see who's our best customer. So to which customer are we selling the most?
Then we want to see which product code is being sold in largest quantity.
And then again, we want to see which product code is generating the highest revenue.
So we need to find three things. So we have this road data,
and in order to do that,
we're going to copy this data. So we're going to create
three sheets for each analysis,
and we're going to just select these Rose like this. We're going to press control, see?
And then let's see how just how many of them up to G.
Jen. We're going to select these, and we're going to press control. We
Why are we doing this like this? Because you see, when we select these combs and when we copy data
to the selected range it automatic sleep reformers the target sheet with everything that was in the previous one. So even the comrades are exactly the same.
So let's go to this one. Select this one as well,
and then go to this one.
So let a G. Jones and then press control week. So we have it copied three times,
and now we want to see first
who's our best customer
in order to do that. We're going to do you use first sort function,
so we're going to go here,
may click on data we go to sort
and then we want to sort by customer name
A to Z
and resource and sell values. And my our data has Heather. So this up there is a heather.
So it's like, OK,
and we get this.
The next step is to create subtitles
so what? We want to be one for every change in customer,
And to create
How much is this customer
has bought? Totally so logistically this
And we have these numbers now for Queen an industrial equipment
for Atticus, Total tools total and so on and so on.
So we're going to click on number two, which is going to remove everything except these totals and grand total.
And now we're going to go in this cone
and again do some sorting.
And this is very nice, because if you create
sub totals and then
leave just them on the screen and you go to sort
and we're going to sort by total
and we won't largest. Smallest
herewith have Schumann. Manufacturing
is the best customer we have. They have purchased total of 2,445,167 0.12.
And so we did the first task.
The second task would be
product is generating the best sales.
So we're going to repeat these steps in a similar way, but slightly different
values that we're going to sort. So let's go here to the product code. We go again to sort
and then we sort by product cold
and we want
smallest to largest.
And it's like here and now we have sort this according to product codes,
and we're going to go again into sub totals.
And now, for every change in product codes,
we're going to
some the totals.
So we want to see what is the total sales per product coat.
We click. Here
we go again on to be quick. We just get the totals
and then we go here
and we go again to sort sorry.
So we're going to sort by
and we want largest the smallest
and get that product cold. 13-258 for 8-9
has generated the largest
and then again, pretty much the same thing. But now we want to do it with quantity units sold. So we go again here to the product Cold re sort by product code. Well, it doesn't matter if it's most largest or the other way around,
then we go to sub totals.
But now we're changing so forever changing brother code,
we are going to sum the quantity
and we're going to click, okay?
And again, we're going to
And then again, we're going to do the sorting by
and we won't largest the smallest and think OK, and we see that this product has generated the largest quantity as well. So if you go to this one sheet, we see that it's the same product
codes, but it doesn't matter. So we have done all these things using basically sort, sub total and
again sort. And in this way we have reached
some very, very simple conclusions, important ones in a very simple matter from a sheet that had 60 rose. Imagine if we had 1000 trolls to do it manually, it would be almost impossible to do it in in some some
reasonably long time
So this was done in
a couple of minutes.
Uh, now, if you have seen this video, you have seen it. But I urge you to try to do this
with a little paws. Little break after a couple of hours and do it by yourself without looking at video.