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.

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 scenarios.

Process Flow

To create a flexible formula:

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.
Define the general properties of the formula. You can also copy from an existing formula.
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.
Validate the formula.
Review the formula.
Assign the flexible formula ID as a method default in the model.
If necessary, rework the formula, or create a new formula.
If your line item activity and scenario have already been staged, go to the Scenario Manager and stage the new or revised formula.

Terminology

Following are commonly used terms:

Term	Definition
planning activities	A named unit of work that is defined by dimensional boundaries and specifications (for example, salary planning activity). A formula target is always associated with a single activity, but in some cases can reference values (sources) from other planning activities.
destination	The destination of a formula resolved calculation—that is, the formula's result. The target can be a single destination or a destination range.
source	The inputs (arguments) used to derive targets. Can be specified as either a multidimensional area of data (total salaries for North America Budget FY04) or a constant value (currency amount, statistic, percentage, etc.) or a combination of both.
operants/operators	Mathematical constructs that can be combined to manipulate source data into target data; for example, (Source 1 plus Source 2) multiplied by 3 = Target A.
unary operators	A single mathematical operator used against one source. Used in aggregations and rollups. Can be a formula (for example, Target B = minus 5).
bi-directional calculations	When formulas reference both to and from the same pair of activities, bi-directional calculations can potentially create circular formula dependencies.
nesting	Used to specify the calculation order within a single formula. Typically, a formula's calculation order is derived by the operand types used in the formula. Parenthesis are used to specify the calculation order. The innermost sources enclosed in multiple parentheses are calculated first.
Boolean logic	Also referred to as conditional logic. It provides the conditions around when and how a formula is calculated. It follows an 'IF, THEN ELSE' type of logic. Additional operands such as 'greater than' , 'less than', 'equal to', 'AND', 'OR' and various combinations of these operands support the definition of the condition.
functions	Pre-built formulas that are frequently used and/or support standard accepted methods. To tailor function to your specific use, the editor provides required and optional inputs (arguments) so that the function can calculate a result.
arguments	Used in functions to calculate a result. For example, a financial function to calculate interest payments needs to have the following arguments: interest rate, principle amount, term of the loan.

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.

Drivers can be defined 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.

Pages Used to Define a Flexible Formula Source

Page Name	Object Name	Navigation	Usage
Flexible Formula Source	BP_FF_SRC_DIM	Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flexible Formula Sources	Define data sources for flexible formulas by planning model and activity scenario.
Dimension Member Selection	BP_FF_SRC_MEMSET	Click the Selected Members link next to a dimension that uses the Multiple Members selection option.	Define dimension members by using a tree or entering a range of values.
Copy Source	BP_FF_SRC_COPY	Click the Copy Source button on the Flexible Formula Source page.	Copy an existing formula source to create a new source.

Creating a Flexible Formula Source

Access the Flexible Formula Source page.

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.

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 drop-down 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.

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.

Dimension and Selection Option

For each listed dimension specify the selection option. The default is Same as Target.

Examples of the Selection Options include:

Selection Option	Result
All Members	Sources all values associated with the dimension.
Single Member	Allows you to define one value used as the source. You enter this member under the last column called From Value.
Same as Target	Indicates that the source value/member is the same value as the target or destination when the formula is used. This is the default value.
Multiple Members	Requires you to define which values are to be used as the source. Click the selected members in the Selected Members column to access the Dimension Member Selection page.

From Value	Define the source value for the Single Member option.
Selected Members	For theMultiple Members option, click the Select Members link to access the Dimension Member Selection page.

Selecting Dimension Members for the Flexible Formula Source

Access the Dimension Member Selection page.

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

From Value and To Value	Fields display when you select the By Value option for member selection. Specify a range or values for the selected members. Add rows as needed.
Tree Node	Fields display when you are using the By Tree option for member selection. Pick values from the tree that you want to use as the source.

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

Copying a Formula

Access the Copy Source page.

Enter a unique ID to create a new flexible formula source which is a replica of the existing source.

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.

Pages Used to Define Flexible Formulas

Page Name	Object Name	Navigation	Usage
Define General Properties	BP_FF_GENERAL_PROP	Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flexible Formulas. Click step 1 in the Flexible Formula Wizard when editing an existing formula.	Define general properties of a flexible formula for line item activity scenario in a planning model.
Define Expression	BP_FF_EXPR	Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flexible Formulas. Click step 2 in the Flexible Formula Wizard when editing an existing formula.	Define the expression for calculation of the formula.
Review	BP_FF_REVIEW	Planning and Budgeting, Planning and Budgeting Setup, Setup Model, Flexible Formulas. Click step 3 in the Flexible Formula Wizard when editing an existing formula.	Review the flexible formula and make changes as necessary.
Save Confirmation Page	BP_FF_SAVE	Click Save on the Review page.	Choose one of three options: create another formula, continue editing the current formula, or go to the Assign Planning Method Defaults page.

Defining General Properties of a Flexible Formula

Access the Define General Properties page of the Flexible Formula Wizard.

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.

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.

Formula Expression	Type your expression directly into the expression box, or use the available insert and operator buttons to insert into the expression box. The expression builder supports all mathematical and comparison operators as well as the Boolean operators like IF, AND, OR, and NOT.
Type	Use the Type drop-down field and associated lookup prompts to incorporate into the expression a Driver, Function , or Source.
Insert into Expression	Use to append the function or driver or source at the end of the text in the expression box. To minimize errors, the preferred method for inserting a function into the expression box is to look up the function and use the insert button, rather than typing it in.
Create New	Use to define a Driveror Source type that does not exist. Alternatively, you can define the new driver or source after entering it into the expression, by going to the Expression Components grid at the bottom of the page.
Validate	Click to verify that the expression parameters and syntax are valid, that the built-in function, if you have inserted one into the expression, exists, and that the type of function parameters are properly bound.

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.

Component	This system displays the unresolved components found in the expression builder.
Type	Use the drop-down field to designate the Source or Driver for this component.
ID	Use the lookup prompt to designate the ID for this source or driver.
Description	Click to go to the Method Driver page where you can view and edit the driver, or to the Flexible Formula Source page where you can view and edit the source.
Create New	If you don't find a relevant source or driver in the prompt list, click to a create new one. For a source type, the system takes you to the Flexible Formula Source page where you can define a new source (see Flexible Formula Source page discussion above). For a driver type, the system takes you to the Method Driver page where you can define a new driver ID.

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.

Financial functions	FV (Future Value) IRR (Internal Rate of Return) NPER (Number of payment periods) NPV (Net Present Value) PMT (Payment required) PV (Present Value) RATE (Rate required) SLN (Straight Line Depreciation) SYD (Sum-of-the-Years-Digits) DDB (Double Declining Balance)
Mathematical functions	ROUND (Round to the nearest whole number) TRUNC (Truncate)
Looking backwards and forwards functions (Restricted to time dimension.)	NEXT (Next) PREV (Previous)
Conditional function	IF (If statement)

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.

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).

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 this 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.

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.

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.

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.