Direct Material Costing through BOM explosion

There is often a case when your supplier suddenly changes the price of a material you purchase. The question that arises is how much this change is going to affect your direct material cost. Some ERPs might offer the capability of “What if” analysis while some others don’t. If the second is your case, the easy (or not so easy) way out, is to perform a BOM explosion in an Excel spreadsheet for the SKU you want to check and try to re estimate the total Direct Material Cost.

To do this you have to be an advanced SQL user and have an understanding of Database management and of course Excel and VBA. The trick is to make a connection with your Database Server and perform a recursive query in order to extract every component of your BOM tree diagram. After doing this, you can then change the price of the material which interests you and find out how much it affects the total material cost.
When I faced the same problem I decided to write some routines of my own. I made an "Excel Add In" that presents a form with the tree diagram. There I can easily insert the new price and re estimate the cost without even sweating.