Hello there, let's take another practice session on pivot table. I sincerely want that you have a very, very good grip on pivot table and its application and hence here goes another case study. What we have before us is closing stock information for a company. So, you'll notice on different dates, certain material type has been purchased from the respective plants, manufacturing plants, and they are still lying unsold as on 31st, March 2012, the quantity is 10 and the book value for that particular item is this 2768 Indian rupees. Now, I want to prepare certain ms reports based on this data and already I have prepared some sample output for your particular reference. I go to sample output.

Let me start talking about the first report optimizing the window. Now, what it says is within the report, the material type that has been manufactured and brought from the copper plan What is the quantity that still remains unsold and the corresponding book value and same for Alia, Jamshedpur these are all the cities of India eastern part of India. Now as I go to the next report it also shows me the aging that is from all the items that have come from the copper plant which your of manufacturing do they pertain to probably will able to give me some insights into maybe to a years of the six years were the years where this item was not manufactured properly. There were a lot of rejections and a lot of complaints and because of which it didn't get sold. So, that was second report third report where I may want to look at how much money has been blocked in the closing inventory in terms of money.

So, in this current report, what I get to see is that this is the aging for all the plants I have three items and majority of my money is blocked under the second item which is called rst almost 60% down there. Remaining 38% and er B happens to be just very minimal amount in terms of money. Let's make these three reports. I go to sheet two, I choose radar Ctrl A, I go to Insert, insert pivot table. Yeah. Once I press OK, it populates a pivot table in a new worksheet.

Now I'm going to activate one setting which have been doing so very often, right click pivot table options, display and classic pivot table layout. Okay, once having done so, let's see what options do I have first, I can give the plant in roofie. Perfect. Then I may want date purchase in the rule field. So either I place it just before this panel, you notice there's some difference how the border has been shown to you in green, right? So I'm placing it here.

Alternatively, I may put date purchase below the plant out here in the rows section. Now this is pure and broad deets. I want to group them up. per year or month, whichever I want at this moment I'm going to right click on any one date right click and say group and only choose your for the time being years Okay. So now you notice the breakup in terms of plant wise year wise breakup. Now if I want I can put the material type in the column fields, which will give me the entire series at of material type er B rst in time, I'm going to put the quantity in the main action area.

So, now what I get to see is land wise year wise what material are still lying unsold and this is very much similar to the report which we saw in the sample sheet whereby we had plant wise year wise material wise the quantity that is still lying unsold. Similarly, if you want to just to slice the data or the pivot table in such a manner that you do not want the year, you just want the material type, you could have put first through copper, that's plant and then material type. That's Go back and do this for you. So I'm going to take the date purchase out back to the pivot table field, what I have is a two dimensional data whereby horizontally the module type exists, and vertically the plan. So what I'm gonna do is material type from column I'm going to put that before in fact after row, I can try both options and see which report looks much more appropriate to my EMI support.

So this was about quantity. What about amount? I want to find out which material has captured the maximum funds and it has blocked that much amount of funds. So what I do I check off all the options away. I'll start from beginning I put the date purchase in the roofie that's already yours. I will put material type in the column fees.

Mm hmm. Then I put book value in the mean action area, which shows me under different years, what amount has been blocked under different categories of product. I'll take a moment To quickly format this using comma separator and reducing the decimal. Now, I want an option which allows me to calculate percentage of total grand total. So I'm going to choose the cell, which I'm going to mark is yellow. There, we'll double click and from the panel have value field settings, we will go to show value as from the, from the drop down, we will choose percentage of grand total.

Let's see what's going to happen. Okay. So yes, it tells me if the total book value of all the stock put together is hundred percent, what is the break up in terms of the different material types, so 1.7 to 60 and 37.78%. If you're getting different answers while you're practicing, please note that you may have included some of quantity, please know it's some of book value right now. In fact, I just go one step ahead. I will double click on the yellow cell and try to see what happens if I change the setting to percentage Through total.

So now it tells me if every year, whatever amount of goods, the worth of goods that remain unsold. What are the breakup, for example, in 2008, the goods that were manufactured and brought in 2008, out of that hundred percent of them belong to time. So again, depends on what question you're trying to answer, especially from the top management. And that is how you're going to slice and dice the data based on pivot table. And to end this all, we are going to put the plant in the report filter, and we're going to go to pivot table options. Further, on the left hand side extreme left hand side, you have the drop down of options.

I'm going to choose Show report filter pages. So yes, I know you would have guessed it by now that what is going to do, right click on it, and I press OK. In a second time, I get three sheets, one for the upper one for heavier one, four jumpship. Good. So this was a quick practice session on pivot table.