Understanding Flexible Formulas

The flexible formula interface is a simple three-step wizard that guides you through the creation of a flexible formula method (FLEX). You assign to each FLEX an identifier (ID) that is referenced when assigning the method to a line item. Like the other methods, you can assign FLEX IDs on a row-by-row basis in the data entry grid or you can set them up as a default method with the option to allow or disallow the default to be overwritten in the data entry grid.

The flexible formula wizard provides for documentation of the FLEX ID, what is its purpose, and how the formula calculates. Documenting any dependent or referential details is optional but considered best practice.

FLEX IDs have an active/inactive flag. Consider using this feature whenever you wish to inactivate an existing formula (because it no longer applies), or whenever creating new FLEX IDs that are not to be 'released' (activated) until a future budget or plan cycle.

Some of the more powerful features of the FLEX method include the ability to span activities and scenarios. Most other methods have a limited or no ability to build a calculation that references data from one scenario to another. The Annual Growth Rate method (ANN%) for instance, does support 'growing' the proposed budget based on a historical scenario and a percentage amount that the user enters using the delivered algorithm: ((Method base) × [1 + (Growth rate)] = (Method amount)).

The FLEX method allows you to calculate a proposed budget from any 'comparison scenario' for the same activity that the coordinator has previously set up. Using a FLEX ID, a formula can calculate the current proposed budget based on last year's budget, the top-down plan, as well as history. The expression can be modified in the FLEX ID user interface to whatever you require, without having to do a customization. For instance, the ANN% algorithm above could be modified to: ((Method base) × [1 + (Growth Value)] = (Method amount)), or a driver amount could be used. To extend the formula further, you can capture multiple accounts together, sum them up, and then perform an annual percentage increase.

The FLEX method should be used when a Planning & Budgeting model has multiple line item type activities and there is a requirement to reference data in one activity to drive calculations in another activity. For example, if a customer has a revenue activity, then the budget data for revenue may be referenced to calculate data in another activity, such as expenses or balance sheet items. The coordinator must set up the Planning & Budgeting model to support 'data references' between activities.

Once you set up a data reference relationship between activities, that reference is one-way. In the above example, revenue data can be referenced in both the expense and balance sheet activities, but expense or balance sheet data cannot also be referenced in the revenue activity, because this may result in a circular logic error.

Data that is referenced between activities is only for the method amounts. Adjustments, allocations and the total amounts are not used in the data references nor in the FLEX method.

Data sources that are referenced from other line item activities, or across other planning centers, will use values from the master version. It may be necessary to submit or copy a working version to master in order to have the most current data sources when using flexible formulas. Data sources from within the same activity scenario for the planning center (or slice of data), comes from within the corresponding version.

Functions are similar to pre-delivered methods when you consider that functions support a relatively complex algorithm where the user merely has to supply the necessary data (arguments) in order to complete the calculation. The reason these are designated separately as 'functions' is because these calculations are part of ACE (analytic calculation engine) and are exposed to the Planning & Budgeting application through the FLEX method. Multiple functions can be a component of a larger FLEX method expression. We support conditional logic functions, financial functions, time-based functions, depreciation methods, as well as truncating and rounding functions.

See Defining Flexible Formulas.

Although the Planning & Budgeting application does not impose any limits on how many levels deep an IF function can go, be aware that there are performance implications (calculation times) when creating deeply nested expressions. In general, avoid exceeding three levels on nested IF functions. Alternatives to deeply nested IF functions may be to add more FLEX IDs, more rows to calculate intermediate values, as well as method drivers and driver lookup tables.

Sources are another powerful feature used in conjunction with the FLEX method. A 'source' is a user-defined range of data within a single line item activity—also referred to as a 'region' or 'data slice'. One capability of the FLEX method is to select a 'node-level' member—also known as a parent-level member. This feature supports defining a source at a roll-up level. For instance, you can have a source defined for an account called Total Salary. Defining a source at the roll-up level eliminates having to pick individual detail accounts that comprise total salary.

Sources can be defined narrowly or broadly depending on the modeling requirements. Another example of a source is North American Product Sales—a broad area of data that encompasses product sales data for all products, customers, channels, etc. This named source could be used in multiple FLEX IDs, for example to drive marketing budgets, revenue and cost allocations and so forth. A more narrow source would specify product sales for product XYZ in the Western Sales Region. This source may be used to help calculate direct cost of sales as well as selling costs and regional promotions and discounts. Sources behave like a variable in a formula expression. They can be used in many different FLEX formulas, and a single expression can be made up of many different sources. Whereas the existing RELATE method can include only one monetary account, the FLEX method has no such limit.

Drivers and lookup tables are supported in FLEX methods and provide another way to create a consistent set of assumptions across one or more Planning & Budgeting models, when assigned to method groups under the FLEX method. A single FLEX ID expression can support multiple drivers and lookup tables. Drivers are an alternative to using statistical and currency accounts, because they do not get exported back to general ledger.

To create a flexible formula:

1. Define the flexible formula source and driver. You can search for an existing source and driver or you can create a new source and driver.

   Note: Optionally, you can create the formula source and driver during the formula-building process itself.

2. Define the general properties of the formula. You can also copy from an existing formula.

3. Build the formula expression in a free-form entry text box. You can search for and insert a built-in function, source and driver. If you haven't already done so, at this point you can create a new source or driver as necessary.

4. Validate the formula.

5. Review the formula.

6. Assign the flexible formula ID as a method default in the model.

7. If necessary, rework the formula, or create a new formula.

8. If your line item activity and scenario have already been staged and released, go to the Scenario Manager and place the line item activity scenario on hold, stage the new or revised formula, then release the activity scenario when stage is complete.

Before you create a flexible formula you must:

• Define activities and activity groups.

• Define scenarios and scenario groups.

• Create planning models that are associated with an activity group and scenario group.