This chapter describes Allocation Specification functionality.
· Allocation Specification Summary & Detail Screens
· Navigation within the Allocation Specification Summary Screen
· Navigation within the Allocation Specification Detail Screen
§ Initial Definition Process Tab
§ Source Process Tab
§ Operator Process Tab
§ Driver Process Tab
§ Outputs Process Tab
§ Review Process Tab
· Allocation Examples
Upon initially navigating to Profitability Management > Rule Specification > Allocation Specification, a summary screen is displayed showing a set of Allocation rules. Using search criteria, you can control the set of Allocation rules that are displayed. When you Add, Edit, or View a rule, a detailed screen is displayed.
When you first navigate to the Allocation Specification summary screen, the allocations stored within your current default Folder are presented in a summary grid. The Allocation Specification summary screen has two containers: Search and Allocation Specification.
Your default Folder functions as a search constraint. The value of your default Folder is set in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability. You may select a different Folder or you may remove the Folder constraint entirely by selecting the “blank” Folder, i.e., no Folder. You may also search by Allocation Name or by Allocation Type.
Figure 71: Allocation Specification Search Screen
· Search Control: You may search for Allocation rules by Folder, by Allocation Name, or by Allocation Type. Enter your desired search criteria and click on the Search control.
· Reset Control: Restores the default Folder, removes any Allocation Name or Allocation Type constraint you may have specified, and refreshes the screen.
The Allocation Specification container presents a grid containing all of the Allocation rules that meet your search criteria. The Allocation Specification summary grid offers several controls that allow you to perform different functions when an Allocation rule is selected. To select an Allocation rule, click on a check box in the first column of the grid. More than one Allocation rule can be selected at a time but this will cause some of the controls to become disabled. Clicking on a checkbox a second time de-selects the Allocation rule.
You may select or deselect all of the Allocation rules in the summary grid by clicking on the check box in the upper left-hand corner of the summary grid directly to the left of the Name column header.
· Add: Clicking on the Add control begins the process of building a new Allocation rule. The Add control is disabled if any rows in the grid have been selected.
· View: Selecting a single row out of the grid enables the View control. Clicking on the View control allows you to view the contents of an Allocation rule on a read-only basis. The View control is only enabled when a single allocation rule has been selected.
· Edit: Selecting a single row out of the grid enables the Edit control. Clicking on the Edit control allows you to modify a previously saved Allocation rule. The Edit control is only enabled when a single allocation rule has been selected.
· Delete: Selecting one or more rows out of the grid enables the Delete control. Clicking on the Delete control deletes the rules you have selected.
· Copy: Selecting a single row out of the grid enables the Copy control. Clicking the Copy control allows you to create a copy of an existing Allocation rule. The Copy control is only enabled when a single Allocation rule has been selected. When you click on Copy, a Save As pop window will appear. Click Save after entering the Name, Description, Folder, and Access Type Details.
· Run: After choosing a single row out of the grid, clicking on the Run control allows you to execute the selected Allocation rule. The As-of-Date that applies to the rule execution is visible in the upper right corner of the screen; it may be reset in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability. The Run control is only enabled when a single allocation rule has been selected.
· View Dependencies: Clicking on the View Dependencies control generates a report on any rule objects that depend on the Allocation rule you have selected. The View Dependencies control is only enabled when a single allocation rule has been selected.
The following columns categorize each allocation rule in the summary Pane:
Figure 72: Allocation Specification Summary Pane
· Name: Displays the Allocation rule's short name. Performing a “mouse-over” on an Allocation Name displays the Allocation rule's long name and system identifier.
· Creation Date: Displays the date and time at which an Allocation rule was created.
· Created By: Displays the name of the user who created an Allocation rule.
· Allocation Type: Displays an Allocation rule type. Supported rule types are:
§ Constant
§ Static Driver
§ Leaf
§ Field
§ Dynamic Driver
§ Static Driver Table
§ Lookup Driver Table
· Last Modified Date: Displays the date and time at which an Allocation rule was last modified.
· Modified By: Displays the name of the user who last modified an Allocation rule.
· Access Type: Displays the “Read/Write” or “Read Only” property of an Allocation rule. Only the creator of a rule may change its Access Type.
· Status: Before having executed an Allocation rule for the first time, the Status is blank. After having executed an Allocation rule, the words “View Log” are displayed as a hyperlink. Clicking on the View Log hyperlink opens a log viewer enabling you to view the execution log for the Allocation rule's last run.
When you Add, Edit, or View an Allocation rule, the Allocation Specification Detail Screen is displayed. The detail screen is composed of 6 process tabs that are described below. The appearance of the detail screen depends on which tab is active. Regardless of which tab is currently active, the Process Tabs container and the Allocation Rule Definition container are always the first two containers displayed. Other containers shown depend on which tab is currently active. The Audit Trail container is a standard footer container for every OFSAA rule type. It displays Created By, Creation Date, Last Modified By, and Modification Date on the Audit Trail tab. The User Comments tab may be used to add comments to any rule, subject to a maximum of 4000 characters.
Each of the 6 process tabs is designed to create, edit, or view different components of an allocation's specification. You may navigate from any tab to any other tab at any time. The 6 process tabs are:
· Initial Definition
· Source
· Operator
· Driver
· Outputs
· Review
Like the Process tabs container, the Allocation Rule Definition container is always displayed regardless of which process tab is active. The Allocation Rule Definition container, however, is active only on the Initial Definition tab; it is displayed in a “read-only” format in every other process tab. Its usage on the Initial Definition tab is described below.
Figure 73: Allocation Rule Definition Container
Regardless of which tab is active, you may always return to the Initial Definition table to rename a rule, change its description, its Folder, or its Access Type.
The Initial Definition process tab allows you to specify Rule Name, Rule Description, Folder, Access Type, and Allocation Type.
Specify the Allocation Rule Name and Description, select a Folder in which the Allocation rule is to be stored, and specify whether you want the Allocation rule to be “Read/Write” or “Read Only” (Access Type). Naming your Allocation rule is required before it can be saved. Default values for Folder and Access Type are stored in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability.
Legal Entity is an optional run-time parameter. If the "Disable Legal Entity" checkbox in the application preferences screen is checked, then Legal Entity will no longer be a run-time parameter.
If the Legal Entity is disabled, then while defining a Variable Allocation rule user will have to select a value for the Legal Entity dimension in all applicable tabs in the allocation specification screen.
Figure 74: Allocation Rule Definition
For the already defined Variable Allocation rules, the user should edit the allocations and modify the value of the Legal Entity dimension in all applicable tabs in the allocation specification screen.
Figure 75: Allocation Source Pane
The value of Legal Entity used when your rule executes is specified within your batch definition (for batch processes) or is obtained from your Oracle Insurance Allocation Manager for Enterprise Profitability Applications Preferences (for interactive executions launched from a Summary screen). If this check box is not selected on the Initial Definition Process tab, then you must specify a value for Legal Entity in your allocation rule's Source, Driver, and Outputs.
NOTE:
Legal Entity is designed to support implementations that require multi-entity or multi-tenant functionality. If your implementation does not require this functionality, you may utilize the Default Legal Entity in all your processes and you may declare all of your allocation rules to be Variable. For details, see Management Ledger.
Figure 76: Allocation Specification (New) - Initial Definition
When you initially build an Allocation rule, you must select its Allocation Type. Once an Allocation rule has been saved, you may no longer change its type. After you have chosen an Allocation Type on the Initial Definition process tab, the appearance of subsequent process tabs will depend upon the Allocation type you have chosen. The available rule types are Constant, Static Driver, Leaf, Field, Dynamic Driver, Static Driver Table, and Lookup Driver Table.
Most Allocation rules distribute or aggregate balances using driver data. Examples include:
· Expense allocations as a function of square footage occupied or headcount
· Aggregation of instrument balances to the Management Ledger
· Reclassification of Management Ledger balances to dimensions not found in the original General Ledger data.
Drivers can be stored as components of your overall allocation model or they can be stored as facts within your business data. Headcount and square footage statistics, for example, are frequently stored as memo accounts within your General Ledger. When you load the OFSAA Management Ledger table with your General Ledger data, those headcount and square footage statistics may be utilized as drivers within your allocation rules. These kinds of business-data resident driver are referred to as Dynamic Drivers.
In other cases, you will embed your driver data into an Allocation rule or into a driver table that the allocation engine supports. These kinds of drivers are referred to as Static Drivers. Oracle Insurance Allocation Manager for Enterprise Profitability supports three kinds of rules that use static drivers: Static Driver, Static Driver Table, and Lookup Driver Table.
Dynamic Drivers often have many advantages over Static Drivers. An Allocation rule that uses a static driver will take the same value or values every time you use it in a rule, but an Allocation rule that uses a dynamic driver may have different driver sets from day-to-day or month-to-month. Additionally, whereas you must normally pre-compute your static drivers, dynamic drivers are generated at run time. Dynamic drivers, frequently used in full cost-absorption allocation models, are often generated by other allocation rules.
Static drivers sometimes have advantages over dynamic drivers. For example, you may have pre-computed unit costs that you wish to use to drive your allocations to generate partial absorption costing.
Allocation types are described in detail below.
· Constant: A Constant Allocation rule creates a simple balanced transaction consisting of one debit and one credit. You may optionally specify either one debit or one credit (at a minimum, you must supply at least one debit or one credit). The Constant rule type only operates against the Management Ledger. For Constant allocation rules, the Operator and Driver process tabs are disabled; you need only specify a fixed amount in the Source tab and debit and/or credit in the Outputs process tab.
· Static Driver: The Static Driver method enables you to perform simple factor calculations against a set of source balances. Source balances may be drawn from the Management Ledger table, Instrument tables, or Transaction Summary tables. For Static Driver rules, the Driver process tab is disabled. For this kind of rule, you define where to get your source data on the Source process tab, a static driver amount on the Operator process tab, and the resulting debits and/or credits on the Outputs process tab.
· Leaf: Leaf type allocations are used only against the Management Ledger table. They are typically used to operate two sets of rows that differ in a single dimension.
· Field: A Field type allocation is typically used to multiply two columns within a single row in an instrument table update Allocation rule.
· Dynamic Driver: Typically, Dynamic Driver allocation rules aggregate or distribute balances using dynamic data (business resident driver data) such as headcount, square footage, or instrument-level balances. Dynamic Driver data, however, need not be limited to statistics you may have sourced as part of your ETL load to the OFSAA data model. Dynamic Driver data can be “captured” or developed within an Allocation rule. For example, balances by product within each cost center can normally be obtained from your instrument data. You can build allocation rules to aggregate these statistics from your instrument-level data and post them to your Management Ledger for use in subsequent rules, or you can write an allocation that develops this set of driver data by querying your instrument data at runtime. Driver data obtained from your instruments need not be limited to balances. Examples of instrument-level dynamic drivers you might use in allocation rules include:
§ Number of accounts by product by cost center by year of origination
§ Number of loan payments processed by loan processing center by month
§ ATM transaction counts by region by month
The most common distribution method for the Dynamic Driver type of allocation is the “Percent-to-Total” or “Force to 100%” method. In addition to the Force to 100% method, Oracle Insurance Allocation Manager for Enterprise Profitability also supports a Simple method and a Uniform method. Dynamic Driver allocation rules and methods are described in more detail in the sections entitled Driver Process Tab and Output Process Tab.
· Static Driver Table: Static Driver Table allocation rules offer functionality similar to Dynamic Driver allocation rules but use driver data that is stored in the Oracle Insurance Allocation Manager for Enterprise Profitability rule type called Static Table Driver. See Static Table Drivers for a guide to building Static Table Driver rules and for additional explanation of their use.
· Lookup Driver Table: Lookup Driver Table allocation rules support multi-factor allocations at the Instrument-level. In addition to supporting dimension-based allocation methods, the Lookup Driver Table rule type also supports allocations based on instrument-level balances, rates, codes, or other instrument-level measures. Typical use cases include allocation of loan loss reserve, economic provision, or risk equity first as a function of the product, a dimension, but also as a function of instrument-level measures such as loan-to-value ratio, credit score, loss in event of default, probability of default, remaining term to maturity, and so on.
The driver data used in a Lookup Driver Table allocation rule is stored in user-defined lookup tables that are registered into the OFSAA data model. The data from your user-defined lookup table is matched to Instruments tables. The logic governing how your lookup data is matched to Instrument tables is stored in a Lookup Table Driver rule. Lookup Driver Table Allocation rules incorporate Lookup Table Driver rules in the Allocation Specification > Driver process tab.
See Lookup Table Drivers for a guide to building Lookup Table Driver rules and additional explanation of their use.
Use the Source process tab to specify an Allocation rule's data source. For an Allocation of the Constant type, you simply specify an amount. For any other type of rule, you must specify a source table & column and, optionally, a set of constraints. Alternatively, you may specify an Expression to specify a more complex Source than a simple source table and column.
For Container: Choose a source table and then choose either a column from that table or choose an Expression rule.
The management ledger level refers to both the Management Ledger table. This section describes the usage of the Management Ledger as the Source in allocation rules. The Management Ledger is a seeded table of Management Ledger class of tables (see OFS Data Model Utilities User Guide for details on adding user-defined dimensions to the Management Ledger or for defining new user-defined Management Ledger tables). For details on the new Management Ledger table, see Management Ledger.
When your Source is the Management Ledger table, you will typically use the <Current Amount> macro as your column name. The Management Ledger table is the default table for new allocation rules (except for the Constant type), and <Current Amount> is the default column. The <Current Amount> macro selects the current month from your Management Ledger based on your As-of-Date and Fiscal Year definitions. If your As-of-Date is set to any day in March, <Current Amount> will generally be interpreted as Fiscal Month number 3. If your fiscal year begins in April, however, your March data is stored in the Management Ledger under Fiscal Month number 12 since March is the last month in your fiscal year. For details on the fiscal year, see Fiscal Year Information.
NOTE:
Allocation rules that aggregate instrument-level data to the Management Ledger table maintain literal As-of-Dates when posting to the management ledger, but when data is retrieved from the Management Ledger table (i.e., in Allocation Sources or Allocation Drivers), it is consolidated into a month-to-date balance. Similarly, all outputs at the management ledger level are inherently month-to-date balances.
When your Source is the Management Ledger table, Oracle Insurance Allocation Manager for Enterprise Profitability also supports the following macros:
· <Last_Mo_Amount>
· <Months_Ago_Amt>
· <Current_Amount>
· <YTD_Amount>
· <Months_Ago_YTD_Amt>
· <Last_Mo_YTD_Amt>
· <Accrual_Basis>
<Last Mo_Amount> selects month-to-date balances from the month before your As-of-Date. <Months_Ago_Amount> selects month-to-date balance as of a designated number of months ago. For example, with a typical January to December fiscal year, if today's As-of-Date is March 31, 2015 (Fiscal Month = 3, Fiscal Year = 2015) then 6 months ago in the Enter Months field. This corresponds to September, 2014 (Fiscal Month = 9, Fiscal Year = 2014). The month range for Enter Months is from -99 to 999. For details on the fiscal year, see Fiscal Year Information.
Figure 77: Allocation Specification – Source Tab
When your Source is the Management Ledger table, use the <Current Amount> macro as your column name. Except for rules of the Constant type, < Current Amount > is the default column when you build a new rule invoking Management Ledger. The <Current Amount> macro selects the current month from Management Ledger based on your As-of-Date and Fiscal Year definitions. If your As-of-Date is set to any day in March, <Current Amount> will generally be interpreted with FISCAL_MONTH as 3. If your fiscal year begins in April, however, your March data is stored in Management Ledger with FISCAL_MONTH as 12 since March is the last month in your fiscal year. For details on the fiscal year, see Fiscal Year Information.
In addition to <Current Amount>, Oracle Insurance Allocation Manager for Enterprise Profitability also offers macros for:
· <YTD Amount>
· <Last Month Amount>
· <Last Month YTD Amount>
When your Source is an Instrument or Transaction Summary table, you may choose any valid measure in the table. Valid measures include only rates, balances, and numeric statistics such as activity counts.
Figure 78: Instrument or Transaction Summary Source
When choosing an Expression to act as the source for your Allocation rule, the expressions available to you are limited to those built upon the table you selected in the For container.
NOTE:
Expressions may not be used with Management Ledger tables but will be supported in a future release.
Figure 79: Expression Container
The Allocation Source Container is used to provide dimensional constraints on your Source data. For any dimension, you may constrain your source data by selecting a leaf member, a rollup member within a Hierarchy, or a Hierarchy Filter.
At least one leaf-level dimension member is required for hierarchies used in allocations.
For allocation rules that source data from the Management Ledger-level, you must select a Source scenario from the Allocation Source container. The default for new allocation rules is <Use Application Preferences>. When you use this default value, the Scenario (also called Consolidation Code) is determined by the value that is set in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability for the user who is running the rule. If you do not select <Use Application Preferences>, you must select a defined dimension member value (for example, Actual, Budget, Forecast, Forecast Prior). These values are provided with the data model, but you may add additional dimension members in the Consolidation Code dimension.
The Source value for the Scenario may also be provided as a task parameter if the rule is executed within:
· a Simplified Batch,
· a standard ICC batch, or
· from the command line
When the Source value for the Scenario is passed as a task parameter, it overrides the value set in Application Preferences. For details on passing task parameters, see the OFS Analytical Applications Infrastructure User Guide.
Figure 80: Source Tab
Some examples of how the Source tab is used to provide input data to an allocation rule include:
Desired Data |
Constraint |
|---|---|
Get all expenses for all GL Accounts within a specific cost center |
Single leaf constraint on Organizational Unit plus single leaf constraint on Financial Element 457 – Non-Interest Expense |
Get all current mortgage balances for adjustable rate products originated in the past year |
Hierarchy member constraint on the Product dimension plus a Data Filter constraint |
Get all initial General Ledger balances plus all allocated balances for a specified set of cost centers for one GL Account |
Single leaf constraint on General Ledger Account plus a Hierarchy member constraint on Organizational Unit |
Get ending balances for all balance sheet assets for the North, South, and East divisions (but not the West division except for the South-West sub-region) |
Single leaf constraint on Financial Element plus Hierarchy member constraint on the GL Account dimension plus an Organizational Hierarchy Filter |
The dimensions listed in the Allocation Source container are limited to your Key Processing Dimensions. Financial Element, Organizational Unit, General Ledger Account, Common Chart of Accounts, Legal Entity, and Product are seeded Key Processing Dimensions for all OFS Analytical Applications. You may add your own Key Processing Dimensions (see Installation & Configuration Guide for guidance on adding your own processing dimensions). If you have enabled multicurrency, the Currency dimension will also be displayed. For details on multi-currency calculations within Oracle Insurance Allocation Manager for Enterprise Profitability, see Multi-Currency across OFSAA Applications.
The default for each dimension is <All> (meaning no constraint). To select a constraint, click on the Hierarchy Browser ellipses (…) next to the dimension you wish to constrain.
Figure 81: Hierarchy Browser on Source Tab
The Hierarchy Browser defaults to a list of leaves for the dimension you have chosen (the radio button selector near the top of the browser window defaults to Dimension Leaf Member). You may scroll up and down to find the leaf member you want or you may search for the member's name (short description) using the binoculars at the bottom of the browser window. Additional search functionality is provided by the Search control within the browser. This additional functionality allows you to search by Dimension Member, Name, or Code.
Figure 82: Hierarchy Browser with Additional Search Parameters
To constrain your source using a hierarchy rollup point, click the Hierarchy radio button near the top of the browser window and then search for the hierarchy you wish to use. Once your chosen hierarchy is displayed within the browser window, navigate into the hierarchy until you have found the rollup point or leaf value you want. Click on your selected value and then click OK.
Figure 83: Hierarchy
To constrain your source using a Hierarchy Filter, first select the Hierarchy radio button near the top of the browser window and then search for the hierarchy that supports the Hierarchy Filter you wish to use. Next, click on the Hierarchy Filter radio button near the top of the browser window, search for the Hierarchy Filter you wish to use, and then click OK.
You may optionally select a Data Filter, Group Filter, or Attribute filter rule to further constrain your Source data.
Generally, the Operator process tab allows you to specify how Source data and Driver data will interact to create results.
No Driver is necessary in the specification of a Constant rule. Both the Operator process tab and the Driver process tab are disabled for the Constant rule type.
For Static Driver rules, the Driver process tab is disabled, but the Operator process tab is enabled to allow you to specify a static driver balance. Static amounts are entered into the Factor Operator container.
Figure 84: Operator Process Tab
For all other rule types, the Operator process tab offers both a Factor Operator and an Allocation Operator. The Allocation Operator links the Allocation rule's Source Data with its Driver data. The Factor Operators may be interposed between the Source and Driver.
Factor Operators may be used to either
Store static driver amounts and/or accrual basis macros for Static Driver rule types
Interject constant values and/or accrual basis macros between allocation Sources and Drivers for Leaf, Field, Dynamic Driver, Static Driver Table, or Lookup Driver Table rule types
The Factor Operator allows you to modify Source data by adding, subtracting, multiplying, or dividing Source data by a constant amount, an accrual basis macro, or both.
Figure 85: Factor Operator Container
Examples of Usage of the Factor Operator
· Instrument-level rate times balance allocations commonly use the “Both” type factor operator in which the first-factor operator is “times <accrual-basis> macro” and the second-factor operator is “divided by 100” when posting to a monthly income or expense balance.
· In the above example, if you were to choose a 30/360 accrual basis factor, you could equally well specify your factor operator as “divide by 1200”.
· Instrument-level rate times balance allocations can also utilize actual instrument-level accrual bases instead of applying the same accrual basis to every calculation.
· In a typical percent distribution allocation such as “distribute all Human Resource expense to all cost centers as a function of headcount”, you may sometimes want to distribute less than 100% of total expense. In this example, your Source data would be “all Human Resource expense”, your Driver data would be “head count by cost center” on a percent-to-total basis, and your Factor Operator would be whatever percentage of the total expense you are choosing to allocate.
For all allocation types except Constant and Static Driver, the Allocation Operator links the Allocation rule's Source Data with its Driver data. The most common form of linkage is multiplication, but both multiplication and division are supported. For some allocation types, addition and subtraction are also supported.
Figure 86: Allocation Operator Container
The Driver process tab allows you to specify a set of driver data that is combined with Source data to create allocation outputs. The way in which the Source data and Driver data interact is a function of the type of Allocation rule you are using and the nature of the Operator you have specified.
The Driver process tab is enabled for Leaf, Field, Dynamic Driver, Static Driver Table, and Lookup Driver Table allocation types. The containers displayed on the Driver process tab vary from rule type to rule type.
For the Leaf, Field, and Dynamic Driver allocation types, a For container is displayed in which you can choose either a driver table & column or an Expression to serve as the source of your Driver data.
The Leaf Driver container is only displayed for the Leaf allocation type. Use this container to specify the dimension and leaf value you wish to use.
The Legal Entity dimension is not applicable to allocations that use the Ledger Stat table.
Figure 87: Leaf Driver Container
For the Dynamic Driver allocation type, use this container to supply dimensional constraints for your driver data. You supply dimensional constraints using the hierarchy browser. From the Dynamic Driver container, the behavior of the hierarchy browser is identical to the hierarchy browser described above under Allocation Source Container.
Figure 88: Dynamic Driver Container
For Dynamic Driver allocation rules that obtain their driver data from the Management Ledger-level, you must also select a Driver scenario. The default for new allocation rules is <Use Application Preferences>. When you use this default value, the Scenario (also called Consolidation Code) is determined by the value that is set in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability for the user who is running the rule. If you do not select <Use Application Preferences>, you must select a defined dimension member value (for example, Actual, Budget, Forecast, Forecast Prior). These values are provided with the data model, but you may add additional dimension members in the Consolidation Code dimension.
Given below are the limitations for the User-Defined Consolidation Code dimension:
· Code name must be a string with alphabetic characters alone (either lower case or upper case).
· Code should not have digits, special characters, blank spaces, or punctuation characters.
· Code should not have the word 'adhoc' in it.
The Driver value for Scenario may also be provided as a task parameter if the rule is executed within:
· a Simplified Batch,
· a standard ICC batch, or
· from the command line
When the Source value for the Scenario is passed as a task parameter, it overrides the value set in Application Preferences. For details on passing task parameters, see the OFS Analytical Applications Infrastructure User Guide.
The Distribution Type container is only displayed for the Dynamic Driver allocation type. Use this container to select the Simple, Percent Distribution, or Uniform distribution method.
Figure 89: Distribution Type Container
· Percent Distribution Method: The most common distribution method is Percent Distribution (sometimes referred to as Force to 100%). Typical use cases include expense allocations as a function of a driver set that has not been normalized, i.e., converted to percentages of the total driver set. For example, if you wanted to distribute some expense balance to Departments 1, 2, and 3, and if Departments 1, 2, and 3 had headcounts of 100, 200, and 700, you would choose the Percent Distribution method to allocate 10% (100/1,000) to Department 1, 20% (200/1,000) to Department 2, and 70% (700/1,000) to Department 3.
· Simple Method: Use the Simple distribution method in cases where your dynamic drivers are stored as percentages. You might also use the Simple distribution method if your allocation source data were activity counts and your driver data represented unit costs.
· Uniform Method: Use the Uniform distribution method in cases where you want to allocate equal shares of your source data for each destination in your driver set regardless of driver amount. Continuing with the above headcount example, you may wish to allocate equal shares of 10% of total Human Resource department expense to any Department having non-zero headcount. In this case, you would use Human Resource department expenses as your allocation Source, you would specify a Factor Operator of 10%, you would specify your “Headcount by Cost Center” statistic set as Driver, and you would select the Uniform distribution method. Statistical driver sets are frequently stored in the Management Ledger under user-defined Financial Elements.
For the Static Driver Table allocation type, specify a Folder and select a Static Table Driver rule. By default, the Distribution Type is set to Force to 100%. For a discussion of their use including examples, see Using Static Table Drivers in the Static Table Driver chapter.
Figure 90: Static Table driver
Note that once you have chosen a Static Table Driver rule, a View control is added to the Static Table Driver title bar. Click on this View control to view a read-only version of the Static Table Driver rule you have chosen.
For the Lookup Driver Table allocation type, specify a Folder and select a Lookup Driver Table rule. Note that the drop-down list box for Lookup Table Drivers is limited to Lookup Table Drivers whose source tables match the table specified in the current Allocation rule's Source definition. For a discussion of their use including examples, see Lookup Table Drivers.
Figure 91: Lookup Table Driver
Note that once you have chosen a Lookup Table Driver rule, a View control is added to the Lookup Table Driver title bar. Click on this View control to view a read-only version of the Lookup Table Driver rule you have chosen.
For the Field and Dynamic Driver allocation types, you may optionally select a Data Filter, a Group Filter, or an Attribute Filter rule to further constrain your driver data.
The Outputs process tab allows you to specify where the outputs of an Allocation rule are written. When outputting to the Management Ledger table, the allocation engine creates Management Ledger debits and/or credits. When outputting to Instrument or Transaction Summary tables, the allocation engine updates target columns.
Source-Driver Relationship
This container appears in the Outputs tab only when the Allocation Type is set as Leaf Allocation in the Initial Definition tab.
Figure 92: Outputs Process Tab - Source-Driver Relationship
There are four options available:
· Include Rows Found Only in Driver: When you select this option, the output will include the rows which are available only in Driver.
· Include Only Rows Found in Both Source & Driver: When you select this option, the output will fetch the rows from both source and driver based on defined condition(s).
· Include All Rows: When you select this option, the output will include all the rows which are available in both Driver and source.
· Include Rows Found Only in Source: When you select this option, the output will include the rows which are available only in Source.
Both Debit definitions and Credit definitions are built and maintained on the Outputs process tab. Within the Outputs process tab, the Debit/Credit tabs allow you to navigate back and forth between a rule's Debit definition and its Credit definition. You may also use the Debit/Credit tabs to suppress the output of either Debits or Credits, but you may not suppress the output of both Debits and Credits.
When posting allocation results to the Management Ledger-level, the Oracle Insurance Allocation Manager for Enterprise Profitability allocation engine generates a balanced accounting transaction consisting of multiple debits and credits. As few as one debit or credit may be generated, or you might generate thousands of debits and credits.
Figure 93: Outputs Tab
When the Output Table is Instrument-level: When using an allocation rule to update an Instrument or Transaction Summary table, the Oracle Insurance Allocation Manager for Enterprise Profitability allocation engine updates your chosen output column for each instrument-level account that is found in your Source and for which a matching Driver is found.
When outputting to Instrument or Transaction Summary tables, you may choose to either Replace or Increment your target column values. The default behavior for Allocation rules built before release 6.0 is Replace.
Figure 94: Debit Tab
![When the output table is Management Ledger, hierarchy node member can be selected in Output [Debit and Credit] tabs only for Static Driver and Dynamic driver allocation types. Other allocation types do not support hierarchy node member selection in Output.](OIAM_UG_811_OLH_files/image346.png "Description of Debit/Credit tab within the Output Column option follows")
Specify the output table and column for the Allocation rule.
· You may only output to < Current Amount > when posting allocation results to the Management Ledger level.
· For Constant and Leaf type rules, you may only output to the Management Ledger level.
· For Lookup Driver Table type rules, you may only output to an Instrument or Transaction Summary table.
For Allocation rules that update an Instrument or Transaction Summary tables, you have the option of aggregating your results and posting them to the Management Ledger level.
Figure 95: Debit Tab – Aggregate to Ledger
For Allocation rules that update an Instrument or Transaction Summary tables, you have the option of aggregating your results and posting them to the Management Ledger, or Ledger Stat table. To do this, select the Aggregate to Ledger option as Yes and select Ledger Stat or Management Ledger from the Ledger Table Name drop-down list.
Figure 96: Allocation Specification - Outputs
NOTE:
Lookup Driver Table type allocation rules are capable of sending output to the Management Ledger level.
The Dimension container is displayed for every allocation type. The default value for each dimension is generally <Same as Source>. You may choose a specific dimension member value for any dimension for both debits and credits for any allocation type.
For allocation rules posting to the Management Ledger level, you must select an output scenario. The default for new allocation rules is <Use Application Preferences>. When you use this default value, the Scenario (also called Consolidation Code) is determined by the value that is set in Application Preferences for Oracle Insurance Allocation Manager for Enterprise Profitability for the user who is running the rule. If you do not select <Use Application Preferences>, you must select a defined dimension member value (for example, Actual, Budget, Forecast, Forecast Prior). These values are provided with the data model, but you may add additional dimension members in the Consolidation Code dimension.
The output value for the Scenario may also be provided as a task parameter if the rule is executed within:
· a Simplified Batch,
· a standard ICC batch, or
· from the command line
When the output value for Scenario is passed as a task parameter, it overrides the value set in Application Preferences. For details on passing task parameters, see the OFS Analytical Applications Infrastructure User Guide.
For allocation rules posting to the Management Ledger-level, <Same as Source> for a particular dimension means that for that dimension, the values found in Source records are passed directly to Output records. For example, you might want to allocate 100% of the expenses from one department to a second department. In your original General Ledger data, expenses for the Source cost center might be posted under hundreds of different General Ledger accounts. In this example, you might specify your Credit Output (expense allocation offset) as:
· <Same as Source> for the Organizational Unit dimension
· <Same as Source> for the General Ledger Account dimension
Written in this fashion, your allocation rule will generate a credit to the original department for every original expense balance. If the source department contained balances under 81 different General Ledger accounts, the allocation rule would generate 81 credit records.
Continuing, you might specify your Debit Output as:
· Target Department (leaf value) for the Organizational Unit dimension
· “Allocated Expense” (leaf value) for the General Ledger Account dimension
In this example, Allocated Expense is a user-defined General Ledger account. You would typically define this dimension member in a reserved range of accounts for use in your Oracle Insurance Allocation Manager for Enterprise Profitability model. Note that only one debit row is created in this scenario. Also note: When you choose to output to a specific leaf value, you may not output to a node value. Outputting to node values is not supported.
Other output macros include
· <Same as Driver>
· <Match Source & Driver>
· <Same as Table>
The usage of these macros is discussed in the sections below entitled Dynamic Driver Allocation Type, Static Driver Table Allocation Type, and Lookup Driver Table Allocation Type.
· Constant Allocation Type: For Constant type allocations, you must specify a target leaf value for each of your processing dimensions for both your debit and your credit. You may optionally suppress either the Debit or the Credit.
· Static Driver Allocation Type: For Static Driver allocations, you may choose either a specific dimension member value or the <Same as Source> macro for each of your processing dimensions.
· Leaf Allocation Type: For Leaf allocations, you may choose either a specific dimension member value or the <Same as Source> macro for each of your processing dimensions.
· Field Allocation Type: For Field allocations, you may choose either a specific dimension member value or the <Same as Source> macro for each of your processing dimensions.
· Dynamic Driver Allocation Type: For Dynamic Driver allocations, for each of your processing dimensions you may choose either a specific dimension member value or
§ <Same as Source>
§ <Same as Driver>
§ <Match Source & Driver>
At least one dimension in either your Debit or Credit specification must be either <Same as Driver> or <Match Source & Driver>.
· Same as Driver Macro: The <Same as Driver> macro is used when you want your outputs to inherit values from your driver data. For example, you might want to build an allocation rule to distribute some kind of processing expense to branches using “Number of Checks Processed per Branch” as your driver statistic set. In this example, your statistics “drive” your processing expense to branches so you would want to specify <Same as Driver> in the Organizational Unit dimension of your Debit definition. Since our example is an expense allocation, you might want to construct a Credit definition using <Same as Source> in every dimension.
· Match Source and Driver Macro: The <Match Source & Driver> macro is used when you want to distribute data to one dimension while holding another dimension constant. For example, you might want to build an allocation rule that allocates a Human Resource expense cost pool as a function of headcount but that also allocates an Occupancy Expense cost pool as a function of square footage occupied. If your destinations are cost centers, you can store your statistic sets – one for headcount and one for square footage – on a per Cost Center per Cost Pool basis. In this example, you would use <Match Source & Driver> on the Cost Pool dimension and <Same as Driver> on the Organizational Unit dimension. This causes the rule engine to create two sets of Debits to Cost Centers:
§ One set of Debits from the Human Resource expense Cost Pool using the headcount statistics
§ One set of Debits from the Occupancy expense Cost Pool using the square footage occupied statistics
· Static Driver Table Allocation Type: For Static Driver Table allocations, for each of your processing dimensions you may choose either a specific leaf value or you may choose
§ <Same as Source>
§ <Same as Table>
When you choose <Same as Table> for a dimension, you are telling the allocation engine that you want your rule to inherit its destination dimension member values from the Static Table Driver.
· Lookup Driver Table Allocation Type: For Lookup Driver Table allocations, you may choose either a specific leaf value or the <Same as Source> macro for each of your processing dimensions.
The Review process tab displays a single page, printable report of an Allocation rule's specification.
The review tab shows the dimension values for source and debit/credit output.
Figure 97: Review/Process Tab
The examples are explained in detail in the following sections.
For Static Driver allocation rules, Management Ledger-to-Management Ledger is a common use case.
· Allocate 15% of the occupancy expense from one cost center to another cost center. In this example, the static driver is 15%.
· Create a cost pool by aggregating 25% of the expense found under a select group of General Ledger accounts for a region or a division or a department, or a single cost center. In this kind of aggregation, the static driver is 25%.
· Transfer 100% of loan assets from all of the loan origination centers within a region to a regional holding center. In this example, the static driver is 1.
NOTE:
While such allocations are relatively common, it is more likely when you have a series of such allocations to utilize Static Driver Table rules. Using a Static Driver Table rule, you can accomplish with a single rule what might otherwise require dozens or even hundreds of Static Driver allocation rules.
Instrument-to-Management Ledger is a very common use case. Such allocations are inherently aggregative, i.e., multiple rows from the instrument source map to each row posted to the Management Ledger.
You may wish to aggregate your instrument-level principal balances (current book balances) to the Management Ledger to either enrich your ledger with a dimensionality that is present in your instrument data but not present in your initial Financial Accounting data. For example, General Ledgers normally have more constrained dimensionality than is available in your instrument data. Each row of your instrument data may designate an owning Cost Center, a General Ledger corresponding to the instrument's principal balance, its Product, its Customer Segment, and so on. Your General Ledger, however, may only have dimensions corresponding to Cost Center and GL Account. In this case, even though your Management Ledger table includes columns for Product and Customer Segment, every row from your initial load from your source General Ledger system will populate a single value for these dimensions the meaning of which is “Not Applicable” or “N/A”.
The following example demonstrates how you can use a Static Driver allocation rule to “reclassify” your Management Ledger data using data from the Checking and Savings (CASA) Instrument table. Build a Static Driver allocation rule as follows:
· Set the Source to Current Book Balance for the CASA Instrument table
· Set the Allocation Operator to “multiply by 1”
· Credit Management Ledger for Financial Element 100 (Ending Balance) using <Same as Source> for every dimension
· Debit Management Ledger for Financial Element 100 (Ending Balance) using <Same as Source> for the GL Account and Organizational Unit dimensions; set every other Key Processing Dimension to “N/A”
NOTE:
When allocating debit balances, you must post them using the Debit Output tab; offsets to these debits should be posted using the Credit Output tab. Conversely, when allocating credit balances, you must post them using the Credit Output tab; offsets to these credits should be posted using the Debit Output tab.
This allocation effectively eliminates your original balances and replaces them with “enriched” data, i.e., data that is aligned to Product and Customer Segment as well as to Organizational Unit and General Ledger Account. For a further discussion of this kind of aggregation rule, especially in a context in which there are any variances between the sum of your instrument-level balances and your initial General Ledger balances, see the section below entitled Management Ledger Reclassification Using Instrument-level Driver Data.
Another very typical use case for aggregating instrument-level data to the Management Ledger concerns summarizing Funds Transfer Pricing results. An example for the CASA table might be:
· Set the Source to Transfer Pricing Charge/Credit for the CASA Instrument table
· Set the Allocation Operator to “multiply by 1”
· Credit Ledger/Stat for Financial Element 450 (Transfer Pricing Charge/Credit) using <Same as Source> for every dimension
· Debit Ledger/Stat for Financial Element 450 (Transfer Pricing Charge/Credit) using <Same as Source> for every dimension except for Organizational Unit; for the Organizational Unit dimension, post to your Funding Center
Here, the Funding Center is a shadow cost center that you have established to house all of your transfer pricing offsets. Your Funding Center acts as an interest rate risk management center. For a typical bank whose weighted asset duration exceeds its weighted liability duration, the Funding Center will normally be a profit center (at least in a normal upward sloping yield curve environment). For a more detailed discussion of the Funding Center and of interest rate risk management in general, see the Oracle Financial Services Funds Transfer Pricing User Guide.
Instrument-to-Instrument is a common use case.
· For each instrument, calculate and update a target column as a fixed relationship to some other column. For example, calculate a loan loss reserve as a fixed percentage of the current balance of each mortgage loan.
· For each instrument, calculate a rate times a balance and multiply it by an accrual basis factor and divide it by 100 to update a revenue or expense column. This kind of allocation would use as Expression as a Source where the expression contained a rate times balance calculation. The Static Driver would consist of (1) an accrual basis macro and (2) and factor of 0.01.
Transaction Summary-to-Management Ledger is a common use case.
· Aggregate Transaction Summary level costs to the Management Ledger; post results to a user-defined Financial Element
Transaction Summary-to-Instrument is a common use case.
· Aggregate Transaction Summary level costs to an associated Instrument table column. For example, you may record activity level volumes & costs in your CASA (Checking and Savings) Transaction Summary table. You may wish to aggregate a group of ATM-related activities such as ATM Withdrawal Expense, ATM Inquiry Expense, ATM Transfer Expense, ATM Deposit Expense, and Other ATM Expense to an instrument column in the CASA table called ATM Expense.
Transaction Summary-to-Transaction Summary is an infrequent use case.
· Multiply CASA Transaction Summary volumes by a fixed unit cost and post the result to CASA Transaction Summary costs
· Because Transaction Summary tables commonly store activity volumes, you are more likely to build this kind of rule using Static Table Driver rules that contain unit costs for many activities. To complete your Volume * Unit Cost à Cost process, one Static Driver Table allocation rule could take the place of dozens or hundreds of Static Driver allocation rules.
Leaf allocations only support the Management Ledger-to-Management Ledger use case. Leaf allocations are used to compare a Source set of Management Ledger balances to a Driver set of Management Ledger balances to create an Output set of Management Ledger balances.
In this kind of rule, the allocation engine attempts to match each Source row to a Driver row where the two rows share the same values for every Key Processing Dimension but one. For example, in an implementation in which there are 7 Key Processing Dimensions, for each Source row, the engine will attempt to find a Driver row that matches the Source row in 6 dimensions but which differs in one dimension. The one dimension in which Source and Driver rows must differ is the dimension chosen in the Driver as the “Leaf” dimension.
· Example 1: You may wish to divide a set of Management Ledger Transfer Pricing Charge/Credit balances (stored under Financial Element 450) by a set of Management Ledger Average Balances (stored under Financial Element 140) to generate a third set of Management Ledger Weighted Average Transfer Rates (stored under Financial Element 170). In this case, you would constrain your Source data to Financial Element 450; for your Driver, you would specify Financial Element as your Leaf dimension and you would pick Financial Element 140. Finally, for your output, you would choose a Financial Element of 170.
· Example 2: You may wish to subtract a set of “Aggregated Instrument-level Ending Balances” (stored under a user-defined Financial Element such as 10100) from a set of “original General Ledger ending balances” (stored under Financial Element 100) to generate a set of variances between your General Ledger data and your instrument data. These variance records might be stored under a second user-defined Financial Element such as 20100.
In the Instrument table context, Field allocations perform arithmetic operations on different columns within the same row of data. For example, you might use a Field allocation rule to multiply instrument-level balance times a rate times an accrual basis factor to update a rate-related income or expense column. Such an allocation could update a single row or millions of rows depending on your filtering criteria.
In the Management Ledger context, Field allocations are rarely used. When they are used, Field allocations generally perform arithmetic operations on different columns within the same “logical” row of data. For example, you might wish to generate a result set of rows in the Management Ledger that represent changes in asset values from month to month. In this case, you build a Field allocation that used the < Current Amount > macro for all Management Ledger asset balances as your Source and that subtracted the < Last Month Amount > in the Driver. In this example, you would suppress the Credit output and write the Debit output to a user-defined Financial Element. If your Source Financial Element were 100 (Ending Balance), you might elect to post your results to a user-defined Financial Element whose name was Month-Over-Month Change.
In this current example, the engine will generally perform its arithmetic calculation (in this case, subtraction) on two different columns within the same physical row within the Management Ledger. If you were calculating a Month-over-Month change from December to January, however, the engine would obtain its January value (its Source value) from one row and its December value (the < Last Month Amount > specified in the Driver) from a second row. Similarly, if you were to build a Field Allocation rule to generate Year-over-Year changes, the engine would always be comparing sets of Source rows that were identical in every respect except for their Year Summary values.
Management Ledger Reclassification Using Instrument-level Driver Data
Most commonly, your General Ledger constitutes a starting point for building up your Management Ledger. One way of enriching your Management Ledger is to exploit your instrument-level data to distribute balances to additional dimensions that are not present in your book-of-record General Ledger.
For this example, assume that your General Ledger data is aligned in the Organizational Unit and GL Account dimensions but is not aligned to the Product dimension. For purposes of illustration, imagine a very simple case in which:
Your General Ledger records principal balances for Commercial Loans and Consumer Loans under 2 GL accounts for Branch 1 and Branch 2
Your Loan Instrument table contains thousands of loan records for the same 2 GL Accounts (Commercial Loans and Consumer Loans) for Branch 1 and Branch 2 for two different products
The following table summarizes the balances for this example.
Table |
GL Account |
Branch |
Product |
Balance |
# of Loans |
|---|---|---|---|---|---|
Management Ledger |
Commercial Loan |
1 |
— |
$1,000 |
— |
Management Ledger |
Commercial Loan |
2 |
— |
$2,000 |
— |
Management Ledger |
Consumer Loan |
1 |
— |
$3,000 |
— |
Management Ledger |
Consumer Loan |
2 |
— |
$4,000 |
— |
Loans |
Commercial Loan |
1 |
Land |
$600 |
214 |
Loans |
Commercial Loan |
1 |
Construction |
$400 |
659 |
Loans |
Commercial Loan |
2 |
Land |
$1,400 |
814 |
Loans |
Commercial Loan |
2 |
Construction |
$600 |
907 |
Loans |
Consumer Loan |
1 |
Auto |
$2,100 |
273 |
Loans |
Consumer Loan |
1 |
Personal |
$900 |
622 |
Loans |
Consumer Loan |
2 |
Auto |
$2,600 |
861 |
Loans |
Consumer Loan |
2 |
Personal |
$1,400 |
590 |
Note that the instrument balances and General Ledger balances reconcile perfectly, for example, the 214 Land loans and 659 Construction Loans under Branch 1 have balances totaling $1,000 which reconcile with the General Ledger balance of $1,000 for Commercial Loans under Branch 1.
To “product align” the Management Ledger:
1. Build a Dynamic Driver allocation rule where the Source filters on the Management Ledger for the < Current Amount > macro for the Asset branch of your GL Hierarchy for Financial Element 100 (Ending Balance). Instead of utilizing a rollup node to filter on assets, you could construct a Data Element Filter for the Commercial Loans and Consumer Loans GL accounts. For reasons discussed below, only the Financial Element constraint is truly required.
2. Set the Allocation Operator to Multiply.
3. Set the Dynamic Driver to utilize Ending Balance from your Loan instrument table. Set the Driver's Distribution Type to Percent Distribution. No dimensional constraints or other filters are necessary.
4. Set the Credit Output to Management Ledger (note: when posting outputs to Management Ledger, you MUST output to the < Current Amount > macro). Set < Same as Source > for each Key Processing Dimension.
5. Set the Debit Output to Management Ledger; use < Match Source & Driver > for the GL Account and Organizational Unit dimensions, < Match Driver > for the Product dimension, and < Same as Source > for all other Key Processing Dimensions.
Written in this fashion, the allocation rule will (1) generate credit records that exactly offset the original ledger (debit) balances and (2) aggregate the instrument ending balances on a per GL Account, per Organization Unit, per Product basis and post the results to Management Ledger. The following table summarizes the Management Ledger rows before and after the allocation has been run.
Table 20: Summary of the Management Ledger Rows before and after the Allocation Run
Row Type |
GL Account |
Branch |
Product |
Balance |
|---|---|---|---|---|
Initial Load |
Commercial Loan |
1 |
— |
$1,000 |
Initial Load |
Commercial Loan |
2 |
— |
$2,000 |
Initial Load |
Consumer Loan |
1 |
— |
$3,000 |
Initial Load |
Consumer Loan |
2 |
— |
$4,000 |
Credit |
Commercial Loan |
1 |
— |
($1,000) |
Credit |
Commercial Loan |
2 |
— |
($2,000) |
Credit |
Consumer Loan |
1 |
— |
($3,000) |
Credit |
Consumer Loan |
2 |
— |
($4,000) |
Debit |
Commercial Loan |
1 |
Land |
$600 |
Debit |
Commercial Loan |
1 |
Construction |
$400 |
Debit |
Commercial Loan |
2 |
Land |
$1,400 |
Debit |
Commercial Loan |
2 |
Construction |
$600 |
Debit |
Consumer Loan |
1 |
Auto |
$2,100 |
Debit |
Consumer Loan |
1 |
Personal |
$900 |
Debit |
Consumer Loan |
2 |
Auto |
$2,600 |
Debit |
Consumer Loan |
2 |
Personal |
$1,400 |
Note that:
6. The original Ledger balances are exactly offset by the allocation's Credit records
7. The allocation rule produces a balanced accounting transaction, i.e., a set of Debit and Credit records that sum to zero
8. The allocation rule's Debit records effectively “product align” the Management Ledger
Also, note that it was not strictly necessary to supply any kind of GL Account or Organizational Unit filter in your allocation's Source specification. The reason that doing so is not strictly speaking required is that your rule is written to < Match Source & Driver > in the GL Account and Organizational Unit dimensions. Since only 2 GL Accounts (Commercial Loans and Consumer Loans) and 2 Organizational Units (Branch 1 and Branch 2) are found in the driver data (the instrument records), the Source is effectively constrained to these values even if you do not explicitly filter on them in the Source specification.
Note that the same results from Example #1 above could have been obtained from a Static Driver rule:
· Source = Instrument ending loan balances
· Allocation Operator = “times 1.00”
· Debit = < Same as Source > for all dimensions
· Credit = < Same as Source > for Organization Unit and GL Account and N/A for Product
Since the same results could be obtained more simply, why use the Dynamic Driver rule type to perform Management Ledger “reclassifications”? The answer is that your instruments will not always reconcile to your General Ledger due to timing differences or other reasons. Your institution will have a threshold tolerance level for such outages and tolerance levels will vary from institution to institution. Moreover, you may wish to product align expenses, fees, or other Management Ledger balances using instrument balances (or other measures) as proxies that would never reconcile to the Management Ledger balances.
The following initial data illustrates this second example where the instrument data does not reconcile to the General Ledger data:
Table |
GL Account |
Branch |
Product |
Balance |
# of Loans |
|---|---|---|---|---|---|
Management Ledger |
Commercial Loan |
1 |
— |
$1,000 |
— |
Management Ledger |
Commercial Loan |
2 |
— |
$2,000 |
— |
Management Ledger |
Consumer Loan |
1 |
— |
$3,000 |
— |
Management Ledger |
Consumer Loan |
2 |
— |
$4,000 |
— |
Loans |
Commercial Loan |
1 |
Land |
$603 |
214 |
Loans |
Commercial Loan |
1 |
Construction |
$399 |
659 |
Loans |
Commercial Loan |
2 |
Land |
$1,401 |
814 |
Loans |
Commercial Loan |
2 |
Construction |
$604 |
907 |
Loans |
Consumer Loan |
1 |
Auto |
$2,106 |
273 |
Loans |
Consumer Loan |
1 |
Personal |
$903 |
622 |
Loans |
Consumer Loan |
2 |
Auto |
$2,597 |
861 |
Loans |
Consumer Loan |
2 |
Personal |
$1,399 |
590 |
Note that total Commercial Loans under Branch #1 is now $1,002 whereas the ledger balance is only 1,000. A simple Static Driver allocation that aggregated these balances to the Management Ledger would create one credit record for $1,002 and two debit records totaling $1,002. This would leave a net “unaligned” balance of $2. The Dynamic Driver allocation, however, would still create a single credit record for Commercial Loans under Branch 1 in the Management Ledger in the amount of $1,000; and it would still create two debit records for Commercial Loans under Branch 1 totaling $1,000.
The following table summarizes the data that the Dynamic Driver allocation would create given the original (Example #2) balances shown above:
Row Type |
GL Account |
Branch |
Product |
Balance |
|---|---|---|---|---|
Initial Load |
Commercial Loan |
1 |
— |
$1,000 |
Initial Load |
Commercial Loan |
2 |
— |
$2,000 |
Initial Load |
Consumer Loan |
1 |
— |
$3,000 |
Initial Load |
Consumer Loan |
2 |
— |
$4,000 |
Credit |
Commercial Loan |
1 |
— |
($1,000) |
Credit |
Commercial Loan |
2 |
— |
($2,000) |
Credit |
Consumer Loan |
1 |
— |
($3,000) |
Credit |
Consumer Loan |
2 |
— |
($4,000) |
Debit |
Commercial Loan |
1 |
Land |
$601.80 |
Debit |
Commercial Loan |
1 |
Construction |
$398.20 |
Debit |
Commercial Loan |
2 |
Land |
$1,397.51 |
Debit |
Commercial Loan |
2 |
Construction |
$602.49 |
Debit |
Consumer Loan |
1 |
Auto |
$2,099.70 |
Debit |
Consumer Loan |
1 |
Personal |
$900.30 |
Debit |
Consumer Loan |
2 |
Auto |
$2,599.60 |
Debit |
Consumer Loan |
2 |
Personal |
$1,400.40 |
Percent Distribution: Examples #1 and #2 above utilize instrument-level statistics as Driver data for rules whose Source is the Management Ledger and which Output to the Management Ledger. It is, of course, also possible to use the Management Ledger as Source, the Management Ledger as the source of Driver data, and the Management Ledger as your output target. Multiple examples of such rules can be found in this User Guide. Two classic examples would be distributing Human Resource expenses to Cost Centers as a function of (Management Ledger resident) headcount statistics and distributing Occupancy expenses to Cost Centers as a function of (Management Ledger resident) square footage statistics (i.e., space occupied by the target cost centers).
Uniform: See Uniform Method under Driver Process Tab in Allocation Specification for an example of the Uniform allocation method.
Simple: Your institution might obtain volumetric statistics for different kinds of activities either from your source systems or as memo accounts within your General Ledger. In either case, if you have such activity counts stored within your Management Ledger, you could build allocation rules to develop cost pools to subsequently build other rules to develop unit costs for each of your activities. For example, beyond general marketing expense, your institution might track advertising expense for Time Deposits under a single General Ledger account and record “Number of CD's Sold” for each Time Deposit product under a General Ledger memo account (likely stored in Management Ledger under Financial Element 10,000: Statistic). In this case, you could build a Dynamic Driver allocation rule that used the “Time Deposit Advertising Expense” GL Account as its Source, that divided by the “Number of CD's Sold” on a Percent to Total basis, and that debited a new, user-defined Financial Element 10,100: CD Acquisition Unit Costs (for this allocation, you would want to set your debit GL Account and Org Unit and all other Key Processing Dimension values to a dummy value the meaning of which was “N/A” or “Not Applicable”). In creating these unit costs, you might also have used instrument-level data to dynamically obtain your “Number of CD's Sold” statistic. To accomplish this, your driver would look to the Record Count column (the Record Count column contains the number “1”) of the Time Deposit table (FSI-D-TERM-DEPOSITS); would include a Data Element Filter that isolated new accounts; and would include a Hierarchy Filter on the Product dimension that included only the relevant Time Deposits products.
Under either approach, your result set would be a series of unit costs by Product for acquiring new CDs. Note that in this example, we assumed that the only costs included in acquiring new Time Deposits were the advertising costs directly related to Time Deposit products. In a more realistic example, you might first build a series of allocation rules that created a cost pool for this expense category; or you might have built a more complex Source expression to capture all of the relevant costs dynamically. Moreover, your institution might capture these unit costs within your General Ledger or might develop these costs in an external model. Regardless of how you acquired the unit costs, the following example demonstrates how you might utilize unit costs using a Dynamic Driver allocation rule under the “Simple” method.
Having acquired (or developed) your unit cost statistics, you could build a Dynamic Driver allocation rule as follows:
· Source: Record Count (1) from the Time Deposits table
· Allocation Operator: Multiplication
· Driver: CD Acquisition Unit Costs under Financial Element 10,100 using the Simple method
· Debit Outputs: A user-defined Acquisition Costs column within the Time Deposits table; set the Product dimension to < Match Source & Driver >; set all other Key Processing Dimensions to < Same as Source >
· Credit Outputs: None
Written in this fashion, the allocation engine will read each record, match it to the appropriate unit cost for the record's Product, and update the record with that appropriate unit cost.
NOTE:
You might also set the Credit Output to the aggregate total allocated costs to offset the Management Ledger GL Account or Cost Pool containing the original costs.
Note that in achieving the objective of distributing activity-based costs, it is not strictly necessary to either build cost pools or unit costs. You might be able to simply define your cost pool dynamically within an allocation rule and allocate those costs directly to your instruments on a Percent to Total basis using appropriate instrument-level drivers (in this example, number of new accounts).
One reason you might want to take the more complicated path of actually developing unit costs is to be able to more readily report directly on those unit costs. Another reason you may prefer to deal directly with unit costs for your activities is that you have obtained those unit costs from an independent cost study or an external Activity Based Costing application.
Finally, you might also decide that burdening new Time Deposits with their entire Acquisition Cost (1) in the month in which there were originated and (2) based on the current month's advertising costs was not economically “fair” or realistic. Instead, you might choose to develop unit costs reflecting the average of your YTD or “rolling 12” advertising expense; and you might choose to allocate not simply to new accounts but to all Time Deposit accounts. Choosing either of these methods would complicate the task of reconciling your total account level profitability back to your General Ledger, but choosing “economic” allocation methods for allocating expenses to the account level is common.
As seen from some of the examples above, Dynamic Driver allocation rules can also update balance or rate columns at the instrument-level. The following example uses the Management Ledger as a Source while using an Instrument column as Driver to post to the instrument-level.
In this example, your objective is to distribute Item Processing expenses from your Management Ledger to individual customer account records. If each of your individual customer account records for every demand deposit account carried a statistic called Number of Items Processed, that statistic would make an excellent Percent Distribution driver for Item Processing expense; your target column for such a rule would be a user-defined instrument column called Item Processing Expense. For this rule, you would likely utilize < Match Driver > for each Key Processing Dimension in your Outputs. Note that instrument-level allocations can only alter the target balance or rate column. Instrument-level allocations cannot alter Key Processing Dimension values; the instrument-level Key Processing Dimensions can only be used as lookup keys. If you were to use either < Same as Source > or < Match Source & Driver > on one dimension, your rule would be forced to exclude any instrument rows that did not match your Management Ledger for the dimension in which you chose < Match Source & Driver >.
Each row within an Instrument table describes a unique customer account or position at a point in time. Instrument rows are “wide” or “horizontal”, i.e., they contain potentially hundreds of columns containing attributes or measures. By contrast, Transaction Summary tables (each Instrument table has a corresponding Transaction Summary table) also describe unique customer accounts or positions at a point in time, but they include one or more (meaningful) dimension in their primary keys that are not populated in the corresponding Instrument table. In this sense, Instrument tables and Transaction Summary tables have a parent-child relationship; each row in an Instrument table may have one or more child rows in its corresponding Transaction Summary table; parent and child records share the same business date and “account identifier” (ID-NUMBER), but the “child” records vary in the “differentiating” dimension or dimensions. Each child row in a Transaction Summary table typically contains only two fact columns: Volume & Cost (you may, however, customize your Transaction Summary tables). Unlike Instrument tables, Transaction Summary tables are “tall” or “vertical”.
One way of conceptualizing Transaction Summary tables is as vertical expressions of Instrument tables. Each numeric measure within an Instrument row could be expressed as a single row within a Transaction Summary table. Used in this fashion, you might define a Transaction Summary table to have the same primary key as its parent Instrument table with the addition of one additional Key Processing Dimension called “Measure Name”. In this case, each member in the Measure Name dimension would correspond to a column in the parent instrument table. Note that Key Processing Dimensions are present in all of your business fact tables (Instrument tables, Transaction Summary tables, and the Management Ledger). When you actively utilize a Key Processing Dimension within a Transaction Summary table, you typically do not “actively” use that dimension at the instrument-level (i.e., the value in the Instrument table would typically be “N/A”). Although this is not how Transaction Summary tables are intended to be used, it may help in understanding their structure.
Another way of conceptualizing Transaction Summary tables is as follows. At the instrument-level, the value of General Ledger account for a given row is meant to express the principal balance General Ledger account for that row. When you aggregate all instrument-level current book balances, the resulting total balance should reconcile to your General Ledger principal balance. You may, however, want to reconcile balances other than simply principal balances. You might wish to reconcile average book balances, par balances, deferred balances, interest income or expense balances, accrued interest receivable or payable balances, or fee balances. To accomplish this, you might store all of your balances in instrument records, but store selected balances in child Transaction Summary tables under their respective General Ledger accounts that will reconcile back to your General Ledger.
Loan # |
As-of-Date |
GL Account |
Book Balance |
Par Balance |
Interest Income |
Fee Income |
|---|---|---|---|---|---|---|
1 |
Jan, 2011 |
Mortgages, Book Balance |
100,000 |
99,734 |
713 |
14 |
Loan # |
As-of-Date |
GL Account |
Balance |
|---|---|---|---|
1 |
Jan, 2011 |
Mortgages, Book Balance |
100,000 |
1 |
Jan, 2011 |
Mortgages, Par Balance |
99,734 |
1 |
Jan, 2011 |
Mortgages, Interest Income |
713 |
1 |
Jan, 2011 |
Mortgages, Fee Income |
14 |
The examples above are meant to help you understand the basic structure of Transaction Summary tables. The primary purpose of Transaction Summary tables, however, is to support bottom-up profitability models. For example, the “differentiator” between an Instrument table and its child Transaction Summary table might be a user-defined Key Processing Dimension called Transaction or Activity. If your source systems can provide account-level volume statistics for different kinds of activities, you might develop unit costs for each activity to calculate account-level costs for each activity. For example, imagine that you can collect the following account level statistics (counts over time, typically over a month) from your source systems:
· ATM Inquiries
· ATM Withdrawals
· ATM Deposits
· ATM Transfers, In Network
· ATM Transfers, Out of Network
· Other ATM Transactions
· Direct Deposits (Electronic)
· E-Banking Auto-transfers
· E-Banking Bill Pay
· E-Banking Transfers, In Network
· E-Banking Transfers, Out of Network
· Teller Inquiries
· Teller Withdrawals
· Teller Deposits
· Teller Transfers
· Checks Processed
· Overdrafts Processed
· Paper Statements Processed
You could store these volume statistics as user-defined columns within the CASA (Checking & Savings) instrument table. The listing of such activities (transaction summary counts) might number in the dozens or even in the hundreds, and each activity would require its own extended cost column (typically populated in “rate times volume allocations”). Moreover, when you have a large number of such activities, many activities might have a count of zero resulting in wasted storage in your instrument columns. Finally, if your list of activities changes over time, you would have to modify your CASA table to remove columns corresponding to activities you no longer use and you would have to modify your CASA table to add new columns whenever you introduced new activities.
Alternatively, you could store these volume statistics in the CASA Transaction Summary table utilizing a user-defined Key Processing Dimension called Activity to differentiate child records from parent records. The dimension members within the Activity dimension would correspond to your list of activities.
There are many other advantages to this Transaction Summary approach. First, since your Activity dimension would be a Key Processing Dimension, you could construct an Activity hierarchy. The Activity hierarchy might be useful in a reporting context, but more importantly, higher-level rollup points within your Activity hierarchy are likely to be much more stable than individual activities (leaf members within the Activity dimension). For example, you may wish to construct an account level profitability model for demand deposits in which you want to calculate and report on higher-level cost elements that have a channel orientation such as ATM Expense, Branch Expense, and E-Banking Expense. You might choose to store your volumes and compute your costs for each (leaf level) activity at the Transaction Summary level and then construct Instrument-level columns for ATM Expense, Branch Expense, and E-Banking Expense. Using unit costs, you can construct allocation rules to compute your Transaction Summary levels costs. Subsequently, you can use other allocation rules to roll up your Transaction Summary levels costs to target columns within your Instrument table that correspond to rollup points in your Activity hierarchy. The sections that follow continue with the above example to describe such allocation rules.
Note that using this approach you will not pay any storage penalty if many activities frequently have a zero count for any given account (you will not have wasted instrument columns that have zero counts and zero costs, and Transaction Summary rows will only exist for non-zero counts). Also, note that if you add new activities you need only construct a new Activity member and update your Activity hierarchy to indicate its rollup point. No further maintenance would be required either in terms or your data model or your allocation rules.
To update a Transaction Summary table with unit costs held in your Management Ledger, construct an allocation rule that uses the Volume column of your Transaction Summary table as its Source, that uses your Management Ledger statistics as your driver (using the Simple method), and that debits the Cost column in your Transaction Summary table. In your debit specification, use < Match Source & Driver > for the Activity dimension and < Same as Source > for every other Key Processing Dimension. Your (Management Ledger resident) unit cost drivers would likely be stored under a user-defined Financial Element (one unit cost for each Activity). Note, however, that for each Activity you might potentially have different unit costs for different products. If your statistics were stored under a single Financial Element but varied by Activity and by Product, you could construct your rule to < Match Source & Driver > for both Activity and Product.
Note that the sample list of Activities used in this discussion has a “Channel” orientation. You might wish to construct a smaller list of more fundamental Activities that vary by channel. For example, the list of activities presented above could be re-expressed as follows:
· Inquiries
· Debits Processed
· Credits Processed
· Other Transactions
· Reversals Processed
· Statements Processed
Under this smaller set of Activities, you could choose to store your unit costs in Management Ledger by Activity and by Channel (another user-defined Key Processing Dimension). In this scenario, you would define your Transaction Summary table to utilize both Activity and Channel to differentiate it from its parent Instrument table.
Note that this example uses a Dynamic Driver allocation rule to update the Transaction Summary table. In this example and examples of updates to Instrument tables, you could also use Static Driver Table allocation rules. One advantage to the Static Driver Table kind of rule is that its drivers are static. Even if your unit costs are the same from month to month, they generally need to be stored for every month in Management Ledger (although you can store your unit costs under a fixed month, say, January and then hard code your allocation rules to always use the January balance). For additional examples of using Static Driver Table allocation rules, see Using Static Table Drivers in Static Table Driver.
Once you have run your allocation rule to update the Cost column in your Transaction Summary table, run other allocation rules to aggregate costs to the instrument level. You will need one allocation rule for each instrument-level target column, but each of these aggregation allocations would be structurally very similar. For example, to aggregate the costs associated with each of the ATM-related activity-based cost in your Transaction Summary table, build a Static Driver allocation rule that uses the Transaction Summary table's Cost column as your Source, that multiplies by 1.00 in the Allocation Operator, and that debits the ATM Expense column in your target instrument table. The Source specification would also utilize the ATM Expense rollup point within your Activity hierarchy. Each subsequent allocation would have the same structure but would vary its hierarchy rollup point filter in its Source specification and its Instrument target column in its debit specification.
See Using Static Table Drivers in Static Table Driver.
See Lookup Table Driver for examples of using Lookup Driver Table allocation rules.
An allocation rule is written to aggregate one column from an instrument table to a Management Ledger table. Common aggregations include ending or average balances or interest income or transfer charges/credits to specific Financial Elements in the Management Ledger table. Frequently, there are many other non-interest incomes or non-interest expense columns that users might also want to aggregate to the Management Ledger table. For any given column to be aggregated, the operation for CASA is functionally identical to the same aggregation from any other instrument table. Users need one such rule for each instrument table (one rule per table per column to be aggregated). Additionally, if the user adds a new instrument table, he must add another rule. If he has a family of such rules, for example, 10 rules per table (to aggregate 10 columns), adding another instrument table means adding 10 more rules.
Another common rule type performs column-wise calculations such as “rate x balance x accrual basis factor” that is again identical for different instrument tables.
Another common rule type allocates from Management Ledger to the instrument-level.