# Presentation: 671113

## Trading Partner AnalysisInter-office Reconciliations

- Marion Hoch
- 301-903-4216
- Financial Statement/Superusers Workshop
- March 20 – 22, 2007

## Presentation

- Trading Partner Guide
- Trading Partners
- Trading Partner Function
- Trading Partner Reports
- Confirmations/Reconciliations
- Questions

## Trading Partner Guide

- The guide is available on line at _[http://www.mbe.doe.gov/efasc/frad/otherguidance.htm](http://www.mbe.doe.gov/efasc/frad/otherguidance.htm)_
- Purpose of the guide is to describe STARS trading partner code requirements.
- CF 11 is responsible for updating the Trading Partner Guide.
- The guide is updated when Treasury changes their valid trading partner table.
- When there is a change in the trading partner code rules.
- Changes in the OPI to Allottee Values Crosswalk.
- New reports are developed, old reports are updated or deleted.

## Trading Partners

- Trading Partner Codes are 2 or 4 digit codes that identify other federal agencies.
- The trading partner codes are set up by the Department of the Treasury.
- A valid list of trading partners can be found in Appendix 1 of the Trading Partner Guide.

## Headquarters Reporting Trading Partner Information

- Required for quarterly and yearend reporting of intragovernmental and intra-agency (DOE) revenues, costs and transfers, receivables, payables and other assets and liabilities.
- Used to classify financial statement lines as either intragovernmental or public and to _eliminate intra-DOE balances_ from our financial statements.

## Trading Partner Function

- DOE developed a STARS trading partner function process to meet trading partner code reporting requirements.
- The trading partner function is dependent on the set-up of each Standard General Ledger Account.  Each account  is set up to be either:
- F - SGL balance is always Federal
- Y -  SGL balance can be either Federal or non-Federal
- N – SGL balance is always non-Federal

## Trading Partner Function

**Appendix 2 – DOE SGL Trading Partner Code Rules     **
**Lists the 8 position SGL account number**
**Description of the account**
**Trading Partner – is it a federal only, non federal only or either.**
**Other Conditions – such as trading partner 89 only (DOE)**
** ****Not trading partner 89.**
**Funds and SGL combinations determine the trading partner.  One example Revenue federal or not federal depending on fund.**
**Specific trading partner only such as 20, 99, (Treasury).**
**Certain funds may only be federal and others may only be non-Federal for the account.**

## Trading Partner Function

- Table A of the guide has a detailed flow chart that shows how the trading partner function works.

## Trading Partner Function

**The trading partner function may override data that you have sent in your files.**
**If your file included SGL 2110100 (Accounts Payable Other Federal Agencies) with a blank in the trading partner field the Trading Partner function will change the trading partner to 00. **
**This makes it a federal trading partner as required by the trading partner function with an unknown trading partner**.

## Trading Partner Function

- If your file includes SGL 1310N200   (_Accounts Receivable, Non Federal_) with a 20 in the trading partner field and the OPI for Treasury the function will change the SGL account to a Non Federal account.  The SGL will be changed to 1310 with a Non Federal Attribute for reporting purposes.
- SGL 52000900 Revenue from Services Provided to Others is always changed to a F when the fund is 00556 (3Y) or 00910 (3T), it is an 89 when the fund is 02400 (WF).

## Reports

- DOE GL Journal Detail Report (does not have the trading partner function logic applied)
- Reports with the trading partner function logic.
- DOE Abnormal Balances Report
- DOE Trading Partner FACTS 1 Exception Report

## Reports

- DOE Confirmation of Inter Office Balances – Nominal Accounts
- DOE Confirmation of Inter Office Balances – Real Accounts
- DOE Confirmation of Inter-Entity Funded Balances – Real Accounts
- DOE Financial Statements

## Helpful Reports For Analyzing Trading Partner Data

- DOE AR Customer Trading Partner by Customer ID Report – look up the customer transaction posted to GL from AR module.
- DOE AR Customer Trading Partner by WFO Report – look up supplier/trading partner .
- DOE Trial Balance by Trading Partner Code – trial balance with a specific trading partner parameter.

## Helpful Reports For Analyzing Trading Partner Data

- DOE GL Journal Detail Report _by_ Trading Partner – identifies the specific transactions in the GL for a specific trading partner.
- DOE GL Detail Report _with_ Trading Partner – identifies the specific transactions in the GL without specifying a specific trading partner.

## Hint

- Reports _by_ trading partner – must use a specific trading partner.
- Reports _with_ trading partner – must not use a specific trading partner.

## Reports

- The variance reports must be run _at least_ quarterly and all variances _must be_ corrected  for the June and September Financial Statements.
- Auditors do their preliminary audit work of the financial statements on third quarter data.
- Interagency items are included in the audit.

## DOE Abnormal Balances Report 

| DOE Abnormal Balances Report |  |  |  |
| --- | --- | --- | --- |
| January 2007 |  |  |  |
| Sum of Net Amount |   |   |   |
| Allottee | SGL | TP Code | Total |
| 92 | 21100100 | 00 | $2,100.00  |
|   | 21100100 Total |  | $2,100.00  |
|   | 22130000 | 24 | $793,927.11  |
|   | 22130000 Total |  | $793,927.11  |
|   | 23200800 | 14 | $4,999,730.00  |
|   | 23200800 Total |  | $4,999,730.00  |
| 92 Total |   |   | $5,795,757.11  |
| Grand Total |  |   | $5,795,757.11  |
|  |  |  |  |
| Note liabilities should be credits. |  |  |  |
|  |  |  |  |

**Notes:**
The abnormal balance reports gives you all of the abnormal balances, I pivioted the trading partners.  You should probably be reviewing all of your abnormal balances.  

Liabilities are normally credits, sometimes there is an explanation for the abnormal balances.

## DOE FACTS 1 Trading Partner Exception Report

| DOE FACTS 1 Trading Partner Exception Report Allottee 92 |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- |
| January 2007 data |  |  |  |  |  |  |
| Sum of Net Amount |   |   |   |   |   |   |
| Allottee | Fund | SGL | Trading Partner | OPI | Trading Partner Should Be | Total |
| 92 | 02250 | 21100100 | 89 | RP90 | N89 | ($2,100.00) |
|   |   |   |   | RP90 Total |   | ($2,100.00) |
|   |   |   | 89 Total |   |   | ($2,100.00) |
|   |   | 21100100 Total |  |   |   | ($2,100.00) |
|   | 02250 Total |  |   |   |   | ($2,100.00) |
| 92 Total |   |   |   |   |   | ($2,100.00) |
| Grand Total |   |   |   |   |   | ($2,100.00) |
|  |  |  |  |  |  |  |
|  |  |  |  |  |  |  |
| SGL 21100100 should be federal but not using trading partner 89. |  |  |  |  |  |  |

**Notes:**
I like this report because it tells you what the trading partner SGL combination should be.

## DOE Confirmation of Inter Office Nominal Accounts

| Confirmation of Inter Office Nominal Accounts |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- |
| As of January 2007 |  |  |  |  |  |  |
|   |   |   |   |   | Data |   |
| Office | OPI Allottee | SGL | Trading Partner | OPI | Sum of Allottee Net Amount | Sum of Reciprocal Net Amount |
| 93 | 95 | 52000900 | 89 | SE90 | $0.00  | $11,717.76  |
|   |   |   | 89 Total |   | $0.00  | $11,717.76  |
|   |   | 52000900 Total |  |   | $0.00  | $11,717.76  |
|   |   | 61000000 | 89 | SE90 | $0.00  | $416.70  |
|   |   |   | 89 Total |   | $0.00  | $416.70  |
|   |   | 61000000 Total |  |   | $0.00  | $416.70  |
|   | 95 Total |   |   |   | $0.00  | $12,134.46  |
|   | UNASSIGNED | 61000000 | 89 | 0399 | $45,422.25  | $0.00  |
|   |   |   | 89 Total |   | $45,422.25  | $0.00  |
|   |   | 61000000 Total |  |   | $45,422.25  | $0.00  |
|   | UNASSIGNED Total |  |   |   | $45,422.25  | $0.00  |
| 93 Total |   |   |   |   | $45,422.25  | $12,134.46  |
| Grand Total |  |   |   |   | $45,422.25  | $12,134.46  |
|  |  |  |  |  | Allottee Running Report | Other allottees OPI |
|  |  |  |  |  |  | allottee column |

**Notes:**
I picked this report because it has unassigned because the OPI is not linked to a DOE OPI, it is an unknown OPI.  The office running this report is 93 and the first allottee that sent a file with the OPI of SE90 is allottee 95.  I would have expected SE to have a cost and revenue to offset the Western entries.  

Sometimes we have timing differences with the revenues and expenses.

## Slide 20

| DOE Confirmation of Inter Office Real Accounts |  |  |  |  |
| --- | --- | --- | --- | --- |
| As of January 2007 Allottee 92 |  |  |  |  |
|   |   |   | Data |   |
| Office | OPI Allottee | SGL | Sum of Allottee Net Amount | Sum of Reciprocal Net Amount |
| 92 | 34 | 1310D500 | $656,809.00  | $0.00  |
|   |   | 1310D600 | $0.00  | $0.00  |
|   | 34 Total |   | $656,809.00  | $0.00  |
|   | 60 | 1310D500 | $40,902.00  | $0.00  |
|   |   | 1310D600 | $0.00  | $0.00  |
|   | 60 Total |   | $40,902.00  | $0.00  |
|   | 95 | 1310C100 | $0.00  | $0.00  |
|   |   | 1310D500 | $6,961.60  | $0.00  |
|   |   | 1310E500 | $0.00  | $286.12  |
|   |   | 1310R700 | $0.00  | $0.00  |
|   |   | 21100100 | ($2,100.00) | $0.00  |
|   |   | 21100600 | $0.00  | ($4,548.00) |
|   |   | 21900000 | $0.00  | ($214,844.00) |
|   |   | 23200100 | $0.00  | $0.00  |
|   | 95 Total |   | $4,861.60  | ($219,105.88) |
|   | 96 | 1310D500 | $0.00  | $0.00  |
|   |   | 1310E700 | $0.00  | $4,145,699.13  |
|   | 96 Total |   | $0.00  | $4,145,699.13  |
| 92 Total |   |   | $702,572.60  | $3,926,593.25  |
| Grand Total |   |   | $702,572.60  | $3,926,593.25  |
|  |  |  | Allottee 92 | All Other Allottees |
|  |  |  |  |  |

**Notes:**
This is the real accounts (accounts that do not close) run for allottee 92.  I would expect the two columns to net to 0.  If you look at the next slide you can see the record that allottee 95 sent with an OPI of Bonneville Power.

## DOE Confirmation of Inter Entity Balances Real Accounts  

| DOE Confirmation of Inter Entity Report |  |  |  |
| --- | --- | --- | --- |
| As of January 2007 Office 60 |  |  |  |
|   |   | Data |   |
| Office | OPI Allottee | Sum of Allottee Net Amount | Sum of Reciprocal Net Amount |
| 60 | 01 | $231,875.23  | $0.00  |
|   | 02 | $39,589.60  | $0.00  |
|   | 30 | $81,696.77  | $0.00  |
|   | 31 | $7,958.65  | ($82,672.29) |
|   | 33 | $0.00  | $0.00  |
|   | 34 | $6,481.50  | $0.00  |
|   | 36 | $75,627.44  | $0.00  |
|   | 60 | $922,627.46  | $0.00  |
|   | 63 | $1,481.79  | $0.00  |
|   | 92 | $0.00  | $0.00  |
|   | 94 | $0.00  | $0.00  |
|   | 95 | $1,258.40  | $0.00  |
|   | ALL | ($36,443,164.95) | $0.00  |
|   | UNASSIGNED | $50,103,250.14  | $0.00  |
| 60 Total |   | $15,028,682.03  | ($82,672.29) |
| Grand Total |   | $15,028,682.03  | ($82,672.29) |
|  |  | Allottee 60 | Other Allottees |

## Confirmations

- Allottees and their integrated contractors must perform confirmations of material inter-office balances to ensure there are no significant differences at the end of the third quarter and at year end.
- _Communicate_ with the other field offices to clear the confirmation reports.

## Confirmations

- Each allottee will prepare a final year-end reconciliation of interoffice balances using the STARS Confirmation Reports.
- Significant differences for this final reconciliation must be resolved or brought to the attention of the Director of Financial Reporting and Analysis Division, for resolution by the date stipulated in the year end instructions.

## Confirmation Form

- Offices are encouraged to use the standard confirmation format when requesting confirmation of material balances.
- The confirmation forms can be found at _[http://www.mbe.doe.gov/efasc/frad/otherguidance.htm](http://www.mbe.doe.gov/efasc/frad/otherguidance.htm)_

## Slide 25

|  |
| --- |

## Questions?

- Do you have any questions or comments?
- Marion.Hoch@hq.doe.gov