This chapter describes the Detail Cash Flow Audit Options, which enable you to view daily cash flow results.
Topics:
· Overview of Detail Cash Flow Audit Options
· FSI_O_PROCESS_CASH_FLOWS Table
· The Detail Cash Flow Process
By selecting the Detail Cash Flow option in the Asset Liability Management (ALM) Process rule (the Audit block in Oracle ALM and Oracle Funds Transfer Pricing (FTP), you can view the daily cash flow results. Selecting this option writes out all cash flow and repricing events that occur for processed records. The number of records written is determined within the Audit block of both ALM and FTP processes.
NOTE:
If you are running with multiprocessing enabled, you may get fewer records, for example, FSI_PROCESS_ID_STEP_RUN_OPT.NUM_OF_PROCESSES >1. In this case, only the first process will write detailed cash flows.
The relevant financial elements for each instrument record and the cash flow results are stored in the FSI_O_PROCESS_CASH_FLOWS table.
Column |
Description |
|---|---|
Result Sys ID |
The key column that corresponds to the Sys ID Num of the Process rule. All cash flow results for the processing run are stored with the same Result Sys ID value. See Determining the value of the Result Sys ID. |
Calc Source Cd |
The type of cash flow processing (selected in the Process Rule): 0 - ALM - Regular (scenario based processing) 1 - ALM - Stochastic processing 2 - TP - Regular 3 - TP - Forward Rates 4 - TP - Stochastic processing 5 – TP – Adjustments (Economic Cost) 6 - TP – Adjustments (Liquidity Premium) 7 - TP – Adjustments (Basis Risk Cost) 8 - TP – Adjustments (Pricing Incentive) 9 - TP – Adjustments (Other) |
Record Sequence |
The processing order of the records. |
Cash Flow Sequence |
The order of the events (cash flows, repricing events). |
Scenario Num |
The scenario number assigned in the Forecast Rates rule for scenario-based processing or the rate path number determined in stochastic processing. |
Financial elem ID |
The numeric code describing a piece of financial information described in the row of result data. |
ID Number |
The unique record identifier for the instrument records processed. |
Cash Flow Date |
The date of the event. |
Cash Flow Cd |
The code identifying the type of event. |
Float Value |
The value assigned to each financial element. |
Product Leaf Node |
The product leaf number. |
Org Leaf Node |
The organizational unit leaf member, an optional output dimension in Oracle ALM and always populated in Oracle FTP output. |
Iso Currency Cd |
A currency associated with cash flow values. |
After processing cash flows from either Oracle ALM or Oracle FTP, do the following to view the audit results:
· Determine the value of Result Sys ID
· View data by:
Navigating to Oracle ALM Business Intelligence and viewing results in the Detail Cash Flow Audit Report
Or you can use SQL Developer or a similar SQL query tool. For example, you can write the following SQL statement to view cash flow results:
SELECT * FROM FSI_O_PROCESS_CASH_FLOWS WHERE RESULT_SYS_ID = <your SysID number> ORDER BY SCENARIO_NUM, RECORD_SEQUENCE, CASH_FLOW_DATE, FINANCIAL_ELEM_ID;
1. Navigate to the Process type that you want to query.
2. Select the folder and Process name.
3. Edit or View the rule.
4. At the bottom of the page, there is an Audit Section which contains the Sys ID Number.
5. Alternatively, on the summary page for the selected process type, you can mouseover the Process Name and the Sys ID and Description will appear.
The Financial Element ID column lists the financial elements written for each payment and repricing event processed by the cash flow engine. An initial set of data is also written, recording the balance and rate as of the last payment date. The base set of financial elements written during a cash flow audit process may include some or all of the following:
Financial Element |
Description |
|---|---|
Payment Events |
|
100 |
Ending Par Balance. The final balance on the payment date, after the payment, has occurred. |
430 |
Interest cash flow. |
210 |
Total Principal Runoff, including scheduled payments, prepayments, and balloon payments. |
60 |
Beginning Par Balance. Starting balance and payment date, before payment. |
120 |
Runoff Net Rate. The rate at the time of payment, weighted by ending balance. To view the actual rate, divide financial element 120 by financial element 100. |
490 |
The discount rate used to determine the present value of cash flow on the payment date. |
Repricing Events |
|
250 |
Par Balance at the time of repricing. |
280 |
Before Reprice Net Rate. Rate Before repricing, weighted by repricing balance. To determine the true rate, divide this financial element value by financial element 250. |
290 |
After Reprice Net Rate. Newly assigned net rate after repricing occurs, weighted by repricing balance. To determine the true rate, divide this financial element value by financial element 250. |
Initial Event |
|
100 |
Initial par balance at the start of processing. |
120 |
The initial net rate at the start of processing. |
In addition to these financial elements, other data may be output, depending on the type of processing and the optional financial elements selected. For more information on financial elements, see OFSAA Financial Elements.
The Cash Flow Code column lists a code for each row that describes the event modeled by the cash flow engine:
Cash Flow Code |
Description |
|---|---|
1 |
Initial recording of balances and rates |
2 |
Payment event only |
3 |
Initial recording of balance and rates and maturity of the balance. |
12 |
Reprice during the teasing period. |
14 |
Reprice and payment during tease period |
20 |
Reprice event only (not during tease period) |
22 |
Reprice and payment event together (not during tease period) |
28 |
Reprice event on the teaser end date. |
30 |
Reprice and payment event on the teaser end date. |
32 |
Event on As of Date (Effective Date). Applicable for record with Interest in Advance. |
35 |
Event for a fixed record having Interest Type as Interest in Advance on Origination date, where origination date is the same as Last Payment and Next Payment Date. |
55 |
Event for an adjustable record having Interest Type as Interest in Advance on Origination date, where origination date is the same as Last Payment and Next Payment Date. |
NOTE:
The cash flow code of 3 is seen on an unlikely event where instrument Originates and Matures on the same date, in future, after As of Date.
Cash Flow code of 35 and 55 is seen for Forwarding Rate Agreement or for forward starting record with a similar condition where Interest type is in advance (INT_TYPE=2), and Origination Date is the same as Last Payment and Next Payment Date.
You can copy the results from the Process Cash Flows table (FSI_O_PROCESS_CASH_FLOWS) and paste them into a spreadsheet to facilitate analysis against validated data. If the cash flows do not behave as expected, examine instrument table data or your assumptions. For more information, see the Cash Flow Calculation Process.