top of page

Rollup Equations

This function is one of  the most powerful in this suite of tools.  It will generate equations that will add up the constituents in a structured list for a selected parameter, taking into account the quantities of each.  Put simply, the parent value will equal the sum of its children.

 

The Basic Rollup function simply does as noted above, but there are several Advanced Options that are used under various circumstances.  Sometimes the parent value isn't simply equal to the sum of the children. Additionally, there are some occasions where Exceptions can be made for these options.  

Basic Rollup Equations

Anchor 2

Consider the following table that has structure, in which the parent rows have been highlighted for clarity using the using the Highlight tool.  The table has part numbers, a Quantity column, and a 'Mass' column. 
 
Suppose you would like to know how much the entire system weighs.  You can not simply add up all of the cells in the 'Mass' column as that would neglect both the structure and the quantities. 

Example Table - Prior to Basic Rollup Eqns

Step 1:  Click on the RollupEqns function.

Rollup Equations - 1st input form

Step 2:  Select the column of the parameter that you would like to rollup by using the arrow buttons or the dropdown.  In this case, 'Mass' in column 'G', has been selected.

Rollup Equations - 2nd Input Form

Step 3:  The 'Rollup Equation Builder' form appears and allows the user to select column inwhich to write the equations.  In this case it is the column immediately to the right of the table, Column "I".  The 'Quantity' column is detected automatically, but can be manually selected as well.

Rollup Equations input form complete

Step 4:  Click on 'Insert Equations'.  
 
The equations are automatically written in the selected column, which in this case was the column immediately to the right of the table.  The title is automatically written, noting the source data column.  If the 'Quantity' column is missing, the function will merely write the equations with respect to the structure, assuming a quantity of one (1) for each item.

Example Table - After Basic Rollup Equations

Advanced Options

When the Advanced Options checkbox is checked, the form expands to show the additional functionality.

The five options are:

  1. Parent = Sum of Children (Default)

  2. Parent = Sum of Children and Parent - Always

  3. Material Production Cost Rollup

  4. Maturity Rollup

  5. Alternative Maturity Rollup

Advanced Rollup - Input Form
Anchor 1

Option 1: Parent = Sum of Children

Anchor 3

This option is the default and is similar to the Basic Rollup in that the value of the parent will equal the sum of the immediate children.  However, it allows the user to make Exceptions and associated Highlights, which will be discussed later.

Option 2: Parent = Sum of Children Plus Parent Contribution

Option2

This option results in a value that is equal to the sum of the immediate children plus the contribution of the parent itself. 

 

There are some situations where this is necessary, for instance in manufacturing.  The total labor content is not merely the content required to build sub-assemblies, but to assembly the sub-assemblies into the top level item.  The labor to do so can be considered to be at the top level.  

Example Table - After Advanced Rollup Equations - Option 2

Option 3:  Material Production Cost.

Option3

This option allows users to write rollup equations considering "Make/Buy" decisions.  A BOM may have numerous components that are purchased in an assembly (PIA).  The cost contribution of these parts might not be known.  But if the value of the assembly is known then adding up the children is not necessary and only the value of the parent is needed.  Conversely, if the assembly will be made internally, then the cost of each constituent part will be necessary to build the proper equations to determine the total cost of the end item.  

Example Table - Advanced Rollup Equations - Option 3 material production cost

Note: The line item in the 'Make/Buy' column with the 'Buy' designation has a rollup value of '2', compared with sum of the children '3'.  

Option 4:  Maturity Rollup

This option involves the use of a 'Maturity' column in which, the relative maturities of each line are scored with integers, with values increasing with maturity.

In this case the second line item is more mature than the least mature child.  As such this value is used in lieu of the sum of the children.  As you can see this can make a difference in the result. 

Example Table - Advanced Rollup Equations - Option 4 Maturity Rollup

Option 5:  Alternative Maturity Rollup

In some situations, values at the parent level may be more accurate than the sum of the children.  

 

This function checks for the presence of a parent value.  If one is found it is assumed to be more mature than the sum of the children.

Example Table - Advanced Rollup Equations - Option 5 Alternative Maturity Rollup

In order to see the various exceptions that are available, click the 'Show Exceptions' Check Box.

 

The exceptions apply to options 1 and 3.  Highlighting can apply to options 1, & 3-5.

Exceptions and Highlights:

Exceptions
Rollup Equations Input Form - Advanced Options, Highlighting and Exceptions

In the 'Mass' column one of the values is blank.  When the exception shown above is checked the parent value is used in lieu of the sum of the children.

Also, note in the form above that both the 'Add Exception Comments', and the 'Highlight Exceptions' have been checked.  The rollup value with the exception has been highlighted and has a comment of explanation.  

Example Table - Advanced Rollup Equations with Highlighting and Exceptions
bottom of page