DebtWorksheetCopy.xlsx

	A	B	D	E
1	Debt Reveal Worksheet
2	University Name:		Date Completed:
3	Contact person:		Email:
4
5	Core Data	Fiscal Year:
6	Which university document was used for this analysis? Select		Several possible sources for this data are listed in the drop-down box. There may be others which you are free to use.
7	What is the university's total annual operating revenue?		For audits, GASB requires that revenues are reported separately as (net) operating revenues and (net) non-operating revenues. For example net operating revenues include tuition revenue minus tuition discounts and scholarships. Include both net operating revenues and non-operating revenues in your calculation. This calculation may be reported in the audit within a "Managed Revenues and Expenses" table.
8	What is your university's annual debt service payment?		This should also be available from the same sources in the drop box above. Once in those documents, search for "debt" or "debt service" or "dept payments".
9	(optional) How much debt in total does your university carry?		Finding total university debt may be more difficult to find because 1. some universities have on the book and off the book debt and 2. in may take longer to analyze larger universities with more complex accounting/reporting system.
10
11	Your University's Debt Burden Ratio
12	Total debt payments (from line 8 above)
13	Total revenues (from line 7 above)
14	Debt Ratio: Debt payments/ Total revenue (Line 12 / Line 13)	#DIV/0!	General guidelines for acceptable debt burden limits range from 5% (for on book debt service ) to 10% (for total debt service)	(Gregg Gascon PhD, October 2020 Analysis of the Financial Audits of Salem State University)
15
16	How much undergrad students pay for your university's debt service
17	How many full time equivalent (FTE) undergraduate students are there at your university?		You can retrieve this information at the IPEDS website: search for your university under "Look Up an Institution" and open the "Enrollment" tab
18	This calculated amount is the average that a FTE undergrad student at your institution pays in debt service per year (Line 8/ Line 17)	#DIV/0!
19	What is the average federal student loan awarded per student during the year analyzed?		You can retrieve this information at the IPEDS website: search for your university and open the Student Financial aid tab.
20	How much less student loan debt would your students graduate with, if university debt was abolished (Line 18 x 4)	#DIV/0!	Assumes 4 years full time to degree
21	Challenge: What is the university's total annual revenue from tuition and enrollment fees and auxiliary/enterprise revenue (housing fees, dining, etc.)		The total revenue generated by tuition & enrollment fees and auxiliary service fees (housing, dining, etc.) is reported in your university's annual audit. It may also be estimated in in budget documents.
22	This is the percentage of student payments that go to university debt service (Line 8/Line 21)	#DIV/0!
23
24	Instructional Harm due to Institutional Debt - Representations calculated singularly not in tandem
25	What is the mean F/T faculty salary at your university?		You can retrieve this information at the IPEDS website: search for your university as above and open the "Human Resource" tab. Look at the last graph for the blue bar - "All instructional staff total"
26	National Fringe Benefit rate Public higher ed employees	60.54%	According to the Bureau of Labor Statistics December 2020 report this is the fringe benefit rate for the state and local government higher education sector. Most public universities do not yet pay this full amount.	https://www.bls.gov/news.release/pdf/ecec.pdf
27	Salary + Fringe per FT instructor (Line 23 x 1.6054% of Line 23)	$ -
28	This is the number of FT faculty positions that annual debt payment could pay for instead (Line 27/ Line 8)	#DIV/0!	If campuses weren't servicing debt to banks, freed up funds could be used to bring back laid off faculty, convert adjunct hires to full-time hires, and or reduce tuition and fees. Because the maximal fringe rate was used in this calculation, this figure underestimates the impact.
29
30	How many classes does a full time faculty member teach per year?
31	Total number of class sections (taught by F/T faculty) sacrificed to debt payments (Line 26 x Line 28)	#DIV/0!	If campuses weren't servicing debt to banks, more course sections could run allowing for smaller classes and greater scheduling flexibility.
32
33	What other unnecessary capital-related (facility-related) expenses does your university have?
34
35	What cuts to instructional and student services has your university sustained this year?
36
37	Do you think these cuts would happen if you university didn't have debt service?
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100