1 community investment impact system (ciis) ciis 3.4 release cde tlr excel upload may 2006 community...
TRANSCRIPT
1
Community Investment Impact System (CIIS)
CIIS 3.4 ReleaseCDE TLR Excel Upload
May 2006
Community Development Community Development Financial Institutions Financial Institutions
FundFund
US Department of the TreasuryUS Department of the Treasury
2
IntroductionIntroduction
You should attend this session if you are a CDE with questions about:1.The Transaction Level Report (TLR) functionality
2.Completing the TLR
3.Certifying and submitting the TLR
Please feel free to ask questions throughout the presentation.
3
TLR Introduction Who Should Use TLR Excel Upload? Prepare TLR Crosswalk Prepare Excel Template Submit Excel TLR Manage Submitted Transactions Pre-Certify Transactions Certify TLR FY 2006 Reports
Presentation OutlinePresentation Outline
4
Who Should Use TLR Excel Upload?Who Should Use TLR Excel Upload?
Organizations with a large amount of data (more than 10 transactions).
Organizations with a lack of technical support. Organizations with a large amount of data &
technical support should consider using XML.
5
TLR IntroductionTLR Introduction
Pending Transactions
Pre-Certified Transactions
Certified Transactions
Use
rs m
ay c
opy
Cer
tifie
d T
LR fr
om p
revi
ous
Rep
ortin
g P
erio
d to
Cur
rent
P
endi
ng.
Organization’s Data
Submit Transactions to CIIS
Pre-Certify / Return to Pending
Certify
Users may generate a copy of Certified TLR to current Excel Template for reference and/or future reporting.
6
Prepare TLRPrepare TLR
“As of Date”All data must be reported as of the reporting period end date unless otherwise noted.
Report Period
Include all loans and investments that were outstanding at any time during the reporting period.
The reporting period for the FY 2005 TLR is the Organization's fiscal year ending in 2005.
7
B Originator Transaction ID Report the originator's unique identification for the transaction
MANDATORY DATA FIELD
Validations: • Each Originator Transaction ID may only be used for one transaction per submission year.• Organizations MUST use the same Originator Transaction ID for each submission year that a particular transaction is submitted.• The Originator Transaction ID may NOT exceed 20 characters in length.
Excel Data Format
Text
Excel Column
Double Box indicates “Mandatory Data Field”
Describes data format for Excel Template.
General Validation requirements
Prepare TLRPrepare TLR
TLR Data Point Instructions (Example 1)
8
TLR Data Point Instructions (Example 2)
Prepare TLRPrepare TLR
AU Days DelinquentReport the number of days that a required loan payment is past due at the reporting period end. If a borrower made a partial payment on the due date but still owes any part of the payment, count that payment as past due.
CONDITIONALLY REQUIRED IF:
Transaction Type =• Term• Line of Credit OR• Other
COMPLIANCE
Validations: Transaction Type =• Term Loan• Line of Credit• Debt with Equity OR• Other
Excel Data Format
Numeric
Excel Column
Thick Box indicates “Conditionally Required”
Data field may be used to measure compliance with the Organization’s Assistance Agreement
Describes data format for Excel Template.
General Validation requirements
9
Prepare TLRPrepare TLR
FY 2005 Mandatory CDE Data Points• Originator Transaction ID• Investor TIN• QLICI Type / Investee Type• Date Originated• Original Loan/Investment Amount• Refinancing• Purpose• Transaction Type• Loan Status• Investee & Project Address(es)• Disbursements • Related Entity• QLICI Level• Better Rates & Terms• Areas of Higher Distress
10
Prepare TLRPrepare TLR
FY 2005 Conditionally Required CDE Data Points
• Investee TIN• Advance Purchase Commitment / Seller Organization• Financing - Constr or Perm / Take-out or Acq / Acq or Rehab• Rates & Terms• QALICB Type• Business Description• Principal Balance Outstanding• Projected Impacts
• Fair Value at End of Reporting Period• Jobs at Reporting Period End• Capacity of Community Facility• Square Feet – Office / Retail / Manufacturing• Housing – Rental / Sale• Affordable Housing - Rental Sale
11
CrosswalkCrosswalk
The Crosswalk translates an Organization’s data entries into CIIS-defined values.
Entire Crosswalk must be complete to upload Excel spreadsheet in CIIS.
CIIS saves the Organization’s Crosswalk from year-to-year. Update for new data points annually. Update for changes to your system.
CIIS Crosswalk values: www.cdfifund.gov/what_we_do/ciis.asp CIIS website “Print” “Crosswalk” TLR Instructions
12
Accessing Crosswalk: CIIS Homepage Select “TLR” “Crosswalk Setup”
Data Entry in Crosswalk:
It is critical that the Crosswalk entry represent the exact text being transmitted. All transmitted data must match the identified crosswalk option(s).
CrosswalkCrosswalk
13
Prepare Excel TemplatePrepare Excel Template
Excel Template CIIS Homepage “TLR” menu
“Template Download” “CDE Excel”
www.cdfifund.gov/what_we_do/ciis.asp “CDE TLR - Excel Upload Template”
14
HINT:
If you Certified a TLR in a previous Report Period, you should begin by downloading the previous report instead of a blank template.
Prepare Excel TemplatePrepare Excel Template
15
Users may retrieve Certified transactions for the selected Fiscal Year in an Excel Template.Simply start the retrieval process and
check back later to obtain the file. Users do not need to remain logged into CIIS while the file generates.
Users may retrieve Certified Transactions for a Prior Fiscal Year either in an Excel Template OR Online.
Prepare Excel TemplatePrepare Excel Template
16
Using the Excel Template Do NOT change, update, re-order, or
manipulate the Excel Template. Format Numeric Fields
Review “CIIS Values” in instructions. Do NOT use “Reserved Characters” Multiple Data Points for one
Transaction (Project Address) Each response on a separate row.
’ * ()
‘ < ^
“” > @
# = +
& % !
OriginatorTransactionID
DateOriginated
OriginalLoanInvestmentAmount Purpose
TransactionType
ProjectStreetAddressLine1 ProjectCity
ProjectState
ProjectZipCode5
Tran -1 1/1/2003 500000.00 RERHCOM EQTYINV 1500 Pennsylvania Avenue, NWWashington DC 20220Tran -1 601 13th St., NW Washington DC 20005Tran -1 601 13th St., NW Washington DC 20005Tran -1 4501 Ford Ave Alexandria VA 22302
Prepare Excel TemplatePrepare Excel Template
17
Submit Excel TLR Submit Excel TLR
On CIIS Home Page: Select “TLR” menu “Data
Entry”
On Data Entry screen: Select “Excel Upload”
Identify pathname. Select “Upload Excel data”.
Pending Transactions
Organization’s Data
Submit Transactions to CIIS
18
HINT:
The Fund recommends that users attempt to upload only 2-3 transactions on the first submission.
Once the user has successfully transmitted and validated the 2-3 transactions, successfully formatting, submitting, validating the entire portfolio should be simpler.
Submit Excel TLR Submit Excel TLR
19
“Uploaded file is not an ms excel file” Displays when Excel file is still OPEN on User’s Desktop
“Fatal Error: Missing Originator Transaction ID.”Displays when File is missing a ‘key’ data point.
“CIIS has received and is processing the uploaded file.” Successful Submission - CIIS has received file and begun Validation process
Submit Excel TLR Submit Excel TLR
20
Manage Submitted TLRManage Submitted TLR
1. General Validations CIIS will run General Validations, as noted in the
instructions. Error reports
Once submission is complete users may log off while CIIS validates TLR.
CIIS provides a report of any General Validation errors.
To make corrections: Re-Submit Excel OR Make corrections online.
Users may download an Excel file only “Error” transactions.
21
Manage Submitted TLRManage Submitted TLR
22
2. Address Verification Address Verification tests for “geocodable”
(ability to map) Investee & Project Addresses. Address Verification occurs after transaction
“passes” all General Validations. Error report
CIIS provides a report of submitted addresses that are not “geocodable.”
To make corrections: Re-Submit Excel OR Make corrections online.
Manage Submitted TLRManage Submitted TLR
23
Transaction Status (Pending List) Entry - Transaction is not validated. Error – Errors exist in General Validations Error: Address Verification Failed - Passed General
Validations & Failed Address Verification Address Verification Passed – Passed General
Validations & Address Verification. Ready to Pre-Certify.
Manage Submitted TLRManage Submitted TLR
24
Display of Pending TransactionsPending Transactions List displays up to 200
transactions per page.Use “Next” & “Previous” buttons to view additional
pages of transactions.
Organize Pending Transactions “Filter” Pending Transactions by Transaction Status For example: View all transactions “Address
Verification Passed” status
Manage Submitted TLRManage Submitted TLR
25
Pre-Certify Transactions Pre-Certify Transactions
Pre-Certify all Pending Transactions to be certified. Users may Pre-Certify transactions when Transactions
status = “Address Verification Passed”
Pending Transactions
Pre-Certified Transactions
Organization’s Data
Submit Transactions to CIIS
Pre-Certify / Return to Pending
26
Users may view Pre-Certified transactions’ key data points online.
Users may Pre-Certify multiple times. Users may send Pre-Certified transactions back
to Pending in batch. Users may download Pre-Certified transactions to
Excel Template.
Pre-Certify Transactions Pre-Certify Transactions
27
Certify TLRCertify TLR
Pending Transactions
Pre-Certified Transactions
Certified Transactions
Use
rs m
ay c
opy
Cer
tifie
d T
LR fr
om p
revi
ous
Rep
ortin
g P
erio
d to
Cur
rent
P
endi
ng.
Organization’s Data
Submit Transactions to CIIS
Pre-Certify / Return to Pending
Certify
Users may generate a copy of Certified TLR to current Excel Template for reference and/or future reporting.
28
CIIS has a single Certification for ILR and TLR.
Organizations may only Certify one time per Report Period.
Users may Certify TLR when:ILR is complete.TLR – There are one or more Pre-Certified
Transactions.TLR – There are no Transactions in the Pending List.
How to Certify: Select “Certify” from CIIS home page
Certify TLRCertify TLR
29
FY 2006 ReportsFY 2006 Reports
Purpose
FY 2005 –Business Loans/Investments:
Business – Fixed Assets
Business – Working Capital
FY 2006 Update: Change to Business Loans/Investments:
Microenterprise
Business
30
FY 2006 ReportsFY 2006 Reports
• FY 2005 - Projected Jobs• Projected Jobs to be Created
– if purpose = business, report business jobs– if purpose = real estate, report construction jobs
• FY 2006 Update: Additional Detail in Projected Jobs
• Projected Jobs to be Created – Business• Projected Jobs to be Created – Construction• Projected Jobs to be Created – Tenant Jobs
31
Useful informationUseful information
Additional CIIS information is available at www.cdfifund.gov/what_we_do/ciis.asp
Contact the CIIS Help Desk at [email protected] or (703) 373 -1516