Spreadsheet Models for Enrollment Projections - PowerPoint PPT Presentation

1 / 36
About This Presentation
Title:

Spreadsheet Models for Enrollment Projections

Description:

j = state index stands for class level. UCF's Enrollment Projection Modeling Methods ... Estimated fall retained UG enrollment ... – PowerPoint PPT presentation

Number of Views:625
Avg rating:3.0/5.0
Slides: 37
Provided by: robertlarm7
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com