Simplify Your Workflow with Bill for Excel for Production

Bill for excel for Production

hey guys and welcome to the template on the bill  of materials now with this template you will be   able to easily create a bomb to keep track  of individual components that will be needed   to manufacture a product using this file you  will also be able to track the unit cost that   will allow you to quickly identify where  your major costs really are and find some   ways for optimization now once you've actually  created the bom you can also just print it out   in landscape mode and the whole template is  set up for this already generally speaking   this template is super easy to customize but  i want to walk you through the different steps   that you need to take in order to make sure  that this is fully suited for your needs   so let's first take a quick look at the different  sheets that we have in this template and we start   off with the so-called inventory master list  we go over here we just see a little table   giving us the final output so in my  pre-populated example we actually want to   manufacture a computer setup which is divided into  a monitor and an actual pc we got different skus   stock keeping units so these are just numerical  values associated with a specific product   there are different categories so if  you think about the manufacturing of   a computer you know you may have some screws  a stand a power supply and so on and so forth   then what's really important is the unit  of measurement now those could be pieces   those could be kilograms pounds or even tons  depending on what you actually want to produce   then for now plenty of placeholders in the name  column but of course you can customize them as   you see fit and then the last step it's very  important that you keep track of the unit cost   because in the next step you will want to  analyze where your major costs are actually   located and for this we can use the next  sheet called analysis of inventory master   now this is a pivot table categorizing  your final output with the category   the names and the total unit cost so you see  that if we were to manufacture this setup   given all of the information we currently  have in our inventory master list   our cost would be a total of eight hundred and  three point two dollars of course every single   time since this is a pivot table if you update  any information here on the inventory master list   you will need to manually refresh the inventory  master analysis you can easily do that by just   right clicking into the pivot table and  hitting refresh that's all you need to do   now moving on to the bill of materials i want  to show you what you can actually do with this   so again we want to have a computer setup as our  finished product the general rule of thumb here is   do not change any of the gray cells all you need  to change is really just this light green these   two columns and the one cell up here for the  finished product because what you'll be doing is   you simply enter the sku of one particular  product so let me just maybe delete these   you can also take these out you see  everything else disappears just automatically   once you enter in this sku the data will come  in automatically once you update the quantity   let's say we need like four of these ram blocks  we'll see that the unit cost divided by the   multiplied with the quantity will actually  give us the total cost and then in the end   all of this will somewhat update to our total  cost down here of 802. but notice that every   single time i change the quantity both this  total down here as well as my total cost   will change automatically so really all you  need to do in here is just bring in all the skus update the quantities now these are just  arbitrary values that i'm putting in here   and you'll be all set with your bill of materials  if you need more space let's say this is not   enough for you you can just easily add additional  rows simply go in here right click hit insert   once you bring in the new sku you see that those  formulas will also update automatically so really   maximum comfort for you maximum convenience and  if you decide to have less lines you can just take   them out just as easily and that's it with a bit  of materials there's just one last thing i want   to raise your attention on and that is you need  to make sure that the headers here exactly match   your headers over here in the inventory master  list why is that because the formulas in the gray   cells over here will be picking up those names  essentially it'll be looking for the category   in the inventory master list if if excel doesn't  find an exact match for this the formula would   work but it's super easy for you just bear  in mind that whenever you update any of these   headers over here let's say rather than saying  final output you want to call this main component let's go over here you see the formulas  are not working but if i also rename   this to main component my formulas are  back in so that's really the only thing   you need to pay attention to everything else  is fully automated so i hope that you will   be enjoying this template super convenient  to use and i wish you great success with it