U.S. Geological Survey Manual
U.S. Geological Survey Instructional Memorandum
No. APS 2003-02
Issuance Date: February 6, 2003
Expiration Date: September 30, 2003
Archive Date: January 21, 2009
Subject: Federal Funding for the Water Resources Cooperative Program
Instructions: IM replaced by the Financial Operating Procedures Handbook, Chapter 4 Agreements Used to Accept Funds, Section 4 States, Counties, Municipalities, U.S. Territories and Tribal Governments, Worksheet 5 Administrative Matching Funds Worksheet.
In fiscal year (FY) 2003, what will U.S. Geological Survey (USGS) include in the Federal share of cooperative work performed with States and localities in the Water Resources Cooperative Program?
The USGS policy is to account for the full costs of every project. For appropriated projects, the accounts tied directly to the project record the direct costs. Facilities costs chargeable to appropriated dollars, however, are recorded in a holding account in each cost center and are not distributed to accounts. Similarly, USGS uses Science Support funds to pay the appropriated share of bureau costs and does not use Federal Financial System (FFS) to distribute bureau costs to accounts funded by appropriated dollars. To calculate the full costs of a project funded from appropriated dollars, a cost center adds facilities costs and bureau costs to the costs recorded in the relevant FFS accounts.
The USGS includes all its costs when it calculates the Federal share of a cooperative project, not just the costs recorded in the accounts tied to the project. Therefore, the Federal share listed on a joint funding agreement is greater than the funding or costs recorded in the accounts tied to the project, because the accounts do not include either facilities costs or bureau costs.
In FY 2003, USGS also includes in the Federal share of a cooperative project a portion of the funds appropriated for the Water Resources Cooperative Program and expended in the regional offices. These funds also do not appear in the FFS accounts tied to a cooperative project.
In summary, the Federal share of a cooperative project includes direct costs and common services costs, both of which appear in the FFS accounts tied to the cooperative project, and bureau costs, facilities costs, and costs incurred by the regions on behalf of the Water Resources Cooperative Program, none of which appear in the FFS accounts tied to the cooperative project.
How can a cost center estimate the Federal share of cooperative work performed with States and localities in the Water Resources Cooperative program?
A cost center can use the attached worksheet to calculate the Federal share. A cost center needs the following information for the worksheet:
The last item is a percentage calculated by dividing the Water Resources Cooperative Program funds expended by the regions by the total of all other Water Resources Cooperative Program funds. The Associate Director for Water provides the Water Resources Regional Costs rate once USGS receives its FY 2003 appropriation.
To use the worksheet, a cost center must also estimate the percentage of costs recorded in the FFS account tied to the cooperative project that will be charged to the Federal (appropriated) share. Adjusting this percentage will change the distribution of total costs between the Federal share and the cooperator share and allow the cost center to arrive at the desired distribution of costs for the agreement.
In the example on the attached worksheet, the cost center set out to create a joint funding agreement with costs of roughly $3.2 million divided evenly between the Federal share and the cooperator share. Using the “Goal Seek” tool in Excel, the cost center determined that assigning 48.85 percent of the costs recorded in the FFS account tied to the cooperative project will produce a Federal share for the entire joint funding agreement of 49.99 percent.
How should a cost center use the “Goal Seek” tool in Excel?
To find the fund split percentages that will yield the desired distribution of total costs between the Federal share and the cooperator share, one clicks on cell C38 in the worksheet, opens the Excel Tools Menu, and chooses “Goal Seek.” In the second box in Goal Seek, one enters the desired percentage for the Federal share of the cooperative agreement. If the goal is 50 percent of the costs in the Federal share, one enters “0.50.” One then clicks on the third box in Goal Seek and types in c19, the cell that contains the Federal percentage for the FFS funds split. Excel then calculates the funds split percentage that assigns 50 percent of the total costs to the Federal share.
How should a cost center document its calculation of the Federal share of a cooperative project?
In FY 2003, USGS includes in the Federal share of a cooperative project costs that do not appear in the FFS account tied to the cooperative project. Therefore, a cost center needs to document how it calculated the Federal share. In other words, a cost center needs to document a bridge between the estimated costs in the joint funding agreement and the funding amounts in FFS and BASIS+ and the funds split percentages entered in FFS.
Completing the worksheet and including the name of the cost center, the name of the cooperator(s), the project and account number, along with the date will create the necessary documentation. A cost center should attach the completed version of the worksheet to the joint funding agreement and include it in the official file.
How should a cost center otherwise use the worksheet calculations?
Besides calculating the distribution of costs between the Federal share and the cooperator share, the worksheet calculates the gross funding that should appear in BASIS+ and in FFS and the net funding that should appear in FFS.
For the Federal share, gross funding in BASIS+ and FFS equals the sum of direct costs and common services costs. Net funding in FFS equals direct costs.
For the cooperator share, gross funding in BASIS+ equals the sum of direct costs, common services costs, facilities costs, and bureau costs. Gross funding in FFS equals the sum of direct costs, common services costs, facilities costs, and bureau costs minus the dollar value of in-kind services. Net funding in FFS equals the sum of direct costs and facilities costs minus the dollar value of in-kind services.
The worksheet also calculates the funds split percentages that a cost center should enter into the funds split table in FFS. The USGS Business Practices Handbook, Chapter 2, section L5 contains further instructions on entering funds split information into FFS.
Return to Instructional
Return to Survey Manual Home Page