Clemson University Algorithmic Budgeting Rules and Setup

How Clemson University Uses Algorithmic Budgeting Rules JENNIFER WILLIAMS, CLEMSON UNIVERSITY MONDAY, SEPTEMBER 25 3:30PM

Introduction During this session, we ll discuss how Clemson uses algorithmic budgeting rules to maintain two main budget codes. I ll discuss initial and yearly set up, the reasons why we decided to use only two codes, and share examples of algorithmic rules using case statements. 2 COHESION SUMMIT

Agenda 1 About Clemson University 2 Setting up Period Based Budgeting 3 Validation Tables 4 Rule Tables 5 Algo Support Form (RORALGS) 6 Algo Budgeting Rules Form (RBRABRC) 7 Using CASE Statements 8 Yearly Updates 9 Box Folder for Examples 10 Questions 3 COHESION SUMMIT

About Clemson University

Clemson University Located in the Upstate of South Carolina between Lake Hartwell and the Experimental Forest 80+ Majors and 90+ Minors 6 Year Graduation Rate 85.5% Fall 2022 Enrollment: 28,466 UG 22,566 GR 5,900 Fall 2022 FAFSA Applications: 15,531 o UG 13,950 o GR 1,581 5 COHESION SUMMIT

Setting up Period Based Budgeting

Initial Setup Prior to any setup in Banner, we discussed our options and decided to use an approach that would require more up-front work but allow for easy maintenance during the new year roll. Most of the up-front work was writing the body of the algorithmic rules used on RBRABRC, but a good template makes a huge difference. We created summer versions of each rule 7 COHESION SUMMIT

Validation Tables

RTVPBTP-Period Budget Type Validation Students can have more than one non-Pell type. For example, a student can have IM, FM, and Pell budget type. o Main o Pell 9 COHESION SUMMIT

RTVPBGP-Period Budget Group Validation Students will be sorted into budget groups based on similar attributes. 10 COHESION SUMMIT

RTVPBCP-Period Budget Component Validation Each possible budget component must be established. 11 COHESION SUMMIT

RTVBCAT-Budget Component Category Validation Multiple period budget components can be combined into a single category. You can still calculate the components separately, but they will be displayed to the student as a single category. For example: TUIT and FEES can be combined into T+F (Tuition and Fees). 12 COHESION SUMMIT

RTVABRC-Algorithmic Budgeting Rule Validation Any budget component that will use an algorithmic rule should be defined in this table. For Clemson, we created SUM and PELL versions of certain components. 13 COHESION SUMMIT

Rules Forms

RBRPBYR- Period Budgeting Aid Year Rules Budget Groups Tab: Define which groups will be used for the year. o Select an active Group Code from the List that was defined on the RTVPBGP form. o The Long Description will automatically be populated based on what is on the validation form but can be changed to be more user friendly. This is what will show in self-service to the student. o Assign a priority order for the groups to be used in the assignment process. o The Comment field is for internal use only and can be used to describe the group. 15 COHESION SUMMIT

RBRPBYR- Period Budgeting Aid Year Rules Budget Types Tab: Define which types will be used for the aid year. o Select an active Type Code from the list defined on RTVPBTP. o Assign the appropriate EFC methodology to be used. (F)ederal, (I)nstituional or (N)one. If you are defining the PELL budget type, you must use a value of N . o Check the Pell indicator box for the Pell budget type. o The Long Description may be updated to a more user-friendly description. This is not displayed in Self-Service, but can be used in web text, letters, or reports. o The Comment field is for internal use only and can be used to describe the type. 16 COHESION SUMMIT

RBRPBYR- Period Budgeting Aid Year Rules Budget Categories Tab: Define budget categories which will be used for the year. o Select an active Category Code that was defined on RTVBCAT. o Establish the Print Order for display purposes. o The Long Description will automatically be populated based on what is on the validation form but can be changed to be more user friendly. This is what will show in self-service to the student. o The Comment field is for internal use only and can be used to describe the category. 17 COHESION SUMMIT

RBRPBYR- Period Budgeting Aid Year Rules Budget ComponentsTab: Define budget components that will be used for the aid year. o Select an active Component Code that has been defined on RTVPBCP. o Assign the appropriate Category Code to the component. o Multiple components can be assigned the same category to present the sum of the components in Banner Self-Service, letters, web text, etc. o Check the Default indicator if the component should be added when assigning components to the budget group/type. o Check the Used for < Pell indicator if the component should be used when determining the Pell budget when the student is less than half time. o A default amount or a default algorithmic rule code can be entered that will allow the budget component to be created with the value when assigning the components to the budget group/type. The amount to be used for the Pell budget type should reflect the full year/full time amount. o The Long Description will use the value which was defined on the RTVPBCP form. The Long Description may be updated to a more user-friendly description. This is not displayed in Self-Service, but can be used in web text, letters, or reports. o The Comment field is for internal use only and can be used to describe the component. o 18 COHESION SUMMIT

RBRPBYR- Period Budgeting Aid Year Rules 19 COHESION SUMMIT

RBRPBGR-Period Budget Group Aid Year Rules Establish group rules for the aid year to associate the budget types that are valid for that group. Period Budget Group Aid Year rules can be rolled as a part of the New Year Roll process (ROPROLL). Types Tab: Associate the applicable period budget types with the period budget group for the aid year. The EFC and Pell indicator values are display only and reflect the values established on the RBRPBYR form. 20 COHESION SUMMIT

RBRPBGR-Period Budget Group Aid Year Rules Pell Tab: This tab is only accessible when a budget type defined as Pell is associated with the group code. o Establish the budget components that are to be assigned to the student s Pell budget for the group. o Establish the full-time/full-year amount for the component or enter the algorithmic rule code that should be used. Algorithmic rules used for calculating the component amounts for Pell should return the full-year/full-time amount. Ex: MEALS_PELL (different amounts for commuters vs on campus or off campus) o Select the Add Default Components icon to add the period budget components that have been designated Default on the RBRPBYR form. o 21 COHESION SUMMIT

RBRPBDR-Period Budget Detail Rules Establish the budget components for each budget group/non-Pell budget type. This information can be rolled as a part of the New Year Roll. The components and amounts/rules can be defined specific to a period by entering the period in the keyblock. If a period is not defined, the components and amounts/rules will be used as a default for the budget group/type. The budget grouping process will use a hierarchy to determine the appropriate components and amounts by first determining if the period being processed is defined for the group on RBRPBDR. o If the period is defined, use those components and amounts. o If the period is not defined, use the components and amounts for the group where the period is Null. The budget components and amounts are the same for both fall and spring periods, so we do not specify a Period. 22 COHESION SUMMIT

RBRPBDR-Period Budget Detail Rules Establish the appropriate budget components and amounts for the period budget group, type, and optionally period. The amounts and the algorithmic rules that are assigned to the Period Budget Group/non-Pell type should reflect the amount for one period. Select the Add Default Components icon. o 23 COHESION SUMMIT

Algo Support Form (RORALGS)

RORALGS-Algorithmic Support Form This form allows you to define specific amounts to be returned by an algorithmic rule. If you are using this form to assist with algo rules, determine the criteria and amounts to be entered for calculating the budget component. The Algo Support rules can be rolled as a part of the New Year Roll Process. o For the period budget component that will use the algorithmic support rules, determine the criteria that must be met for each amount that should be assigned to the student for the budget component. For example, if the component is tuition, determine all of the criteria that would have a different amount. GR or UG, enrollment, residency status, etc. o Enter each of the criteria which must be met as a key value. Our Key_1 values are as follows: BOOKS, CBBS, FEES, GR_MEAL, GR_ROOM, GR_TRAN, GR_TUIT, LAPTOP, PERS, UG_MEAL, UG_ROOM, UG_TRAN, UG_TUIT. Enter the amount to be assigned to the component when the student meets all of the criteria of the all the keys associated with the amount. Enter a comment for the algo support rule and/or for each Key to provide supporting info. o o o 25 COHESION SUMMIT

RORALGS 26 COHESION SUMMIT

Algo Budgeting Rules Form (RBRABRC)

RBRABRC-Algorithmic Budgeting Rules This form is used to create rules required for calculating the component amount to be assigned to the individual student. These rules can be rolled as part of the New Year Roll Process. o Enter the Rule Code in the Keyblock. The Rule Codes were established on RTVABRC. o Enter a sequence number and a SQL Statement. o Save the SQL statement. o Rules are processed in sequence order. o Select the Validate icon to ensure the SQL is correct. o Once validated, a green check will appear in the Validated indicator o All sequences must be validated before using the Test Rule function. This is helpful if you have multiple sequences. You can use the Test Rule function to verify that you have validated all sequences. 28 COHESION SUMMIT

RBRABRCExample Rule using RORALGS This rule finds UG students who have a SGASTDN record and hours locked on ROAENRL and are in a non-summer aid period. The Level Code is specified in Key_2, the Residency Code in Key_3 and Key_4, and the Financial Aid locked hours in Key_5 and Key_6. SELECT RORALGS_AMT FROM RORALGS, RORENRL, SGBSTDN, RORSTAT WHERE RORALGS_KEY_1 = 'UG_TUIT' AND SGBSTDN.ROWID = F_GET_SGBSTDN_ROWID(SGBSTDN_PIDM,:PERIOD) AND SGBSTDN_LEVL_CODE = RORALGS_KEY_2 AND (SGBSTDN_RESD_CODE = RORALGS_KEY_3 OR SGBSTDN_RESD_CODE = RORALGS_KEY_4) AND RORSTAT_APRD_CODE IN ('FALL','SPRING','FA/SPR') AND RORENRL_FINAID_ADJ_HR BETWEEN RORALGS_KEY_5 AND RORALGS_KEY_6 AND RORENRL_ENRR_CODE = 'STANDARD' AND RORENRL_TERM_CODE = :PERIOD AND RORALGS_AIDY_CODE = :AIDY AND RORSTAT_AIDY_CODE = RORALGS_AIDY_CODE AND SGBSTDN_PIDM = RORENRL_PIDM AND RORSTAT_PIDM = RORENRL_PIDM AND RORENRL_PIDM = :PIDM 29 COHESION SUMMIT

Using CASE Statements

ALGO Rule with Case Statement This rule uses a CASE statement to assign an amount based on the total number of hours a student is enrolled in and their residency status. This rule is used when the student has a SGASTDN record and locked hours on ROAENRL. /*UG STUDENTS HAVE SGASTDN AND RORENRL RECORD*/ SELECT (CASE WHEN RORENRL_FINAID_ADJ_HR >= 12 AND SGBSTDN_RESD_CODE IN ('0','1') THEN 6298 WHEN RORENRL_FINAID_ADJ_HR >= 6 AND SGBSTDN_RESD_CODE IN ('0','1') THEN 5058 WHEN RORENRL_FINAID_ADJ_HR >= 1 AND SGBSTDN_RESD_CODE IN ('0','1') THEN 1686 WHEN RORENRL_FINAID_ADJ_HR >= 12 AND SGBSTDN_RESD_CODE IN ('2','Q') THEN 15306 WHEN RORENRL_FINAID_ADJ_HR >= 6 AND SGBSTDN_RESD_CODE IN ('2','Q') THEN 12105 WHEN RORENRL_FINAID_ADJ_HR >= 1 AND SGBSTDN_RESD_CODE IN ('2','Q') THEN 4035 WHEN SGBSTDN_RESD_CODE IN ('0','1') THEN 6298 WHEN SGBSTDN_RESD_CODE IN ('2','Q') THEN 15306 ELSE 0 END) FROM RORENRL,SGBSTDN WHERE RORENRL_ENRR_CODE = 'STANDARD' AND SGBSTDN.ROWID = F_GET_SGBSTDN_ROWID(RORENRL_PIDM,:PERIOD) AND SGBSTDN_STST_CODE = 'AS' AND SGBSTDN_STYP_CODE NOT IN ('S') AND SGBSTDN_LEVL_CODE = 'UG' AND RORENRL_TERM_CODE = :PERIOD AND RORENRL_PIDM = SGBSTDN_PIDM AND RORENRL_PIDM = :PIDM 31 COHESION SUMMIT

ALGO Rule with Case Statement ***It is extremely important to get the sequencing correct for this method to work. Based on the sequencing, the student will get assigned the first rule where they meet the criteria. So, our process is written to have the most restrictive rules at a higher sequence number. There are three sequences of UG Tuition. 1. /*UG STUDENTS HAVE SGASTDN AND RORENRL RECORD*/ 2. /*UG STUDENTS HAVE SGASTDN BUT NO RORENRL RECORD*/ 3. /*UG STUDENTS HAVE SAAADMS BUT NO RORENRL RECORD*/ When a student is initially accepted and prior to the creation of a SGASTDN record, they will fall to the third sequence which defaults to full-time amounts for in-state and out-of-state. Once a SGASTDN record is created, or for returning students prior to the census, they would fall to the 2nd sequence, which also defaults to full-time amounts. Once the hours are locked at the census date, all students are re-budgeted and would fall into the first sequence. The tuition amounts are then adjusted based on full time, part time, or less than half time amounts. 32 COHESION SUMMIT

RORRULE

RORRULE- Financial Aid Selection Rules The RORRULE form is used to create the Period Budget Group assignment rules that are used by the RBRPBGP process for each period. The Rule Type G must be used. These rules can be rolled as a part of the New Year Roll Process. The priority of these groups is also very important. Each student who goes through the Budget Grouping process will be placed into the first group where they meet the specified criteria. The priority is set on the RBRPBYR form. 34 COHESION SUMMIT

RORRULE- Financial Aid Selection Rules **It is important to only use the appropriate binding variables in your rules. For example. The RBRPBGP process accepts the :PERIOD binding variable, where the RPEPCKG process does not. If you do not specify the PERIOD as a parameter in the process, RBRPBGP will use the Current Period on ROAINST. ROAINST-Global Institution Financial Aid Options: When all rules and forms are set-up correctly, the Period Budgeting Enabled checkbox must be activated on the Defaults tab. This is what turns on Period Budgeting. In addition, a default Period Budget Group must be established. If a student does not meet the criteria of the groups you have established, they will be placed in this group. This is typically a REVIEW group. 35 COHESION SUMMIT

RORRULE- UG Student Example SELECT DISTINCT (RORSTAT_PIDM) FROM RORSTAT LEFT JOIN SGBSTDN ON SGBSTDN_PIDM = RORSTAT_PIDM AND SGBSTDN.ROWID = F_GET_SGBSTDN_ROWID(SGBSTDN_PIDM,:PERIOD ) AND SGBSTDN_TERM_CODE_EFF <= :PERIOD AND SGBSTDN_STST_CODE = 'AS' AND SGBSTDN_STYP_CODE IN ('C','U','F','T') AND SGBSTDN_LEVL_CODE = 'UG' LEFT JOIN SAVADMT ON SAVADMT_PIDM = RORSTAT_PIDM AND SAVADMT_TERM_CODE >= '20' || SUBSTR(RORSTAT_AIDY_CODE,1,2) || '05' AND SAVADMT_TERM_CODE <= :PERIOD AND SAVADMT_LEVL_CODE = 'UG' AND (SAVADMT_INST_ACC_IND = 'Y' OR SAVADMT_STDN_ACC_IND = 'Y') WHERE (SGBSTDN_PIDM IS NOT NULL OR SAVADMT_PIDM IS NOT NULL) AND RORSTAT_AIDY_CODE = :AIDY 36 COHESION SUMMIT

RORRULE- GR Student Example SELECT DISTINCT (RORSTAT_PIDM) FROM RORSTAT LEFT JOIN SGBSTDN ON SGBSTDN_PIDM = RORSTAT_PIDM AND SGBSTDN.ROWID = F_GET_SGBSTDN_ROWID(SGBSTDN_PIDM,:PERIOD ) AND SGBSTDN_TERM_CODE_EFF <= :PERIOD AND SGBSTDN_STST_CODE = 'AS' AND SGBSTDN_STYP_CODE IN ('M','N','G','L') AND SGBSTDN_LEVL_CODE = 'GR' AND SGBSTDN_PROGRAM_1 NOT LIKE 'ND%' AND SGBSTDN_PROGRAM_1 NOT LIKE 'CERT%' LEFT JOIN SAVADMT ON SAVADMT_PIDM = RORSTAT_PIDM AND SAVADMT_TERM_CODE >= '20' || SUBSTR(RORSTAT_AIDY_CODE,1,2) || '05' AND SAVADMT_TERM_CODE <= :PERIOD AND SAVADMT_LEVL_CODE = 'GR' AND SAVADMT_ADMT_CODE ='GR' AND SAVADMT_APDC_CODE <> '40' AND (SAVADMT_INST_ACC_IND = 'Y' OR SAVADMT_STDN_ACC_IND = 'Y') WHERE (SGBSTDN_PIDM IS NOT NULL OR SAVADMT_PIDM IS NOT NULL) AND RORSTAT_AIDY_CODE = :AIDY 37 COHESION SUMMIT

RORRULE- Cancel Fall Student Example SELECT DISTINCT(RORSTAT_PIDM) FROM RORSTAT LEFT JOIN ROBUSDF ON ROBUSDF_PIDM = RORSTAT_PIDM AND ROBUSDF_AIDY_CODE = :AIDY AND ROBUSDF_VALUE_14 IS NOT NULL LEFT JOIN SAVADMT ON SAVADMT_PIDM = RORSTAT_PIDM AND SAVADMT_TERM_CODE = :PERIOD AND SAVADMT_APDC_CODE IN ('CN','CP','70','NS','80') LEFT JOIN SGBSTDN ON SGBSTDN_PIDM = RORSTAT_PIDM AND SGBSTDN.ROWID = F_GET_SGBSTDN_ROWID(SGBSTDN_PIDM,'20 ||SUBSTR(RORSTAT_AIDY_CODE,1,2) || '08') AND SGBSTDN_STST_CODE = 'AS' AND SGBSTDN_STYP_CODE IN ('C','U','F','N','G','M') WHERE (ROBUSDF_PIDM IS NOT NULL OR (SAVADMT_PIDM IS NOT NULL AND SGBSTDN_PIDM IS NULL)) AND RORSTAT_AIDY_CODE = :AIDY AND RORSTAT_PIDM = :PIDM 38 COHESION SUMMIT

Yearly Updates

Forms to Update during New Year Roll RBRPBYR- Budget Components Tab Update any Amounts that have been hard-coded RBRPBGR- Pell Tab Update Pell amounts that have been hard-coded RBRPBDR- Detail Rules by Group Code and Type Code Update the hard-coded amounts for any group code/type code combination. Ex: UG/Main, GR/Main Pell Type cannot be adjusted on this form RORALGS- Algorithmic Support Form Update the Amount field for all components. RBRABRC- Algorithmic Budgeting Rules Update amounts hard-coded in specific algo rules 40 COHESION SUMMIT

Example Algo Rules

Box Folder for Example Rules https://clemson.box.com/s/goj4wk2k8m3gq09tp2aje6rz69m7syaz 42 COHESION SUMMIT

Questions? 43 COHESION SUMMIT

Thank You! Jennifer A. Williams M.Ed.CLEMSON UNIVERSITY Associate Director Office of Student Financial Aid E: alberts@clemson.edu P: 864.656.2280 | F: 864.656.1831 www.clemson.edu/financial-aid 44 COHESION SUMMIT