MANAGING YOUR PORTFOLIO WITH IDIS REPORTS

1. MANAGING YOUR PORTFOLIO WITH IDIS REPORTS

2. The Acquisition and Analysis of Formula Grantee Performance and Financial Data Welcome to training on IDIS, BOSMAC07, Spreadsheets, and EXCEL filters. This training will show how to bring IDIS data to the desktop and use EXCEL filters and BOSMAC07’ spowerfuladvancedplusfiltersto make the data more useful. This self-paced training is designed for new CPD employees and those not so new who have yet to explore the full potential of IDIS Reports; BOSMAC07; and spreadsheets. An important note: The deeper your knowledge of the Entitlement programs the greater the reward to be realized from these tools. The presentation consists of some 85+ slides. You may not get through the presentation in one session. Or, you may want to review some of the slides more than once. In such cases there is no need to start again at the beginning. Simplyright click on the slide – any slide – and you will be given a choice as to which slide you want to start with or review again.Some of the slides are linked – (underlined, different font color) e.g., slide 25 - to other slides. To return to the hyperlinked slide click “return.”

3. Before we begin… A little reminder… The Acquisition and Analysis of Formula Grantee Performance and Financial Data 3

4. RESPONSIVE PROFESSIONAL COURTEOUS HELPFUL THESE ARE THE QUALITIES WE ALWAYS BRING TO OUR GRANTEE/PARTNER RELATIONSHIPS 4

5. The Acquisition and Analysis of Formula Grantee Performance and Financial Data • This training will focus on three areas: • IDIS – CPD’s Integrated Disbursement and Information System – interwoven throughout the training; (Slides 7 – 15) • CPD’s BOSMAC07 – an EXCEL based program containing powerful advancedplus filters to analyze Grantee performance and financial data; (Slides 17 – 51) • Understanding spreadsheets and EXCEL filters – how to use the three EXCEL filters to analyze spreadsheet data. (Slides 59 – 87)

6. The Acquisition and Analysis of Formula Grantee Performance and Financial Data As a CPD Representative you will learn the contents of some of the key IDIS Reports and how they can be used with EXCEL filters and BOSMAC07 to allow you to more efficiently and effectively monitor the performance of your assigned grantees. As a CPD Manager you will learn how to use BOSMAC07 as a powerful management tool. We begin with now with how HUD acquires Grantee performance and financial data

7. The Acquisition and Analysis of Formula Grantee Performance and Financial Data DataAcquisition • IDIS (Integrated Disbursement and Information System). IDIS is used to acquire the data. 7

8. The Acquisition and Analysis of Formula Grantee Performance and Financial Data • IDIS (Integrated Disbursement and Information System) IDIS is a mainframe computer system located in HUD Headquarters. See: http://www.hud.gov/offices/cpd/communitydevelopment/training/CDBGTrainingManual.pdf http://lms.learningevolution.com/IDIS/loginhtml.asp?v=IDIS Grantees remotely enter information about their program and performance. Among many other things, they describe activities they are funding; beneficiaries of those activities; funding amounts; accomplishment information and so forth. A few of the 40 IDIS reports are described in subsequent slides. To see all the IDIS Reports click the All IDIS REPORTS button here and on the BOSMAC07 main menu. 8

9. The Acquisition and Analysis of Formula Grantee Performance and Financial Data • DataAnalysis • BOSMAC07(Boston Macro) BOSMAC07can be used to analyzethe data. BOSMAC07,developed by the Boston Office of CPD, is an EXCEL program using Visual Basic coding to get IDIS Reports into an EXCEL format. More importantly, its VB coding contains many advancedplus filters for data analysis. [To see an example of what BOSMAC07 can do for you see slide 88.]

10. The Acquisition and Analysis of Formula Grantee Performance and Financial Data To summarize: IDIS is used to acquire the data BOSMAC07can be used to analyzethe data. We now turn to downloading IDIS Reports – and then examine some of the key reports for CAPER analysis – what they contain and how they can be used

11. IDIS Reports • To download reports from IDIS : • IDIS OnLine: http://www.hud.gov/offices/cpd/systems/idis/reengineering/index.cfm • IDIS legacy system: • Open IDIS and sign in. • Press F4 • Enter E 01 (this takes you to the report selection screen) • Press F1; type (grantee name) • Select your grantee from list (put an “X” next to the name and press ENTER) • Select (up to 10 on a single screen) the report you want to download (e.g. PR02,PR03, etc.), mark priority as immediate “I”, and which year you want to receive. • Press ENTER, then F9, then F6, and finally F8. • Next, download your report. Type “D” and press ENTER. • When your report is listed as ONMAINFRAME, click the Start Menu button (bottom left hand corner of your computer screen) and choose Run…. • Type the following: • “C:\IDISDATA\xpidis space (yourHnumber) space (yourIDISpassword) space p” – • Click OK.  • Note: grantees type the following: • C:\IDISDOWN\RPTDOWN USERID P [no password needed]

12. PR01: Displays all CPD grants (EN) and Program Income (PI) to entitlements and states, and the subfunds and subgrants grantees have created from these grants. (BOSMAC07 uses this report to perform the timeliness test and the ratio of PI to EN test. It is critical that you make sure grantees timely report all PI.) Notice types of money Some Important IDIS Reports and the Information they contain 12

13. NOTE Some Important IDIS Reports and the Information they contain • PR02:List of Activities and real-time financial data by Program Year and project. Contains all CDBG, HOME & ESG activities for years selected in IDIS Report. The Project ID is copied to all activities.

14. PR03:CDBG Activity Summary Report (GPR)for the year selected. This is the Grantee’s performancereport. (BOSMAC07 uses this report to perform the CAPER [PR03a] completeness test) PR06:Summary of Consolidated Plan Projects for Report Year xxxx Report. Gives project level information only. Individual activities are not listed PR07:Drawdown Report by Voucher Number. Gives activity level detailed real-time drawdown financial information only. PR08:Grantee Summary Activity Report. Has all activities for all years for allfunding sources. Funding sources are not identified. Has IDIS matrix code for each Project ID & name, Activity# & name, Committed, Drawn and date of last drawdown IDIS Reports Cont’ NOTE for the PR03: activities that are open for several years have accomplishment data reported in a separate IDIS screen for each year. The process for enabling this is described in IDIS Ref Manual Section 4 pg 17. 14

15. IDIS Reports Cont’ • PR14: Provides information on activities carried out by CDBG Community Development Financial Institutions (CDFI) and Neighborhood Revitalization Strategy Areas(NRSA). This report is important for the Public Service Cap Test because funds expended for such activities in the NRSA are excluded from the Public Service Cap calculation.(BOSMAC07 uses this report along with the PR01 and PRO3 to perform the PS cap test.) • PR22:, This HOME Activities Report has all HOME activities for all years. Activity#, Street address, # of HOME assisted units, Commit Date, Committed, Drawn, Status and date of final draw. (BOSMAC07 uses this report to perform the 120 day final draw test.)If you want to see commitments, draws and status for all activities click on the tab at bottom of spreadsheet • PR27:This report contains financial information on HOME grants, subgrants, and subfunds, including commitments, program income, disbursements, project commitments / disbursements, administrative funds, CHDO operating funds, CHDO reservations and projects, CHDO loans, CHDO capacity building, reservations to State recipients and subrecipients, and total program . Useful in measuring whether the program requirement for committing and disbursing HOME grant funds are being met.

16. IDIS Reports Cont’ IDIS Reports must be downloaded from the mainframe to a CPD Rep’s desktop/laptop computer and converted by BOSMAC07. At this time these IDIS reports, downloaded in a text format (.rpt), are not easily converted to a useable format like EXCEL. However, the new IDIS OnLinewill, by September 09, export IDIS Reports in EXCEL or PDF formats. However, BOSMAC07will continue to work with Legacy IDIS and will continue to be of great value to both because it does much more than convert Legacy IDIS reports. (An attempt will be made to produce a BOSMAC09that will work seamlessly with the new IDIS OnLine.) The remainder of this presentation will be devoted to BOSMAC07; what can be done with its powerful advancedplusfilters; spreadsheets;and EXCEL filters. 16

17. And now…

18. BOSMAC07 NOT … DOES DOWNLOAD REPORTS FROM IDIS… … DOES DOWNLOAD REPORTS FROM IDIS… NOT AND HAS NOTHING TO DO WITH DOWNLOADING REPORTS ! So … what does it do? And how does it work?

19. HOW DOES BOSMAC WORK? BOSMAC BOSMAC IS NOT IDIS!! DATA IDISDATA\IDISDATA (IDIS)

20. BOSMAC07 • Converts .rpt IDIS files to EXCEL • Creates a Reports Management Tool • Creates a Data and Program Analytic Tool • Creates powerful tools for management to efficiently oversee grantees and staff (see slide #53). We will learn about each of these functions as we proceed

21. BOSMAC07 What can CPD Rep use it for? • To learn more about grantee programs. • To better performRemote Monitoring. • To better prepare for on-site monitoring. • To perform betterCAPER reviews. • To achieve substantial improvements in Grantee Reporting – data “clean up.”

22. BOSMAC07 Return QUESTIONS ABOUT a GRANTEE’S PERFORMANCE? • How many and whichPrior Years’Activities are NOT Complete? • How many and whichPrior Years’ Activities have NO AccompNarr.? • How many and which CurrentPr03 Year Activities have NO Accomplishment Narrative? • How many and whichPrior years’ Activities have 0 Expenditures? • How many and whichCurrent Year Activities have 0 Expenditures? • Are there any Job Activities (all years) with 0 Accomplishment or > $35,000/Job? HOME activities with # units and FD > 120 days – by address – Public service and Administration cap test – From the PR03a button WITH ONE MOUSE CLICK ALL OF ABOVE AT A SINGLE GLANCE ONE CLICK ONE CLICK

23. WHY ANALYSIS To Make meaningful assessments about a grantee’s performance To be effective and efficient in this task we must combine: CPD PROGRAMS IDIS KNOWLEDGE? WORD SKILLS? EXCEL ACCESS ABILITIES? ANALYTIC Curiosity is helpful…You have to want to know something; you have to have questions, otherwise the spreadsheet is of little value to you. Asking questions is what is meant when we say “database query.” We use EXCEL filters to do this. More about filters later. Obtain and objectively evaluate qualitative and quantitative information related to the grants management process

24. Now let’s look at BOSMAC07 … NOTE: Some of the features shown will be modified if and when BOSMAC09 is created to work seamlessly with the new IDIS OnLine

25. MAIN MENU- THREE PARTS Return This floating button (can be moved) appears on all screens. Click to get back to the main menu Instructions Analysis Reports Management

26. Instructions Much more detailed information can be found in the HELP button on the main menu

27. Reports Management

28. Reports Management Reports must first be downloaded from IDIS download to IDIS CONVERTED FILES SAVEDTO Rolling the mouse pointer (w/o clicking) over this button tells you what it does

29. Converting Reports(e.g. PR03) download IDIS SAVES TO Converts IDIS .rpt file (e.g., PR03) to a Spreadsheet – (BOSMAC07 creates the grantee name, name of report and date report downloaded) We will take a closer look at spreadsheets in subsequent slides.

30. Text and spreadsheet views open at same time for PR01, 02, 03, 06, 07, 08, 14, and 22 Converting Reports The “CONVERT” button automatically saves its output data file to your BOSMACFILES folder as a text file. Every IDIS Report is converted to a text file. Note that the PR01, 02, 03, 06, 07, 08, 14, and 22 reports are saved as spreadsheets as well. For these reports, both the text view and spreadsheet view are opened at the same time. Shifting between the two views is a matter of clicking on their respective tabs. To access these files simply click the “CONVERTED REPORTS” or the “ALL IDIS REPORTS” button ( if file has been converted). Now let’s take a look a the conversion process and spreadsheets. We will use the PR03 IDIS Report. [Even though this BOSMAC07conversion may no longer be necessary with IDIS OnLine it is still helpful to understand the process.]

31. ANATOMY OF A SPREADSHEET One row of spreadsheet contains an entire page of a PR03 Report. PR03 BOSMAC07 extracts all fields from the PR03 IDIS report. The extracted fields include Project ID/Name, Activity#/Name, Address, Description, Status, Eligibility citation, IDIS Matrix Code, national objective, budget data, income categories, racial data, and Accomplishment Narrative.

32. Cells are called data fields. ANATOMY OF A SPREADSHEET Drop down arrows activate “filters” [Please also see slide 56.] Columns contain information about the activity Each row is one record (activity) What do filters do? You have to want to know something; you have to have questions, otherwise the spreadsheet is of little value to you. And to have good questions you need a good command of program knowledge. Asking questions is what is meant when we say “database query.” We use filters to do this. These simple filters will show all completed 1997 activities with a low-mod job objective. [Filter in sequence left to right]

33. ANATOMY OF A SPREADSHEET It is very helpful and efficient when working with BOSMAC07 and IDIS to have the below reference material handily accessible on your desktop so you can quickly look up the IDIS Matrix Codes definitions; more complete descriptions of what each report contains and so forth. These materials can be found in the BOSMAC07Installation Kit or click on picture below. [Save pdf files to your desktop or other conveniently accessible location.]

34. The Acquisition and Analysis of Formula Grantee Performance and Financial Data We will now return to the BOSMAC07 presentation. We will explore EXCEL and the use of filters in more detail in later slides. (See slides 43, 46, 49, and 59-87)

35. We’ve covered the first of the three buttons in the Reports Management section of the Main Menu and will now continue with the CONVERTED REPORTS and All IDIS Reports buttons.

36. Reports Management Clicking on a grantee will show all the reports converted for that grantee.- by report name and date downloaded. Rolling the mouse pointer over this button tells you what it does Where does the Converted Reports button get the list from? ?

37. Reports Management Rolling the mouse pointer over this button tells you what it does This button shows every report in IDIS – by report number, name and .rpt name Return

38. Reports Management What is the relationship among these buttons?

39. Reports Management When you click the CONVERT button BOSMAC07 “looks” in the BOSMACFILES folder and if it sees that it has already converted the file you will get a message “no new files in your Idisdata folder” The CONVERT Button saves converted IDIS Reports files to the BOSMACFILES folder and all other buttons go to BOSMACFILES folder to get the reports to perform the advanced filter functions - and they in turn also save their output files to BOSMACFILES folder ?

40. Analysis Buttons This is the most powerful feature of BOSMAC07!

41. BOSMAC07’sADVANCEDPLUS FILTERS You can run these buttons only after you have downloaded and converted the PR01, 02, 03, 06, 07, 08, 14, and 22 IDIS Reports. The output files are always saved to your BOSMACFILES folder. We will now briefly explore the PR03a, PR03b, PR01a, PR08a, and PR22a advancedplusfilters.

42. BOSMAC07’sADVANCEDPLUSFILTERS[CAPER [PR03a] COMPLETENESS TEST] The CAPER PR03a button (filter) identifies activities with problems or “missing” data

43. BOSMAC07’sADVANCEDPLUSFILTERS[CAPER [PR03a] COMPLETENESS TEST] When you select “ALL” Return to Main Menu If you select "ALL": The CDBG PR03 Completeness Test first converts the sumactvt.rpt (PR03) in your C:\IDISDATA\IDISDATA folder then compiles a list of allPR03 files in your BOSMACFILES folder. Each PR03.xls file then has ten "filters" applied to it and the 'count' of each filter is entered into columns D thru M of the above table in 'Caper' tab at bottom of screen. Used to compare grantees – returns the first six data points shown on slide # 22. and gives a quick summary of all grantees in your BOSMACFILES folder.- you can easily spot a grantee that needs further scrutiny, e.g., Lowell re Column N - Jobs not produced. In that case you need to run the filter on that grantee alone. We need to return to the Main menu.

44. MAIN MENU Click again on PR03a button

45. BOSMAC07’sADVANCEDPLUSFILTERS[CAPER [PR03a] COMPLETENESS TEST] This time we will select a single PR03

46. BOSMAC07’sADVANCEDPLUSFILTERS[CAPER [PR03a] COMPLETENESS TEST] When you select a single file – this will produce the data points on slide 88 If you select a single grantee (your options are “All” or any “one”) the report will contain a spreadsheet for each column of the “Single Grantee” report. That is, 10 additional tabs, one for each column in the spreadsheet named Caper tab, are also added to the PR03 workbook as tabs 'Cpr-D' thru 'Cpr-M.' These tabs contains all records (activities) that make up the 'count' for each column in the 'Caper' tab summary report. These spreadsheets can be displayed by clicking on the appropriate tab at bottom of the spreadsheet. For example to see a list of all the job creation activities with 0 jobs or >$35,000/job click one the Cpr-N tab. After you open one of these tabs you can see activities that comprise the summary data, e.g., the previous years activities that are not complete and have no accomplishment narrative, and so on. Bottom of spreadsheet [return to 14]

47. BOSMAC07’sADVANCEDPLUSFILTERS[PR03b] We just saw how the PR03a button works. Here is a brief explanation of how the PR03b button works. • [Return] The PR03b button will automatically perform a public service cap test using the PR01, PR03, and PR14 Reports; and the Admin Cap using PRO3 and PR01 Reports. The logic for the PS CAP is as follows:

48. BOSMAC07’sADVANCEDPLUSFILTERS[PR01a] Timeliness and Ratio of PI to EN Report produced by the PR01a filter [See Columns K and M] Return

49. BOSMAC07’sADVANCEDPLUSFILTERS[PR08] The PR08a button produces a graph that shows the % distribution by category for all formula funds for a selected period. You may select a single year or a range of years. Notice at the bottom of the screen there are tabs that correspond to the use categories. Clicking on a tab will reveal a spreadsheet containing all activities and associated data for that category. This can be seen on the next slide.

50. BOSMAC07’sADVANCEDPLUSFILTERS[PR08] Return to Main Menu This is the spreadsheet that shows all the housing activities from all entitlement sources for the selected period. This spreadsheet can be manipulated as any other using filters described in slides 53 – 81. The PR08 Report does not identify the funding source of the activities.