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

Oleg Shilovitsky
12 October, 2021 | 3 min for reading

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

## Also on OpenBOM

4 6
7 August, 2024

As summer reaches its peak and many of us take a well-deserved break, it’s an excellent time to step back…

3 August, 2024

In the modern dynamic landscape of product development, the release process is a crucial and complex element that can determine…

2 August, 2024

Getting your product from concept to reality often means working closely with a contract manufacturer (CM). Sharing detailed product data…

1 August, 2024

OpenBOM provides an effortless way to collect and manage all your design information, attaching it to item and BOM database…

31 July, 2024

In today’s manufacturing world, precise calculations and data analysis are crucial for success. From cost and weight to compliance, having…

30 July, 2024

Digital Twins is a trend and technology that aims to provide a virtual representation of a physical object, process, or…

26 July, 2024

Welcome to the final installment in our series on best practices for utilizing OpenBOM’s new xBOM feature. In previous articles,…

26 July, 2024

We continue to discuss the capabilities of recently released xBOM services. It expands OpenBOM product structure (BOM) management in many…

25 July, 2024

In today’s digital age, the ability to seamlessly connect and identify items is crucial for efficient operations and effective data…