Sign Banking Document Massachusetts Online

Mis report form

hello champs welcome back my name is Mohammad Sami and in this video we are going to have a look at using a spreadsheet application like Microsoft Excel to create business reports call them a mass or management information system reports if you will now I have collected some sample data that mimics a data file created from a transaction processing system like a sales software in a company most operations oft where maintain data in in the manner that is depicted in the samples here although the sample size is small the idea itself can be applied to much larger sets of data that you might encounter a natural system also note that these techniques can be utilized for data in isolation purposes as well so production support guys listen up now let's get on with it first of all we'll have a look at the transaction data that we are going to use as a sample today now it contains product keys sales document number the date in which the transaction was made the channel the quantity the unit the revenue earned against the sale of that quantity and the currency in which the transaction was made you can see that the product keys are maintained in a manner which is efficient for storing inside a database but do not generally make sense to common users so what we need to do is we need to get the text out of text for that product in order to understand it better and when you actually submit a report to a higher management you would want the products name ideally instead of the key being supplied in the reports so for that let's go ahead and massage our data a little bit to make it more presentable we are going to use a function inside a method cell called the vlookup for that purpose now just beside the product column let's create or insert another column called product description and before we start using the vlookup let's just have a look at what kind of products that our company sells for example the product ID pds zero one actually represents a note book called no speedy one so at the product key PDS zero two represents a note book called speedy two similarly zero four represents our USB adapter so on and so forth so let's just try and get this product descriptions into a raw data first so for that type in your formula or the function name that is we look up three look open appearances give it the first lookup value that is the key for your lookup which will be your product ID followed by a comma the table array from which that you are going to look up that is nothing but your product table there you go now you can see that PDS 0-7 is actually a camera connector let's just cross check if it is correct Vidia 0-7 is a camera connector great and you can go ahead and supply the same formula to all these cells go ahead and drag the formula for all the columns or rows rather for all the infos there you go now you can see that the formula has adapted to the key that it is supposed to fetch data for like you can see we look up of a 4 a 5 a 6 a 7 the array is constant in this case now that we have our product descriptive description let's just try and get you know let's say you want a report which gives you sales by month of the year so the date is not going to much help you much so what you can do is create your own master data for the date and use the same B lookup function to get the month and the year of that date you could you could also go ahead and try and use a complex formula by extracting the day of the month number from the middle and the year from the end but I just find it easier to do a vlookup so I've created a master table for all my dates where the date has a month and a year maintain against it let's just go ahead and do what we look up against this table let's just go ahead and get our month here using the build cup again so equals vlookup oh the key that is the calendar thing followed by your calendar the calendar master re again just ring with the numbers at the end of the column names come on you need the second column that is column B so that's number two and which is an exact match so there you go that's July 2007 for you I can just drag the formula again okay there you go so you have all of it now and there now that it looks nice and now that we have massage the data quite a bit we'll just go ahead and see how what kind of the report we can play it on top of it go to the report tab and we are gonna use something called as a pivot table in order to create a report so click on the pivot table option and will ask you for a table range so go back to your transaction data and select the table range the entire transaction data that you have and say okay there you go now you can just go ahead and drop your fro labels and let us say you want to look have a look at your venue sum of revenue if you want to change the function the aggregation function here you can just go ahead and select the value field you can use count average max main product stuff like that so I'm gonna use some in this example so there you go you can you have a nice report now where and you you can see how much revenue is generated out of sales from which product let us say you want to have a look at month wise sales in an year so you can now go ahead and drag in your month here there you go you have Argos sales at 70 mm or rather is it 7 lakhs 29,000 634 so that's your revenue for August and from the sale of these products you can even change certain design options to make it look more like a report for example if you want to show it in a tabular form you can do that and you can play around with that and explore you know all the different functions for yourself thank you very much