BOSMAC07 ORIENTATION

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 s powerful
advancedplus filters to 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. Simply
right 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
The Acquisition and Analysis of Formula Grantee
Performance and Financial Data

• A little reminder

3
4
PROFESSIONAL

• RESPONSIVE

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 CPDs Integrated Disbursement and
  Information System interwoven throughout the
  training (Slides 7 15)
• CPDs 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
Data Acquisition
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/CDBGTrainingMan
ual.pdf http//lms.learningevolution.com/IDIS/logi
nhtml.asp?vIDIS
8
9
The Acquisition and Analysis of Formula Grantee
Performance and Financial Data
Data Analysis
BOSMAC07 (Boston Macro)
BOSMAC07 can be used to analyze the 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
BOSMAC07 can be used to analyze the 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/reengi
neering/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
Some Important IDIS Reports and the Information
they contain

• 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.)

12
13
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
IDIS Reports Cont

• PR03 CDBG Activity Summary Report (GPR)for
  the year selected. This is the Grantees
  performance report. (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 all
  funding sources. Funding sources are not
  identified. Has IDIS matrix code for
  each Project ID name, Activity name,
  Committed, Drawn and date of last
  drawdown

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 Reps 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
OnLine will, by September 09, export IDIS Reports
in EXCEL or PDF formats.
However, BOSMAC07 will 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 BOSMAC09 that 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
advancedplus filters 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
What can CPD Rep use it for?
BOSMAC07

• To learn more about grantee programs.
• To better perform Remote Monitoring.
• To better prepare for on-site monitoring.
• To perform better CAPER reviews.
• To achieve substantial improvements in Grantee
  Reporting data clean up.

22
BOSMAC07
Return

• QUESTIONS ABOUT a GRANTEES PERFORMANCE?
• How many and which Prior Years Activities are
  NOT Complete?
• How many and which Prior Years Activities have
  NO Accomp Narr.?
• How many and which Current Pr03 Year Activities
  have NO Accomplishment Narrative?
• How many and which Prior years Activities have 0
  Expenditures?
• How many and which Current Year Activities have 0
  Expenditures?
• Are there any Job Activities (all years) with 0
  Accomplishment or gt 35,000/Job?
• HOME activities with units and FD gt 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 grantees
performance
To be effective and efficient in this task
we must combine
KNOWLEDGE?
SKILLS?
ABILITIES?
ANALYTIC
Curiosity is helpfulYou 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 lets 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
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 SAVED TO
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
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 lets take a look a the conversion process
and spreadsheets. We will use the PR03 IDIS
Report. Even though this BOSMAC07 conversion 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
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
BOSMAC07 Installation 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
Weve 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
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
BOSMAC07s ADVANCEDPLUS 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 advancedplus filters.
42
BOSMAC07s ADVANCEDPLUS FILTERS CAPER PR03a
COMPLETENESS TEST
The CAPER PR03a button (filter) identifies
activities with problems or missing data
43
BOSMAC07s ADVANCEDPLUS FILTERS CAPER PR03a
COMPLETENESS TEST
When you select ALL
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 all PR03 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
BOSMAC07s ADVANCEDPLUS FILTERS CAPER PR03a
COMPLETENESS TEST
This time we will select a single PR03
46
BOSMAC07s ADVANCEDPLUS FILTERS CAPER PR03a
COMPLETENESS TEST
When you select a single file this will produce
the data points on slide 88
return to 14
47
BOSMAC07s ADVANCEDPLUS FILTERS 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
BOSMAC07s ADVANCEDPLUS FILTERS PR01a
Timeliness and Ratio of PI to EN Report produced
by the PR01a filter See Columns K and M
Return
49
BOSMAC07s ADVANCEDPLUS FILTERS 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
BOSMAC07s ADVANCEDPLUS FILTERS 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.
51
BOSMAC07s ADVANCEDPLUS FILTERS PR22a
HOME Final Draw gt 120 day Report produced by the
PR22a filter
Return
52
MAIN MENU
53
Powerful Management Tool
BOSMAC07 provides management with powerful tools
to efficiently oversee management of grantees and
staff. For example, a manager may ask staff to
send their converted PR03, PR01, PR14, and PR 22
reports to a folder on the J drive named
bosmacfiles. The manager, using the copy of
BOSMAC07 in that folder, can run the filters
PR03a, b, c , etc - on all the files in that
folder and compare performance of grantees and
reps. Alternatively, the converted files can be
moved to a BOSMACFILES folder on the managers
C or G drive , and filters run from that
location. For example, see next two slides
running the PR03a button (selecting ALL) will
result in a spreadsheet showing- for all the
grantees in the bosmacfiles folder data counts
that provides answers to all of the questions
listed on slides 22and 86. Selecting a single
report will reveal all the activities that
comprise the column count. The manager can e-mail
the rep and request follow-up action on any noted
discrepancies . This is especially helpful around
CAPER review time. return
54
BOSMAC07s ADVANCEDPLUS FILTERS CAPER PR03a
COMPLETENESS TEST
When you select a single file this will produce
the data points on slide 88
Continued on next slide
55
BOSMAC07s ADVANCEDPLUS FILTERS CAPER PR03a
COMPLETENESS TEST
We can see from the previous slide that the
grantee has not produced - as of the end of their
2008 program year - the 10 jobs proposed (funded
from 2002 to 2004) and there is no narrative to
explain the status of progress on this activity.
See Column CA
In this case the manager would send an e-mail to
the CPD rep and request a follow-up to find out
what is going on. Ideally, the rep would have
already noted this discrepancy and ascertained
from the grantee whether this is a failure on the
grantees part to enter the current information
(data clean up issue) or that there is a real
performance problem. Reps should review the PR03
report on a quarterly basis.
Lets take a further look at the Accomplishment
Narrative field.
56
CAPER PR03a COMPLETENESS TEST
Column G for example, shows a count and of
previous years activities that are not complete
and have no accomplishment narrative. If you want
to see the specific activities that comprise this
count simply click the CprG tab at the bottom of
the screen. You will then see all the activities
that comprise the count - as shown in the bottom
part of the slide. Now you might say why are we
concerned that there are not accomplishment
narratives in activities that are not complete.
Dont they fill that in when the activity is
completed? NO. We the public - want to know
what is going on or NOT going on during the
program year. The accomplishment narrative should
be distinguished from the status of the activity
which is reported in a different column and only
by category. i.e., Underway , complete or
cancelled. It should also be distinguished from
accomplishment data, e.g., units completed. Again
we want to know what is happening with the
activity during the program year. We want detail
sufficient to know whether adequate progress is
being made. For example, contract signed (date)
construction begun (date) construction 40
complete as of (data) and so on. Finally, click
on the floating MAIN MENU BUTTON.
57
RECAP
IDIS contains program and financial data entered
by grantees
This data (reports) can be analyzed by BOSMAC07
once it is downloaded to the Reps
C\IDISDATA/IDISDATA folder and converted (saved
in the bosmacfiles folder ) by BOSMAC07 . Once
converted, these files can be copied or moved to
any drive to a bosmacfiles folder with its own
copy of BOSMAC07 . This can provide management
with a powerful and efficient oversight tool.
BOSMAC07 must be located in a folder named
bosmacfiles. All reports generated by BOSMAC07
are automatically saved into the bosmacfiles
folder
57
58
RECAP
In order to make optimal use of these tools the
user must have
KNOWLEDGE?
SKILLS?
ABILITIES?
ANALYTIC
Curiosity is helpfulYou 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. We
will now explore EXCEL filters in more detail..
Obtain and objectively evaluate qualitative and
quantitative information related to the grants
management process
59
EXCEL FILTERS
Excel Filters allow you to hide information you
do not need.

• For example, if you want to see all activities
  that meet the Low/Mod Area Benefit National
  Objective, you can use a filter to hide all the
  activities that do not meet that criteria.
• Hiding a row does not delete it all the data
  is still there but not visible

60
ACTIVATING FILTERS
To activate filters go to the EXCEL menu bar,
click the DATA tab, then the filter icon.
Return
61
FILTERS
There are three types of filters in
EXCEL Simple filters used to isolate one
criterion in one or more spreadsheet data columns
for example in a YEAR column showing multiple
years you may want to see only the data
associated with a particular year Custom
filters used to isolate only two criteria in a
one or more spreadsheet columns say we want to
see only public services. Two mtx codes, in the
same column, are required to capture all Public
Services. Advanced filters used to isolate
more than two criteria in a single column.
BOSMAC07s advancedplus filters go well beyond
this
62
USING FILTERS IN EXCEL

• Simple filters are used when we are trying to
  isolate only one criterion in one or more
  columns.

• Custom filters are used when we are trying to
  isolate only two criteria in one or more columns.

• Advanced filters are used when we are trying to
  include more than two criteria in a single column.

63
USING FILTERS IN EXCEL
Simple filters (alone / in combination)

• Note the drop down arrows at top of each column

Lets see how these filters work.
Say we want to see all 1997 Low-Mod Housing
activities still underway in the grantees 2008
performance year. You would use the simple filter
in combination as shown. Apply the filters in
sequence as shown.
Selecting 1997will show all1997 activities and
nothing more.
64
USING FILTERS IN EXCEL
Custom filters (alone / in combination)
65
USING FILTERS IN EXCEL
Custom filters (alone / in combination)
ALL PUBLIC SERVICES DONE FROM 2000 TO END OF 2002
151 activities totaling 6,813,293.71
66
EXAMPLES OF SIMPLE FILTERS IN EXCEL

• Simple filters (alone / in combination)

• Select all activities in 2002

• Filter Year Column to display only 2002

• Select all area benefit activities (LMA) in 2002

We now have on the spreadsheet only and all
low-mod area benefit activities associated with
the year 2002.
We now have on the spreadsheet only and all
activities associated with the year 2002.

• Filter Column H to display only area benefit act.

67
EXAMPLES OF CUSTOM FILTERS IN EXCEL

• Custom filters (alone / in combination)

Why do we need to use a custom filter?

• Select all public service activities
• Filter Matrix Code (J) for Public Services

Because capturing all PS requires two Matrix
codes - located in the same column.
68
EXAMPLES OF CUSTOM FILTERS IN EXCEL
Custom filters (alone / in combination)

• Select all public service activities between 2000
  2002

• Filter the Year column for this time period

69
USING FILTERS IN EXCEL
We are going to use the Matrix Code Column (J) to
find all Public Services and the Year Column (A)
to find those between 2000 and 2001
Custom filters (Summary)
ALL PUBLIC SERVICES
ALL PUBLIC SERVICES DONE BETWEEN 2000 AND 2001
151 activities totaling 6,813,293.71
70

Advanced Filters

• We previously performed the PS Cap test for a
  particular grantee for the 2002 annual
  performance period using the PR03 and PR01.

We now want to determine whether that grantee has
increased obligations for any 2002 Public
Services after the closeout date of the 2002
performance period. The PR03 is frozen as of
the date of the end of the grantees performance
period.
Lets say we are now six months later 6 months
after the close of the program year
To do this we need to use the PR02 Report to look
at current, real-time financial data for those
same 02 PS activities (Identified in the 02
PR03 Report).
Lets take a look at the PR02 Report
71
PR02
72
Advanced Filter Steps

• Open the 2002 PR03 report for the selected city,
  We are looking for all the 2002 PS Activities.
• We need to select and copy all the activity
  numbers of the 2002 PS activities from the PR03
  Report and bring them into the PR02 Report so we
  can filter for them in the PR02 Report which
  shows real-time drawdowns up to the date the
  report is downloaded.
• Remember the PR02 Report contains all
  activities for all years.
• Open the PR02 Report

Again, we want to see if any additional
obligations were made to these activities after
the close of the performance period.
73
STEP2 PR02
Select CDBG
74
STEP3

• Insert a new worksheet on the PR02 Report

75
STEP4

• Go to PR03 Report and select and copy the
  activity numbers in column D (include the column
  name).

• Tip - to select and copy
• Click in Act heading
• Use scroll bar to scroll to end
• To Select column press Shift key and click on
  last activity
• To copy column right mouse click and select copy.

76
STEP5

• Paste Column D from the PR03 to cell A1 on the
  PR02 sheet1 the sheet we inserted in step 3.

77
STEP6

• Click on PR02 Temp Tab

78
STEP7

• Click on PR02 Data

Select advanced filter
79
STEP8

• Click in Criteria Range Field

Remember this click if you forget this click
the filter will not work.
80
STEPS910
Notice that Column A1 is inserted into the
Criteria Range field

• Select Column A1 of PR02 sheet 1

• Click OK

81
STEPS Cont
The amount obligated for the Public Services
identified in the PR03 Report as of the date the
PR02 was downloaded is shown in Column H of
thePR02
82
STEP11

• Compare this figure

with that on the PR03
83
Result
Screen shot is for illustrative purposes only
the numbers are not reflective of the results of
previous slide.
84

• Is the PR03 adequate to test the PS Cap?

85
RECAP

• Simple filters are used when we are trying to
  isolate only one criterion in one or more
  columns.

• Custom filters are used when we are trying to
  isolate only two criteria in one or more columns.

• Advanced filters are used when we are trying to
  include more than two criteria in a single column.

CRITERIA RANGE
86
RECAP

• Simple filters are used when we are only trying
  to isolate one criterion in one or more columns.

The drop down arrows display all the criteria
(data) in the column. You select the one you
want to see. In this case we want all 2002
projects.

• Custom filters are used when we are trying to
  isolate only two criteria in one or more columns.

This is the second criterion. Enter 03t in MTX
field
These are 0ne Criterion. We want all 05
projects. Enter 05 in MTX field.
87
End of Filters Lesson
That concludes the fundamentals of the use of
EXCEL spreadsheet filters.
It is important to master this skill and you
might want to practice using them. Using the
advanced filters appears complicated but once you
use it a few times it becomes quite easy.
The good news is while important to know how to
use these filters you do not need this skill in
order to use BOSMAC07 which has even more
powerful filters that can be used with a simple
click of the mouse button. These filters provide
answers to such questions as we listed in slide
22 and are repeated on the next slide.
88
BOSMAC07
QUESTIONS ABOUT a GRANTEES PERFORMANCE?

• How many and which Prior Years Activities are
  NOT Complete?
• How many and which Prior Years Activities have
  NO Accomp Narr.?
• How many and which Current Pr03 Year Activities
  have NO Accomplishment Narrative?
• How many and which Prior years Activities have 0
  Expenditures?
• How many and which Current Year Activities have 0
  Expenditures?
• Is the grantee spending timely? (From the PR01
  button)
• Are there any Job Activities (all years) with 0
  Accomplishment or gt 35,000/Job?
• HOME activities with 0 units and FD gt 120 days
  by address
• Public service and Administration cap test
• return to 9 return to 46

From the PR03a button
ALL OF ABOVE AT A SINGLE GLANCE
WITH ONE MOUSE CLICK
ONE CLICK
ONE CLICK
89
YOU HAVE LEARNED

• How grantee data is acquired

• How grantee data can be analyzed

• How BOSMAC07 works

• How BOSMAC07 can be used as a management tool (
  see slide 51)

• The value of spreadsheet filters and how to use
  them

90
(No Transcript)