Effortlessly Manage Your Excel Bill Sheet for Production

Excel bill sheet for Production

[Music] hello everyone welcome to excel highway your one-stop shop for all your excel needs today i want to share with you a simple excel file that you can use to explode your bill of material or bom bom this could be very useful for people working in supply chain um managing production inventory working as a buyer this could be helpful for you so it's very simple there's a sheet called boom bom billow material the column name is where you fro column a sorry is where you define the product product name here i have product a b and c and columns b through how however you want is where you define the components of the product one two three and of course in your business you'll have actual uh words and actual products like uh table it's built from four pieces of legs and one piece that's the main main plaque so this is where you define for each product a for example i need ten units of component one five of a component one is component five and five of component a and so on and so on and i have two sheets here one is a single bomb explosion where you select a product product abc you define the quantity and it's going to tell you how much do you need and you'll notice that once i change the product the components change so if product c for example you need one of this four of this three and three if i'm just going to add one as the quantity you should see exactly the same if i want 10 units it's going to multiply everything by 10 and so on and how does this sheet work where there's basically a few hidden columns over here this will give us all of the components so i'm using the offset function so using offset referencing here and using the row number to pull the component number so in this case i'm using row -4 because it starts with row number five and this goes you know there's 13 but of course if you have more columns you can just drag the formula so this is going to pull me the component name now the component quantity is simply using index match so if you're not familiar with index match index match is a combination of vlookup and age lookup basically i am looking for d2 the match here this will give me the row which row i am and match a5 the component so basically it's looking at this finding the row and finding the column and giving me that combination in this case component number two for product c is number one if i will change this now to five you will see 5 and if i change it to product b it will go back to 1. so this is the index match and i'm adding an if error equals 0 because i'm using this later and it's easier to do that way so that's the second function the third function which is the easiest is simply uh looking at whether or not there's any quantity and if there is i'm adding an index so you see i put a 1 over here then 2 then 3 then 4 because there's nothing left then i'm going to use that index to show me the information so i'm looking at i'm using index match this time only once not two matches so i'm looking for the value all right which matches um again i'm using row so this will give me in this case five minus four give me one so i'm looking for the number one over here once i find it i'm looking for the match in num in column a so essentially i'm going to pull the numbers one two three and four if i have a product with less components in this case three so i'm going to find one two and three and sequentially the if error leaving it blank will leave the blanks and now i'm doing the same thing just with the component and multiplying it by this number so three very simple formulas gives you a lot of ability to do this dynamically so this is in the case you want to just understand one product what is your quantity now this sheet gives you the ability to have multiple products exploded so i have all three products with all three quantity and i can see the overall quantity of the components so if i take down take them out you will see it'll go back to zero and i can of course change that i can have more than one line for the same product maybe if you want to add a customer here or something like that or a lead time or required delivery date so right now this is built for only nine products but of course you can drag this and once you learn the formulas you can do that for as many as you want so this is the end result how does this actually happen so again it's hidden sheets but it's actually very simple so what i'm looking for is for each i'm basically using the same trick with the index match but i'm just doing it for all of the products listed over here so basically i have i'm looking for the index match for product a and component number one and i'm multiplying it by 60. so it's essentially the same formula as i had here only in this case i'm doing it for all the items so i'm just dragging it and each time i'm looking for a new row and if there's nothing there it's just going to give me zero this formula gives me a sum and now i'm just going to use an age lookup to pull that information that's it that's all the magic over here and the component name you can find in different methods here i'm just gonna use the first one to equal whatever i have over here which is coming from the bone and the others i'm just using again some sort of sophisticated index match each time i'm looking for the value above and adding another index but you can do it in so many ways it doesn't really matter so this is it here's where you define the boom here is where you explode one product here is where you explode more i hope you enjoyed the video and you learned something from here if you did leave a comment share this subscribe and uh hope to see you next time take care