Process Group/Policy Details
Subject: Cost Allocation
Process Group: Allocate Revenue
Title of Policy: CAlloc Allocate Revenue
Effective Date: 07/01/2014
Approved/Revision Date: 07/01/2014
Approved by: State Controller
Process Group Description
This process group includes the allocation of interest earnings.
Rationale or background to policy:
Treasury needs an Average Daily Balance (ADB) report that shows All Funds, but then separates those funds into an Interest-bearing subset and a Non-interest bearing subset. The ADBs of the interest- bearing funds only will be uploaded to the STAT table. The ADB of each interest-bearing fund will then be converted into a percent of total ADB of all interest-bearing funds, and those percentages will be applied against the pool.
Departments need to ensure that that their departmental chart of accounts elements structure supports identifying the cash balances formerly reported in Reporting Category codes.
Policy Statement:
CAlloc.PO.01 CAlloc Allocate Revenue
The allocation of interest earnings will use an infoAdvantage intelligent query computing the average daily balance for each fund and saved as an Excel spreadsheet. That spreadsheet will be saved and accessed on Treasury’s drive and then uploaded into the STAT table via the Automated Document Submission (ADS) tool. The procedure is detailed below.
Procedure(s):
CAlloc.PR.02.01
1. Configure the Cost Allocation Setup Tables
The Cost Allocation setup involves a hierarchical structure, defined below, including the setup tables for Cost Allocation control, series, step, and pool/base inheritance. These are explained below.
Cost Allocation Control Setup (ALOC) table - At the highest level is a control record known as the Cost Allocation Control Setup (ALOC) table. Each control record is identified by a unique allocation identifier. The unique identifier needs to be the first letter of the Cabinet. For example, the Colorado Department of Labor and Employment will use the letter “K” at the beginning of all allocation IDs.
The Allocation identifier is selected from the Cost Allocation Control Setup table. The Process is always run for an entire allocation and not for a single series or step within that allocation. The allocation frequency is inferred onto the parameter page from the Control Setup table. The following requirements exist based on allocation frequency:
1. Daily: Daily Run Date is required.
2. Monthly: Fiscal Year and Month (Accounting Period) are required.
3. Quarterly: Fiscal Year and Fiscal Quarter are required.
4. Annual: Fiscal Year is required.
Cost Allocation Series Setup record - The second step is the Cost Allocation Series Setup (SRS) record. Multiple series can be defined for any given allocation of revenues. If a department wants to allocate multiple groups or series of revenues within one run of the Cost Allocation Process, then those groups can be specified at this level in the hierarchy.
Cost Allocation Step Setup (STEP) - The third step is the Cost Allocation Step Setup (STEP) record within each allocation series. At least one step is required for the allocation process to run correctly.
Pool/base inheritance - For the ALOC, SRS and STEP tables, Pool/Base inheritance must be defined for each of the Chart of Account (COA) elements. The Inheritance Type is used to define the source of COA elements for the allocation. The source may either be Pool or Base. The Cost Allocation Process will read each COA Inheritance Type from one of the three setup tables.
The Cost Allocation Process will first read the Inheritance Type setting on the STEP table. If the value is Pool or Base, then the process will assume that value for the allocation.
If the Cost Allocation Step Setup value is Default, then the process must look to the Inheritance Type setting on the SRS table. If the value is Pool or Base on the SRS table, then the process will assume that value for the allocation.
If both the STEP table and the SRS table values are Default, then the process must look to the Inheritance Type setting on the ALOC table. There is no default option on the ALOC table, so the value must be Pool or Base. The process will assume that value for the allocation. Bases are the recipient accounting distributions in the Cost Allocation Process.
Statistical: The allocation percentage is calculated during the allocation process based on the statistical units defined at the setup level for each base record.
For steps where the base type equals 'Statistical’, the allocation percentage cannot be defined at setup time. Instead, the allocation percentages are calculated during the Cost Allocation Process. For this base type, statistical records must first be created on the Statistical Unit table. Departments are required to enter the Statistical Group and Record ID. The Statistical Units are automatically inferred from the Statistical Unit Setup table. The Pool/Base Setup table is linked to the Statistical Setup table so that if there is a change to a record on the latter table, then the Statistical Unit value is automatically updated on Pool/Base Setup. The technical term for this relationship is that Pool/Base Setup’s Statistical Unit field is a 'maintained’ replicate from the same field on the Statistical Unit table.
2. Establish the Pools and the Bases
Pools and Bases are initially defined on the Pool/Base Setup (PLBS). The key to this table includes Cost Allocation ID, Series ID and Step ID so that the user is aware that the Pools and Bases are being associated with an allocation step. Pool records specify the source accounting distributions that contain the costs to be allocated to different distributions (Bases). Base records specify the destination accounting distributions in which pooled costs will be allocated. At least one Pool is required before a Base record can be defined for a step. Otherwise there is no source for an allocation. Multiple Pools and Bases can be defined for any Step, each Pool and Base being uniquely identified by a system-generated Pool/Base sequence number.
All Pool records have to be set up with an allocation percentage of 100%. The allocation percentage for each Base in the Step must be defined. The sum of the allocation percentages for all Bases in a single Step should equal 100%.
The final step in the setup process is Pool/Base Distribution Setup (PBDIST). One accounting distribution must be defined for each Pool and Base record. Should this be a separate point (#3?)
3. Run infoAdvantage Report, ADS upload and STAT table:
Average Daily Balance for Interest Allocations
- Log into Info Advantage
- Open My Favorites and locate the selected ADB Report
- Click the public folder drop down
- Click the DE1-Core Reports drop down
- Click the State wide reports dropdown
- Open the Treasury Folder
- Select the ADB Report
- Enter the required material for the selected report and run it
ATS (Automatic Tranaction Submission) tool
- Select File > Save As and pick the location you want to save the report (be sure to save it as an Excel file)
- Toggle back to Advantage
- Using the Search feature, navigate to Page Search
- Enter Page Code = ADS
- Click Browse
- Click Automated Transaction Submission
- Click Browse
- Find the ADB spreadsheet and click Open
- Click Upload
Statistical Unit (STAT)
- Jump to the STAT table
- Verify the data in the Statistical Group, Statistical Record Id, Description and Statistical Unit fields were uploaded correctly
4. Run the Ledger Engine
The Ledger Engine job will be processed to update the Ledgers. The Cost Allocation Ledger is a summary of the Accounting Journal. It includes all of the COA elements that were specified in the PBDIST records.
5. Run the Cost Allocation chain job with parameters.
Process the Offline Validation Run Mode and specify parameters in the CAPA Table
Process the Base Accumulation Run Mode and specify parameters in the CAPA Table
Process the Compute Allocation Run Mode and specify parameters in the CAPA Table
Submit a request to the CGI Technical team for Generate Document
5. Review the Chain Job Reports and the CA transaction.