Building Flexible Formulas for Your Model

This chapter provides an overview of flexible formulas and discusses how to:

• Define a flexible formula source.

• Define a flexible formula.

• Assign a flexible formula.

• Copy flexible formulas.

• Updating the planning model when you edit flexible formulas.

Understanding Flexible Formulas

Planning and Budgeting has a formula-writing feature that enables you to define your own calculations. With flexible formulas you can be creative and imaginative when developing models that adhere to both internal and statutory accounting policies, as well as industry best practices. Typically, a coordinator defines these 'free-form' formulas used within a scenario, that can then be associated with an account as a default, or available to end-users to pick from during the preparation of their plans or budgets. Flexible formulas apply only within or across line item activities in your proposed scenarios. You can also use flexible formulas in top-down, bottom-up and forecast scenario types.

Considerations when Creating a Flexible Formula

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, Sources and Drivers

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 Using Built-In Functions.

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.

Process Flow

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.

Terminology

Following are commonly used terms:

Prerequisites

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.

Defining a Flexible Formula Source

When you build a flexible formula, you must bind its components as valid sources or drivers.

You can define the sources ahead of time, or you can define them on the fly as you build your formula. A source can be used by multiple flexible formulas defined for an activity scenario in a planning model.

You can also define drivers before or during the creation of formulas. You create drivers via the Method Driver page and associate them in the Planning Method Group page under the FLEX method, which is associated with a given activity.

Pages Used to Define a Flexible Formula Source

Creating a Flexible Formula Source

Access the Flexible Formula Source page (Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flexible Formula Sources).

Activity

If you are working with more than one line item activity within your scenario, you may choose as a source one of the other line item activities, by entering it in this field. For other line item activities to be available when prompted, they must be used within the same scenario and there must be a "References Data From" relationship in the activity group.

Use Comparison Scenarios and Analysis Base

If you select the Use Comparison Scenarios check box, the system displays the Analysis Base field in which you can choose a source. For example, some of the options might include: Current Year Budget, Current Year Forecast, Prior Year Actuals, Year To Date Actuals, or an Analysis Base defined by the coordinator. The Analysis Bases available in the dropdown will be only those defined in the Comparison Scenarios grid on the Data Source page for the line item activity scenario. We provide this check box so that you can span across scenarios for data sources. See Defining Data Sources for Line Item Activities. See Understanding Planning and Budgeting Activities. See Establishing Activities and Activity Groups.

In the Dimension Member Selection box on the Flexible Formula Source page you indicate the source (or inputs) from each dimension that is associated with the activity scenario in which you are defining a source and formula.

Examples of the Selection Options include:

You should qualify at least one dimension on the Flexible Formula Source page as Single Member or Multiple Members, but it is not required. Defining all dimensions on the Flexible Formula Source page as Same as Target is only applicable when used in conjunction with the PREV and NEXT functions. Additionally, do not select the same value for a dimension on the Flexible Formula Source page as the account to which you apply the formula on the Line Item Details page, as this will create a circular reference, resulting in a zero amount. For example, selecting All Members for the account dimension on the source would create a circular reference.

Note. For example, PREV and NEXT would be used with a Flexible Formula source that has all selection options set to Same as Target when working with the element of time, or budget period, that is defined as a driver source. If you are using a monthly calendar for your plan or budget, you may want to specify the three previous periods for each month to derive an average. In order to define the three previous budget periods for a formula, create a driver using the same calendar and assign 1 to the first period, 2 to the second period, 3 to the third period, and so on. This way you can capture in your flexible formula calculation each of the three previous (PREV) periods for each budget period in your plan or budget.

For the Currency Code dimension (CURRENCY_CD), if you are working with monetary amounts, then set the Selection Option for the Currency CD to Same As Target. If you are trying to retrieve statistical values, then set the Selection Option to Single Member and set it to blank, since statistics have blank currency codes. This is to avoid adding different currencies together, which the system does not support. The system does not support currency conversion from source to target within flexible formulas.

If you want to use flexible formula sources with the built-in functions of PREV, NEXT, IRR or NPV, you set the Budget Period dimension member to Same as Target.

Selecting Dimension Members for the Flexible Formula Source

Access the Dimension Member Selection page (click the Selected Members link next to a dimension that uses the Multiple Members selection option).

For a dimension that uses the Multiple Members option, you define members either By Tree or By Value.

Click OK to return to the Flexible Member Source page which now displays the range you have specified.

Note. When constructing the formula, be mindful that the formula does not do any currency conversion.

Copying a Formula Source

Access the Copy Source page (click the Copy Source button on the Flexible Formula Source page).

You can copy a flexible formula source ID from one scenario to another. The target Activity ID, Scenario ID, and Flexible Formula Source ID default to the same Activity ID, Scenario ID, and Flexible Formula Source ID as the source; however, you can override the Activity ID and Scenario ID by selecting from the dropdown list. Scenarios in the target dropdown list are restricted to those within the same activity as the source scenario.

You can specify the same Flexible Formula Source ID for the target as the Flexible Formula Source ID for the source, if you are copying from one scenario to a different scenario. But if you are copying within the same scenario, then you must enter a different Flexible Formula Source ID.

Defining Flexible Formulas

The flexible formula wizard lets you build the expression for the formula using a free-format entry text box, or you may use the insert buttons to build the expression. However, you must bind whatever you enter as a valid source or driver. If you don't find a relevant source or driver in the prompt list, you can create a new one on the fly (please refer to the section above).

This section discusses how to:

• Define the general properties.

• Define the expression.

• Review the flexible formula.

• Copy the flexible formula.

Pages Used to Define Flexible Formulas

Defining General Properties of a Flexible Formula

Access the Define General Properties page of the Flexible Formula Wizard (Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flexible Formulas).

Description	Enter a short description of this formula. Consider using a meaningful description, since this is the description the end users will see when picking from a list of formula ID's that have been created.
Status	The Inactive status can be used when you no longer require the formula for the activity scenario in a planning model.
Notes	Optionally enter any free-form text describing the various pieces of the formula's expression.
Copy Formula	Click to copy an existing formula, which you can replicate or modify as necessary.

Defining the Expression of a Flexible Formula

Access the Define Expression page of the Flexible Formula Wizard (click step 2 in the Flexible Formula Wizard).

Expand the box at the top of the page to view the General Formula Properties defined in the first step of the Flexible Formula Wizard.

Validating the Expression

When you click validate, if the system does not recognize a source or driver, it populates the grid in the Expression Components box below with that source or driver, where you can resolve it.

After creating a new driver ID, be sure to associate it with the FLEX method in the Planning Method Group page related to the corresponding line item activity scenario.

See Setting Up Methods.

Using Built-In Functions

The table below lists the functions that are delivered for building formulas. You cannot define any new functions.

Note. If you want to use the built-in functions PREV, NEXT, IRR or NPV, you must create a flexible formula source with the Budget Period dimension member selection set to Same as Target.

See Functions, Sources and Drivers.

The following is an illustration of how to use the same function more than once in an expression: Suppose you want to build a formula

ROUND(NUM_UNITS) × ROUND(COST)

and you select the ROUND built-in function from the prompt, and insert it into the expression. The system populates the expression statement box with

ROUND(DATA)

and drops DATA in the grid below. You can then associate DATA to a driver ID called NUM_UNITS. If you then choose ROUND a second time and insert it into the expression, the system populates another ROUND(DATA) into the expression box and it does not place the second token DATA into the grid since a token with the label DATA already exists in the grid. So the expression is now bound to

ROUND(NUM_UNITS) × ROUND(NUM_UNITS)

and this is incorrect. In order to create a placeholder for the second token DATA in the grid, you can rename the token as ROUND(DATA1) in the expression statement box and click Validate to add DATA1 to the grid. You can then bind DATA1 to driver ID COST and this yields the desired expression

ROUND(NUM_UNITS) × ROUND(COST)

Reviewing the Flexible Formula

Access the Review page of the Flexible Formula Wizard (click step 3 in the Flexible Formula Wizard).

General Formula Properties	Use the Edit button in this box to update the general properties page you defined in step 1 of the wizard.
Expression	Use the Edit button in this box to modify the expression defined in step 2 of the wizard.
Destination	Use the Edit button in this box to change the accounts (or additional dimension when used) to which the formula is assigned. When you click this button the system takes you to the Assign Planning Method Defaults page for the activity scenario to which the formula is assigned.

When you are done, click Save.

On the Save Confirmation page the system gives you these options: Create New Formula, Continue with Current Formula, or Assign Method Defaults. The Assign Method Defaults option takes you to the Assign Planning Method Defaults page (see 'Assigning the Flexible Formula' discussion below).

Copying the Flexible Formula

Access the Copy Formula page (click Copy Formula on the Define General Properties page).

Copy a flexible formula ID from one scenario to another by specifying the Scenario ID and Flexible Formula ID for the target. The target Activity ID, Scenario ID, and Flexible Formula ID default to the same Activity ID, Scenario ID, and Flexible Formula ID as the source; however, you can override the Activity ID and Scenario ID by selecting from the dropdown list. Scenarios in the target dropdown list are restricted to those within the same activity as the source scenario.

You can specify the same Flexible Formula ID for the target as the source Flexible Formula ID if you are coping from one scenario to a different scenario. But if you are copying within the same scenario, then you must enter a different Flexible Formula ID.

The system checks whether the flexible formula you are copying uses any sources in its expression. If there are any sources in the expression, the system checks whether each source already exists for the business unit, model, activity and new scenario. If it does not, then the system copies the flexible formula source into the new scenario. Since copying a flexible formula copies the sources into the target line item and target scenario, before you copy a flexible formula from one line item to another, make sure all expression components are mapped to a valid flexible formula source.

Note. The Copy Model feature which copies an existing planning model to a new model, also copies all flexible formulas used within that model.

See Copying Existing Planning Models.

Copying a Group of Flexible Formulas

Access the Flexible Formula Group Copy page (Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flex Formula Group Copy).

This page enables you to copy flexible formulas of a given model/activity/scenario combination to another activity/scenario within the model.

The target activity and scenario default to the source activity and scenario, but you can specify a different activity and scenario as the target for the flexible formulas.

The Function Evaluator grid contains all of the flexible formulas that are defined in the source activity/scenario. Click the Select check box for each formula you want to copy.

Assigning the Flexible Formula

Once you have created a flexible formula and saved it, you have the option of associating it with a destination.

Navigate to the Assign Planning Method Defaults page to assign the FLEX method and Flexible Formula ID to an account or set of accounts. When you are using the Additional Dimension option, you can also associate a defined flexible formula using the second dimension; account dimension is always required and available. For a detailed discussion of the Assign Planning Method Defaults page please see 'Building the Planning Model' chapter.

When you are using a Flexible Formula ID that uses a driver ID, be sure to include that Method Driver ID in the Planning Method Group under the FLEX method. Assign any attributes (for example, fiscal year, lookup tables, and amounts) that apply to the driver ID, which in turn is used by the Flexible Formula ID.

All formulas are automatically staged if they are defined prior to the stage process. You need to run the stage formula option only if formulas are revised or created post stage. For a discussion of the staging process see the discussion on Scenario Manager and Process Summary in 'Building the Planning Model' chapter.

See Establishing Planning Method Groups.

See Using Model and Scenario Manager.

See Assigning Planning Method Defaults to Line Item Activities.

Assigning FLEX to Multiple Dimensions

When assigning a FLEX ID using the Assign Planning Methods Default page, you attach or assign the FLEX ID to an account or range of accounts. There is also an option to assign the FLEX ID to an additional dimension – such as a cost center or range of cost centers. Assigning a FLEX ID to both an account and an additional dimension creates fairly specific default criteria (rules) where the FLEX ID is to be applied. When the assignment cannot be specified in sufficient detail using account and the additional dimension, then consider assigning the FLEX ID using row-level assignments, or allow user-overrides at the row level. The method defaults are dynamic (rule-based) and will update when new accounts or additional dimension members are added to the Planning & Budgeting application, whereas the row-level assignments are static.

Overriding Controls

After a formula is assigned to an account, the coordinator can still define whether to allow override of the method and/or formula ID or not for the specified account (and additional dimension, if defined). For example, you can assign a formula to an account on the Override Controls page, and check the Method box leaving the method unlocked. The preparer, when editing their planning center for the corresponding activity scenario, can then override the formula for any individual line item using that account. For a detailed discussion of the Override Controls page, see the 'Building the Planning Model' chapter.

See Assigning Planning Method Defaults to Line Item Activities.

Updating the Planning Model when You Edit Flexible Formulas

FLEX IDs change over time and need to be maintained. For instance, changes to the expression (operators, sources and drivers) or the addition of a new FLEX ID require that you update the Planning & Budgeting model. The Scenario Manager tracks whether there have been updates to the flexible formulas associated with line item activities and whether or not the planning model needs to be updated to accept the changes. To update the planning model, change any affected line item activity scenario status to On Hold and select 'Stage Formulas' from the Scenario Manager page. After staging the formulas, set the line item activity scenarios back to the Released status to allow users update access. Note that this formula stage process does not perform a calculation. Furthermore, some formulas may require the working version to be copied to master in order to take effect and display the right numbers in a related line item activity, for example when formulas cross planning centers or line item activities. Model-wide calculations are done via the Model Recalculation batch process. To update drivers select the Refresh Driver Parameters batch process.