Best Practices of Formulas, Rollups, and Cost Comparison using OpenBOM

I have never seen a single company that is not interested in cost calculations when it comes to product development, manufacturing, and many other processes. The ability to rollup cost (and many other attributes) is an essential part of the planning process, yet companies are struggling to do so in a very efficient way. 

When it comes to the calculations, there are two main dimensions of the complexity – (1) how to maintain the central source of truth for the information about unit values (cost, mass, etc.) and (2) how to collect a hierarchy of data objects and roll up the values between levels in the hierarchy. Both are pretty much killers if you try to solve it using Excel or other spreadsheets. Typically, companies are copying spreadsheets between projects, which breaks the first complexity level. The second one becomes a nightmare of lookups if you try to manage it manually, but it won’t work as flexibly as you need it. 

Reference instance model with product structure

OpenBOM comes ready for the challenges I mentioned above. Its data model is tuned for the use cases when product structure is represented by multiple complex branches and the item data is centralized in the item catalogs and automatically reused in all item instances. To make a use case realistic I decided to manage multiple cost values to differentiate between batches of 100, 1000, and 10000 units. 

This is how it looks. In the picture below, you can see an item catalog with all the information. Keep in mind that OpenBOM automatically calculates the costs for assemblies (new feature!).

And as you can see a BOM automatically instantiates the data properties from a catalog.

How to define formulas and data structure 

As you want to define a formula for the use case I built above, you need to prepare both unit data (in catalogs) and calculated data (in BOMs) to keep them organized together. Check the picture below – I added cost 10, cost 100, and cost 1000 pricing value. For BOMs, you need to define price 10, price 100, and price 1000 values. These values will calculate values where cost XYZ values will keep the unit cost. 

Here is how you do it in the catalog with 3 additional properties and values. A similar situation is in BOMs, which connect in real-time to the catalog with 3 additional properties to calculate the rollup.

The formula definition must be done for each calculated property and it includes properties to be used for calculation and also condition to save calculated properties in the catalog for assemblies and sub-assemblies. Here is an example of formula definition:

I defined the formula, what is next? 

Once formulas are defined you have a model that can be used to calculate cost rollups for multiple sets of data. As a guidance, it is important to ensure that all items are defined in the catalog(s) and all end item property data is defined (eg. cost for each item). Another important thing is to define quantities and validate that the formula is set correctly for all items. If you see ERR message in the cell, it means some of the formulas are not set correctly. Checking the formula or absence of some data can help you to find the problem. 

Video Demo

The following video demonstrates the steps I described above. Watch the video to get more info. 

Conclusion

OpenBOM provides a super powerful set of formulas and calculation capabilities that allows you to streamline and simplify your estimation process (Eg. cost, mass, etc.). It gives you automatic recalculation when you change the data (catalogs, BOMs) and takes a lot of pain out of the way when you need to manage complex product data. 

Check out what OpenBOM can do for you REGISTER FOR FREE and start your 14-day trial today. 

Best, Oleg