For those who don't know, the Bill Of Material is a list of all the components required to build a Product.
Each product type will have its own list of components, although some of these components may be used for multiple products. It does not matter where the components are saved in BUZ; as long as the Inventory Code is listed in the Bill Of Material sheet, BUZ will deduct the stock as required.
Bill Of Material Component vs a Sales Order Component
Previously, the Inventory items added were for the Sales Questions and Answers and related to pricing an order, but not all the components would be selected in the Sales area.
For example,
A roller blind will have a top tube; because the tube is dependent on the width of the window, we remove the option to select a smaller tube in the Questions and answers and add the cost of the tube into the Price grid with the Material.
Now, we need to use the information from the Questions and Answers to list all the components.
Steps for adding a Bill of Material to your profile
Once you have completed the basic steps to setup set up a product in BUZ, you can then proceed to create and include a Bill of materials.
To do this, you will need to
Set up
...
a new Inventory Group for the detailed list of components.
Enter the contacts for all of your suppliers
Add
...
your Inventory Items to the Group
Add Inventory items
...
to the Job Sheet
Purchase Orders
Stocktake
Set up your New Inventory Group
You will need an additional Group (or 2) for any components you may have, you can have as many component groups as you think is necessary.As an example you might have a group for motors and another for the “tubes and rails”, or you may have a group for all the components that you order from Supplier ABC and another group for all the components from Supplier XYZdon't need to add any extra component groups; you could add the bill of material to the current components group. Personally, I find it easier to separate the Sales components and the Bill of material components; this way, it is easier to manage things like downloading the group for stocktake and updating different areas. I know that everything in the group is related to the bill of material only,
This is how my layout would look
...
Roller Blinds as my Main Group, which contains a list of Materials
Roll - Sales Components, items for pricing the order
...
Roll - BOM Components much more comprehensive list of parts
...
These are just examples of planning the groups. There is no wrong answer, and you can have all the items in a Single Components Group or Muliplt if needed.
Enter the contacts for all of your suppliers
Adding suppliers is similar to adding a customer
...
Select the + Add Supplier button and complete the form, or you can download the CSV if you have want to add multiple suppliers that you would like to add.
More information can be found here.
Add
...
your Inventory Items to the Group
Now that you have setup set up the Group and create created a supplier Code and description, you can upload the inventory items.
Fill in all items from the menu on the right. If you do not order in from overseas, you will not need to use the Forex columns.
...
You can find more information about the Inventory Items here
Add Inventory items
...
to the Job Sheet.
...
The job sheet has a few requirements for the bill of materials to work.
Macro
We have written a macro to include in the job sheet; if your current job sheet does not include this, you may want to download the template below and copy it to your job sheet.
...
Components Tab
You will also need a Components Tab. The tab MUST be named Components for the software to recognise the inventory list.
If your templates does do not include a components sheet, you will need to add one in, ; select the + icon at the bottom of the page to add a new Tab and rename it Components, ; it must contain this name so that the program recognises the tab.
Named Field
There will need to be a named field that encompasses all the data in the Componenets sheet, and this will need to be named ComponentsRange
...
For more information about the Name Manager, https://exceljet.net/glossary/name-manager#:~:text=To%20open%20the%20Name%20Manager,the%20keyboard%20shortcut%20Control%20%2B%20F3.
You can find all these requirements in the Template below
Job Sheet Templates - Download a template here.
Explaining the layout
| View file | ||
|---|---|---|
|
•Row 1 consists on of the inventory codes (these can be exported from BUZ then Copy and Paste with Transpose)
•Row 2 consists of the wastage percentage (we start with 10%, so 1.1 is entered, as you use BUZ and start to get a more accurate wastage % you can change this)
...
More about the layout
Generally, the first few Columns columns relate to the Order Id and line item
...
Column C is the formula for fabric =Data!$BD11
Column D contains the formula to calculate material M2 =Data!BA11*Data!BB11/1000000)
...
Note: If you like a clean page that doesn't have #value, 0 or formulas
...
with a calculation in the unused cells, you can add an extra part to the formula.
If the row contains no data in the Data tab, then show nothing
...
The remaining Columns E+ will have every possible option available for the product, ; there are various different formulas that you can use to determine the quantity required.
I would recommend downloading the Inventory Items spreadsheet from BUZ and using the Codes and Descriptions from the list in the your jobsheetjob sheet.
Row 1 for the Inventory Item Codes
...
Now that you have the list of components, you can allocate any wastage if required
...
Row 4 + contains the formulas, ; the result in the table will be deducted from the inventory in BUZ , well once the job has been completed.
Generally, most of the Formulas will be based off on the Job Sheet Tab or the Data tab, ; using other sheets can lead to issues, to . To find out more about this, you may find more information here. Job Sheet - What order does the sheet load in?
If statements are a common formula to use in this table but you might also find VLOOKUP, HLOOKUP, Index and Match helpful. If you don't know much about these or need a refresher, there is lots of helpful sites that have some good examples, personally I like https://exceljet.net/Let ’s go through a few examples for of some basic formulas
This is my example Jobsheet, ; the formula in the components tab will be based off on Column G
If a statement with
...
one condition
...
If the Jobsheet tab G10 = Motor then input 1 into the cell, if G10 does not = Motor then leave blank or ““
...
If statement with
...
two conditions
...
If the Jobsheet tab G10 = Chain and K10 = Silver, then input 1 one into the cell, ; if either of these values are is not Chain or silver, then leave blank or ““
...
=IF(AND('Job Sheet'!G18="Chain",'Job Sheet'!K18="Silver"),1,0)
The unit of measure is very important essential too.
The required amount to be deducted might not equal 1, ; the item could be a the length of chain, a Chain; for this example, if there is a chain and it that is Silver, then return the value in the J Column.
...
Some cells will contain a whole number, like 1-99 for products that have with an each “each” value, while others may have a length, Roll, SQM, or even hours.
Below is an excel Excel file with many more examples, ; feel free to use any of these, keeping in mind that you will need to edit them to reference the right areas in your jobsheetjob sheet.
Now you should have something that looks like this
...
Data Tab
Next, you need to update the Data Tab
You will need to add the table that you created so that if there is are extra products that dont fit in to don't fit into this list, then BUZ will copy the last line and duplicate it for all the extra products.
...
One last thing, you need to add a value into cell C1, just so that BUZ knows that it is active, ; it doesnt doesn't matter what you add in there; I generally add my Row Headings there.
...
Save and upload the jobsheet job sheet to the Group
Testing
Place an order in BUZ and add multiple products with various options so that you can test all the formulas.
Download the jobsheet job sheet and check that the appropriate columns have values showing, ; if not, you may need to change the formula.
...
When you are happy with the jobsheetjob sheet, Invoice the test order, ; once you have invoiced the order, the stock will be drawn from BUZ, ; in my example below, I had 10 of each item on in stock, ; anything showing less than 10 ten was deducted from this order.
...
•There must be something in cell C1 of the components Sheet of the Job Sheet workbook for it to work. We recommend that you add in adding the date that you start using the Components Spreadsheet.
•If you do not put a Min and Max on the Inventory item, BUZ will not check to see if you have stock on hand and will generate an order for everything thing that was on the Accepted Orders each time, and you will not be able to keep an accurate record of what is on hand
•If you do not add a supplier into BUZ and onto the Inventory item, you will not get an order for any supplier
•How to transpose in excelExcel? is when When you are going to paste, select “Paste Special”, and you should get the box on the right to select choose Transpose
...
We understand that there is are a lot of components that go into making each product but . Still, like your Materials and all their information, once entered, you will only ever have to tweak it to keep it up to date, and you will no longer have the worry of about running out of stock.
Thank you
Any If you have any further questions, please contact get in touch with us at support@buzsoftware.com.au