Course Project: Part 1 – Budget Spreadsheet Assignment
Instructions
You will develop an Excel spreadsheet with 5-year trend data
on a variety of key performance indicators and budget items showing the
financial status of the selected institution.
The US Department of Education’s National Center for
Education Statistics coordinates the Integrated Postsecondary Education Data
System (IPEDS) surveys every year. The IPEDS surveys are federal requirements
of all institutions that receive Title IV aid. IPEDS data are especially
helpful for enrollment and budget analysts to compare their own institutions
with comparative peer institutions. Use link: Retrieving IPEDS Data for
Selected Institutions in the Learn section for a helpful video tutorial
with the step-by-step instructions.
In this assignment, you will download data that are publicly
available from IPEDS (if you cannot locate the information from your own
institution’s website). Use the most recently available 5 years of reported
data for the following most important key performance indicators and budget
data that must be included in the spreadsheet:
1.
Fall Enrollment
· Enrollment of first time in college (FTIC) students
· Enrollment of new transfer students
· Total undergraduate enrollment
· Total graduate enrollment
· Full-time enrollment
· Part-time enrollment
· Grand total enrollment
2.
Retention Rates
· Full-time retention rate
· Part-time retention rate
3.
Completions
· Bachelor’s degrees
· Master’s and doctoral degrees combined
· Grand total completions
4.
Graduation Rates
· 150% of normal time
5.
Student Financial Aid and Net Price
· Number of undergraduate
students awarded federal, state, local, institutional or other sources
of grant aid
· Percent of undergraduate students awarded federal, state,
local, institutional, or other sources of grant aid
· Average amount of federal, state, local, institutional, or
other sources of grant aid awarded to undergraduate students
· Number of undergraduate students awarded federal student
loans
· Percent of undergraduate students awarded federal student
loans
· Average amount of federal student loans awarded to
undergraduate students
6.
Frequently Used/Derived Variables
· Total price for in-state students living on campus
· Total price for out-of-state students living on campus
7.
Finance
· Revenues
i. Tuition and fees
ii. Sales and services of auxiliary enterprises
iii. Total operating revenues
iv. Gifts, including contributions from affiliated organizations
v. Total nonoperating revenues
vi. Capital grants and gifts
vii. Total all revenues and other additions
· Expenses and salaries and wages by function and total
expenses by natural classification
i. Instruction current year total
ii. Research current year total
iii. Public service current year total
iv. Academic support current year total
v. Student services current year total
vi. Institutional support current year total
vii. Auxiliary enterprises current year total
· Endowment assets
i. Value of endowment assets at the end of the fiscal year
· Assets and liabilities
i. Total assets
ii. Total liabilities
· Plant, property, equipment
i. Total for plant, property and equipment
ii. Other capital assets – ending balance
You are welcome to provide additional information as a means
of preparing broader context about the institution. Because of the amount of
details required for this budget assignment, it is recommended that you use
multiple worksheets within the same Excel file instead of placing all the data
into the same worksheet.
