CASE STUDY: BUDGET SPREADSHEET ASSIGNMENT INSTRUCTIONS
You will develop an Excel spreadsheet with 3-year trend data on a variety of key performance indicators and budget items showing the financial status of the institution selected for the Case Study: Institutional Profile earlier in the course . In addition to providing data on your selected institution,select the following key state and national trends, assuming the data are available publicly:
• Total undergraduate enrollment
• Total graduate enrollment
• Total in-state enrollment
• Total out-of-state enrollment
• Student loan default rate
• Student borrowing
• Core revenues per student FTE
• Core expenses per student FTE by function
• Total state support for higher education
• Total federal support for higher education
As you review the data, how does your institution compare to the state and national trends? Is there anything that catches your attention, either positively or negatively?
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. You can find a link to a helpful video tutorial with the step-by-step instructions on retrieving IPEDS data for selected institutions on the Case Study: Budget Spreadsheet Assignment page under Case Study: Budget Spreadsheet Resources.
In this assignment, you will download data that are publicly available from US Department of Education’s National Center for Education Statistics coordinates the Integrated Postsecondary Education Data System (IPEDS) if you cannot locate the information from your own institution’s website. Use the most recently available 3 years of reported data.
One option that might be simpler is to download multiple years of the Data Feedback Report. You can identify your institution, then select “View Previous Data Feedback Reports.” This report provides you with a single year snapshot of these measures all in one file, so you would then need to download prior years to get the historical data. You can find the link to IPEDS data on the Case Study: Budget Spreadsheet Assignment page under Case Study: Budget Spreadsheet Resources.
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.