What Is It?

The OAE Flexible Calculations feature lets you use math – simple or complex – in rate cards to auto-calculate standard values.

Why Do I Need It?

Rate cards in OAE make it easy to apply cost or markup values because there’s default math behind the scenes, but there’s so much more estimators might need to do with rate cards. Sometimes you need full control over all the calculations rate cards are doing to produce their results. The OAE Flexible Calculations feature gives estimators that full control in their rate cards, like using a formula for Units columns or for default Structure groups with Cost columns.

These custom rate card formulas can be configured by admins in the system’s default rate cards, so they’re automatically added to new projects. But of course, projects can vary a lot, and having to manually adjust a rate card formula for the differences between projects wastes time and is prone to errors.

Wherever possible, OAE saves time by minimizing repetitive manual entry, and that is true in rate cards too. Custom rate card formulas can access data from custom work item and project properties, as well as data from other Structure columns, and more. This lets you have as many standardized rate card formulas as possible while still supporting variations between projects.

How Does It Work?

The OAE Flexible Calculations feature operates where rate cards and math/calculations intersect, so let’s review those two elements.

Rate Card Review

The Rows

There is a row for:

• Each estimation column group defined in the project’s Structure that contains at least one column with Input As set to Units or Cost.
• Any override columns you’ve added for modifying a column group’s values, which are shown under the row for the column group they belong to.

The Columns

These are based on the rate card Structure. Note that the Aggregate type configured there affects whether or not the rate card will display a column.

• The Type column populates with the Name of the column or column group in the project’s Structure, plus any defined subtypes.
• The Percent column lets you determine how much of the group totals a given subtype makes up (when subtypes have been added). A percentage value appears for estimation columns with Input As set to Units.
• The other columns come from the rate card’s Structure. By default, rate card columns with Aggregate type COGS or Revenue – plus any additional Units columns beyond the first – will appear here.

The Fields

OAE analyzes the Structure for both the estimate and the rate card to judge which fields you’re likely to edit, and shows those as editable. Default formulas for cost and markup are applied to values entered:

Any number entered in a field will be interpreted as a direct cost, so in the above screenshot, the 60 in the first row returns $60/hour.

A multiplier entered (e.g. x1.2) will be interpreted according to the aggregate type of the column, plus whether the column is the first of its type or an additional column of its type. See our Admin Guide to Rate Cards for details.

Overrides

As noted above, by default only some fields are editable in rate cards. But you can make all fields editable – and do custom math in them – by turning on Overrides in the rate card toolbar. This will also display the first of any Units columns that are hidden by default.

You can enter a formula right into any field.

Calculations Review

OAE offers a number of places to set up calculations in various forms. The two we need here are Structure calculated columns and rate card formulas.

Here’s a sample calculated column that takes its input from the value of the Consumable row in the Cost column in the rate card.

Continuing the example, the value that the above calculated column uses as input is, in turn, supplied by a custom formula that replaces the default cost formula.

The full formula is:
=0.5 * (($_row['Support Hours'] + $_units) * $_proj['consumablesrate']).

More on what all that is in the next section. In short, when estimators enter labor data in the Crew, Days, Trips, etc. columns in the estimate, the cost for the Consumables for that line item is supplied automatically.

To put those two pieces together, when you view the Consumables column in the work and line items tables, OAE processes the formula for the column, then feeds that value to the calculated column.

Available Variables and Functions

Math in rate cards can use the following types of variables, functions, etc. – they can be quite complex!

• Estimate columns
• Rate card aggregates
• Rate card columns
• Project custom properties
• Work item custom properties
• Common math operators
• Common boolean tests
• Common conditional expressions
• Unit conversion
• Operators and expressions used by math.js

For more information, see the OAE training guide for Math.

Example Use Cases

Consumables

On average, each type of project goes through a certain amount of printer paper, gloves, and so on (stuff that gets used up and replaced by new items) per hour, day, etc., of labor. Since estimators will already be entering the labor data, you can configure OAE to calculate the cost of that consumable stuff. If you know that, for instance, your average is $7.50 per labor hour, it’s easy to set up a formula that populates a Consumables column (for instance) with =7.5 x Total Labor.

If this example sounds familiar, that’s because we already showed you how it could work – in the Calculations Review section above.

Support

Similar to consumables, projects involve labor overhead in the form of production support hours. If you know that, for a given project type, you’ll need support hours equaling 20% of the total production hours, a formula can figure that for each line item.

Of course, the support percentage may vary across projects, and you don’t want to have to modify formulas for each project. So, as indicated above, you can create a custom property for “Support Hours %,” and use a variable (say $supportpercent) for it in your rate card formula. That way, projects can have consistent formulas that produce varying support calculations.

As for getting the value of ‘total production hours’, there’s the rate card aggregate variable $_units – that contains the sum of all Units columns in the rate card – but you might have non-production labor columns in your Structure. Once again, a formula has the answer – just use one that adds up the values in your production columns, like =$row['CA'] + $row['EL'] + $row['PA'] (where CA is Carpenters, EL is Electricians, and PA is Painters). That can give you a Total Production column to use for calculating support, consumables, etc.

Get OAE Today

If you’re already using OAE, you get the OAE Flexible Calculations feature automatically. If you aren’t using OAE yet, contact us today to get started.