Anatomy of Formula and Calculations in OpenBOM

Data is a central part of every business these days and to have the ability to manage data and glean intelligence from multiple sources of information is extremely important. 

In the manufacturing and construction industry, it always comes down to the ability to collect information and make calculations. This is one of the reasons why Excel (or Spreadsheets) is the second-best software that is used by manufacturing and construction companies. The reason is simple – a combination of flexibility and an easy way to perform calculations. However, when it comes to product data, product structures, bill of materials, inventory, specifications, and many related pieces of information, the flexibility comes with a huge price of inefficiency. 

OpenBOM solves this problem by providing a powerful and yet flexible data model and the ability to manage items (catalogs) and product structure (BOMs). This model solves the problem I mentioned above – managing data robustly and flexibly. In addition to that, OpenBOM brings a calculation model, which I’m going to talk about today. 

Before jumping into the details of the advanced model of OpenBOM calculations, I want you to check these two articles that can give you an idea of how simple OpenBOM formulas can be. Check out these two articles: 

Jumpstart your efforts with OpenBOM cost rollup quick start 

Bill of Materials Formulas and Conditional Rollups 

Learn more about OpenBOM’s data model, properties, formulas, and calculations in OpenBOM Training Library online before you start. 

Formulas Definition and Scope 

The foundation of OpenBOM formulas is how to define them and what it includes. OpenBOM formulas operate on the object records in such a way that they are defined as a function between properties and related to a specific object – e.g. Catalog, Bill of Materials, Order. Each formula is evaluated in an isolated space and corresponds to a specific object (eg. a record with a Part Number and its properties). 

As you can see in the picture below, the simplest formula is defined for the catalog between two properties – Quantity on Hand and Cost to calculate the inventor cost. 

Each formula is defined for a specific property (below Inventory Cost) and includes properties in the formula (Cost and Quantity on Hands). The formula is evaluated for each record of the object independently and results are presented in the user interface. 

You can easily identify the formula data properties by a blue corner on the property cell. Hover over shows that data is calculated and provides the details about a formula. 

In the case, an error happens or the formula cannot be calculated, you will see the red-colored corner and ERR text. 

There are 3 main types of formulas in OpenBOM. 

• Catalog and Order (BOM) formulas 
• BOM formulas 
• Totals and Rollup formulas 

Each of these formulas work similarly but behave differently in the way they retrieve data. Below, I explain the difference and specific characteristics of each formula type and how they work. 

Catalog and Order (BOM) Formulas 

Catalogs and Order formulas are the simplest formulas in OpenBOM. These formulas operate in a specific object type (catalog or order) and use the data defined in the catalog or Order BOM to calculate the value. 

Check the picture below. As you can see the formula to calculate an inventory cost to multiple Quantity are Hand and Cost. Both values are in the catalog and the calculations apply to all the rows. You can define a specific formula for each row by canceling the [x] apply to all rows flag.BOM Formulas 

The formulas in Bill of Materials are slightly different because they use the data properties coming from both objects – catalogs and BOMs. This capability makes it a powerful tool that can calculate data values in the context of BOM but use the catalog properties. Keep in mind that these formulas can only be used to calculate BOM properties, but they can use data from the catalog. 

The most typical use of these formulas is to calculate the value of the property in the context of the BOM (eg. Cost), but use data coming from both places- Quantity from BOM and Unit Cost from the catalog. Below is an example of such a formula defined in the BOM. 

You can clearly see the [Cost] property in a squared bracket indicating that the data is coming from the catalog and the rest of the properties are in the BOM. The same logic is used when each line is calculated separately based on the Part Number value.Totals and Rollup Formulas 

The third (and the most complex) type of calculation is called roll ups or totals. The purpose of these formulas is to provide a sum of the calculations for multiple rows. There are two types of summaries – simple (Total) and multi-levels (Rollup). The first one is only collecting value and creates a total in the same object (eg. Catalog or BOM). 

A more complex option is when the formula is configured to roll up values from lower levels of BOM structure to upper levels. In this case, the formula must include three elements:

• The calculated rollup value (eg. Extended Cos) 
• The rolled unit value (eg. Cost) 
• Quantity 

The unit value is usually defined or calculated in the catalog. The calculated rollup value is defined in the BOM. Here is an example.

Learn more about advanced formulas and rollups here.

Conclusion 

OpenBOM provides a powerful and robust mechanism for calculating values using formulas. These formulas can remind you of Excel but they are completely different. These formulas rely on the logical data model (part numbers and property names) as well as on the calculations content of OpenBOM’s data model – catalogs, product structure, and other objects. These data model objects make OpenBOM powerful because they can perform calculations that correspond to the actual data model you create in OpenBOM – parts, products, orders, and others. 

Learn how OpenBOM allows you to create powerful formulas in catalogs, BOMs, and other objects. REGISTER FOR FREE and start your 14-day trial to experiment and see what OpenBOM can do for you. 

Best, Oleg