Simple BOM, inventory and sales spreadsheet
Budget: $30 – $250 AUD
I am looking for someone to help me create a simple Bill of Materials (BOM) and inventory management system. I need to track electronic components in the spreadsheet but I do not need to track item locations. The spreadsheet should be able to notify me when the stock level reaches a certain quantity so that I can re-order stock as needed. Additionally, I do not require any forecasting functionality.
I want to be able to create assemblies in a BOM from inventory items, display assembly manufacturing costs and save each assembly. Modification/update of assembly function will be required as components are sometimes discontinued and new components have to be used.
I want to be able to store all necessary component information in the inventory database, such as Item code, item name, description, supplier, order code, quantity, unit cost, inventory value, re-order level, re-order qty, notes.
I want to be able to add "other costs" to each assembly in the BOM, for costs such a packaging, shipping, insurance, admin fees, business expenses, labour etc. I would need to be able to enter these costs separately and then designate what proportion is to be assigned to each assembly. Eg. there are annual costs that must be apportioned across all sales, usually be percentage of total cost apportioned to each assembly sale.
I want to be able to enter component stock purchases and have the inventory updated automatically for qty and unit cost (combining new costs with cost of stock on hand).
I want to keep track of sales to customers, including customer information, assemblies sold, sale price, shipping cost, transaction fees and assembly serial numbers, manufacturing date etc.
Each time an assembly sale is entered, I want the inventory quantities to be automatically updated.
Also, a notification whenever component re-order quantity is reached, including highlighting or changing the fill colour of the row.
What I DO NOT need is invoicing, purchase orders, complex sales analysis.
I do like the attached screenshot of a sample BOM page. Although, this particular template (the entire spreadsheet) is too complicated for me and too hard to edit.
I can provide exact details of information required to be recorded.
I want to be able to create assemblies in a BOM from inventory items, display assembly manufacturing costs and save each assembly. Modification/update of assembly function will be required as components are sometimes discontinued and new components have to be used.
I want to be able to store all necessary component information in the inventory database, such as Item code, item name, description, supplier, order code, quantity, unit cost, inventory value, re-order level, re-order qty, notes.
I want to be able to add "other costs" to each assembly in the BOM, for costs such a packaging, shipping, insurance, admin fees, business expenses, labour etc. I would need to be able to enter these costs separately and then designate what proportion is to be assigned to each assembly. Eg. there are annual costs that must be apportioned across all sales, usually be percentage of total cost apportioned to each assembly sale.
I want to be able to enter component stock purchases and have the inventory updated automatically for qty and unit cost (combining new costs with cost of stock on hand).
I want to keep track of sales to customers, including customer information, assemblies sold, sale price, shipping cost, transaction fees and assembly serial numbers, manufacturing date etc.
Each time an assembly sale is entered, I want the inventory quantities to be automatically updated.
Also, a notification whenever component re-order quantity is reached, including highlighting or changing the fill colour of the row.
What I DO NOT need is invoicing, purchase orders, complex sales analysis.
I do like the attached screenshot of a sample BOM page. Although, this particular template (the entire spreadsheet) is too complicated for me and too hard to edit.
I can provide exact details of information required to be recorded.