Spreadsheet Models for Enrollment Projections

1
Spreadsheet Models for Enrollment Projections

• Sandra Archer
• Interim Director, University Analysis and
  Planning Support
• University of Central Florida
• 23rd SUS Data Workshop
• IR Meeting
• July 25, 2006
• Tallahassee, Florida

Presentation available www.uaps.ucf.edu
2
Goals for the Presentation

• Share ideas for methods of developing enrollment
  projections
• Understand challenge of enrollment projections in
  a growth environment
• Discuss alternative modeling approaches
• New insight into the use of SAS and Excel
  features to manage data and create reports
• Take away Sample Excel sheet models for your use

3
The University of Central Florida
Stands for Opportunity

• Established in 1963 (first classes in 1968),
  Metropolitan Research University
• Grown from 1,948 to 45,000 students in 37 years
• 38,000 undergrads and 7,000 grads
• Ten colleges
• 12 regional campus sites
• 7th largest public university in U.S.
• 89 of lower division and 67 of upper division
  students are full-time

• Carnegie classification
• Undergraduate Professions plus arts sciences,
  high graduate coexistence
• Graduate Comprehensive doctoral (no medical)
  Medical school approved
• 92 Bachelors, 94 Masters, 3 Specialist, and 25
  Ph.D. programs
• Largest undergraduate enrollment in state
• Approximately 1,200 full-time faculty 9,000
  total employees

4
Why Do Enrollment Modeling?

• Projecting income from tuition
• Planning courses and curriculum
• Allocating resources to academic departments
• Long-term master planning
• Strategic planning
• Admissions policies
• How accurate do these projections have to be?
• See Hopkins, David S. P. and Massy, William F.,
  Planning Models for Colleges and Universities,
  Stanford University Press, Stanford, CA, 1981 for
  additional information on enrollment planning

5
Enrollment Models

• Objective find simplest model that predicts
  future enrollment based on past enrollment levels
  and new students enrolling
• Methods
• Regression (REG)
• Grade progression ratio method (GPR)
• Markov chain models (MC)
• Cohort flow models (CF)
• Notation
• Nj(t) number of students in state j at time t
• fj(t) number of students enrolling in state j
  at time t
• j state indexstands for class level

6
Regression Models

• Student inventory predicted returning students
  plus expected new students
• Prediction of returning students estimated by
  multivariate regression
• N(t) F Nj(t-1), fj(t-1), Nj(t-2), fj(t-2),
  f(t)

New students
Returning students
7
Grade Progression Ratio

• Ratio of students in one class level at time t to
  students in next-lower class level at time t-1
• Assumes
• Students follow an orderly progression form one
  state to another
• All students in each state move on to next state
  in one time period or drop out of the system for
  good
• Very simple model good for year-to-year
  projections
• Data readily available
• Not usable in higher education
• Estimate the GPR from historical data
• aj-1,j(t) Nj(t)/ Nj-1(t-1)
• Apply GPR to current enrollment level to predict
  next time period enrollment

8
Markov Chain

• Stochastic process
• Fluctuate in time because of random events
• System can be in various states
• Markov propertyeach outcome depends only on the
  one immediately preceding it
• Cross-sectional outlook
• Transition fraction
• pij fraction of students in class i in one
  period that can be found in class j in the
  subsequent time period

9
Cohort Flow Models

• Adopt a longitudinal outlook
• Take account of students origins
• Consider students accumulated duration of stay
• Students are grouped into cohorts at the time
  they enter the university (cohort survivor
  fractions)
• Could be viewed as a special case of Markov chain
  model where states are expanded to include origin
  and length of stay
• Cohorts typically defined for fall semester
• Combine with semester transition fractions to
  generate annual estimate

10
Combined Cohort-Markov Model
Cohort
Markov
Survivors
Transition
Transition
Transition
11
Overall Enrollment Projection
5.7 compounding annual growth
More than doubled in 13 years
12
UCF Approach

• Overall enrollment by level
• Use combined cohort-Markov model for next five
  years
• Use combined population and high school graduate
  growth rate projections for years 6 - 10 years
• Enrollment and degrees by program
• Conduct at major code level (degree track)
• Develop initial enrollment projections and degree
  projections
• Programs conduct review of estimates and modify
  projections
• Not conducted this year

1
2
3
13
UCF Approach
New student estimates from Admissions and
Graduate Studies
Estimated Headcount enrollment 2006 - 2011
Projected FTE by lower, upper, Grad I, Grad II,
Medical
Estimated SCH
Returning student estimates (UG 10-year cohorts)
(G 1-year return)
High School Graduates
Estimated Headcount enrollment 2012 - 2018
Population Growth
14
5-Year Model History

• Initial development
• Excel spreadsheet
• Manual adjustments/overwrites to improve
  prediction
• Historical data not updated
• Needed an approach that would generate
  appropriate adjustment factors that would be
  useful for prediction, independent of manual fine
  tuning adjustments
• Re-engineered in 2000

15
5-Year Model

• Retained basic conceptual structure
• Developed new spreadsheet structure
• Updated data and formulas
• Revised unclass HC to a weighted formula
• Selection of optimum adjustment parameters for
  prediction of next year HC
• Utilized multiplicative correction parameters
• Annual update of historical input data

16
5-Year Model

• Predicts headcount (HC)
• Estimates student credit hours (SCH) from HC
  based on previous behavior
• Estimates FTE from SCH (40 hrs UG, 32 hrs Grad)

17
Data Inputs to Determine HC

• New Student Input
• Estimated HC of new students by type (FTICs, CC
  Trans, Other Trans Graduate)
• By semester for five future years
• Provided by administrators
• New Undergraduate Student Allocation Fractions
• Historical allocation of each entrant type of
  undergraduate students (FTIC, CCT, OT) to a
  student classification (Fresh, Soph, Jr, Sr)
• Undergraduate Fall Retention Fractions
• Historical surviving (fall to fall) undergraduate
  students from annual entering cohort
• Ten years of entering cohorts
• Average of the two most recent cohorts
• Graduate Fall Continuation Fractions
• Historical rate of graduate students continuing
  fall to fall (two-year average)
• Computed only using the total number of graduate
  students not cohort based
• Semester Transition Fractions
• Students by level allocated to student
  classifications in the subsequent semester

New students by class / term
Students retuning in the Fall
Students retuning in the Summer and Spring
18
5-Year Model Details

• Summer semester
• Use Spring to Summer transition rate (from
  previous year) multiplied by previous Spring
  enrollments (data) by class plus new Summer
  students
• Fall semester
• Use Fall cohorts with cohort retention in class
  factors (based on student file) plus new Fall
  students plus continuing Summer students
• Spring semester
• Use Fall to Spring transition rate (from previous
  year) multiplied by Fall enrollments (modeled) by
  class plus new Spring students

19
5 Year Model
20
5-Year Model Adjustment Parameter Determination

• Adjustment parameters
• Existing approach transition rate ci, group size
  Xi, and adjustment parameter ai
• ciXi ai
• New approach
• aiciXi
• Select ai so that the predicted values for the
  previous year match the actual values
• Minimize the squared deviations of the difference
  (predicted minus actual)
• Implemented in Excel using Solver

21
Adjustment Parameter Optimization Setup
Adjustment made for each class/term
Factors that make 2004 a perfect fit are
applied to 2005 2010 prediction
22
User Inputs Allow for Adjustments
Control over data applied in the model Allows
for what if scenarios
23
5-Year Model Output
By Term
By Classification
Headcount
SCH by Level
24
5-Year Model Results Predicted HC
Model is very accurate for the next year, with
correction factors
Error increases out in the future
25
5-Year Model Conclusions

• Excel allows for what if analysis and
  adjustments
• Model is fairly accurate in the short term
  increasing error in future years
• Based on historical student behavior
• Data-driven process
• Detail at student level and term

• Future developments
• Detailed Grad I / II
• Retention trends
• Regional course offering trends
• Increase in web courses
• GOT (Graduate on Track program)
• Targeted programs
• Community College Consortium
• Medical School
• Increased support for graduate students

How can we account for changes in student
behavior?
5-Year Model Template
26
10-Year Projection Extension Model
Where prediction year t0

• Short-term detailed model projects t1 t5
• Extension model projects t6 t10
• Applies growth factor to t5 estimates to obtain
  t6 and repeats the process on an annual basis
  until t10 estimates are obtained
• Lower, Upper, or Graduate growth factor
• Average population growth and high school
  graduation growth

27
10-Year Projection Extension Model

• Using the population and the high school graduate
  growth data, a composite annual growth rate was
  computed for each of the regions
• 11-County Service Region
• 4 counties
• Other Florida
• Method applied to FTIC, CC Trans, Other Trans,
  Graduate

72 of UCFs new students from these areas
Example Growth in FTICs based on service area
population graduation growth (weighted)
28
10-Year Model Population Growth

• Population growth for Florida from Office of
  Economic and Demographic Research
  (http//edr.state.fl.us/)
• Projections by county for persons in the 18-24
  and 25-44 age groups
• Growth rates vary by county, the relevant UCF
  growth rates were developed by focusing on the
  counties that are currently the primary source of
  the universitys students
• Lower Level mostly First Time In College (FTIC)
  students
• Upper Level mostly Community College Transfers
  (CCT)
• Other transfers split between upper and lower

29
10-Year Model High School Graduation

• Graduation projections from Florida Department of
  Education (http//www.firn.edu/doe/evaluation/pdf/
  projhsgrad.pdf)
• Overall growth rate accounts for the time since
  high school graduation until college entry
• 0 years for FTIC
• 2 years for CCT
• 4 years for Graduate
• Combined to estimate the growth for Lower Level,
  Upper Level, and Graduate students

estimate the growth rate for the entering student
cohort
30
10-Year Model Combined Growth

• Time-adjusted growth factors using the average of
  the population-based and the high school-based
  growth rates

31
10-Year Model Results

• Growth factors applied to 5-year model output FTE
  and HC

• Historical predictions tested with actual
  population and graduation growth rates

Some years over some under
32
10-Year Model Output

• Regional campus growth rates provided by
  administration
• Overall growth allocated to the campuses

33
10-Year Projection Extension Model
34
10-Year Model Conclusions

• Starts with detailed 5-year model output as a
  base
• Applies high school graduation and population
  projections weighted by the areas that supply
  our students
• Regional growth allocation based on
  administrative input
• Future developments
• Workforce demand
• Regional, web, and other trends

10-Year Model Template
35
Program Enrollment Projection Model
Check out www.uaps.ucf.edu
36
Questions
???

• Sandra Archer
• Interim Director, University Analysis and
  Planning Support
• University of Central Florida
• 12424 Research Parkway, Suite 215
• Orlando, FL 32826-3207
• 407-882-0287
• archer_at_mail.ucf.edu
• www.uaps.ucf.edu

For more information www.uaps.ucf.edu/enrollment/
methods.html 5-Year and 10-Year Excel model
templates will be posted on the UAPS website