Once you have completed the basic steps to 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 your 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.
For 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 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 want to add multiple suppliers.
More information can be found here.
Add your Inventory Items to the Group
Now that you have set up 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 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
If your templates do not include a components sheet, you will need to add one; 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.
Job Sheet Templates - Download a template here.
Explaining the layout
•Row 1 consists 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)
•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 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
=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 formulas that you can use to determine the quantity required.
I recommend downloading the Inventory Items spreadsheet from BUZ and using the Codes and Descriptions from the list in your job sheet.
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 once the job has been completed.
Generally, most of the Formulas will be based on 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 ’s go through a few examples of some basic formulas
This is my example Jobsheet; the formula in the components tab will be based 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 one into the cell; if either of these values 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 essential too.
The required amount to be deducted might not equal 1; the item could be the length of a Chain; for this example, if there is a chain that is Silver, then return the value in the J Column.
Some cells will contain a whole number, like 1-99 for products with 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 job 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 you created so that if there are extra products that 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 doesn't matter what you add in there; I generally add my Row Headings there.
Save and upload the 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 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 job 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 in stock; anything showing less than 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 adding the date 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? When you are going to paste, select “Paste Special”, and you should get the box on the right to choose Transpose
We understand that there are a lot of components that go into making each product. 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 worry about running out of stock.
Thank you
If you have any further questions, please get in touch with us at support@buzsoftware.com.au