Effortlessly Create and Manage Your Bill for Excel for Engineering

Bill for excel for Engineering

hello and welcome to our YouTube channel channel career at 360 today I am doing a succession of my data modeling and data automation and in today's exercise we'll be dealing with how to do our automation of a bill of material now the problem is this that in many companies very regularly we have to do a costing of a new item or a project and what happens that we have a standard Willapa deal available with us but sometimes we have to download the updated purchase orders and whenever we download the purchase order list that contains sometimes thousands of purchase order which has been issued and last six months one year more than one year whatever it is and the problem is that for doing the costing of that bill of material or for that product we have to pick the last sometimes we have to pick the rate of from the last purchase order so that we can issue our updated price to our customers so that takes a lot of time but through this technique I show you that how we can in this seven step I've explained you how we can do the automation now before I go to these seven points over here let me show you my salt template over here okay so for example this is my but material codes or CSK use this is the descriptions of the CSK use unit of management the total quantity as per the badge the rates from the last purchase orders and their product cost and now what happens is that I've downloaded by Bill of my bill of material over here okay and then we have downloaded the purchase order over here and I will tell you step by step through those things now what happens over here I put our data validation as we all know what is the data validation so we what is this all my CS case you are here and in the moment I change you can see that there is a change over here and the Bill of Materials change system has picked the rates and everything and right now the cost for this badge is 73 thousand over here and now if I change it to rather item let's say I pick this 200 intent you can see this is change and that's 74 something has changed to this so that means this whole bill of material format is speaking the items for each SKU from the from the build up rutile and picking the the rates and we all know that one of the problems so this is about we want to you want to continuous for a building materials for the last purchase order this is the problem and the problem is that whenever we apply a vlookup formula on the purchase order file it will pick the first purchase order which is available and can pick the the first purchase order but now we want to pick the purchase order form from the last purchase order in the purchase order list and the first challenge over here is that to pick the csq because for example there are many common raw materials and some of the packing materials which are available for hundreds of items so how we do know that if they are let's say 100 products and if I change the code of the product my system will go and pick the right item or CSK you with the right quantity so why because let me show you this over here before I show you my badge so over here this is a paint company for example and we have almost CSG use and here we have four different sizes one is 1/2 litre of a paint one liter then one gallon and I think 5 gallons and every size is available in more than one hundred and eighty six colors and these are the colors and this is the quantity we produced per batch so if I take it down over here we have almost three hundred and forty-four different ESCA use the the finished goods which we produce now to handle this 344 we have a build of material and this is the file of mark below 40 L so initially this is my product code which is called SKU and then this is my C SKU which is the packaging material or the raw material their description a unit of measurement and the quantity used in for each for each size yeah and every color has its own metal cane label cells and ghasts all these things so if I go down here for these 300 and something of my products if I see my bill of material which every product has at least 20 items so this list goal to almost 7,200 lines and we have a complete detailed bill of material available for all our products and lastly and lastly we have the purchase order we have downloaded the purchase order starting from 2016 because some of the items we purchase for two years for three years so I want to bring the large date and we have almost more than 1,000 purchase order over here okay so now you have understood that we have the batch quantities we have the bill of material which is a standard quantities to produce the things and we have the purchase or to stop on to the template now to explain you everything what I'll do I have to delete this thing let me delete all these things so that we can start it from the from the sketch so first of all what I have done here I put a data validation and I put all these scores over here with from the batch one because the bad file contains the all 344 items or SKU and I bring these scores from here and their description from here as you can see from here so the moment I change the code the the description also get change okay so let's select the first one which is available here 101 so now everything is seen so what I will do first of all now the biggest problem that the challenge over here which I'll I can show you the first challenge is how to pick the right item of that specific product because again I tell you there are many common items here so I want to make sure that as a right item or CS key to be selected against the bill of material because that C SKU has a right quantity for that item okay because that C SKU which is a raw material or a packing is coming hundreds of times into my file so every time it comings it has different quantities as for the different sizes of my program so that code is appearing hundreds of time into my build of material so I want to pick the right code for the right product okay so what I want to do and secondly second in the second challenges that once I select the code I want all the materials of this item to be displayed over here so these are technique what is that technique which I have which I have done here what I have done is that Isis is equals to concatenate I said this code because 101 is a unique code and concatenate it with one okay and this is how I have produced a unique code against each item I can copy down over here so now when I copy this unique code has copied everywhere okay now what I have done is if you look into my Bill of material because every item has 21 items so what I have done is that this is the most you know time-consuming thing which I have done once for my file and that will get that every every for everything let's say 101 is my first item so now there's 21 series over here for 1 0 2 as you can see I put again 1 to 21 over here and front 1 2 3 I put 1 1 1 to 31 everywhere and even if you go to my last product over here it will also show you that from 1 to 20 months why because I want to combine this series with the its SKU code to bring it a unique thing and once I've done over here I have done the same thing over here that I've joined this I jumped with the cska over here and now the things become easy for me because this that's the only logic I can do so now I can you simply apply Renuka formula over here else to be lookup record start find me this code where in to buy Billa Patil file ok and then you start from here two three dollar sign comma 3 comma 0 with suppose I want exact match enter and now I have been done item for each things and I can copy it over here and I think please change it to see SQ because this is a a CSV you okay and this is also a CSV you description not the SKU description I'm sorry for that and then I can apply our another vlookup formula and what is that tree look of formula find again this code because this is a unique code there into the bomb file and one two three and fourth fourth is my description and I want to leave my dollar sign before I form our one two three four I think comma zero let it start again and now with the same color I picked the right item with the right quantity and you drop a sure but again is the same is equal to be Luca I could start find from this Unicode go into the bomb file go at the start two three four five dollar signs comma 5 comma zero and I've selected my things over here and now as far as the batch quantity is concerned again it is available over here into the batch quantity so I want to pick this column I and then I will multiply it with this 2,000 because that is for one quantity for each and we produce 2,000 quantities per batch so what I will do is equals to we look up a bracket start again find this unique code we're over here in Mark Baum file go on to the top two three four five six dollar sign comma 6 comma 0 and then I can enter it over here and then I can go over here into the formula and say and multiply it with this quantity and what I will do I will block the cells because I want to compete everywhere and now system will simply cooperate everywhere so now what I have done is let's see is take an example so now if I just change my code to 107 I can see that if it is a baby blue all the baby blue items are selected over here and we will double check once we put the X look up over here to see the quantities of each we read it verify it then so now if I come out to my first sheet where what we have done we have find out that the problem is to pick the last code then we have we have to pick the things so we I think we have done the fourth step we have done till 4th step where we have used a unique cell concatenate and every Luca to solve the first problem which is the which is number 2 so first problem which is number 2 has been solved and which say is that we have to select the right see SKU for each right ske now let's come on to our third problem this second problem we have to pick the purchase order rate of the last purchase order and now we will do this through a step number five and what is that I will go over here into my first time and I say is equals to X lookup brackets start X lookup this item which are purchased there so what is a lookup array as per X formula look go into a purchase order files and the the items are over here sorry over here in column E and I will say from first to last slick column e dollar sign comma and if we're done in return what is array array is this unit price so I will stick from here to here dollar sign and then I say comma comma minus 1 in my previous recordings of if you go into the excel file minus 1 means that start from the from the bottom otherwise the 0 is by default I don't have to do anything if I want to pick the first one so I can use a comma I can close it over here so closing it you here and enter means they picked from the from the first one but because I want to pick the last so I will put two commas and say -1 break it close and say enter and as you can see that system has selected the rate and I can operate this thing everywhere and now what what I have done is that there is around how many items there are three hundred and forty four different items in the batch with thousands of seven thousand rows of a bill of material with more than one thousand of the purchase orders and everything gets connected with my template and now if I change 107 to another code let's say I want to change it to let's say 250 and as we can see that Brown has changed everything is brown and the quantities have to change or let's see some of the so let's say I verify some of the things so let's say the code is are 500 109 so I will select this thing and say copy and go into map your file and we'll put our filter on on this item and say control V and Center and say oh so the last but Jaisal Noor asked for this item has a rate of three point four eight and not if I go over here for the item one on 501 or nine it says three point four eight and when this three point four it has changed let me highlight it for for you to easily look at it this when this three point four it has sheet it has changed two months ago because till February we were buying at three forty four and now in the month of March we have star via the price has increased and from three four - four it has goes to three 48 so now if it is walking four four one four one formula I we know we all know that it can work for every former but against for your for yourself will flick up and the code which is called a carbon black and the number let's copy this number of a carbon black from here and go into my purchase order file and select all and then go over here get one more time and copy that carbon black code and say okay and carbon black last number is four point two four and in our template it is four point two four and when this code has changed again a few months ago or four point two four I think yeah let us change lastly in 2018 since 2018 we are buying it at the same rate so whatever is this a system has changed for example just for an example if I change it to five year just to show you the proof so if I put a five manually over here and I go to my template you can see whatever is the last date system has picked the lasted from from our five okay so now if I go into my into my info basically what we have done previously we used to download the purchase order file and then spend some minutes or hours depending on the volume of it to find your last purchase order to fill in our templates but now what we have done is we have used a simple different techniques to bring in this and now I know that next time what I have to do I only have to upload my purchase order files over here because normally builds or files never change or they normally change once in a year so I only have to update my purchase order files and rest I can do it in just few minutes so we what we have done is we have saved almost 80 to 90% of our processing time through this automation techniques and we have bring in more accuracy through here and next time what you have to do only have to up lit up update the purchase order and the things will be running for you and you can apply this concept of a vlookup with concurrent concatenate and and next lookup and many of other reports okay and I hope that you have learned something good today and you may apply some some of these things into into your into your company if you have liked these things I will request you to give up to subscribe my channel carrier at 360 give me a thumbs up and share it with your friends and families so that they can also get benefit from these videos stay tuned because I'll be bringing in some more similar stuff for you to to learn all these hand through our this hands-on practical training by [Music]