Data and Tools for ABB Tuition Revenue and Distribution Calculations

New ABB Tuition Data Model and Resources

In December 2016, a number of tables were made available that enable campus users to do and see all calculations relevant to the distribution of net operating fee revenue under ABB. Links to a variety of information that will enable users to understand and use the data are provided below.

Please note that a number of improvements, including (but not limited to) the ability to include students with exemptions as well as data quality checks, have been made as the data have been moved to AIDB. The budget allocations made through FY16 under ABB represent final allocation decisions; no changes to budgets for prior years will be made on the basis of the data now made available through AIDB. Historical data are included here only to facilitate trend analysis.

Data Model Details

Details about how to use model to make ABB tuition calculations

  • Documentation of how to connect to data and perform all necessary calculations.
  • SQL code that does all ABB calculations (revised 5/2/2017). 
  • Access database that performs the same calculations (not updated since February).

Excel Tools

An Excel file with separate tabs based on portions of the SQL code is available. The file includes:

  • Net operating revenue by fiscal year by tuition group by campus detail,
  • Net operating revenue detail for graduate/professional tuition categories by quarter, campus, category, residency, and year in program for professional students,
  • Distribution of ABB revenue to Seattle schools and colleges using old ABB parameters (UG: 60% SCH, 40% Degrees; Grad: 20% SCH, 80% Major enrollments)

NEW:  An Excel file that provides information about the FY17 True-Up, as well as the incremental FY18 tuition revenue that would be generated under two scenarios is now available. 

  • This file is in the same format that has been used in prior years to show the distribution of tuition revenue under ABB. 
  • The "Summary" tab shows true-up and incremental tuition under both scenarios for all schools/colleges. 
  • The other tabs follow the naming convention used in prior years, which is explained on the "Reference" tab.

The following SQL codes can be used to produce the calculations found in the Excel file:

  • NetRevenueToUnitsThrough2017: This file allows the user to calculate the distribution of revenue under the old ABB distribution rules. These calculations are relevant through the true-up for FY17.

  • NetRevenueToUnitsNewParameters: This file calculates the distribution for FY17 using the new distribution rules. Students in the new Graduate School tuition categories are separated out so the different distribution rules can be applied. This file can be used going forward.

  • ProjectionBaseNewParameters: This file produces the output that can be used to support projections. It breaks out students into all the new tuition categories and uses the new distribution rules. The major difference between this file and NetRevenueToUnitsNewParameters is that projections require a different level of granularity, which this file supports.

Office Hours

Currently, weekly ABB tuition revenue and distribution Office Hours are now scheduled for 60 minutes in the UW Tower, T-12 Large Work Room:

  • May 25, 2:00 pm

June:

  • Friday, June 2 at 1:00 pm
  • Friday, June 16 at 1:00 pm
  • Friday, June 23 at 1:00 pm  
  • Thursday,  June 29 at 9:00 am

July:

  • Thursday, July 7 at 9:00 am
  • Thursday, July 13 at 3:00 pm
  • Thursday, July 20 at 9:00 am
  • Thursday, July 27 at 9:00 am

August (if needed):

  • Thursday, Aug 3 at 1:00 pm
  • Thursday, Aug 10 at 1:00 pm
  • Thursday, Aug 17 at 1:00 pm
  • Thursday, Aug 24 at 1:00 pm
  • Thursday, Aug 31 at 1:00 pm

OPB