Once you have completed the basic steps to setup 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 your New Inventory Group for the detailed list of components.
Enter the contacts for all of your suppliers
Add in your Inventory Items to the Group
Add Inventory items into 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 XYZ
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 multiple suppliers that you would like to add.
More information can be found here
Add in your Inventory Items to the Group
Now that you have setup the Group and create 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 into Job Sheet
If your templates does 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.
Explaining the layout
•Row 1 consists on 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)
•Row 3 consists of the inventory description (these can be exported from BUZ then Copy and Paste with Transpose)
•From Row 4 down are formulas calculating the stock deduction. This formula includes the wastage percentage (as per example →)
More about the layout
Generally the first few Columns relate to the Order Id and line item
For this example
Column A =Data!AW11 (Line Item Number)
Column B =Data!AV11 (Order Item Product Key Identifier)
Next will be the material
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 that have 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
=IF(Data!AV11="","",(Insert formula here))
Example =IF(Data!AV11="","",Data!BA11*Data!BB11/1000000)
Components
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 jobsheet
Row 1 for the Inventory Item Codes
Row 3 for the Inventory Item Descriptions
Below is an example for transposing Inventory Item Codes and Descriptions to the Components Tab
Now that you have the list of components you can allocate any wastage if required
Wastage
Row 2 - add in the percentage % for waste.
% can read as a decimal place
Example
10% = 1.1
30%= 1.3
no wastage = 1
Calculations
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 the Job Sheet Tab or the Data tab, using other sheets can lead to issues, 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 go through a few examples for some basic formulas
This is my example Jobsheet, the formula in the components tab will be based off Column G
If statement with 1 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 2 conditions
If the Jobsheet tab G10 = Chain and K10 = Silver then input 1 into the cell, if either of these values are 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 too.
The required amount to be deducted might not equal 1, the item could be a length of chain, for this example if there is a chain and it is Silver then return the value in the J Column
Some cells will contain a whole number like 1-99 for products that have an each value, while others may have a length, Roll, SQM, or even hours.
Below is an 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 jobsheet.
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 extra products that dont fit in to 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 matter what you add in there I generally add my Row Headings there
Save and upload the jobsheet 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 and check that the appropriate columns have values showing, if not you may need to change the formula.
When you are happy with the jobsheet, 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 stock, anything showing less than 10 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 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 excel? is when you are going to paste select “Paste Special” and you should get the box on the right to select Transpose
We understand that there is a lot of components that go into making each product but 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 running out of stock.
Thank you
Any further questions please contact us at support@buzsoftware.com.au