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. 

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

Related Posts

Also on OpenBOM

4 6
15 March, 2024

In the complex landscape of product development, managing costs efficiently is paramount for success. Yet, traditional methods such as spreadsheets…

15 March, 2024

What manufacturers need in a supplier when they are a startup is usually going to be different than what they…

14 March, 2024

Revision control, an integral part of Product Data Management (PDM) and Product Lifecycle Management (PLM) systems, plays a crucial role…

12 March, 2024

In today’s dynamic engineering and manufacturing processes, managing changes effectively is crucial for organizational success. Whether it’s product design alterations,…

9 March, 2024

In the complex world of product data management, where dependencies and components intertwine across multiple levels, having the ability to…

8 March, 2024

Taking your product idea from concept to product launch and successfully selling your product takes a lot of work. In…

8 March, 2024

Earlier today, we hosted our (almost regular) OpenBOM Live Demo Sessions. Keep in mind that if you missed one of…

6 March, 2024

In engineering and manufacturing, data is inherently structured and interconnected. This starts with basic assembly-component relationships and expands to encompass…

5 March, 2024

In the evolving landscape of digital transformation, companies are increasingly moving away from traditional, often manual systems towards more sophisticated,…

To the top