Simple part and sub-assembly tracking in Excel

Nick_S

New Member
Joined
Oct 9, 2008
Messages
3
Hello,
There's lots of information out there on the kind of information to include in a Bill Of Materials (BOM). I'm pretty comfortable around the different formulae I could use to construct something. I'm really struggling to get my head around how to deal with sub-assemblies in tracking parts.
For a finished part that we buy in I can have a table to capture purchases of that part and another table to show it being consumed as parts are sent out. But what to do with parts that are used to make a new single component - how do I get those to interact with my purchases and consumption? Do I need to track sub-assembly parts separately? How do they metamorphosize into a new sub-assembly part? It's the logic of how to pull something together that escapes me.
Any suggestions or examples would be greatly appreciated!
Thank you,
Nick
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,392
Office Version
  1. 2016
Platform
  1. Windows
The way I've seen sub-assembly handled is by treating the group which does the sub-assembly as an internal customer and supplier. There will be accounting differences but logistically it works.

The sub-assembly has it's own part number which you'll stock and sell to end customers. If any component changes (e.g. new material but otherwise same specification for the sub-assembly) then you should create a new sub-assembly part (e.g. in case of a recall as the new material was toxic so you must be able to differentiate between the old/new components and old/new sub-assembly).

I don't think Excel is suitable for this kind of requirement, especially if components have a shelf life or you have alternative components which can be used as stock and sales is tricky enough but including BOM management would be complex.

Just my 2 cents.
 

Nick_S

New Member
Joined
Oct 9, 2008
Messages
3
Hi Toadstool,
Many thanks - that's the direction I was heading - to keep it simple though I'd like just one level of purchasing - so that one person can enter components for the sub-assembly or a part that will go straight back out of the door without having to do too much differentiation. I then need a way of 'consuming' the purchased components for the sub-assembly and then putting back into stock the finished sub-assembly so that can be sold.
We don't have the need / budget for a full on ERP system just yet.
Kind regards,
Nick
 

Watch MrExcel Video

Forum statistics

Threads
1,127,778
Messages
5,626,827
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top