A Step-by-Step Guide in Preparing Donor Report Using the Query GM_RCARE_969

(1)

A Step-by-Step Guide in Preparing Donor Report

Using the Query GM_RCARE_969

I. INTRODUCTION

The GM_RCARE_969 is a PeopleSoft Query report that extracts information from the Project Costing module, which collects data from all the other modules including the General Ledger, Purchasing, Contracts, Accounts Payable, Asset Management, Accounts Receivable, etc. This query provides a list of all transactions in a Fund Code during the accounting period specified by the user. It presents financial information in the original currency of the transaction and in the base currency of the business unit. As such, it is an ideal tool to use as basis in preparing manual donor reports.

(2)

CARE USA Shared Services Center Page 2

II. INSTRUCTIONS FOR RUNNING THE REPORT

1. Log on to PeopleSoft. Click on Main Menu > Reporting Tools > Query > Query Viewer

2. Type GM_RCARE_969 in the “Begins With” box then click the Search button.

3. Look for the GM_RCARE_969 from the list of Query Names. Click HTML to show the report parameters.

4. Enter all required parameters in the fields provided. You may click on the spy glass, where available, to choose the appropriate value or data. When running this report, user will be asked to provide the following information:

 Fund Code – identifies the award for which the report is being generated

 Accounting Date From – the beginning date of transactions that user wants the report to include

(3)

CARE USA Shared Services Center Page 3 5. Click View Results button to run the report.

(4)

CARE USA Shared Services Center Page 4 7. To export data, click on Excel Spreadsheet next to the “Download Results In”, then choose the

Save as option.

8. Enter a file name.

(5)

III. PREPARING THE DONOR REPORT IN EXCEL

1. In Excel, immediately make a copy of the original spreadsheet before working on the data or making any changes. This will ensure that report can be traced back to the original source. In case of errors, the user can also simply re-do the work using a fresh copy of the original spreadsheet.

2. Select Row 1, and delete.

(6)

4. Filter the data as follows:

a. By Account Code - The report will include all transactions, whether revenues, expenses or balance sheet accounts. Select expense accounts (5XXXXX) only, unless otherwise specified by the donor. (Use Text Filter “Begins with 5”.)

Note: There are some donors that require information on “Unliquidated Obligations”. This would include Advances to Subrecipients. In such a situation, the Country Office should modify the account code filter, to include the appropriate Advance Accounts such as 130000.

b. By Analysis Type – The report will include transactions with various analysis types. Select the following Analysis Types only:

i. ACT - transactions that come from the accounts payable module ii. GLE - transactions that come from the general ledger

iii. CST – Allocated Shared Program Costs

(7)

These transactions must be included in the report. Transactions with other analysis types should not be included.

Note: There are some donors that require information on “Unliquidated Obligations”. This would include Purchase Orders placed with vendors. In such a situation, the Country Office should modify the Analysis Type filter, to include the appropriate Analysis Type COM (Purchase Orders).

c. Additional filters may be applied depending on donor requirements. Data may be filtered by Business Unit, if reporting for a specific BU only. It may also be filtered by PID, in case the donor requires separate reporting by PID, etc.

5. If the donor requires report in the base currency of the Business Unit (US Dollars for CARE USA country offices), translated using the current exchange rate type of “ASK”, use the amount information in the column labeled “BU Amount”. This is second to the last column on the rightmost part of the spreadsheet. (See picture below.)

(8)

7. If the donor requires reporting in local currency, convert the non-local currency transactions into local currency using the specified conversion methodology, following the process outlined in item 6) above.

8. Sort data according to reporting requirement. If the Fund Code has multiple PIDs, and donor requires separate presentation of data for each PID, sort data by PID. If not, then simply sort data by Activity ID. (Activity ID reclassifications should reveal zero sum transactions in the generic Activity ID 9999999999).

9. Insert a subtotal for each Activity ID.

10. Use the Subtotal by Activity ID to report by Donor Line Item. If the country office has not made the reclassification of transactions by Activity ID, then the user will have to make the Activity ID determination at this time. Decide on the appropriate donor line item categorizations for the expenses, and indicate the same in a separate column in the report.

Note: User may want to save this information for future adjustments to reclassify transactions by Activity ID.

(9)

IV. OTHER CONSIDERATIONS

1. Transaction Date versus Accounting Date

For transactions entered through the Accounts Payable module, the Transaction Date

represents the Invoice Date; Accounting Date is when the transaction is reflected in the financial statements.

For transactions entered through the General Ledger, the Journal Date represents both the Accounting Date and the Transaction Date.

The Accounting Date should be used as basis for determining the cut-off date for transactions to be included or not in donor reporting. Example, a voucher with an invoice date of August 29, 2012 will use the same date as Transaction Date. If the Accounting Date of the transaction is September 01, 2012, then the transaction should be included in the report for September. 2. Reporting Match

For reports that include match transactions, the user may run the same GM_RCARE_969 report, and follow the same procedures as outlined above.

3. Reporting Cash Received

Certain grants require financial reports that include history of cash receipts. To obtain this information from PeopleSoft, user may use the delivered report using the navigation path Accounts Receivable>>>Payments>>>Reports>>>Accounting Entries.

a) Enter the From Date and To Date. b) Enter the Business Unit.

c) In the Sort by Ledger field, select “Detail – All” from the dropdown menu.

d) In the Chartfield Selection box, select the account, fund code and project ID chartfields. e) Click Run button.

(10)

g) Select the Administration tab, and then click on the Accounting Entries hyperlink, as shown in the picture below.

h) The report should show cash receipts in both the cash accounts and in the Grants/Contracts Receivable/Payable accounts, by fund code and project ID.

4. Donor Report Format

Donors usually impose financial report requirements in prescribed formats. For reports prepared prior to PeopleSoft implementation, simply update the existing report with new information from PeopleSoft. User is encouraged to establish Prior Reporting Period balances and ensure that these balances reconcile with Scala.

(11)

5. Completeness

Check to make sure that the following transactions are included in the GM_RCARE_969 report: a) International and national staff payroll allocation

b) Shared Program Costs allocation

User may also run the following Query reports to determine whether certain vouchers or journals have been posted:

 SSC_Unposted_Vouchers

 SSC_Journal_Header_Unpost

 SSC_Voucher_Status_Report

These reports provide information on unposted transactions that country office may want to post to be included in the report.

6. Compliance with Report Due Dates

If the country office expects delay in report submission, especially during this period of transition, it is advised that Grants Manager or Project Manager follow these practices in managing donor expectations and compliance with report deadlines:

a) For expired grants with final report requirements:

a. Request for a one-time extension of the report deadline. Be sure the request is reasonable. Avoid requesting for additional extensions.

b. If request is not approved, inform SSC immediately and request prioritization of transaction processing (e.g. SPC allocation).

b) For current grants with interim report requirements:

a. Request for a one-time extension of the report deadline. Be sure the request is reasonable. Avoid requesting for additional extensions.

b. If request is not approved, prepare the report based on actual information recorded in PeopleSoft as of the cut-off date.

c. In subsequent report submissions, ensure that any expenses not previously reported are captured.

7. Support from Manila Shared Services Center

The Shared Services Center has the following responsibilities regarding donor reporting: a) Prepare SF-425 financial report required by the US Government

b) Prepare donor reports required by US-based corporations, foundations and other US-based non-government NGOs for awards managed by CARE USA Headquarters

c) Develop donor report template specifications in behalf of country offices

d) Develop donor report templates and query reports in behalf of the country offices e) Support, assist and advise country offices in preparing reports for other donors

f) Create reporting milestones in the Grants module as part of the award setup, based on reporting frequency and deadlines imposed in the grant agreement. Country offices are expected to update the reporting milestone status upon completion of the report. 8. Updating Reporting Milestones

(12)

award), the Country Office or Headquarters Business Unit responsible in its submission is required to update the Milestone Status to indicate that the milestone is completed. To update the Milestone Status, follow these steps:

a) Navigate to Grants >>Awards >> Award Profile

Enter your Business Unit in the designated field. Enter the Fund Code in the Award ID field, and click on the Search button.

b) Select the Milestones tab.

c) Click on the Spy Glass in the Milestone Status field, and select the status “COM – Completed”.

(13)

9. Use Reporting Tools to review completed and pending reporting milestones Navigate to Reporting Tools >> Query >> Query Viewer.

In the Query Name field, type “GM” and click Search.

Select the report GM_RCARE_MILESTONES and click on the HTML link to run the report.