Expenditure Reports – Source of Data

Issue Source: Atlas Helpdesk, Workshops, Atlas User Survey

Affected Parties: Country Offices, Headquarters

Categorization: Reporting – Data Synchronization, Reporting – Transaction Data Integrity, Efficiency – Office Organization/User Capacity

The confusion over project expenditure reports often results from misunderstanding of the various data sources in Atlas and the differences between them. There are three primary sources for reports in Atlas: the General Ledger (including sub-system accounting detail), Projects Resources, and Commitment Control. These three sources serve different purposes and thus will not necessarily agree with each other. The various Atlas reports have been designed to pull their information from the most suitable source.

Commitment Control (KK)

1. Commitment Control is used to manage and control spending against pre-defined budgets. The control of transactions based on available budget and the update of information in the Commitment Control ledgers is handled by the budget check process in each sub-module. There are other processes in the various sub-modules that must take place before a transaction is considered final. And exceptions in these processes, such as a matching exception on a voucher, can sometimes further delay posting to the other data sources. Therefore, while a transaction may be recorded in Commitment Control, it may not be recorded in the other sources of data. And in the end, the transaction may be rejected for other reasons and subsequently removed from Commitment Control.
   
   Further, budget periods are not closed in the same manner as accounting periods. All information in Commitment Control are recorded based on the budget period and not the accounting period. Therefore, the data in Commitment Control is subject to change after accounting periods have been closed. It is therefore not sufficient for official reporting.
   
   Commitment Control is best used to monitor current up-to-the-minute spending and available funds, but it is not intended to be used for any reporting to donors, government counterparts, or other outside parties.

2. General Ledger and Sub-module Accounting Ledgers (GL)
   The General Ledger serves as the official financial record. UNDP’s financial statements are produced from the General Ledger, as are most reports produced for outside parties. Processes such as encumbrance accounting have been developed by UNDP so that the financial information recorded in the General Ledger fully meets the requirements of UNDP Financial Rules and Regulations. While we strive to ensure that postings to the ledger are completed as frequently as possible, exceptions and other issues can sometimes delay posting by days or weeks. The Financial Management Dashboard indicators, data cleanup queries, and the move from a yearly to a monthly closure, however, are all intended to decrease the time it takes from to post to the ledger. Based on current schedules and assuming no exceptions to a transaction, it should now take no more than 2 days for a transaction to be reflected in the General Ledger and associated reports.
   
   The General Ledger contains not only project related expenditure information but all financial activity for UNDP and its partner agencies, including balance sheet transactions.
   
   While detail in the General Ledger itself is at an aggregate level, reports and drill-downs link the information back to detailed transactional information that exists in sub-module accounting ledgers. We collectively refer to the General Ledger itself as well as the sub-module accounting ledgers to be the “General Ledger” source in reports.

3. Projects Resource Table (PRT)
   The Projects Resource table contains financial information associated with projects. Unlike the actual General Ledger, information in the Projects Resource table is not aggregated, but is at a detailed transactional level. Further information on finalized project budgets is available from the Projects Resource table. For this reason, many reports on the financial status of Projects are developed from the Projects Resource table. Generally, expenditure information in the Projects Resource table should match expenditure information from the General Ledger. However, there are some exceptions including realized gains and losses and encumbrances. These exceptions are discussed in more detail below.
   
   The existence of the Projects Resource as a separate data source from the General Ledger is due to decisions made by PeopleSoft as the vendor of the Atlas solution. In many other PeopleSoft implementations only a sub-set of expenditures are related to projects. However, at UNDP all expenditures are tied to a project, even administrative costs. The Projects Resource table is intended to provide a single source for project-related information with appropriate transaction details for analysis. Further, it contains activity level detail, which the General Ledger does not unless also including the associated journal and sub-system activity. However, PeopleSoft has also chosen not to include some information such as realized gains and losses in Projects. We have therefore made use of this table in number of situations as it can provide more detailed information without as much expense in system performance.
   There are 3 core reports on project financial activity in ATLAS: Combined Delivery Report (CDR), Cost Sharing Apportionment (CS) and the Project Budget Balance Report (PBB). In addition, some new reports have been released recently such as the Quarterly Progress Report and Periodic Expenditure report.

The CDR is UNDP’s official project expenditure report. It provides information at the aggregate level and does not contain transactional level details such as journal ids or voucher ids. The CDR retrieves its information from the General Ledger source. Users can use the GL-based queries and inquires to drilldown to the detail transactional level. However, many users are unfamiliar with this approach. Instead many offices use the UN_EXP_DETAIL query and Project Transaction Detail to analyze their official expenditures. The information in these detail queries/reports come from a different source than the CDR, the Projects Resources table. Due to the differences in sources described below, however, this detail may not exactly match the GL information reflected in the CDR.

The Cost Sharing Apportionment (CS) report, like the CDR, uses the General Ledger as its source. The CS report is used to identify and resolve any deficit at the fund/project/donor level. It should always match the information in the CDR since both reports utilize the same source.

The Project Budget Balance (PBB) report uses the Projects Resource Table as its source. Therefore, it may not exactly match the information in the CDR or CS reports. However, the UN_EXP_DETAIL query and Project Transaction Detail, which show detailed transactional information, will both agree with this report since they all use the Projects Resource Table as the source.

The differences between the two sources, the General Ledger (CDR/CS reports) and Project Resources Table (PBB report) include the following:

1. Data Source Timing
   There may be slight timing differences between posting to the General Ledger and posting to the Project Resource Table. The Project Resource Table posting is automatic on a nightly basis. The General Ledger posting in the past was done on a weekly basis, but is now typically posted on a daily basis. However, there are occasionally journal errors on one transaction that may prevent a larger set of transactions from posting until resolved. This may add a slight delay to the posting process. Therefore, it is sometimes necessary to wait a few days before both sources are fully updated.

2. Differences in Encumbrance Calculation Methodology
   CDR and PBB will show some differences in calculated encumbrances based on differences in encumbrances in the GL and Projects Resources Table. The encumbrances (also known as un-liquidated obligations) in the GL are created by the Encumbrance Accounting process based on the UNDP Financial Rules and Regulations. The Projects encumbrances are based on a delivered solution provided by PeopleSoft. Though both the PBB and the CDR should include the same POs, the USD equivalent will not match because the encumbrances in the GL are revalued where as the encumbrances in Project Resources Table retain the original USD equivalent value.

3. Double counting of expenditure
   Due to the design of the Encumbrance Accounting process, all GL based reports will have a “double counting” problem because of encumbrances. This is because encumbrance accounting process is only run periodically and posted similar to an accrual. But the reversal of that accrual is not posted until the next iteration of encumbrance accounting. If the PO is paid through a voucher during the period following the last Encumbrance Accounting run, both the PO and the voucher will record the expenditure, and the PO expenditure will not be reversed until the next run of Encumbrance Accounting. This means that any expenditure report from the GL is only accurate if run as of the period end date after encumbrance accounting is run: 30 June, 30 Sep or 31 Dec (after encumbrance accounting has been run for these dates) This ensures that the accurate encumbrance for that period is recorded as well as any vouchers, without any “double counting”.

4. Differences in Realized Gain and Loss
   GL-based reports such as the CDR include realized gain and loss whereas PBB does not. The FX gain/loss represents the difference between the USD equivalent of a voucher as of the invoice date of the voucher and the USD equivalent of the payment as of the accounting date of the payment. This information is not carried over into the Projects Resource Table and is therefore not reflected in the Project Budget Balance report. The impact is more pronounced for countries with higher volatility in exchange rates versus the US dollar.

5. Small Rounding Differences
   The method in which non-USD transactions amounts are converted from the Accounts Payable is slightly different in Projects than it is in the General. This will sometimes result in very small differences in amounts between GL-based reports and Project Resource-based reports. The amount differences are usually less only a few cents and are never more than 1 USD. Therefore, these differences are not material and typically do not present reconciliation problems.

6. Differences in Encumbrance Due to Accounting Date when Closing Purchase Orders
   Encumbrances on the PBB are incorrect when offices close POs and do not enter the correct accounting date in the workbench. This may result in remaining encumbered funds that are returned in the wrong year (or wrong quarter). Typically this occurs when prior year purchase orders are closed shortly after the end of the year but before the prior year is closed. This is because the default accounting date used is the current date, and offices often do not realize they need to change back to the end date of the prior year. Since encumbrance accounting operates based on the status of a PO only (and closed POs are not recorded as open obligations), the funds are returned in GL based reports in the prior year as they should be since the PO is marked as closed. But the PBB will show the funds returned in the following year. Use of the proper accounting date has been emphasized at the recent BOM Regional Workshops and will also be noted on the networks as year-end approaches.

The typical supporting document for the CDR is the query UN_EXP_DETAIL or the Project Transaction Detail report which are based on the Projects Resource Table data source. The Project Transaction Detail report is identical to the query but can be scheduled and is better formatted for readability. Users run these reports and, using Excel, summarize the results by account code. Sum the totals for the analysis types UNACT, UNFEE, and compare to the CDR. There will however be the differences as this query is extracted from the Project Resources Table. These differences, described in detail above, usually relate to encumbrances or to realized gain or loss. Encumbrances can be separated from other expenditures because they have their own analysis group, UNCOM, in the report. Realized gains and losses can be separated via the account.

Many of the inconsistencies between the data sources result from the differences between standard system setup and UNDP policies. While ideally we would like to make the data sources as close as possible, making the sources exactly match would be impossible without costly customizations. Unique financial policies such as the requirement to record purchase order obligations against the ledger also contribute to the problem. Users also contribute to the data problems by using wrong accounting dates when closing purchase orders; wrong accounts in the transactions, etc.

Donors and project counterparts will often question expenditure numbers. Often users become confused when looking for supporting details due to the differences in the various sources. This exposes us to the financial risk as well as to the wrong image.

UNDP has taken several measures to help reduce the problem:

1. Conducted numerous trainings to explain the various differences between data sources
2. The LRC and various BOM departments have updated OnDemand training content with information on reports and the impact of system tasks on reports.
3. Distributed information on reading and understanding reports on the Finance Network, the Regional Support Team (RST) web site, and other areas.
4. Made updates to existing reports in order to reduce inconsistencies. For example, Project Budget Balance was updated to more accurately report encumbrances (although these encumbrances still may not match the official encumbrances in the ledger).
5. Created reporting databases extracting data from the appropriate source. This reporting database is used by certain ATLAS reports and the Atlas Executive Snapshot.

Unfortunately, when Atlas was initially configured, little attention was given in configuring the module for the eventual reporting requirements of headquarters and country offices. Most of the attention on reporting began after transactions already began to be captured in the system. Therefore, the system configuration doesn’t always match the needs of the user community in terms of reporting. We have managed to work around many of these problems by using unique methods of reporting. But some of these methods have a cost in performance may not be the best approach longer term. The move to IPSAS and the possible upgrade of Atlas Financials to a newer version of PeopleSoft will provide some opportunity to revisit those decisions and make adjustments when needed.

For example, the move to IPSAS will eliminate the current Encumbrance Accounting process and will likely eliminate much of the mismatch of expenditures between the General Ledger and Project Resource Table expenditures. And the upgrade of Financials may provide better reporting of activity-level details from the General Ledger.