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
20 December, 2024

We hear this story a lot, your small business is growing. Orders are increasing, your team is expanding, and your...

20 December, 2024

There is no single company in the world that will tell you that BOM cost is not important. However, when...

19 December, 2024

Welcome to the OpenBOM Tutorial Series! We are excited to kick off a brand-new series of tutorial videos designed to...

18 December, 2024

Engineering isolation hurts production and business. In many manufacturing companies, engineering operates in its own silo – disconnected from production...

17 December, 2024

As we wrap up another exciting year, it’s always good to look back at the advancements and innovations OpenBOM introduced...

14 December, 2024

Efficient data management and integration are crucial for modern manufacturing companies. Engineering and manufacturing processes need to be connected and...

13 December, 2024

Excel has been a trusted tool for managing Bills of Materials (BOMs) in engineering and manufacturing for decades. Its accessibility...

12 December, 2024

In the fast-paced world of manufacturing, operational efficiency, and material cost control are crucial for staying competitive. Product development is...

12 December, 2024

Products are getting more complex. In a world where software is quickly becoming the most complex part of every car,...

To the top