David Rhodes and Holly Langer-Evans

2014-2015 QA Analysis Training

Analysis Preparation

2

The Process

3

Uploading Process

• One flat file to upload ISIR Records and Verification Criteria

• Upload Capacity 3,000 records

4

Prepare Flat File of Verified Records

1. SSN

2. First two letters of the last name (CAPS)

3. Initial Transaction

4. Paid-On Transaction

5. Include a “Y” if records met your school criteria

6. School criteria code(s) record met

Field Length: Up to 56 characters

5

School 1: School Criteria

Code DescriptionA Parents filed a tax return but AGI is listed as zero or

blank with an EFC of $6000 or less and who have not done the IRS Data Retrieval when filing the FAFSA

B Dependent students reporting an AGI greater than $15,000, with Parental Contribution (field 313 of the ISIR) < $10,000 and EFC not equal to 0.

C Married parents where total parent AGI is > than $47,000, whose EFC is $6000 or less and whose untaxed income is zero or blank

6

School CriteriaCode Description

D Students with single parent and parent AGI greater than $35,000, whose EFC is $6000 or less and whose untaxed income is zero or blank.

E There are no specified criteria. Reviewing advisors use these tracks to select students for verification, and review where information appears discrepant. Dependent Population

F There are no specified criteria. Reviewing advisors use these tracks to select students for verification review, and where information appears discrepant. Independent Population

7

Question to Answer

8

Pell Grant Improper Payment Report

• Access Report• Run Report

9

Pell Grant Improper Payment Report

• Right click on report

• Select New• Select Report

10

Pell Grant Improper Payment Report

• Access Report

-Select OK

-Select Run Icon

11

Pell Improper Payment Report• Open your Pell

Improper Payment Reportto begin your Analysis

• How to save report depends on your browser

ISIR Categories

Grey Ribbon Bar

12

Internet Explorer Browser

Steps Instructions

1 Run the Pell Improper Payments report

2 In the report window, right click and select “View source”

3 In the source window, select File/Save

4 Select the destination file location and enter a File name

5 Save as type = HTML File

6 Go to the file location and open it

13

Saving using Firefox

Steps Instructions

1 Run the Pell Improper Payments report.

2 (Assuming the report was run in preview mode) In the report frame, right click and select “This Frame” and then “View Frame Source”

3 In the source window, select File/Save Page As

4 Select the destination file location and enter a File name, with a .htm extension

5 Save as type = HTML File

6 Go to the file location and open it

14

Chrome Browser

Steps Directions

1 Run the Pell Improper Payments report

2 (Assuming the report was run in preview mode) In the report frame, right click and select “View Page Source”

3 In the source window, right click and select Save As

4 Select the destination file location and enter a File name, with a .htm extension

5 Save as type = Webpage, HTML Only

6 Go to the file location and open it

15

Create A Roll Up Table• From the Ribbon Bar,

locate Selected _By_CPS and select the down arrow

• From the Menu, select Roll Up, and then Selected_By_CPS

16

What is the Report telling you?• 546 records not selected

by CPS

• 1306 records selected by the CPS

Add a category to the table: Institutional Verification Criteria

17

Add a field to the Roll up Table• Select the icon

Click on Chart/Rollup Tool select, INST_VERIF_CRIT and drag it into the Group By column

Hint: Drop it in the Group by column when you see the red line

• Select OK

___________

18

What is our Focus?

• records without codes

• records not selected by CPS that were selected by the school

• selected by both CPS and School

19

Displays as a bar chart. To change to a cross tabulation select

Filter on the 546 Records• Go to Gray Ribbon at the top of the

original Report we ran andselect the down arrow onSelected_By_CPS, SelectFilter and select equals, Scroll back to report

• Next to equals box select downarrow and select NO

• Select Add Condition Box, Selectthe Select_By_School Field, SelectEquals, Select down arrow and selectYes

• Select Filter

20

What Does this Report Tell Us?• Identifies records not

verified by the CPS but were verified by the school. Identified the verification groupings

• It doesn’t tell us the impact the verification groups had on Pell or EFC

21

Add ABS Pell and ABS EFC

22

• Select the iconClick ADD (Y) select,ABS_EFC_Change

• Select the iconClick ADD (Y) select,ABS_PELL_Change

• Select the icon

and change it to

SUM

Roll Up: Report: Cumulative effect on EFC and Pell

Display report as Cross Tabulation

23

Select SUM to obtain cumulative dollartotals for ABS EFC and ABS Pell Change.

Average effect on Pell and EFC

24

Caution: this ICON will not display a visual change when you select SUM, AVG or Count

Select AVG to obtain the AVG change to ABS PELL andABS EFC

How to Customize Results

Drag corner to make report larger

Use Icon to add Categories

Use down arrow to select ascending or descending order

25

Change count to sum or AVG

Ribbon Bar –Gray Box

Change count to sum or AVG

Can move pane by clicking on blue bar and dragging it to the side or up or down.

This displays a cross tabulation

Displays report as a bar graph

Narrow the Search: Group by X• Select the icon

• Select

• Select

26

Narrow the Search: Group by X

• Criteria C – Average ABS EFC and AVG PellGroup X:Filing Status

27

Revised Criteria

• Married parents where total parent AGI is > than $47,000, whose EFC is $6000 or less and whose untaxed income is zero or blank and NOT selected by the CPS, Will File

28

Live Demonstration

29

School 2: School criteriaCode Description

9IV215 Pell eligible Dependent Students whose parent's AGI >$24,000; filed 1040; not eligible for 1040A/EZ; no auto zero EFC flag; not a dislocated worker (basis for using $24,000 as income cutoff = threshold for auto zero EFC

9IV315 Dependent Student's whose Parent's AGI = Student's AGI and the AGI >0; EFC> 0; DRT Flag '02' does not exist for both student and parent and not selected for 9IV215; both parent and student filed tax return

9IV415 ISIR comment code 361 exists; student does not meet selection criteria for steps 1,2 or 3, And Dependent student's parent marital status= '1' married/remarried and parent tax filing status = '1' single, '3' married filing separate or '4' head of household : OR dependent student's parent Marital status = '2' never married and parent tax filing status = '2' married filing joint : parent filed tax return : need > = $500

30

Analysis: Cross Tabulation

• Sum of ABS EFC & ABS PELL

31

Analysis:• Average change of ABS Pell and ABS EFC • Let’s take a further look at Criteria 91V215

32

Narrow your Search: Group by X • Criteria 215 – Average ABS EFC and AVG Pell

Group X: PAGI CategoryNote PAGI Category 50,000 and higher

33

Revised Criteria

• Pell eligible Dependent Students whose parent's AGI >$50,000; filed 1040; not eligible for 1040A/EZ; no auto zero EFC flag; not a dislocated worker; and NOT selected by CPS

34

School 3: School criteria

35

Code Description

DV11 Dependent, Pell eligible, Parent AGI >=35,000 and <=50,000

DV12 Dependent, Pell eligible, Parent AGI >=60,000 and <=120,000

DV13 Dependent, Pell eligible, Parents income exclusion >= 5,000

DV14 Dependent, Pell eligible, No Pell Grant eligibility for 12/13

DV15 Dependent, Pell eligible, (AGI/wages) < 75

DV16 Dependent, V4 or V5 verification code

DVPT Dependent, Pell eligible, Parent taxes paid >=40% of income

DVRC Dependent, Pell eligible, Reject Override C=‘Yes’

SVST Independent, Pell eligible, ISIR Comment code 155 (high tax paid)

SV12 Independent, CPS selected, Student AGI >=15,000 and <=40,000

SVRC Independent, Pell eligible, Reject Override C=‘Yes’

School #3

• School verification very similar to CPS verification• Let’s look at the 30 records

36

Cross Tabulation Results

37

Analyze: Selected by CPS and by School

• AVG ABS Pell and EFC Change by descending order• What does this tell us?

38

Narrow your Search: Group by X

DV14 Criteria: Dependent, Pell eligible, No Pell Grant eligibility for 12/13 – Newly Pell eligible

39

Revised Criteria

Dependent, Pell eligible, No Pell Grant eligibility for 12/13

AND PARAGI >=35,000 and Selected by CPS

40

School #4

• There are 32 school criteria• You can download the criteria from the

webinar download center• Which criteria make a significant impact

on EFC/Pell?• Which criteria would you eliminate or

modify?

41

Analysis

• Display Pell Change in Descending Order

42

Analysis

43

Narrow your Search: Group by X VFAGG Criteria: Parent # of family members equals blank, zero, 1 or 2 and parent marital status = married and both parents living together. Group X by filing status and PARAGI

Average ABS Pell and EFC Changes

44

Revised Criteria

• Parent # of family members equals blank, zero, 1 or 2 and parent marital status = married and both parents living together, Not Selected by the CPS AND Parent AGI > = 50,000

45

Analysis Assignment1. Open the Pell Grant Improper Payment Report

2. Create a Roll-Up-Table

3. Use the Group X feature to further analyze data

4. Determine which school criteria would add the most to CPS verification and how you might need to “tweak” your school criteria to maximize its return on investment as an add on to federal verification

5. Know and have the definitions of your school criteria in mind when interpreting your results

46

Analysis Preparation

47

Resource

• QA Web Site- Training and Guidance Link

http://ifap.ed.gov/qadocs/ToolsforSchools/2012QASession1.pdf

48

Regional Representatives

Holly Langer-Evans• Phone:617 289 0136• Email: Holly.Langer-Evans@ed.gov Michael Cagle•Phone: 206-615-2586•EMAIL: Michael.Cagle@ed.gov

Anne Tuccillo•Phone: 202-377-4378•Email: Anne.Tuccillo@ed.gov

49