Setting Up Cash Forecasting

Define Time Sets Page

POS_TIME_SPAN_PNL

Configure time segments with corresponding start and end cycles and store associated time periods

Position Field Types Page

POS_FIELD_TYPE_PNL

Define user-friendly field names.

Forecast Mapping Page

CSH_FCST_GRP_BY

Set up dimensions for reporting cash forecasting numbers.

Position Source Set Page

SOURCE_SET_DEFN

Define position source sets, such as actuals, forecast, projected, or intraday.

Position Source Page

POS_SOURCE_TBL

Define position records, drill back details, and selection criteria for position sources.

Position Source Contacts Page

POS_SOURCE_CNTC

Associate source contacts with a Contact Type of Cash Forecast to a position source.

Drill Back Details Page

POS_SOURCE_KEYS

Define the method for drilling back to the source application, used to view details of a transaction from the Cash Forecasting Results page.

Selection Criteria Page

POS_SOURCE_SQL

Enter selection criteria for a source or regenerate SQL for several sources in batch.

Forecast Rules Page

CSH_FCST_RULES

Identify forecasting rules to shift or replace forecasting values.

Calculate Time Spans Page

TR_TSPAN_REQ

Run the Treasury Position Time Spans process (TR_POSN_TSPAN), which calculates time spans for a specified time set.

Create Position Source SQL Page

TR_CREATE_SQL

Enter the parameters and execute a process that creates the SQL defined for a position source.

Worksheet Definition Page

WORKSHEET_DEFN

Define how your cash forecast worksheet will look and the calculations that will be made, including reporting currencies, forecast rules, source sets, and variance attributes.

Worksheet Line Items Page

WORKSHEET_LINE_ITM

Define line items, position sources, and position source processing attributes for the worksheet.

Number

Indicates the sequential time segment that you are modifying.

Start

Select a start type to indicate how the system determines when a time segment starts. Values are:

• Defined by Period above: Starts the current time segment when the prior time segment ends. You cannot select this option for the first segment.

• Specific Date: Starts the current time segment on a specific date. When you select this option, enter the date in the Start Date field.

• Specific Period: Starts the current time segment on the first day of a specified period. When you select this option, select the calendar on which to base the period start.

• Today: Starts the current time segment today. Select this value when you want the time span to be based on a current date. Every time the system calculates the time span, this field is updated with the current date.

Cycle

Select a cycle type to indicate the repetition frequency of a time segment. Values are:

• Relative: Cycles the time segment a relative number of times. When you select this value, enter the number of times it repeats, and then enter the frequency of repetition in the Relative field. You can repeat a number of days, months, or years. For example, to have this time segment repeat daily for five days, you would enter 5 in the Number field, and select Days in the Relative field.

• Use Period Definitions: Cycles the time segment based on the period definitions from a business calendar. When you select this value, select the calendar on which to base this frequency. For example, suppose that you want this time segment to cycle by quarters. Select a defined quarterly calendar in frequency.

End

Use to control when a time segment ends. After completing the appropriate number of cycles, the following values are available to control the end of the segment:

• End of Next Cycle: Completes the time segment at the end of the next cycle. Select the calendar to use to determine the ending.

• End of Period: Completes the time segment when the period that is defined by the selected calendar ends. Select the calendar to use to determine the ending period.

• End of Previous Cycle: Completes the time segment when the previous cycle ends. Select the calendar to use to determine the ending period.

• Number of Periods: Completes the time segment after repeating the cycle a given number of periods. Enter the number of times the period should cycle.

Calculate

Click this button to generate the time span intervals. Your results appear in the Resulting Time Periods group box.

Record

Select to base the prompts on the actual database records. In the Record Prompt field, enter the field on which prompting is based.

Translate

Select to base prompting on translate values rather than the records themselves, and select the field name to use for translation. This value applies only to field records that use translate values.

Meta Field

Select to incorporate a SQL statement with this alias. (Typically, you do not select this check box.)

%THIS DEAL

Other

This Deal

None

Y

Alias name with meta text ¹

ACCOUNT

Bank Account

Account

Record

BANK_ACCTNUM_VW

Alias name with record prompt

CANCEL_STATUS

Other

Cancel Status

Translate

POS_XLAT_PROMPT

CANCEL_STATUS

Alias name with record prompt and field

AMOUNT

Amount

Amount

None

Alias name with only field type value

Destination Field

System-defined groupings include the following:

• Business Unit (BUSINESS_UNIT_BANK)

• Bank SetID (BANK_SETID)

• Bank Account (BANK_ACCT_KEY)

• Currency (CURRENCY_CD)

• Bank Counterparty (BANK_CD)

The system also provides seven generic Destination Field values:

• GRP_BY_FIELD1

• GRP_BY_FIELD2

• GRP_BY_FIELD3

• GRP_BY_FIELD4

• GRP_BY_FIELD5

• GRP_BY_FIELD6

• GRP_BY_FIELD7

You can map these fields to create groupings that represent a dimension to be displayed in your cash forecast results. For example, you might choose to map to a geographic region by country.

Alias Name

Select from user-defined Position Field Type values found on the Position Field Type page. Do not map to the alias names used by system-defined mappings.

Default Value

Enter a default value for each grouping field. If the source does not include a value for this field, the system uses the default value you define to group data.

SetID

Enter a SetID.

Description

Enter a description.

Source Set

Enter a category for grouping position source data.

Comments

(Optional) Enter comments.

Record

Select the database from which the position source derives data.

Deal View

Select to permit remapping of amount fields for different instruments.

Display Only

Select to Indicate that the source data is informational only and will not be included in worksheet calculations. Display-only position sources can be added to the worksheet definition, in the Non-Cash Flow Details grid on the Worksheet Line Items page.

Reverse Amount Sign

Select to reverse the sign on the amount from the source when the data are summarized.

Field Name

Displays available field names of the selected record.

Click the Position Field Types icon to define the sets of table fields that are available to the position management tool.

Click the Long Description icon to enter a comprehensive description for the field.

Default Value

Choose default values for the bank account key, bank code, bank SetID, business unit, and currency, in case the fields in the source are empty.

Contact ID

Enter the ID for a contact.

Name, Phone, Ext, and Email ID

Displays the contact’s name, phone, phone extension, and email ID, if available, from the contacts master table.

Drill to Source

Select one of the following methods for drilling to source:

• Component

• DoModalComponent

• External URL

• None

Menu Name, Component Name, and Target Record

If you choose to drill to source by component, you must specify the menu and a component name. Specify a target record if the component points to a generic search page that doesn’t go through a search. You must specify the target record in order to populate the correct fields.

URL Address

Enter a URL if you select the URL method of drilling to the source.

Source Fieldname

Enter the fieldnames from the record, and map them to the generic key fields in the Destination Field column.

Destination Field

Map delivered character, date/time, and number key fields to source fields, such as the amount, value date, and bank. You identify the destination key fields and the system extracts the information that will be stored on the detail table, which will be used when you drill down to the details.

Include in Drill

Select the mapped fields to be used for drilling back to the source.

Include in Rank

Select the mapped fields to be used for ranking the rows. A RANK() function is used to assign sequential numbers to the source detail result set. Provide sufficient Rank fields to identify a unique row in the group.

Create SQL

Click the Create SQL button to create the combined SQL statement from the various statements for different sets of criteria.

View SQL

Click the View All SQL to see the SQL that has been generated from the entered criteria. When the process finishes, a dialog box appears with the SQL statement.

Selection SQL ID

Create an ID for the selection criteria for a source.

Description

Describe the SQL Criteria.

Open

Select from blank, (, ((, (((, or ((((, depending on your SQL statement and proper SQL syntax.

Alias Name

Select the appropriate alias name.

Operator

Select an operator for your SQL statement.

String Value

Enter any string value or choose one using the lookup prompt.

Number Value

Enter a numeric value.

+/-

Enter an addition/subtraction operator.

Date Value

Enter a calendar date or choose one using the calendar prompt.

Date Increment

Enter an incremental value of days.

Close

Select from blank, ), )), ))), )))), depending on your SQL statement and proper SQL syntax.

And or Or

Choose from blank, AND, or OR.

Add row or Delete row

Click to add or delete the current row.

Rule ID and Rule Type

Define the rule by creating a rule ID, and specify a rule type for the rule. Options are:

• Amount

• Date

• Payment Method

• Replacement

• Worksheet Offset

Description

Enter a description.

Weighting

Set the percentage that you want to apply to this cash forecast rule. You can also weight worksheet line items on the Worksheet Line Items page.

For example, you use the weight of 1.1 to forecast a 10 percent increase or 110 percent of your current situation. Similarly, you use a weight of .9 for a 10 percent decrease or 90 percent of your current cash situation.

Measurement

Choose the Per Unit measurement option to enter a value in the Unit Price field.

Alias Name

Enter the alias name for another field from which you want to derive the amount.

Target Field

Select one of the available target record fields.

Unit Price

Enter a unit price value when you select the Per Unit measurement.

Process Frequency

Specify the frequency with which the process or job is to be run. Choose from Always, Once, or Never.

SetID

Enter a SetID.

Time Set and Description

Enter the time interval from sample data delivered with your system, or that you defined on the Define Time Sets page.

Process Frequency

Specify the frequency with which the process or job is to be run. Choose from Always, Once, or Never.

SetID

Enter a SetID.

Position Source ID and Description

Identify the record that will be used as the base when building the SQL to retrieve the impacted rows of data according to the search criteria. The SQL will retrieve distinct records for the main source record primary key.

Print Worksheet

Click this link to preview the worksheet definition and print the worksheet.

The process generates Worksheet Definition Report (TRX5100).

Copy Worksheet

Click this link to open the Copy Worksheet page, where you can specify a new worksheet ID and create a copy of the active worksheet definition.

Allow Manual Updates

To enable locking, select the Allow Manual Updates check box. If a worksheet is locked, the value does not change if the cash forecast is recalculated. It stays as it is until it is unlocked.

Variance Alerts Percents

Set up threshold levels of variance between two source sets. You can choose to show a variance that’s acceptable as well as levels for displaying a warning or an alert.

There are three alert statuses you can use to indicate variance levels on the Cash Forecast Results page. Enter a numerical value to trigger an alert status as follows:

• Acceptable Percent: A variance value that is equal to or higher than the acceptable percent (always zero) but less than the warning percent will trigger a green circle acceptable status.

• Warning Percent: A variance value that is equal to or higher than the warning percent but less than the alert percent triggers a yellow triangle warning status.

• Alert Percent: A variance value that is equal to or higher than the alert percent will trigger a red square alert status.

Reporting Currencies

Define at least one and up to three different currencies for the cash worksheet. You must specify one currency as the default. This will be your position currency. Reporting currencies will be one of the parameters you can use to display your cash forecasting results on the Cash Forecasting Results page.

Position Source Sets

Enter one or more categories for grouping position source data that you defined on the Position Source Set page. You must specify one position source set as the default.

Carry Total Forward

Select the check box for an individual source set to populate the total carried forward row for line items, when viewing by Time Set on the Cash Forecasting Results page.

Forecast Rules

(Optional) Choose one Amount rule and/or one date type rule (Date or Payment Method). You can associate a maximum of one amount rule and one date type rule per worksheet definition.

Variance Definitions

In order to define variance for comparisons, you must have more than one source set associated with the worksheet. You define variance levels in the Variance Alert Percents group box.

The variance percent is always calculated as follows:

The Cash Position Variance Report shows the variance amount and variance percent between source sets that are defined on the Worksheet Definition page. You must provide variance definitions on the worksheet template to generate variance results.

See Cash Position Variance Report Page.

Source Set 1, Source Set 2, and Description

Enter source sets against which to calculate a variance between the two. You must also enter a description for each pair of source sets.

Line Item

Enter an item category that determines the characteristic of the line you want to show in your cash forecasting results.

Position Source Set

Enter a category for grouping position source data that you defined on the Position Source Set page.

Source

Identify a source record to be used in the search criteria. It will be part of the FROM statement of the SQL that will be generated to retrieve cash forecasting data.

Weighting

Enter a weighting value for the worksheet line items.

You can add a weighting value to the cash forecast by defining a forecast rule weighting value on the Forecast Rules page and then applying the rule to the worksheet on the Worksheet Definition page.

Selection SQL ID

Enter an ID for the selection criteria for a source that you defined on the Selection Criteria page.

Replacement Rule

Enter a rule that you defined on the Forecast Rules page.

Line Item

Enter an item category that determines the characteristic of the line you want to show in your cash forecasting results.

Position Source Set

Enter a category for grouping position source data that you defined on the Position Source Set page.

Source

Identify a source record to be used in the search criteria. It will be part of the FROM statement of the SQL that will be generated to retrieve cash forecasting data.

Weighting

Enter a weighting value for the worksheet line items.

You can add a weighting value to the cash forecast by defining a forecast rule weighting value on the Forecast Rules page and then applying the rule to the worksheet on the Worksheet Definition page.

Selection SQL ID

Enter an ID for the selection criteria for a source that you defined on the Selection Criteria page.

Replacement Rule

Enter a replacement rule that you defined on the Forecast Rules page.