5144 ISA Long-Term Student A/R
Controller's Office
Purpose
Track the student Income Share Agreement (ISA) long-term accounts receivable
Manager
Karen LaPoint
Year-End Calculation
| Steps |
|---|
| Request the All Accounts report in VEMO from Kara Pitts |
| Copy the ISA Calculation FY## from the prior year Audit folder into the current year Audit folder to use as a starting point |
| On the Journals tab: * insert a column for a new fiscal year (prior to the other fiscal year columns) * copy / paste special as values the entries in the prior fiscal year column * create rows for the current fiscal year, keeping Grad and UG students on separate tabs * update the Inception To-Date formula to include the column for the new fiscal year * you will revisit this tab after the calculations |
| Copy the Template tab to create a FY20## Pool tab for the current fiscal year |
| Begin with the FY2019 Pool tab to update it for current fiscal year activity * look for these students on the All Accounts report to see what activity has occurred this fiscal year * on the All Accounts report insert a column to indicate which pool the student is associated with * update the funding amount in the current year column on the FY2019 Pool tab * update the ISA Rate (it should tie to the sum of the rates on the All Accounts report) |
| Repeat the process for the FY2019 Pool tab for all the other existing Pool tabs |
| Use the Template tab or a copy of a prior year Pool tab to create a tab for the current year * populate all the student information * roll the years in row 3 forward |
| Look up the most recent starting salary data available to use to populate the salary projection for the new year pool, using the Grad rate for MAT students and the UG rate for the other pool https://www.clarkson.edu/career |
| Download the HQM Corporate Bond Yield Curve Spot Rates: 2019-2023 https://home.treasury.gov/data/treasury-coupon-issues-and-corporate-bond-yield-curve/corporate-bond-yield-curve * Locate the Jun column for the current fiscal year (get rid of any other columns you don't want) * The pools should mature 1 year less than the number of years used last fiscal year * Use these percentages as the discount rate on the Pool tabs |
| Reconcile the updated pool tabs with Fund 470 (dedicated to ISA transactions) * copy the FY## 470 GL tab * download all the current fiscal year transactions in Fund 470 and merge them onto the dataset on the GL tab for the current year * sort by Ref (Student ID) * enter the appropriate pool for the new student transactions * update the pivot to include the entire dataset * compare the grand total by pool with the associated total loan principal balance on the individual pool tabs * follow up on any differences (Kara Pitts for UG and Sheila Morton for MAT Grads) |
| Return to the Journals tab * By pool, create the formulas in the current year column * Bad Debt = The bad debt calculation on the pool tab less the amounts booked in prior years; Inception to-date should match the amount on the pool tab * Doubtful accounts = the offset amount to bad debt * LISA A/R NPV = The net Interest/Discount figure on the pool tab less the amounts booked in prior years; Inception to-date should match the amount on the pool tab * Expense = the offset amount to LISA A/R NPV Post the resulting journal in PS Financials |