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

Page Name	Definition Name	Navigation	Usage
Flexible Formula Source	BP_FF_SRC_DIM	select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select 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.

Flexible Formula Source Page

Use the Flexible Formula Source page (BP_FF_SRC_DIM) to define data sources for flexible formulas by planning model and activity scenario.

Navigation

select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select Flexible Formula Sources

Image: Flexible Formula Source page

This example illustrates the fields and controls on the Flexible Formula Source page. You can find definitions for the fields and controls later on this 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 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 Data Source Page.

See Considerations When Creating 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.

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.

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.

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

Use the Dimension Member Selection page (BP_FF_SRC_MEMSET) to define dimension members by using a tree or entering a range of values.

Navigation

Click the Selected Members link next to a dimension that uses the Multiple Members selection option.

Image: Dimension Member Selection page

This example illustrates the fields and controls on the Dimension Member Selection page. You can find definitions for the fields and controls later on this 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.

Note: You can select the dimension members by node, but if there are many members associated with this node it could impact processing. Anytime there are many dimension members and/or nodes, this could impact processing.

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

Use the Copy Source page (BP_FF_SRC_COPY) to copy an existing formula source to create a new source.

Navigation

Click the Copy Source button on the Flexible Formula Source page.

Image: Copy Source page

This example illustrates the fields and controls on the Copy Source page. You can find definitions for the fields and controls later on this 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.