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

Page Name	Definition Name	Navigation	Usage
Define General Properties	BP_FF_GENERAL_PROP	select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select 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	select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select 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	select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select 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	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.
Copy Formula	BP_FF_COPY	Click Copy Formula on the Define General Properties page.	Copy the formula by replicating it to a new flexible formula ID, which you can then modify as needed.
Flexible Formula Group Copy	BP_FF_GRP_COPY	select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select Flex Formula Group Copy	Copy a group of flexible formulas.

Define General Properties Page

Use the Define General Properties page (BP_FF_GENERAL_PROP) to define general properties of a flexible formula for line item activity scenario in a planning model.

Navigation

select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select Flexible Formulas.

Click step 1 in the Flexible Formula Wizard when editing an existing formula.

Image: Define General Properties page

This example illustrates the fields and controls on the Define General Properties page. You can find definitions for the fields and controls later on this page.

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.

Define Expression Page

Use the Define Expression page (BP_FF_EXPR) to define the expression for calculation of the formula.

Navigation

select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select Flexible Formulas.

Click step 2 in the Flexible Formula Wizard when editing an existing formula.

Image: Define Expression page

This example illustrates the fields and controls on the Define Expression page. You can find definitions for the fields and controls later on this page.

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 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 Driver or 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 dropdown field to designate the Source or Driver for this component.

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.

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.


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) STTD (Summarized Total to Date) SYD (Sum-of-the-Years-Digits) DDB (Double Declining Balance) TTD (Total to Date)
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)

Review Page

Use the Review page (BP_FF_REVIEW) to review the flexible formula and make changes as necessary.

Navigation

select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select Flexible Formulas.

Click step 3 in the Flexible Formula Wizard when editing an existing formula.

Image: Review page

This example illustrates the fields and controls on the Review page. You can find definitions for the fields and controls later on this page.

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

Copy Formula Page

Use the Copy Formula page (BP_FF_COPY) to copy the formula by replicating it to a new flexible formula ID, which you can then modify as needed.

Navigation

Click Copy Formula on the Define General Properties page.

Image: Copy Formula page

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

Flexible Formula Group Copy Page

Use the Flexible Formula Group Copy page (BP_FF_GRP_COPY) to copy a group of flexible formulas.

Navigation

select Planning and Budgeting, then select Planning and Budgeting Setup, then select Setup Model, then select Flex Formula Group Copy

Image: Flexible Formula Group Copy page

This example illustrates the fields and controls on the Flexible Formula Group Copy page. You can find definitions for the fields and controls later on this page.

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.

Activity

Specify the target activity ID.

Scenario

Specify the target scenario ID.

Flexible Formula ID

The formula ID in the source activity/scenario.

New Flexible Formula ID

Enter a new ID for the formula. If you are copying the formula to the same activity/scenario, you must enter a new ID, otherwise you can use the original ID or change it, depending on your requirements.

Copy Formula

Click to copy the selected formulas to the target scenario and activity. The Formula Status field is updated when the copy is complete.

Formula Status

This field is initially blank. It updates when a formula is copied. Values are:

Valid: Indicates the copy was successful, and the formula is valid.

Invalid: Indicates the copy was not successful. The following conditions cause an Invalid formula status:

Copying a flexible formula between activities that have different planning center dimensions.
Copying a flexible formula with an invalid source or driver.