Community Investment Impact System (CIIS) CIIS 3.4 Release CDE TLR Excel Upload May 2006

1. US Department of the Treasury Community Development Financial Institutions Fund Community Investment Impact System (CIIS) CIIS 3.4 Release CDE TLR Excel Upload May 2006

2. Introduction • You should attend this session if you are a CDE with questions about: • The Transaction Level Report (TLR) functionality • Completing the TLR • Certifying and submitting the TLR • Please feel free to ask questions throughout the presentation.

3. Presentation Outline • 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

4. 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 Introduction Organization’s Data Submit Transactions to CIIS Pending Transactions Pre-Certify / Return to Pending Users may copy Certified TLR from previous Reporting Period to Current Pending. Pre-Certified Transactions Certify Certified Transactions Users may generate a copy of Certified TLR to current Excel Template for reference and/or future reporting.

6. Prepare TLR 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. “As of Date” All data must be reported as of the reporting period end date unless otherwise noted.

7. Prepare TLR • TLR Data Point Instructions (Example 1) Excel Column Double Box indicates “Mandatory Data Field” General Validation requirements Describes data format for Excel Template.

8. Prepare TLR • TLR Data Point Instructions (Example 2) Excel Column Thick Box indicates “Conditionally Required” Data field may be used to measure compliance with the Organization’s Assistance Agreement General Validation requirements Describes data format for Excel Template.

9. 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 Prepare TLR • FY 2005 Mandatory CDE Data Points

10. Prepare 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. Crosswalk • 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. Crosswalk • 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).

13. Prepare 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 Template

15. Prepare Excel Template • 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.

16. Prepare Excel Template • 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.

17. Submit Excel TLR Organization’s Data • On CIIS Home Page: • Select “TLR” menu  “Data Entry” • On Data Entry screen: • Select “Excel Upload” • Identify pathname. • Select “Upload Excel data”. Submit Transactions to CIIS Pending Transactions

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

19. Submit Excel TLR • “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

20. Manage Submitted TLR • 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 TLR

22. Manage Submitted TLR 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.

23. Manage Submitted TLR • 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.

24. Manage Submitted TLR • Display of Pending Transactions • Pending 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

25. Pre-Certify Transactions Organization’s Data • Pre-Certify all Pending Transactions to be certified. • Users may Pre-Certify transactions when Transactions status = “Address Verification Passed” Submit Transactions to CIIS Pending Transactions Pre-Certify / Return to Pending Pre-Certified Transactions

26. Pre-Certify Transactions • 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.

27. Certify TLR Organization’s Data Submit Transactions to CIIS Pending Transactions Pre-Certify / Return to Pending Users may copy Certified TLR from previous Reporting Period to Current Pending. Pre-Certified Transactions Certify Certified Transactions Users may generate a copy of Certified TLR to current Excel Template for reference and/or future reporting.

28. Certify TLR • 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

29. FY 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 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 information • Additional CIIS information is available at www.cdfifund.gov/what_we_do/ciis.asp • Contact the CIIS Help Desk at ciishelp@kearneyco.com or (703) 373 -1516