The checklist below may be used for ITCs whose data is included with their fiscal agent’s data and need a way to must extract their 025 fund's cash, expenditure, receipt and ITC's specific OPU data for the ITCs ITC's financial reporting. (May also be used for ITCs who are their own COG who have other funds but need to extract just 025 fund). The checklist will cover:
- how to import the provided rpd-json files into USAS-R and extract the ITC’s 025 Cash, Expenditure, Revenue and OPU data from their respective grids into spreadsheets
- using provided EMISFFE spreadsheets to copy the required ODE columns and insert them into the newly created redesign spreadsheets.
- adding/editing any additional columns in order to prepare the spreadsheets for EMISFFE import
- importing into EMISFFE.
- 1.Ensure Ensure June of the financial period you are reporting for is marked as 'Current'
- 2. Import the ‘Extract’ .rpd-json JSON files (provided below) into the Report Manager grid
- ITC Cash Extract
- ITC Expenditure Extract
- ITC Receipt Extract
- ITC OPU Extract
- 3. Download the 'EMISFFE First 6 Columns' Excel spreadsheets (provided below) to your computer.
- EMISFFE Cash First 6 Columns
- EMISFFE Expenditure first 6 Columns
- EMISFFE Receipt First 6 Columns
- EMISFFE OPU First 6 Columns
- 4. Generate and format ITC Cash Extract
- In USAS-R, generate the ITC Cash Extract spreadsheet for the 025 funds
- Open the EMISFFE Cash First 6 Columns spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) of the ITC Cash Extract spreadsheet (copying down the data to match the number of cash rows)
- Add the IRN in Column B (format the column as text to include leading zeroes)
- Edit the Fund Class in Column K to a one character code (table of codes listed to the right)
Fund Class codes listed on page 3 of EMIS Cash Record chapter) - Edit the ODE Brief Description in Column I if it exceeds 21 characters.
- Before saving the file in CSV format, remove Line 1 (header row). It cannot be included when importing into EMISFFE. (example below)
- 5. Generate and format ITC Expenditure Extract
- In USAS-R, generate the ITC Expenditure Extract spreadsheet for the 025 funds
- Open the ‘EMISFFE Expenditure First 6 Columns’ spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) of the ITC Expenditure Extract spreadsheet (copying down the data to match the number of expenditure rows)
- Add the IRN in Column B (format the column as text to include leading zeroes)
- Add the General Fund Debt-Bond Retire fund to Column S (column after encumbrances). Enter a blank space into the cells in the column by pressing spacebar (and copy down all records)
- Before saving the file in CSV format, remove Line 1 (header row). It cannot be included when importing into FFE. (example below)
...
- 7. Generate and format ITC OPU Extract
- In USAS-R, generate the OPU Extract for all OPUs associated with the 025 funds
- Open the EMISFFE OPU First 6 Columns spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) on the ITC OPU Extract spreadsheet (copying down the data to match the number of OPU rows)
- Add the IRN in Column B (format the column as text to include leading zeroes)
- Edit the Line Number (Column E). The line number cannot be duplicated so please increment this number
- If the Central Office (Column J) contains a ‘true’, the value of ‘true’ must be changed to ‘C’. If the entity doesn’t have a central office designated, you must enter blank space into the cells in the column by pressing spacebar (and copy down all records).
- Before saving the file in CSV format, remove Line 1 (header row). It cannot be included when importing into FFE. (example below)
- 8. Import CSV files into EMISFFE
- Log into ITCs EMISFFE account
- Cash Record
- Click on Cash (QC) and ensure existing Cash record is empty. If it isn't, use the 'Delete All Cash' to remove the existing records
- Click on Home, Import Data and import the ITC Cash Extract CSV file
- Ensure no errors were generated and review the Cash Record (example below)
...
- OPU Record
- Click on Operational Unit (QC) and ensure existing OPU record is empty. If it isn't, use the 'Delete All Operational Unit' to remove the existing records
- Click on Home, Import Data and import the ITC OPU Extract CSV file
- Ensure no errors were generated and review the OPU Record (example below)
- OPU Record
- 89. The QC data in EMISFFE includes the following. If the ITC has Cash Reconciliation, Civil Proceedings and Federal Assistance Summary and Detail information to enter for the reporting period, they may enter it in manually in EMISFFE
- 910. Once the QC data have been imported into EMISFFE, use the ‘Export data’ option to extract the reporting year's “H - July/Financial” data set and select only the ‘QC’ record to export and click on 'Export File'. It will create an IRN#_FFE-SEQ file that will need to be loaded into the Financial Data Source in EMIS-R.
...