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

Oleg Shilovitsky
Oleg Shilovitsky
12 October, 2021 | 3 min for reading
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. 


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

Related Posts

Also on OpenBOM

4 6
7 December, 2023

In the modern engineering and manufacturing environment, seamless integration between software tools is a game-changer. At OpenBOM, we focus on…

6 December, 2023

Last month, I wrote an article discussing whether it’s a good idea to store cost and supplier data in CAD…

5 December, 2023

Welcome to another release of OpenBOM. As always, I’m super excited to share everything new we are bringing to our…

1 December, 2023

In my previous blog posts, I emphasized the importance of organizing digital information and developing integrated processes using OpenBOM. In…

30 November, 2023

The success of any business depends on its ability to innovate and bring new products to market efficiently. For manufacturers,…

29 November, 2023

The new year is fast approaching, and as I counted earlier today, we have only 18 working days left until…

28 November, 2023

In the world of product design and manufacturing, the complexity of aligning data between Computer-Aided Design (CAD) software and Bill…

25 November, 2023

Digital transformation is important for everyone these days and you can embrace the future of digital transformation with OpenBOM. The…

24 November, 2023

Everyone knows that China is the global manufacturing powerhouse of the world that attracts business from all around the world….

To the top