Formulas, Calculations, & Rollups

This set of resources provides you with practical information on how to work with formulas, totals and rollups

Why Use Formulas in OpenBOM?

OpenBOM formulas and calculation are a powerful yet very flexible mechanism to do a very basic, but important calculations. Formulas are equations you create in a cell which perform simple arithmetic operations for you, using the designated cells. The basic principle to understand is that a formula equation is in the usage of properties as parameters for the formula. The formula is always defined in the context of a specific line (Item).

Here is a simple formula calculating Ext Cost based on quantity and formula.

Definition of Totals, Formulas and Rollups

Totals: A setting that when enabled, displays column sums in the bottom row of the single-level BOM screen.

Formulas: A mathematical function created by the Formula Builder in a single cell. This may be applied to all rows, even sub-levels when define as a “rollup.”

Rollups: A feature of a formula. When a formula is defined as a “rollup,” OpenBOM will look down all sub-levels, SUM that property and return the SUM up onw level. Multi-level BOMs only.

How Do Calculations Work?

In a regular formula without sub-levels, formulas work in OpenBOM pretty much like in Excel.

However, if there are sub-levels, things work a little differently. We first compute the cost for each regular sub-level item using the regular formula. Then we sum it up for all the sub-level items and put it into the cost for the parent, and then apply the formula in the parent level, as you see in the picture below.

Watch this video for more details:

Creating and Editing a Formula in OpenBOM

The format for formulas include number property types and arithmetic operators:

Target property = (Property 1) (Property 2) (Property 3)….

Let’s build a formula step-by-step:

1. Right-click on the desired number property type (e.g. Total Cost).

2. Enter the first desired number property type into the Formula Builder (e.g. “Quantity”). Then select the desired arithmetic operator (e.g. “*”). Arithmetic operators available for formulas are:

Multiplication (*), Division (/), Addition (+), and Subtraction (-)

3. Add the next desired number property type (e.g. “Cost”). Continue adding properties and operators as desired.

4. Click “Apply for all rows” if you want the formula to be applied to all the components in the BOM. When finished building the formula, click on “Save”.

If you hover over the cell where the formula is, you can see the definition of the formula that’s behind the cell.

Furthermore, if there is an error in your Formula, you will see the following indication: “#ERR”.

In addition to this, the “Auto Formula” option below will copy all Formulas to new items when you Add Item to BOM:

Using the Rollup Option of a Formula

When it comes to bills of materials, roll-ups are super important because roll-up calculations allow you to get an assessment of cost, weight, or other key data.

To create a simple rollup in one level, use Enable Rollup 

1. On the Property dropdown, click “Enable Rollup Totals”

2. The rollup returns the sum of the column with the Property, Total Cost.

Conditional Rollup

OpenBOM rollup provides the option to control what branches of the multi-level product structure will be included in rollup calculation. To control it, use a conditional rollup feature. This feature is very useful for Purchased Assemblies and similar use cases. 

A typical example would be a pump or electric motor, but I’m sure you can come with many others. At the same time, you have a 3D model of the product with this “purchased assembly” and you need its parts to be in the BOM because of different reasons (eg. mass, compliance, and others). Let’s say I need to run a cost calculation, but I want to rollup the formula stop under certain conditions. It is hard to define these conditions upfront, but thanks to OpenBOM flexible data model, you define a property that can say, it is a purchased assembly or a normal assembly. 

Saving Rollup Values To OpenBOM catalogs

OpenBOM item information is stored in catalog(s) – a database of all items. An additional option to save calculated values  of rollup for each assembly and sub-assembly is useful to have unit cost of each assembly and sub-assembly stored in the catalog (not only for BOM).  

The option to save rollup values to catalog by default is switched off to prevent overwriting the value of cost you might be using for purchased assemblies. NOTE: when using this option keep in mind that all calculated values (assembly cost) will be saved in the catalog and replace the values you put manually. 

Formula Support in Catalogs

Here is a quick preview of our powerful formulas feature in the Catalog. Our customers asked us about these formulas a long time ago and we are happy to bring it to OpenBOM.

The functionality of the formulas in the Catalogs are very similar to BOMs. You can define a formula in each line and also create a rollup.

The formula in Catalogs can bring many new opportunities and support very useful customer cases. In the video you can find below, we demonstrate how you can organize the formula to calculate the total stock cost in the company. It is a very easy formula – unit cost x quantity on hand.

Formulas in Catalogs are a very powerful tool and can be very helpful. The calculation of inventory cost is just one case. I’m going to show some other very useful situations when the Catalog formula is absolutely needed.

Manage Packaging Units and Use Catalog Formulas to Normalize BOM Units of Measure​

In this section, we will walk through how to manage a situation in which you’re buying materials in one unit of measure and consuming it in a different unit. Imagine, for example, that you buy paint in gallons and consume it in grams in BOMs. Or another example, you buy screws in boxes, but consume them in EA units in BOMs.

To handle these cases in OpenBOM, you can create a Catalog including purchasing costs, units of measure and a conversion factor. Then you create a formula in the Catalog to calculate the unit of measure, based on packaging prices and the conversion factor. Finally, you’ll need to create a typical cost rollup quantity in BOM (e.g. Extended Cost = Unit Cost * Quantity).

The picture below shows you how it works. Change packaging costs for any product leads to a recalculation of BOM costs automatically.

Watch this video for more details:

Mastering Formulas, Totals and Rollups

To better explain this topic, we hosted a webinar, which you can find the full recording of and slides in the resources below.

Steve Hess, director of user experience at OpenBOM, shared his knowledge about widely used scenarios of applying formulas and a variety of calculations in BOM management – eg. rollups, totals, etc. Everything you wanted to ask about applying formulas, rollups, etc. We demonstrated the OpenBOM Formula Editor and how it can be used in rollups.

Check out all these resources to learn more:

Loading...