Synopsis:
- What kinds of BOM’s can be imported?
- How do I need to organize my BOM in excel?
- How do I configure a BOM Import Schema?
- How do I perform the import?
Webinar transcript
APT University: Importing BOM’s from Excel
Hello and welcome to APTU – Today our focus is going to be BOM Import. Today’s lesson is part of our series on Manufacturing so if you’d like to learn more, please visit localhost/apt and you’ll find all of our previous APTU videos under the RESOURCES column.
At a High Level, a Bill of Materials is your recipe for a manufactured item, and the tools we’ll be discussing today specifically address the Assembly item type in AdvancePro, you will only have this available if you have the manufacturing module enabled, so if you don’t see the MFG tag in your AdvancePro workspace on the upper right, feel free to reach out to your AdvancePro advisor.
So AdvancePro can import assembly Bills of Material either from a QuickBooks file or from an excel file, Today our focus is going to be excel, so the main tool to do this is found under Admin> Utilities > Excel import/export and down at the bottom you’ll see import assemblies.
So this is where we can set up the assembly schema, which tells AdvancePro exactly what to look for in each column of our excel file. Lets go ahead and look at a suitable excel file.
So if you need one of these files as a blank, you can find it on localhost/apt under the resources tab in knowledgebase, there’s a whole section there for our importing schemas.
So here you can see that the order of the field names that appear in schema settings needs to match the order of your column headers in the import schema, the required ones appear on the right hand side of the schema layout by default, so we have the assembly SKU, this will be the SKU of the finished product, the assembly name, which is the product name for the finished product, then we have the component SKU, This is the first part or ingredient, it must already be entered in AdvancePro and have a vendor assigned. Then we have the corresponding name for the component, which needs to match the name corresponding the the SKU, then we have the quantity of the component on that row of the spreadsheet being used, and the vendor account number associated with the vendor you want to use for cost prices, so if you buy that component from more than one vendor, pick the vendor account number for the vendor with the cost price you want to use with respect to the cost price used for this finished good.
So if your assembly has more than a single component in the BOM, you’ll see that means you’ll use one row for each component SKU, and any information with respect to the assembly item or finished good gets duplicated on every row that pertains to that finished good. There’s a pile of other fields that can be entered that fill out the various settings available on the finished good, including selling price, adjustments to cost for labour, whether or not to sell the finished good on a B2B website, Product notes, weights, and dimensions.
One thing to note about this import is that all components will be imported as a basic item type, so if your BOM’s have variables, you will need to enter those BOM’s directly into AdvancePro. Also, AdvancePro does allow you to use assembly items as a component in other assembly items, this is called a sub assembly and we have a whole APTU video on how those work, you can import assembly items that have assembly items as components, but those assembly items do require a vendor to be attached, even if it’s just a placeholder vendor, and you cannot create a sub-assembly and use that sub assembly as a component in another assembly in the same sheet, so for this situation you would use one sheet for the sub-assembly, then you would import it, and then you would import the finished good that contains that sub assembly as a secondary sheet.
What you can see here is that you can import multiple assembly items, even ones using the same components on a single sheet, you just change the sku and assembly name and AdvancePro will start building a new BOM, you can also use spreadsheets to update existing BOM’s. In order to have a successful import, all of the required columns must be completely filled in, the headers need to match in the sheet the way they are shown in AdvancePro as well – which means no spaces, only underscores in the top row headers, and your schema settings in AdvancePro needs to be saved with the correct format, and with the headers listed in the order that matches your excel file. Fortunately the schema is saved so if you import BOM’s on a regular basis, you will only need to set your schema up the first time.
Once you’re happy with your excel file, go ahead and open up the import assembly area in AdvancePro, just to review we’re in Admin>utilities> excel import/export, and import assemblies move from the manage schemas tab over to the ready to import tab, and select your schema and browse for the file, then click import, if the import is successful AdvancePro will say so, and if it’s unsuccessful you’ll get a little report telling you what failed and why.
Then we can go to view products, filter down to our assembly items, and go have a look at our freshly imported assembly items!
This concludes our discussion today.