Budget Tracking Amounts & Formulas

As part of the initial analysis necessary to implement CGI Advantage Financial, you must determine, define, and configure the types of accounting and procurement transactions that need to be controlled or tracked on your budgets.  CGI Advantage Financial includes over 30 pre-defined budget tracking amount fields for budget tracking purposes.  Examples of delivered budget tracking amount fields include Adopted, Current Budget, Pre-Encumbered, and Cash Expenses. 

Budget tracking amount fields are the "buckets" used system-wide to track different kinds of activities such as budget modifications, accumulation of accounting transactions, and calculated amounts of transactions posted against a budget amount.  Each pre-defined budget amount field is defined on the Budget Tracking Amount (BUDTAM) table.  Not all budget amounts are displayed online and many should not have the online options changed because they do not apply.  A filter on the table selects only amounts that are stand alone and updated by only final documents.  Also displayed are calculated amounts that have a formula that can be flexibly defined.  Amounts automatically calculated by the application, such as those that sum a specific amount field for all children lines on a parent line and amounts updated by pending documents are not displayed.

• Field ID - A required unique ID is assigned to every budget amount.  These ID's are later used to identify buckets when defining formulas and controls instead of using the data attribute names from the database.  Those data attribute names are associated with each ID in a field on the BUDTAM table that is not visible online.  This ID is protected from online changes as it is already likely defined to a formula or constraint.

• Field Name - A required text field is available to assign a name to an amount field to be used for reporting.  The name should match the name displayed to users online when viewing budget query pages.  Changes to the name field on the BUDTAM table do not flow through to various inquiry or summary pages where that amount is displayed.  Changing those items is an HTML change.

• Field Type - A designation is given to each amount field as to whether it is an Expense, Revenue, or Budget amount field.  Primary use of this designation is to identify a list of amounts that are built into a budget structure of a particular type: Expense, Revenue, or Both.   Secondary uses include filtering and editing of fields within the application where a bucket ID is specified.

• Formula ID - Budget amounts that are not stand alone and are not calculated by an application code formula, must have a Formula ID selected.  Many formulas are delivered and new ones can be added directly into the database as a configuration.  Formulas are defined to a single budget bucket, which allows the filtering seen in the pick for Formula ID on the BUDTAM table.

• Pending Flags - When amounts from documents with a Submit Phase of Pending on the Document Control (DCTRL) table, should upon validation, be considered as an 'official' budget update, this flag will be selected.  The term official was used to mean the pending document should add to or take away from budget availability. 

For every stand alone budget bucket, there are two accompanying buckets:  Pending Increases and Pending Decreases.  Documents with the Pending submit phase will update one of these buckets upon validation and not the 'accepted' bucket, even if a workflow rule is not met.  The budget posting routine will then execute all controls that apply to the budget line.  If those controls are to consider the amount from that document upon validation, then the include flag should be selected.  Additionally, if a workflow rule is met and the document does go to pending status and the budget line should be increased or decreased by the amount of that pending document, then the include flag should be selected.

The functionality just described would be put to use if there was $1000 available on a budget line and five payment requests were to be entered against that budget line for $300 each.  Given a pending submit phase on the Document Control (DCTRL) table and a workflow rule being met on each, then the first three documents will accept and the last two will fail.  This functionality must be weighed against the desire to get all five documents into workflow and let the approvers decide which three will be submitted to final for disbursement and which two will not.  Additionally, when approvals are slow in being applied, one document that is eventually rejected will withhold funds from others that would be approved if allowed to get to pending.

When a pending flag is selected, the budget inquiry page will display the total amount for the primary bucket as the total of updates from accepted documents as well as pending documents of that particular pending flag.  The drill down into the screen label of the stand alone bucket will display all three amounts separately: accepted, pending increases, and pending decreases.

Note: if a pending amount is to be considered for a bucket that is often updated by a document that would be closing out part or all of a referenced document that updated another bucket, then the opposite pending flag should be selected for the referenced bucket.  If not, then the liquidation of that budget amount will not be considered with the recording of the new budget amount.

• Include Pending Increases - A flag to select when pending increases to a budget amount are to be considered as actual budget line updates.

• Include Pending Decreases - A flag to select when pending decreases to a budget amount are to be considered as actual budget line updates.

Examples of Delivered Budget Amounts

Not all amount fields are on every delivered budget structure primarily because the Expense/Revenue choice on the Budget Structure (BUDST) table determines which buckets are included.  Additionally, some buckets have been developed and put into baseline for budget structures created more recently than others.  All descriptions about the use of the following amount fields are as they are delivered.  Configurations to the Posting Code setup on a site would alter their use.

Each description of a budget amount below is put in terms of the most prevalent types of updates.  As updates to any stand alone budget amount is determined by the posting code used, there may be situations other than those described where updates are made to the amount field.

• Adopted - Two stand alone amounts have this name, but with different data attributes.  ADPT_AM is the field for recording an official budget amount agreed to by a budget authority for controlling spending.  At some set date within a budget year, adoption of new budget lines and changes to the adopted amount of existing budget lines will cease and only amendments will be allowed.  The setup of Budget Fiscal Year Staging will be that control.  ADPT_REV_BUD is the field for recording an official budget amount agreed to by a budget authority for controlling collection.  For revenue, the adopted amount it is very often an estimate and not a limit on how much can be collected.

• Amendments - Two stand alone amounts have this name, but with different data attributes.  AMND_AM is the field for recording an increase or decrease to a budget line for controlling spending.  AMND_REV_BUD is the field for recording an increase or decrease to a budget line for controlling collection.  For revenue, the amendments amount is very often an adjustment to the original estimate for collection and not used in the calculation of a limit on how much can be collected.

• Allocated - Two stand alone amounts have this name, but with different data attributes.  ALOC_AM is the field for recording allocations for spending budgets and ALOC_REV_AM for revenue budgets.  Allocations are a means of limiting budget availability, namely the Current Budget amount (discussed later). 

The concept of allocation is a means to reserve budget availability without having to create an accounting document (that is, pre encumbrance or encumbrance) that later has to be referenced and liquidated when that reserved amount is being spent.  When the desire is to do a reservation with a budget document the delivered event type, Allocate Expense Budget, or a similar one that is client-created is used.  There are budget formulas delivered to make Original Budget (discussed later) use that amount instead of Adopted.

An entire budget can be adopted with one line on a budget document for 100% of what was authorized, but then another line on a budget document can allocate 75% of that amount.  With the proper formulas selected, the 75% is available to control accounting activity until another budget document is processed to allocate more.  Therefore, the allocation amount serves the purpose of an allotment line, but without the time component.  Many choose to use the allocated amount when there is a need to reserve a budget and in doing so allocate the unreserved budget amount initially.  Later when the reserves should be released, another allocation is done.

• Reversions & Carry Forward - Two stand alone amounts have these names but with different data attributes.  RVRS_AM and CF_AM are used for spending budgets.  RVRS_REV_AM and CF_REV_AM are used for revenue budgets.  Use of these pairs is to record budgets removed (reverted) out of an old year and placed (carried forward) to a new year as part of one of the roll processes done at year end.  The Reversions amount is an amount subtracted to arrive at Current Budget and Carry Forward is an amount added to arrive at Current Budget.  Either or both of these amounts can be included in the calculation of Original Budget if necessary.

• Transfer In & Transfer Out - Two stand alone amounts have these names but with different data attributes.  XFERO_AM and XFERN_AM are used for spending budgets.  XFERO_REV_AM and XFERN_REV_AM are used for revenue budgets.  Use of these pairs is to record budget authority moved out of one line and placed in another line.  While such a transfer can occur with another amount such as amendments, use of the transfer amounts enables an edit to ensure that the transfer out and in amounts equal.  The budget document performs an edit to ensure this as follows:

Total amount of lines with event types defined to the EBTN/RBTN event category must equal the amount of lines with event types defined to the EBTO/RBTO event category.

It is common to find a single document code allowed for just transfers, while other document codes allow all other budget activity other than transfers.  The reason for this is that adoptions and amendments are often performed by budget office personnel while transfers occur at the department level.

On certain structures, a simplification has been made where only one of the two transfer amounts is displayed, labeled as Transfers.  This feature is common on revenue budget structures, but does appear on a limited number of expense budgets.  When transferring with this design, there is a decrease line and an increase line on the budget document to the same event type instead of an increase line to Transfers Out and an increase line to Transfers In.  The budget document edit still enforces a balance because the sum of lines in each event category still works as the event type if the transfer belongs to one event category.  When added together, the amount has to be $0 within that category so that it equals the sum of lines with the other category, which is $0 by default.

When a structure is used that displays only one amount for Transfers, it is important that the Allowed Event Types for Document Code (AETDC) table has only the event type that updates that displayed amount for all document codes of that structure allowing for transfers.  Use of the other event type will result in budget updates being made that cannot be seen online.

Note: Expense budget structure 29 contains a single Transfers amount, which is the XFERN_AM (transfers in).  The delivered event type to update this amount is BG06 because it uses posting code B004.

Note: Revenue budget structures 30, 34, and 42 contain a single Transfers amount, which is the XFERN_REV_AM (transfers in).  The delivered event type to update this amount is BG28 because it uses posting code B016.

• Original Budget & Current Budget - Two calculated amounts have these names but with different data attributes.  ORIG_BUD_AM and CURR_BUD_AM are two used for expense budgets and ORIG_REV_BUD_AM and CURR_REV_BUD_AM are two used for revenue budgets.  Each pair is used to summarize a combination of stand alone budget amounts into a single formula amount for easy online viewing and for budget control definitions.  Exactly what is to be included in each calculated amount field is a client site decision, but as delivered a sample of the formulas are:

Original Budget = Adopted + Carry Forward + Awarded

Current Budget = Original Budget - Budget Reserve + Amendments + Transfers In - Transfers Out

Original Revenue Budget = Adopted + Carry Forward

Current Revenue Budget = Adopted + Carry Forward + Amendments + Transfers

• Uncommitted & Unobligated -These two calculated amounts are used to summarize an available amount of budget after reductions for spending.  Each takes the Current Budget amount, the total available amount of funding from budget documents, and subtracts various spending stand alone amounts for easy online viewing and for budget control definitions.  Exactly what is to be included in each calculated amount field is a client site decision, but as delivered a sample of the formulas are:

Uncommitted = Current Budget - Pre-Encumbered - Encumbered - Accrued Expenses - Cash Expenses

Unobligated = Current Budget - Encumbered - Accrued Expenses - Cash Expenses

• Purchase Reservations - When budget dollars should be reserved on an accounting document, but should not be considered one of the four following amounts (that is, Pre Encumbered, Encumbered, Accrued Expenses, and Cash Expenses), then the Purchase Reservations amount can be used.  This amount is simply a way to separate a type of reservation for future spending.

• Pre Encumbered - The stand alone budget amount most often updated when a purchase requisition, stock requisition, or general accounting pre encumbrance document is processed.

• Encumbered - The stand alone budget amount most often updated when a purchase order or general accounting encumbrance document is processed.

• Accrued Expenses - The stand alone budget amount most often updated when a payment request or general accounting expenditure document is processed to request payment to an external vendor with an object code.  Other updates occur when payroll costs are accrued and internal accounting occurs where cash is not used as an offset, but rather the Due To and Due From clearing accounts are used.

• Cash Expenses - The stand alone budget amount most often updated when a disbursement is processed to request payment to an external vendor with an object code.  Other updates occur when payroll disbursements are recorded and internal accounting occurs where cash is used as an offset.

• Actual Expenses - The calculated budget amount delivered as the sum of Accrued Expenses and Cash Expenses.  The amount provides a total of expenditures similar to that of prior releases of Advantage.

• Unexpended Cash - The calculated budget amount delivered as the difference of Current Budget and Cash Expenses.  The amount is often used for reporting or budget controls as another variation of Unobligated and Uncommitted.

• Unexpended Accrued - The calculated budget amount delivered as Current Budget less Cash Expenses and Accrued Expenses.  The amount is often used for reporting or budget controls as another variation of Unobligated and Uncommitted.

• Charges - The stand alone budget amount exclusively used on cost accounting budgets to record costs indirectly charged to a budget line.  Charges are produced with the Reimbursement chain job or manually by cost accounting personnel.

• Back End Splits - The stand alone budget amount exclusively used on cost accounting budgets to record costs that resulted from a Back End Split process that redistributed costs previously recorded as part of the Reimbursement chain job in Cost Accounting.

• Awarded - The stand alone budget amount exclusively used on cost accounting reimbursable budgets to record the amount assigned to a particular Funding Priority and Funding Line.  This amount and the one below, Revenue Credits, is combined into the amount of available funding for a reimbursable budget line.  That availability formula is: Awarded + Revenue Credits - Cash Expenditures - Back End Splits - Charges.  Other budgetary amounts such as Adopted and Amendments are displayed on reimbursable budgets for other uses, but not for reimbursement availability.

• Revenue Credits - The stand alone budget amount exclusively used on cost accounting budgets to record a project credit that reduces the total amount due by the funding source.  These credits are used when a reduction in Cash Expenditures is not desired to a central expense structure as a result of a desire to adjust a reimbursable budget structure.

• Expected Revenue - The stand alone budget amount used most commonly on the centralized budget structure that equates to what is commonly referred to as an Appropriation Budget.  The amount is equivalent to the Adopted amount for revenue budgets; however; this one is used on budget structures that are both Expense and Revenue budgets.  The two amounts, Expected Revenue and Adopted Revenue, are both supplied for two reasons.  (1) To provide an amount that is familiar to users of prior Advantage Financial products.  (2) To provide another amount if two estimated amounts of revenue are needed.

• Amended Expected Revenue - The stand alone budget amount used to increase the original Expected Revenue amount so that adjustments to the original amount can be tracked separately online.

• Current Expected Revenue - The calculated budget amount that is the sum of Expected and Amended Expected Revenues.  This amount may be used in a budget constraint to define available budget on a structure that is both expense and revenue.

• Total Budget - This calculated amount is intended to summarize one or more other calculated or stand alone amounts into one summary amount of ’total budget’.  The amount may then be used for a budget control definition or for just reporting.  One such formula for the field is to sum of Current Budget and Current Expected Revenue.

• Billed Earned Revenue - The stand alone budget amount most often updated when a receivable document is processed to record an amount owed and billed to an external customer with a revenue source.  For exchange transactions, where goods or services of comparable value are exchanged by both parties, revenue is considered earned when it is realized and earned.  Realized is when goods or services are exchanged for a receivable.  Earned is when an entity has substantially accomplished what it must do to be entitled to the benefits represented by the revenues, that is, when the earnings process is complete or virtually completed.  Both definitions are from FASB Concepts Statement No. 5.

For nonexchange transactions, where an entity receives value without directly giving equal value in return, revenue is considered earned when timing requirements have been met so that the amount is measurable.  See the FASB Statement 33 for more information on this type of revenue.

Other updates to this amount occur for internal accounting where cash is not used as an offset, but rather the Due To and Due From clearing accounts are used.  Requests for payment of a revenue refund will also update this amount until the refund is disbursed.

• Unbilled Earned Revenue - The stand alone budget amount used for receivables when the revenue is considered earned, but cannot be billed because of legal agreements.  Most often recorded with the Accrued Receivable document so that the receivable will not appear on invoices or statements to the customer.

For accounting treatment, it is the same as Billed Earned Revenue, but for budget treatment, it updates a different amount field so that it will not be automatically put into calculated buckets that take in Billed Earned Revenue.  The options are always there to have this amount placed into a formula so that it is treated just like Billed Earned Revenue or to change the posting code setup so that updates from Accrued Receivables update Billed Earned Revenue. 

• Billed Unearned Revenue - The stand alone budget amount used for receivables where the revenue cannot be considered earned.  In this case, there is not any revenue recorded in the sense that it will close into an equity account at year end, but rather a liability is recorded that updates the budget.  Because the receivable is not yet earned and may be cancelled, a liability is created.

• Collected Earned Revenue - The stand alone budget amount most often updated when a Cash Receipt document is processed to record an amount collected from an external customer with a revenue source.  For exchange transactions, where goods or services of comparable value are exchanged by both parties, revenue is considered earned when it is realized and earned.  Realized is when goods or services are exchanged for cash.  Earned is when an entity has substantially accomplished what it must do to be entitled to the benefits represented by the revenues, that is, when the earnings process is complete or virtually completed.  Both definitions are from FASB Concepts Statement No. 5.

For nonexchange transactions, where an entity receives value without directly giving equal value in return, revenue is considered earned when timing requirements have been met so that the amount is measurable.  Refer to the FASB Statement 33 for more information on this type of revenue.

Other updates to this amount occur for internal accounting where cash is used as the offset.  Disbursements of revenue refunds will also update this amount.

• Collected Unearned Revenue - The stand alone budget amount used for collections where it cannot be considered earned.  In this case, there is not any revenue recorded in the sense that it will close into an equity account at year end, but rather a liability is recorded that updates the budget.  Because the money is not yet earned and may have to be returned, a liability is created.

• Total Revenue - The calculated budget amount most commonly defined as the sum of Collected Earned and Billed Earned Revenue.  In addition to reporting and budget controls, the amount can be used in Budget Linking.

• Unrecognized Revenue - The calculated budget amount most commonly defined as the difference between Current Budget - Total Revenue.  Such a definition makes the amount the difference between what was expected for a revenue budget and what has actually transpired against that budget.

• Sum of Child Adopted - The system calculated budget amount that is used only at budget levels above the lowest level.  The amount in this field serves to allow budget guidelines to control the balance of Adopted at the parent and the total Adopted of all the children for that parent.  The amount is not usually visible online.

• Sum of Child Allocated - The system calculated budget amount that is used only at budget levels above the lowest level.  The amount in this field serves to allow budget guidelines to control the balance of Allocated at the parent and the total Allocated of all the children for that parent.  The amount is not usually visible online.

• Sum of Child Original Budget - The system calculated budget amount that is used only at budget levels above the lowest level.  The amount in this field serves to allow budget guidelines to control the balance of Original Budget at the parent and the total Original Budget of all the children for that parent.  The amount is not usually visible online.

• Sum of Child Current Budget - The system has two such calculated budget amounts that are used only at budget levels above the lowest level.  The amounts in these fields serves to allow budget guidelines to control the balance of Current Budget at the parent and the total Current Budget of all the children for that parent.  One such amount exists for expense budgets (CHILD_SUM_4) and another for revenue budgets (CHILD_REV_SUM).  The amounts are not usually visible online.

• Linked Expected Revenue - The system calculated budget amount that is the sum of all budget links defined to an expense budget line for Expected Revenue.  At budget levels above that linked to a revenue budget, the amount is the sum of the linked amount for all child budget lines of a parent budget line.

• Linked Total Revenue - The system calculated budget amount that is the sum of all budget links defined to an expense budget line for Total Revenue.  At budget levels above that linked to a revenue budget, the amount is the sum of the linked amount for all child budget lines of a parent budget line.

• Linked Collected Revenue - The system calculated budget amount that is the sum of all budget links defined to an expense budget line for Collected Earned Revenue.  At budget levels above that linked to a revenue budget, the amount is the sum of the linked amount for all child budget lines of a parent budget line.

Budget Formulas

Budget formulas are defined on the Budget Formula Administration (BFADM) table.  Each formula is defined to just one calculated budget amount.  Every calculated budget amount used that is not automatically calculated by the application (that is, the Child Sum Amounts) must have a formula selected for it on the Budget Tracking Amount (BUDTAM) table.  Choices seen by the pick on that table are those from BFADM filtered for the particular budget amount chosen.

• Formula ID - A required unique ID is assigned to every budget formula.

• Formula Name - A required text field is available to assign a name to a formula for identification purposes when selecting formulas for a calculated budget amount.

• Formula Description - A required text field is available for defining how a formula is to be calculated using names assigned to budgetary amounts.

• Formula Definition - A required field available for defining in terms of budget bucket ID's how a formula is to be calculated.  Each bucket ID should be proceeded with a capital B.  The symbols for greater than (>) and lesser than (<) are prohibited from definition into a formula.  If such is needed, the budget should be controlled with a budget constraint or guideline.

• Bucket ID - The required identification of which calculated bucket a formula applies is specified in this field.  There is no need for a proceeding B in this field.

• Bucket Name - A display only field for the name of the bucket for the formula.  The value is inferred from the Budget Tracking Amount table.

• Needs Initialization Flag - When a formula has one or more of the budget amounts defined in it adjusted so that past calculations now may not be correct, this flag is set to true.  Changes to the Budget Tracking Amount table to either Include Pending flags or the Formula ID field will cause such a compromise.  Budget Inquiry pages will be incorrect as will the Constraint Amount table.

New records can be added to meet any formula needs and modifications to existing ones can be performed.  However, the recommended approach when a delivered record should be changed is to add a new record.  Following this recommendation will ensure that data from future upgrades will not overlay any client-modified record.  When adding new records, a Formula ID should be used that is four-digits.  Delivered formulas will never use an ID that large.