OHIO BOARD OF REGENTS

Specifications for Resource Analysis (RA) in HEI

Revised May 27, 1999

Introduction

Resource Analysis (RA) is the cost allocation process used by the Regents to determine the cost of instruction related to the instructional subsidy. It allocates all of the Instruction and General (I&G) costs for each campus to the subject codes and levels of all course sections taught by the campus.

The RA process is in a state of development. It is subject to change.

Scope

All costs for every campus reported in the Faculty and Instructional Non-faculty Funding (FF), Funding Unit Expenditure (FX), College Expenditure (CP), and Campus Expenditure (CX) files are allocated to the combinations of subject codes and levels of the enrolled course sections offered by the campus.

Overview

The costs that are allocated in RA are the actual expenses reported by the campuses at the end of each fiscal year.

All of these costs are allocated to some combination of subject and level of the various course sections offered by the campus in some term of the fiscal year. These costs then are converted to a per student full-time equivalent (FTE) basis and aggregated together for all public colleges and universities in the state.

The statewide average cost per student FTE for each combination of subject and level of any course section offered by any campus of a public college or university becomes the basis of state instructional subsidy for instruction in that subject at that level.

The process is executed for each fiscal year and calculates both Unrestricted and Total (Restricted plus Unrestricted) I&G costs.

Allocation Methodology

All source expenses are allocated to the object, subject, and level by multiplying the expense by a ratio. The ratios are constructed in such a way that the sum of all of the ratios used to allocate a given expense to all of its objects, subjects, and levels is equal to one. This ensures that the allocated costs are equal to the input costs. Depending on the expense, the ratios consist of such measures as student credit hours, course credit hours, instructor compensation, and square feet of facility space. The assumption is that the measured parameters reflect cost.

General Allocation Description

The following is a brief description of each step of the cost allocation process. A more detailed description of the same steps follows the table.

Step	Costs Allocated and Objects of Allocation	Allocation Strategies
1	Allocate instructors' campus level, fiscal year, salaries, and fringe benefits among the subjects and levels of the courses that they teach. For graduate level courses (Level D or M) the level for cost allocation is adjusted to reflect the predominant level of the students enrolled in the course. Also, course sections offered by Medical Funding unit should be excluded from this step as well as the salaries paid by Medical Funding units. Medical Funding unit expenses will be allocated separately in Step 5.	Use both Course Credit Hours (CCH) and Student Credit Hours (SCH) as allocators and take the average. Weight SCH by a curve, which relates SCH in a course section to hours worked by the instructor.* Use number of courses instead of credit hours for instructors who teach any zero credit hour course sections and in the CCH allocation for instructors who teach individual studies courses which have fewer than four students enrolled. Distribute all credit hours among team teachers evenly. CCH is calculated as the average SCH for all students enrolled in the course section.
2	Subtract the allocated instructors' salaries and fringe benefits from the Funding Unit Expenditure (FX) file.	This is not an allocation step, but it is necessary because faculty salaries and fringe benefits are carried both in the Faculty and Instructional Non-faculty Funding (FF) file and the Funding Unit Expenditure (FX) file. The funding unit carried in the FF file identifies the funding unit that pays the instructor.
3	Prorate college expenses to funding unit.	Use Total Funding Unit expenses to prorate both Unrestricted and Total College expenses.
4	Allocate Funding Unit Overhead expenses to the subjects and levels of the course sections offered by the funding unit.	Use instructors' compensation as the allocator in the CCH allocation and SCH as the allocator in the SCH allocation. The funding unit reported in the Course Sections Taught (ST) file identifies the funding unit that offers the course section.
5	Expenses from the Medical Funding units are allocated both to the professional level of the Medical Subject code corresponding to their name and to the subjects and levels of the course sections offered by the Medical Funding unit.	Convert professional medical headcounts from the Medical Enrollment (ME) file to SCH (15 SCH per head). Then allocate all of the funds to the subject code corresponding to the funding unit name at professional level and to the subjects and levels of other course sections offered by these funding units based on SCH. Medical Funding units are: MEDICINE (511201), DENTISTRY (510401), OPTOMETRY (511701), VETERINARY MEDICINE (512401), CLINICAL PSYCHOLOGY (420101), and OSTEOPATHY (511901)
6	Summarize the unallocated funding unit costs to the campus level.	This is not an allocation step, but it is necessary because funding units without course sections may have some or all of their costs unallocated at this point in the process. It is a simple summarization to the campus level creating a new campus expense called Unallocated Funding Unit Overhead.
7	Allocate student services, academic support, institutional support, and the unallocated funding unit overhead expenses to all subjects and levels of course sections offered by the campus.	SCH is the allocator.
8	Allocate library expenses to all subjects and levels of course sections offered by the campus.	SCH is the allocator, but graduate SCH is weighted by a factor of three.
9	Allocate Plant Operation and Maintenance (POM) expenses to various cost categories at the campus level. The categories are: Instructional Classrooms, Instructional Laboratories, Other Areas Used for Instruction, Student Services, Graduate Libraries, Undergraduate Libraries, and Institutional Support.	Use Net Assignable Square Footage (NASF) by function code from the Area Inventory (AI) file to allocate to Instructional (functions 10 and 21), Student Services (function 52), Libraries (function 40), and Institutional Support (functions 51 and 54). Use weighted SCH to further allocate libraries to graduate and undergraduate. Use NASF by Area Type to further allocate Instructional to Classroom (area type 110), Lab (area type 210 and 215), and Other (all other area type values).
10	Allocate campus POM for student services, institutional support, undergraduate libraries, and classrooms to all subjects and levels of course sections offered by the campus. Restrict undergraduate libraries to undergraduate levels.	SCH is the allocator.
11	Allocate campus graduate library POM to all subjects and graduate levels of course sections offered by the campus.	The instructors' salaries and fringe benefits allocated to all subject code and level combinations of course sections offered by the campus calculated in Step 1 is the allocator.
12	Allocate campus lab POM to all subjects of areas classified as Instructional and Laboratory in Step 9. Allocate to only those subjects that have generated SCH. For those campuses with no subject codes that have both NASF in instructional laboratories and SCH, allocate to all subject and level combinations which have SCH based on SCH.	NASF from the Area Inventory (AI) file summarized by subject for areas identified as Instructional and Laboratory in Step 9 is the allocator for allocating to subject. SCH is the allocator from subject to level.
13	Allocate campus Other Instructional POM to all subjects of areas classified as Instructional and Other in Step 9. Allocate to only those subjects that have SCH (because Regents allocates cost on a per student basis). For those campuses with no subject codes that have both NASF in Instructional Other Rooms and Instructor's Compensation, allocate to all subject and level combinations which have SCH based on SCH.	NASF from the Area Inventory (AI) file summarized by subject for areas identified as Instructional and Other in Step 9 is the allocator for allocating to subject. Instructor's compensation based on the CCR allocation is the allocator from Subject to Level..
14	Allocate Law Library expense to Subject 220101, Level P.	All costs are allocated to a single subject code and level.
15	Allocate Med Library expense to Subject 511201, Level P.	All costs are allocated to a single subject code and level.
20	Balance all allocated cost to input cost for each campus.
21	Combine all allocated cost into a single table summed by fiscal year, campus, subject and level.
22	Add faculty data to the cost allocation table. Faculty data is the total Course Credit Hours reported for the sections in this aggregation broken down by the rank of the instructors. Faculty Rank is derived from the FD file Appointment Status and Faculty Rank. Use GA as the Rank if the Appointment Status is GA, use Faculty Rank for all other Appointment Statuses, combine Ranks NR, OT, and UK into an "All Others" Rank.	Course Credit Hours of the sections are calculated and allocated to the instructors the same as in Step 1. It's the average of the Student Credit Hours of the enrolled students in the Course Credit Hours of the section. In the case of team teaching the Course Credits are divided equally among all instructors of the section.
23	Add section size to the cost allocation table. This should be average section size both for all sections and excluding individual instruction. For the second group HEI will exclude all sections with fewer than four enrollees. In addition, add a distribution of the number of sections with various ranges of enrollments (1-3, 4-10, 11-50, >50).	Keep number of sections and number of enrollees in the cost allocation table, so that the averages can be rolled up to higher levels of aggregation.

*The curve used for the weighted SCH was selected by a study documented in Curve Fitting for Student Credit Hour Weights in Resource Analysis.

Data Tables Used in Resource Analysis

1. Academic Calendar: Reports calendar type for converting Student Credit Hours to FTE.
2. Area Inventory: Reports Function Code, Room Type and NASF for allocating POM.
3. Campus Expenses: Reports campus level costs to be allocated.
4. College Expenses: Reports college level costs to be allocated.
5. Course Enrollments: Reports SCH and course section size.
6. Course Inventory: Reports the Subject Code and Level of a course.
7. Course Section Taught: Relates instructor to course section.
8. Course Section Type: Might be used to identify individual studies, more likely we will use section size.
9. Course Section: Reports Funding Unit offering the section.
10. Faculty Demographics: Reports Instructor rank and full-time/part-time.
11. Faculty Funding: Reports instructor compensation to be allocated and Funding Units paying the instructor.
12. Funding Unit: Relates Funding Unit to College.
13. Funding Unit Expenses: Reports funding unit level costs, beyond instructor compensation to be allocated.
14. Institution SSN: Reports which instructor ids are codes for generic instructors.
15. Medical Enrollments: Reports professional medical enrollments.
16. Student Enrollment: Reports Student Rank.

Outputs

Outputs of the Resource Analysis process consist of:

A permanent table (Resource Analysis Allocated Cost table),
The step by step data verification displays,
A query to select and summarize data from the Resource Analysis Allocated Cost table.

Resource Analysis Allocated Cost table

Allocated costs should be saved in a table for each combination of fiscal year, campus, subject, and level.

The attributes are:

Fiscal Year (key),
Campus (key),
Subject Code (key),
Level (key),
Compensation (Unrestricted and Total), allocated in Step 1,
Funding Unit Overhead (Unrestricted and Total), allocated in Step 4,
Medical Funding Unit Cost (Unrestricted and Total), allocated in Step 5,
Student Services (Unrestricted and Total), allocated in Step 7,
Academic Support (Unrestricted and Total), allocated in Step 7,
Institutional Support (Unrestricted and Total), allocated in Step 7,
Unallocated Funding Unit Overhead (Unrestricted and Total), allocated in Step 7, rolled up in step 6,
Library (Unrestricted and Total), allocated in Steps 8 14, and 15,
POM (Unrestricted and Total), allocated in Steps 9 - 13,
Student FTE (Total, Lower Division, Upper Division, Master's, Doctoral, and Professional, added in Step 21,
Instructor FTE (Total, Professor, Assoc. Professor, Asst. Professor, Instructor, Lecturer, GA, All Others), added in Step 22,
Section Size (Number of Regular Sections, Number of Regular Sections, Enrollments in All Sections, Enrollments in Regular Sections), added in Step 23.

Query Input

Selection

One or more fiscal years,
Unrestricted or Total Cost (not both),
Campus varieties

One or more Institution Types
One or more Institutions
One or more Campuses

One or more Levels
Program Code varieties

One or more Subject fields
One or more two digit Subject Codes
One or more four digit Subject Codes
One or more six digit Subject Codes

One or more Subsidy Models (Subsidy Model selection is mutually exclusive with Subject Code or Level selection)
Output format (screen oriented or download).

Cost Allocation Output Display format

Each aggregation being displayed will use the following format.

Aggregation Allocated Cost per Student FTE

Fiscal Year Institution Type Institution Campus Subject (see Note 1) Level (see Note 2 Compensation Funding Unit Overhead Medical Funding Unit Student Services Institutional Support Academic Support Funding Unit Rollup Library POM

9999
XX XXXX XXXX XXXXXX XX
$999,999

$999,999

$999,999

$999,999

$999,999

$999,999

$999,999

$999,999

$999,999

Student FTE Lower Division Upper Division Master's Doctoral Professional

999,999

999,999

999,999

999,999

999,999

999,999

Instructor CCH Professor Assoc. Professor Asst. Professor Instructor Lecturer GA All Others Total

99,999

99,999

99,999

99,999

99,999

99,999

99,999

99,999
99,999

Average Class Size (all sections) Average Class Size (regular sections) Number of sections by size

1 - 3

4 - 10

11 - 50

> 50

999
999
99
9 99
9 99
9 99
9

Note 1: Subject may be a 6, 4, or 2 digit subject code, Subject Field Name, the word ALL, or a Subsidy Model Name

Note 2: Level May be a Level Code, the word ALL, or blank if Subject Code is a Subsidy Model Name.

Undecided Issues

How should costs be allocated for cross registered enrollments?

Detailed Specifications

Step Description	SQL Steps
Step 1: Allocate instructors' salaries and fringe benefits among the subjects and levels of the course sections that they teach. Allocate instructors' campus level, fiscal year, salaries, and fringe benefits among the subjects and levels of the courses that they teach. For graduate level courses (Level D or M) the level for cost allocation is adjusted to reflect the predominant level of the students enrolled in the course section. Also, course sections offered by Medical Funding unit should be excluded from this step, as well as the salaries paid by Medical Funding units. Medical Funding unit expenses will be allocated separately in Step 5.
Step 1 a: Join the Course Enrollment (CN) file with the Student Enrollment (SN) file to pick up the Student Rank. Fiscal year is input as a parameter. Build table "A Course Enroll."	SELECT dbo_crse_enroll.*, dbo_stud_enroll.rank_code, [fiscal year] AS FY INTO [a course enroll] FROM dbo_crse_enroll LEFT JOIN dbo_stud_enroll ON (dbo_crse_enroll.term_code = dbo_stud_enroll.term_code) AND (dbo_crse_enroll.yr_num = dbo_stud_enroll.yr_num) AND (dbo_crse_enroll.campus_code = dbo_stud_enroll.campus_code) AND (dbo_crse_enroll.OBRID = dbo_stud_enroll.OBRID) WHERE (((dbo_crse_enroll.yr_num)=[fiscal year]) AND ((dbo_crse_enroll.term_code) In ("SP","WI"))) OR (((dbo_crse_enroll.yr_num)=[fiscal year]-1) AND ((dbo_crse_enroll.term_code) In ("SM","AU")));
Step 1 b: Summarize by course section. Keep total SCH and total SCH for each Student Level. Student Level is related to Rank: Rank JR, SR and NU = Student Level B, Rank MS and NM = Student Level M, Rank DS, ND, and NG = Student Level D. Also, calculate average SCH (average SCH is what will be used for the CCH of the course section), and join Subject Code and Course Level from the Course Inventory (CI) file. Select the level to use in RA as the Course Level if it is G, T, B, or P, otherwise use the Student Level with the largest total SCH. Join with the Course Section table in order to exclude course sections offered by Medical Funding units. Build table "A Course Section."	Step 1b1 Summarize enrollments to course section. SELECT [a course enroll].FY, [a course enroll].yr_num AS year, [a course enroll].term_code AS term, [a course enroll].inst_code AS inst, [a course enroll].campus_code AS campus, [a course enroll].crse_id AS course, [a course enroll].crse_sect_id AS section, Sum([a course enroll].crse_enroll_cr_hrs) AS SCH, Avg([a course enroll].crse_enroll_cr_hrs) AS CCH, Sum(IIf([rank_code] In ("JR","SR","NU"),[crse_enroll_cr_hrs],0)) AS [B SCH], Sum(IIf([rank_code] In ("MS","NM"),[crse_enroll_cr_hrs],0)) AS [M SCH], Sum(IIf([rank_code] In ("DS","ND","NG"),[crse_enroll_cr_hrs],0)) AS [D SCH], Count() AS Enrollments, Sum(IIf([rank_code] In ("JR","SR","NU"),[crse_enroll_cr_hrs],0)) AS [UD SCH], Sum(IIf([rank_code] In ("FR","SO"),[crse_enroll_cr_hrs],0)) AS [LD SCH], Sum(IIf([rank_code] In ("PR"),[crse_enroll_cr_hrs],0)) AS [P SCH] FROM [a course enroll] GROUP BY [a course enroll].FY, [a course enroll].yr_num, [a course enroll].term_code, [a course enroll].inst_code, [a course enroll].campus_code, [a course enroll].crse_id, [a course enroll].crse_sect_id; Step 1b2 Join in funding unit from the Course Section table and subject and level from the CI. SELECT [a Step 1b1 sum to course section]., dbo_crse_section.fund_unit_id AS fund, dbo_crse_inventory.subject_code AS subject, dbo_crse_inventory.crse_level_code AS [CI level] FROM ([a Step 1b1 sum to course section] INNER JOIN dbo_crse_section ON ([a Step 1b1 sum to course section].section = dbo_crse_section.crse_sect_id) AND ([a Step 1b1 sum to course section].course = dbo_crse_section.crse_id) AND ([a Step 1b1 sum to course section].term = dbo_crse_section.term_code) AND ([a Step 1b1 sum to course section].year = dbo_crse_section.yr_num) AND ([a Step 1b1 sum to course section].campus = dbo_crse_section.campus_code)) INNER JOIN dbo_crse_inventory ON ([a Step 1b1 sum to course section].term = dbo_crse_inventory.term_code) AND ([a Step 1b1 sum to course section].year = dbo_crse_inventory.yr_num) AND ([a Step 1b1 sum to course section].inst = dbo_crse_inventory.inst_code) AND ([a Step 1b1 sum to course section].course = dbo_crse_inventory.crse_id); Step 1b3 Eliminate Medical Funding units and calculate RA level. SELECT [a Step 1b2 join funding unit, subject,level].FY, [a Step 1b2 join funding unit, subject,level].year, [a Step 1b2 join funding unit, subject,level].term, [a Step 1b2 join funding unit, subject,level].inst, [a Step 1b2 join funding unit, subject,level].campus, [a Step 1b2 join funding unit, subject,level].course, [a Step 1b2 join funding unit, subject,level].section, [a Step 1b2 join funding unit, subject,level].SCH, [a Step 1b2 join funding unit, subject,level].CCH, [a Step 1b2 join funding unit, subject,level].[B SCH], [a Step 1b2 join funding unit, subject,level].[M SCH], [a Step 1b2 join funding unit, subject,level].[D SCH], [a Step 1b2 join funding unit, subject,level].Enrollments, [a Step 1b2 join funding unit, subject,level].fund, [a Step 1b2 join funding unit, subject,level].subject, [a Step 1b2 join funding unit, subject,level].[CI level], IIf([CI level] Not In ("M","D"),[CI Level],IIf([B SCH]<[M SCH] And [D SCH]<[M SCH],"M",IIf([M SCH]<[B SCH] And [D SCH]<[B SCH],"B","D"))) AS [RA level], [a Step 1b2 join funding unit, subject,level].[UD SCH], [a Step 1b2 join funding unit, subject,level].[LD SCH], [a Step 1b2 join funding unit, subject,level].[P SCH] INTO [a course section] FROM [a Step 1b2 join funding unit, subject,level] LEFT JOIN dbo_medical_fund_unit ON ([a Step 1b2 join funding unit, subject,level].inst = dbo_medical_fund_unit.inst_code) AND ([a Step 1b2 join funding unit, subject,level].year = dbo_medical_fund_unit.yr_num) AND ([a Step 1b2 join funding unit, subject,level].term = dbo_medical_fund_unit.term_code) AND ([a Step 1b2 join funding unit, subject,level].fund = dbo_medical_fund_unit.fund_unit_id) WHERE (((dbo_medical_fund_unit.fund_unit_id) Is Null));
Step 1 c: Join the course sections from Step 1 b with Course Sections Taught (ST) file and the corresponding Course Section Type table. Merge in OBRID for each section instructor if it is not a generic identifier and Course Section Type code if it is Individual Studies (IS) and the section has less than 4 enrollments. Remember, some Individual Studies sections have many students, therefore, should be treated as regular sections. Eliminate course sections with no instructor.	Step 1c0 Join in OBRID. SELECT [a course section]., dbo_crse_sect_taught1.OBRID AS OBRID FROM [a course section] INNER JOIN dbo_crse_sect_taught1 ON ([a course section].section = dbo_crse_sect_taught1.crse_sect_id) AND ([a course section].course = dbo_crse_sect_taught1.crse_id) AND ([a course section].term = dbo_crse_sect_taught1.term_code) AND ([a course section].year = dbo_crse_sect_taught1.yr_num) AND ([a course section].campus = dbo_crse_sect_taught1.campus_code); Step 1c1 Join in section type (IS). SELECT [a Step 1c0 join in OBRID from ST]., dbo_crse_sect_type1.crse_sect_type_code AS [section type] FROM [a Step 1c0 join in OBRID from ST] LEFT JOIN dbo_crse_sect_type1 ON ([a Step 1c0 join in OBRID from ST].OBRID = dbo_crse_sect_type1.OBRID) AND ([a Step 1c0 join in OBRID from ST].section = dbo_crse_sect_type1.crse_sect_id) AND ([a Step 1c0 join in OBRID from ST].course = dbo_crse_sect_type1.crse_id) AND ([a Step 1c0 join in OBRID from ST].campus = dbo_crse_sect_type1.campus_code) AND ([a Step 1c0 join in OBRID from ST].term = dbo_crse_sect_type1.term_code) AND ([a Step 1c0 join in OBRID from ST].year = dbo_crse_sect_type1.yr_num) WHERE (((dbo_crse_sect_type1.crse_sect_type_code) Is Null Or ([crse_sect_type_code]="IS" And [Enrollments]<4)));
Step 1d: Adjust for team teaching. First calculate the number of instructors in each course section, then distribute the CCH and SCH of the course section evenly among them. In this step also calculate the SCH Score for the weighted SCH allocation for this course section, instructor combination. Use the formula: SCH_Score= If([SCH Inst]<500, 0.000000173[SCH Inst]^3 -0.0002525[SCH Inst]^2 +0.1087[SCH Inst]+7, Otherwise, SCH_Score=0.000000173500^3-0.0002525500^2+0.1087 500+7) where SCH Inst is the SCH distributed to this instructor for this course section. Refer to the document Curve Fitting for Resource Analysis for a discussion of the method used to find this formula. Convert Cuyahoga Community College (CYCC) and Southern State Community College (SSCC) to institution rather than campus because their expense reports are by institution. Build table "A Course Instructor."	Step 1d1 Calculate team size. SELECT [a Step 1c1 join in OBRID and section type].year, [a Step 1c1 join in OBRID and section type].term, [a Step 1c1 join in OBRID and section type].campus, [a Step 1c1 join in OBRID and section type].course, [a Step 1c1 join in OBRID and section type].section, Count() AS [team size] FROM [a Step 1c1 join in OBRID and section type] GROUP BY [a Step 1c1 join in OBRID and section type].year, [a Step 1c1 join in OBRID and section type].term, [a Step 1c1 join in OBRID and section type].campus, [a Step 1c1 join in OBRID and section type].course, [a Step 1c1 join in OBRID and section type].section; Step 1d2 Distribute credits among team. SELECT [a Step 1c1 join in OBRID and section type]., [SCH]/[team size] AS [SCH inst], [CCH]/[team size] AS [CCH inst], IIf([SCH Inst]<500,0.000000173[SCH Inst]^3-0.0002525[SCH Inst]^2+0.1087[SCH Inst]+7,0.000000173500^3-0.0002525500^2+0.1087500+7) AS [SCH score], IIf([inst] In ("CYCC","SSCC"),[inst],[a Step 1c1 join in OBRID and section type]![campus]) AS [RA campus] INTO [a course instructor] FROM [a Step 1c1 join in OBRID and section type] INNER JOIN [a Step 1d1 calculate team size] ON ([a Step 1c1 join in OBRID and section type].year = [a Step 1d1 calculate team size].year) AND ([a Step 1c1 join in OBRID and section type].term = [a Step 1d1 calculate team size].term) AND ([a Step 1c1 join in OBRID and section type].campus = [a Step 1d1 calculate team size].campus) AND ([a Step 1c1 join in OBRID and section type].course = [a Step 1d1 calculate team size].course) AND ([a Step 1c1 join in OBRID and section type].section = [a Step 1d1 calculate team size].section);
Step 1e: Summarize a course instructor table by instructor. Calculate: total CCH, total SCH, total SCH score, number of course sections taught, number of course sections taught for which SCH equals zero, and number of course sections taught which are flagged as individual studies. Join Faculty and Instructional Non-faculty Funding (FF) file to pick up salary and fringe benefits. First, summarize the (FF) file by Campus and Instructor ID. In this summary, exclude rows from the Medical Funding units. Medical Funding units are identified in the Medical Funding Unit verification table. Calculate Unrestricted Compensation as Unrestricted Salary plus Unrestricted Fringe Benefits and Total Compensation as Unrestricted Salary plus Unrestricted Fringe Benefits plus Restricted Salary plus Restricted Fringe Benefits for each Campus and Instructor ID combination. Identify the resulting table as the Instructor 2 Table with Compensation.	Step 1e1 Summarize section/instructor to instructor. SELECT [a course instructor].FY AS [fiscal year], [a course instructor].[RA campus], [a course instructor].OBRID, Sum([a course instructor].[SCH inst]) AS [inst SCH], Sum([a course instructor].[CCH inst]) AS [inst CCH], Sum([a course instructor].[SCH score]) AS [inst score], Count() AS sections, Sum(IIf([SCH]=0,1,0)) AS [zero SCH sections], Sum(IIf([section type]="IS",1,0)) AS [IS sections] FROM [a course instructor] GROUP BY [a course instructor].FY, [a course instructor].[RA campus], [a course instructor].OBRID; Step 1e2 Join FF records to pick up comp and funding unit. Eliminate instructors who are not paid. Also, create multiple rows for instructors paid by more than one funding unit. SELECT [a Step 1e1 summarize instructor sections to instructor]., [fac_salary_expens]+[fac_benefits_expens] AS [UR comp], [fac_salary_expens]+[fac_benefits_expens]+[fac_restrict_salary_expens]+[fac_restrict_benefits_expens] AS [Tot comp], dbo_fac_fund.fund_unit_id AS [comp FU] FROM [a Step 1e1 summarize instructor sections to instructor] INNER JOIN dbo_fac_fund ON ([a Step 1e1 summarize instructor sections to instructor].[fiscal year] = dbo_fac_fund.yr_num) AND ([a Step 1e1 summarize instructor sections to instructor].[RA campus] = dbo_fac_fund.campus_code) AND ([a Step 1e1 summarize instructor sections to instructor].OBRID = dbo_fac_fund.OBRID); Step 1e3 Get Medical Funding units for this year. SELECT dbo_medical_fund_unit.fund_unit_id FROM dbo_medical_fund_unit WHERE (((dbo_medical_fund_unit.yr_num)=[fiscal year]) AND ((dbo_medical_fund_unit.term_code)="SP")); Step 1e4 Eliminate Medical Funding units. SELECT [a Step 1e2 join in compensation].* FROM [a Step 1e2 join in compensation] LEFT JOIN [a Step 1e3 get med funding units] ON [a Step 1e2 join in compensation].[comp FU] = [a Step 1e3 get med funding units].fund_unit_id WHERE ((([a Step 1e3 get med funding units].fund_unit_id) Is Null)); Step 1e5 Sum to instructor. SELECT [a Step 1e4 eliminate medical funding units].[fiscal year], [a Step 1e4 eliminate medical funding units].[RA campus], [a Step 1e4 eliminate medical funding units].OBRID, [a Step 1e4 eliminate medical funding units].[inst SCH], [a Step 1e4 eliminate medical funding units].[inst CCH], [a Step 1e4 eliminate medical funding units].[inst score], [a Step 1e4 eliminate medical funding units].sections, [a Step 1e4 eliminate medical funding units].[zero SCH sections], [a Step 1e4 eliminate medical funding units].[IS sections], Sum([a Step 1e4 eliminate medical funding units].[UR comp]) AS [inst UR comp], Sum([a Step 1e4 eliminate medical funding units].[Tot comp]) AS [inst Tot comp] FROM [a Step 1e4 eliminate medical funding units] GROUP BY [a Step 1e4 eliminate medical funding units].[fiscal year], [a Step 1e4 eliminate medical funding units].[RA campus], [a Step 1e4 eliminate medical funding units].OBRID, [a Step 1e4 eliminate medical funding units].[inst SCH], [a Step 1e4 eliminate medical funding units].[inst CCH], [a Step 1e4 eliminate medical funding units].[inst score], [a Step 1e4 eliminate medical funding units].sections, [a Step 1e4 eliminate medical funding units].[zero SCH sections], [a Step 1e4 eliminate medical funding units].[IS sections]; Step 1e6 Join instructor totals. SELECT [a course instructor].FY, [a course instructor].year, [a course instructor].term, [a course instructor].inst, [a course instructor].campus, [a course instructor].[RA campus], [a course instructor].OBRID, [a course instructor].course, [a course instructor].section, [a course instructor].subject, [a course instructor].[RA level], [a course instructor].[section type], [a course instructor].[SCH inst], [a course instructor].[CCH inst], [a course instructor].[SCH score], [a course instructor].Enrollments, [a Step 1e5 sum to instructor].[inst SCH] AS [inst tot SCH], [a Step 1e5 sum to instructor].[inst CCH] AS [inst CCH tot], [a Step 1e5 sum to instructor].[inst score] AS [inst scoretot], [a Step 1e5 sum to instructor].sections AS [inst tot sections], [a Step 1e5 sum to instructor].[zero SCH sections], [a Step 1e5 sum to instructor].[IS sections], [a Step 1e5 sum to instructor].[inst UR comp], [a Step 1e5 sum to instructor].[inst Tot comp] FROM [a course instructor] INNER JOIN [a Step 1e5 sum to instructor] ON ([a course instructor].[RA campus] = [a Step 1e5 sum to instructor].[RA campus]) AND ([a course instructor].OBRID = [a Step 1e5 sum to instructor].OBRID);
Step 1f: Distribute the instructors' compensation to the course sections they teach. If any of the sections taught by the instructor has non-zero SCH, distribute the compensation using SCH by multiplying the compensation by the ratio of the SCH score for the course divided by the total SCH score for the instructor; otherwise, distribute the compensation by dividing the compensation by the number of courses taught. If both the number of course sections with zero SCH is zero and the number of course sections marked as individual studies is zero, distribute the compensation using CCH by multiplying the compensation by the ratio of the CCH for the course divided by the total CCH for the instructor; otherwise, distribute the compensation by dividing the compensation by the number of courses taught. Then, calculate the average distributed compensation as the average of the SCR and CCR compensations. Do all of this for both Unrestricted and Total Compensation. Identify the resulting table as the Section 4 with Comp Allocated table. Build table "A Section Instructor Comp Distribution."	SELECT [a Step 1e6 merge instructor totals]., IIf(IsNull([inst UR comp]) Or [inst UR comp]=0,0,IIf(IsNull([inst scoretot]) Or [inst scoretot]=0,[inst UR comp]/[inst tot sections],[inst UR comp]([SCH score]/[inst scoretot]))) AS [Unr SCH comp], IIf(IsNull([inst Tot comp]) Or [inst Tot comp]=0,0,IIf(IsNull([inst scoretot]) Or [inst scoretot]=0,[inst Tot comp]/[inst tot sections],[inst Tot comp]([SCH score]/[inst scoretot]))) AS [Tot SCH comp], IIf(IsNull([inst UR comp]) Or [inst UR comp]=0,0,IIf(IsNull([inst CCH tot]) Or [inst CCH tot]=0 Or Not [zero SCH sections]=0 Or Not [IS sections]=0,[inst UR comp]/[inst tot sections],[inst UR comp]([CCH inst]/[inst CCH tot]))) AS [Unr CCH comp], IIf(IsNull([inst Tot comp]) Or [inst Tot comp]=0,0,IIf(IsNull([inst CCH tot]) Or [inst CCH tot]=0 Or Not [zero SCH sections]=0 Or Not [IS sections]=0,[inst Tot comp]/[inst tot sections],[inst Tot comp]*([CCH inst]/[inst CCH tot]))) AS [Tot CCH comp], ([Unr SCH comp]+[Unr CCH comp])/2 AS [Avg Unr comp], ([Tot SCH comp]+[Tot CCH comp])/2 AS [Avg Tot comp] INTO [a section instructor comp distribution] FROM [a Step 1e6 merge instructor totals];
Step 1g: Summarize to subject and level. Build table "A Subject Level Comp Cost."	SELECT [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].subject, [a section instructor comp distribution].[RA level], Sum([a section instructor comp distribution].[Avg Unr comp]) AS [Unr comp], Sum([a section instructor comp distribution].[Avg Tot comp]) AS [Tot comp], Count(*) AS Sections, Sum([a section instructor comp distribution].[SCH inst]) AS SCH INTO [a subject level comp cost] FROM [a section instructor comp distribution] GROUP BY [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].subject, [a section instructor comp distribution].[RA level];
Step 2: Subtract the allocated instructors' salaries and fringe benefits from the Funding Unit Expenditure (FX) file.
Step 2a: Select distinct instructors who teach.	SELECT DISTINCT [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].OBRID FROM [a section instructor comp distribution];
Step 2b: Select instructors who teach from the Faculty and Instructional non-Faculty Funding (FF) file, to pick up their comp.	SELECT [a Step 2a select distinct instructors who teach].*, dbo_fac_fund.fund_unit_id, dbo_fac_fund.fac_salary_expens, dbo_fac_fund.fac_restrict_salary_expens, dbo_fac_fund.fac_benefits_expens, dbo_fac_fund.fac_restrict_benefits_expens FROM [a Step 2a select distinct instructors who teach] INNER JOIN dbo_fac_fund ON ([a Step 2a select distinct instructors who teach].OBRID = dbo_fac_fund.OBRID) AND ([a Step 2a select distinct instructors who teach].[RA campus] = dbo_fac_fund.campus_code) AND ([a Step 2a select distinct instructors who teach].FY = dbo_fac_fund.yr_num) WHERE (((dbo_fac_fund.fund_unit_id) Not In ("NA")));
Step 2c: Summarize to funding unit.	SELECT [a Step 2b Select instructors who teach from FF].FY, [a Step 2b Select instructors who teach from FF].[RA campus], [a Step 2b Select instructors who teach from FF].fund_unit_id, Sum([a Step 2b Select instructors who teach from FF].fac_salary_expens) AS [Unres Salary], Sum([a Step 2b Select instructors who teach from FF].fac_restrict_salary_expens) AS [Res Salary], Sum([a Step 2b Select instructors who teach from FF].fac_benefits_expens) AS [Unres Bene], Sum([a Step 2b Select instructors who teach from FF].fac_restrict_benefits_expens) AS [Res Bene], Count(*) AS records FROM [a Step 2b Select instructors who teach from FF] GROUP BY [a Step 2b Select instructors who teach from FF].FY, [a Step 2b Select instructors who teach from FF].[RA campus], [a Step 2b Select instructors who teach from FF].fund_unit_id;
Step 2d and e: Eliminate Medical Funding units.	SELECT dbo_medical_fund_unit.fund_unit_id FROM dbo_medical_fund_unit WHERE (((dbo_medical_fund_unit.yr_num)=[fiscal year]) AND ((dbo_medical_fund_unit.term_code)="SP")); SELECT [a Step 2c Summarize to FU].* FROM [a Step 2c Summarize to FU] LEFT JOIN [a Step 2d select medical funding units] ON [a Step 2c Summarize to FU].fund_unit_id = [a Step 2d select medical funding units].fund_unit_id WHERE ((([a Step 2d select medical funding units].fund_unit_id) Is Null));
Step 2f: Join allocated comp with FX data. Build table "FX with Comp."	SELECT dbo_fund_unit_expens.yr_num, dbo_fund_unit_expens.campus_code, dbo_fund_unit_expens.fund_unit_id, Nz([Unres Salary],0)+nz([Unres Bene],0) AS [Unres Comp], Nz([Unres Salary],0)+nz([Unres Bene],0)+nz([Res Salary],0)+nz([Res Bene],0) AS [Total Comp], [fac_salary_expens]+[benefits_expens]+[other_fund_expens]-[Unres Comp] AS [Unres Other], [fac_salary_expens]+[benefits_expens]+[other_fund_expens]+[restrict_fac_salary_expens]+[restrict_benefits_expens]+[restrict_other_fund_expens]-[Total Comp] AS [Total Other] INTO [a FX with comp] FROM dbo_fund_unit_expens LEFT JOIN [a Step 2e eliminate medical FUs] ON (dbo_fund_unit_expens.fund_unit_id = [a Step 2e eliminate medical FUs].fund_unit_id) AND (dbo_fund_unit_expens.yr_num = [a Step 2e eliminate medical FUs].FY) AND (dbo_fund_unit_expens.campus_code = [a Step 2e eliminate medical FUs].[RA campus]) WHERE (((dbo_fund_unit_expens.yr_num)=[fiscal year]));
Step 3a: Join college from the Funding Unit Inventory (FI) file to Funding Unit Expense table. Use SP term of the fiscal year in the Funding Unit table.	SELECT [a FX with comp].*, dbo_fund_unit.college_id FROM [a FX with comp] LEFT JOIN dbo_fund_unit ON ([a FX with comp].fund_unit_id = dbo_fund_unit.fund_unit_id) AND ([a FX with comp].campus_code = dbo_fund_unit.campus_code) AND ([a FX with comp].yr_num = dbo_fund_unit.yr_num) WHERE (((dbo_fund_unit.term_code)="SP"));
Step 3b: Sum funding unit costs to college.	SELECT DISTINCTROW [Step 3 a Append College to the Funding Unit Rows].yr_num, [Step 3 a Append College to the Funding Unit Rows].campus_code, [Step 3 a Append College to the Funding Unit Rows].college_id, Sum([Total Comp]+[Total Other]) AS [Total College]FROM [Step 3 a Append College to the Funding Unit Rows] GROUP BY [Step 3 a Append College to the Funding Unit Rows].yr_num, [Step 3 a Append College to the Funding Unit Rows].campus_code, [Step 3 a Append College to the Funding Unit Rows].college_id;
Step 3c: Prorate college costs from the College Expenditure (CP) file to funding unit. Use Total Faculty Compensation plus Total Other Expenses fields only to prorate both Unrestricted and Total Expenses. If separate allocation factors are used, the funding unit may contain more unrestricted academic administration than total academic administration, due to the unequal distribution of unrestricted and total expenditures in the subordinate funding units. Then, prorate the college expenses into the Funding Unit with Comp table based on the sum of faculty compensation plus other expenses in each funding unit. Make sure that all funding units in the Funding Unit Comp table are included in the resulting table, even those which have no College field. Build table "an FX with Comp and College."	SELECT [a Step 3a Join college to FU table]., IIf(IsNull([dbo_college_expens]![college_id]) Or [Total College]=0,0,[college_expens](([Total Comp]+[Total Other])/[Total College])) AS [Unres AA], IIf(IsNull([dbo_college_expens]![college_id]) Or [Total College]=0,0,([college_expens]+[restrict_college_expens])*(([Total Comp]+[Total Other])/[Total College])) AS [Total AA] INTO [a FX with comp and college] FROM ([a Step 3a Join college to FU table] LEFT JOIN [a Step 3b sum FU Cost to college] ON ([a Step 3a Join college to FU table].college_id = [a Step 3b sum FU Cost to college].college_id) AND ([a Step 3a Join college to FU table].campus_code = [a Step 3b sum FU Cost to college].campus_code) AND ([a Step 3a Join college to FU table].yr_num = [a Step 3b sum FU Cost to college].yr_num)) LEFT JOIN dbo_college_expens ON ([a Step 3a Join college to FU table].yr_num = dbo_college_expens.yr_num) AND ([a Step 3a Join college to FU table].campus_code = dbo_college_expens.campus_code) AND ([a Step 3a Join college to FU table].college_id = dbo_college_expens.college_id);
Step 4: Allocate funding unit overhead to the subjects and levels of the course sections offered by the funding unit. Start with the summarized Course Sections, sum to Funding Unit, Subject and Level. Then step by step add attributes for SCH and Compensation Cost both at the Funding Unit and Funding Unit, Subject and Level aggregations, also add Funding Unit Overhead Cost and finally allocate the Funding Unit Overhead cost to Subject and Level within the Funding Unit. Steps 4a and b Steps a,b,c deal with SCH	Step 4an Sum scetions to FUSL SCH SELECT [a course section].FY, [a course section].inst, [a course section].campus, [a course section].fund, [a course section].subject, [a course section].[RA level], Sum([a course section].SCH) AS [FUSL SCH], Count() AS Sections FROM [a course section] GROUP BY [a course section].FY, [a course section].inst, [a course section].campus, [a course section].fund, [a course section].subject, [a course section].[RA level]; Step 4bn sum to FU SCH SELECT [a Step 4an Sum to FUSL SCH].FY, [a Step 4an Sum to FUSL SCH].inst, [a Step 4an Sum to FUSL SCH].campus, [a Step 4an Sum to FUSL SCH].fund, Sum([a Step 4an Sum to FUSL SCH].[FUSL SCH]) AS [FU SCH], Sum([a Step 4an Sum to FUSL SCH].Sections) AS [FU Sections] FROM [a Step 4an Sum to FUSL SCH] GROUP BY [a Step 4an Sum to FUSL SCH].FY, [a Step 4an Sum to FUSL SCH].inst, [a Step 4an Sum to FUSL SCH].campus, [a Step 4an Sum to FUSL SCH].fund; Step 4cn Join FU SCH to FUSL SCH SELECT [a Step 4an Sum to FUSL SCH]., [a Step 4bn Sum to FU SCH].[FU SCH], IIf([a Step 4an Sum to FUSL SCH]![inst] In ("CYCC","SSCC"),[a Step 4an Sum to FUSL SCH]![inst],[a Step 4an Sum to FUSL SCH]![campus]) AS [RA campus] FROM [a Step 4an Sum to FUSL SCH] INNER JOIN [a Step 4bn Sum to FU SCH] ON ([a Step 4an Sum to FUSL SCH].fund = [a Step 4bn Sum to FU SCH].fund) AND ([a Step 4an Sum to FUSL SCH].campus = [a Step 4bn Sum to FU SCH].campus) AND ([a Step 4an Sum to FUSL SCH].inst = [a Step 4bn Sum to FU SCH].inst) AND ([a Step 4an Sum to FUSL SCH].FY = [a Step 4bn Sum to FU SCH].FY);
Step 4d adds in the Overhead Cost for each Funding Unit	Step 4dn Join FU Overhead Cost SELECT [a Step 4cn Join FU SCH].*, [a FX with comp and college].[Unres Other], [a FX with comp and college].[Total Other], [a FX with comp and college].[Unres AA], [a FX with comp and college].[Total AA] FROM [a Step 4cn Join FU SCH] INNER JOIN [a FX with comp and college] ON ([a Step 4cn Join FU SCH].[RA campus] = [a FX with comp and college].campus_code) AND ([a Step 4cn Join FU SCH].fund = [a FX with comp and college].fund_unit_id) AND ([a Step 4cn Join FU SCH].FY = [a FX with comp and college].yr_num);
Step 4 e, f, g, h, and i add in the Cost of Compensation.	Step 4en Append offering FU to Comp Cost SELECT [a section instructor comp distribution]., dbo_crse_section.fund_unit_id FROM [a section instructor comp distribution] INNER JOIN dbo_crse_section ON ([a section instructor comp distribution].campus = dbo_crse_section.campus_code) AND ([a section instructor comp distribution].section = dbo_crse_section.crse_sect_id) AND ([a section instructor comp distribution].course = dbo_crse_section.crse_id) AND ([a section instructor comp distribution].term = dbo_crse_section.term_code) AND ([a section instructor comp distribution].year = dbo_crse_section.yr_num); Step 4fn Sum comp cost to FUSL SELECT [a Step 4en append offering FU to comp cost].FY, [a Step 4en append offering FU to comp cost].[RA campus], [a Step 4en append offering FU to comp cost].fund_unit_id, [a Step 4en append offering FU to comp cost].subject, [a Step 4en append offering FU to comp cost].[RA level], Sum([a Step 4en append offering FU to comp cost].[Avg Unr comp]) AS [FUSL Unr Comp], Sum([a Step 4en append offering FU to comp cost].[Avg Tot comp]) AS [FUSL Total Comp] FROM [a Step 4en append offering FU to comp cost] GROUP BY [a Step 4en append offering FU to comp cost].FY, [a Step 4en append offering FU to comp cost].[RA campus], [a Step 4en append offering FU to comp cost].fund_unit_id, [a Step 4en append offering FU to comp cost].subject, [a Step 4en append offering FU to comp cost].[RA level]; Step 4gn Join FUSL Comp Cost SELECT [a Step 4dn Join FU Ovh Cost]., [a Step 4fn Sum comp cost to FUSL].[FUSL Unr Comp], [a Step 4fn Sum comp cost to FUSL].[FUSL Total Comp] FROM [a Step 4dn Join FU Ovh Cost] LEFT JOIN [a Step 4fn Sum comp cost to FUSL] ON ([a Step 4dn Join FU Ovh Cost].[RA level] = [a Step 4fn Sum comp cost to FUSL].[RA level]) AND ([a Step 4dn Join FU Ovh Cost].subject = [a Step 4fn Sum comp cost to FUSL].subject) AND ([a Step 4dn Join FU Ovh Cost].fund = [a Step 4fn Sum comp cost to FUSL].fund_unit_id) AND ([a Step 4dn Join FU Ovh Cost].[RA campus] = [a Step 4fn Sum comp cost to FUSL].[RA campus]) AND ([a Step 4dn Join FU Ovh Cost].FY = [a Step 4fn Sum comp cost to FUSL].FY); Step 4hn Sum Comp Cost to FU SELECT [a Step 4fn Sum comp cost to FUSL].FY, [a Step 4fn Sum comp cost to FUSL].[RA campus], [a Step 4fn Sum comp cost to FUSL].fund_unit_id, Sum([a Step 4fn Sum comp cost to FUSL].[FUSL Unr Comp]) AS [FU Unr Comp], Sum([a Step 4fn Sum comp cost to FUSL].[FUSL Total Comp]) AS [FU Total Comp] FROM [a Step 4fn Sum comp cost to FUSL] GROUP BY [a Step 4fn Sum comp cost to FUSL].FY, [a Step 4fn Sum comp cost to FUSL].[RA campus], [a Step 4fn Sum comp cost to FUSL].fund_unit_id; Step 4in Join FU Comp Cost SELECT [a Step 4gn Join FUSL Comp Cost].*, [a Step 4hn Sum comp cost to FU].[FU Unr Comp], [a Step 4hn Sum comp cost to FU].[FU Total Comp] FROM [a Step 4gn Join FUSL Comp Cost] LEFT JOIN [a Step 4hn Sum comp cost to FU] ON ([a Step 4gn Join FUSL Comp Cost].fund = [a Step 4hn Sum comp cost to FU].fund_unit_id) AND ([a Step 4gn Join FUSL Comp Cost].[RA campus] = [a Step 4hn Sum comp cost to FU].[RA campus]) AND ([a Step 4gn Join FUSL Comp Cost].FY = [a Step 4hn Sum comp cost to FU].FY);
Step 4 j and k allocate the Overhead Cosft from the Funding Unit to Subject and Level and then summarize the results to Subject and Level. Build table "A Subject Level FU Cost."	Step 4jn Allocate FU Overhead SELECT [a Step 4in Join FU Comp Cost]., IIf([FU SCH]=0 And (Nz([FU Unr Comp],0)=0 Or Nz([FU Total Comp])=0),0,IIf(Not [FU SCH]=0,([Unres Other]+[Unres AA])([FUSL SCH]/[FU SCH]),([Unres Other]+[Unres AA])(Nz([FUSL Unr Comp],0)/[FU Unr Comp]))) AS [Unr SCH FUSL OVH], IIf([FU SCH]=0 And (Nz([FU Unr Comp],0)=0 Or Nz([FU Total Comp])=0),0,IIf(Not Nz([FU Unr Comp],0)=0,([Unres Other]+[Unres AA])(Nz([FUSL Unr Comp],0)/[FU Unr Comp]),([Unres Other]+[Unres AA])([FUSL SCH]/[FU SCH]))) AS [Unr CCH FUSL OVH], IIf([FU SCH]=0 And (Nz([FU Unr Comp],0)=0 Or Nz([FU Total Comp])=0),0,IIf(Not [FU SCH]=0,([Total Other]+[Total AA])([FUSL SCH]/[FU SCH]),([Total Other]+[Total AA])(Nz([FUSL Unr Comp],0)/[FU Unr Comp]))) AS [Total SCH FUSL OVH], IIf([FU SCH]=0 And (Nz([FU Unr Comp],0)=0 Or Nz([FU Total Comp])=0),0,IIf(Not [FU Unr Comp]=0,([Total Other]+[Total AA])(Nz([FUSL Unr Comp],0)/[FU Unr Comp]),([Total Other]+[Total AA])*([FUSL SCH]/[FU SCH]))) AS [Total CCH FUSL OVH], ([Unr SCH FUSL OVH]+[Unr CCH FUSL OVH])/2 AS [Avg Unr FUSL OVH], ([Total SCH FUSL OVH]+[Total CCH FUSL OVH])/2 AS [Avg Total FUSL OVH] FROM [a Step 4in Join FU Comp Cost]; Step 4kn Sum to SL SELECT [a Step 4jn Allocate FU Overhead].FY, [a Step 4jn Allocate FU Overhead].[RA campus], [a Step 4jn Allocate FU Overhead].subject, [a Step 4jn Allocate FU Overhead].[RA level], Sum([a Step 4jn Allocate FU Overhead].[Avg Unr FUSL OVH]) AS [Unr Ovh], Sum([a Step 4jn Allocate FU Overhead].[Avg Total FUSL OVH]) AS [Total Ovh] INTO [a subject level FU cost] FROM [a Step 4jn Allocate FU Overhead] GROUP BY [a Step 4jn Allocate FU Overhead].FY, [a Step 4jn Allocate FU Overhead].[RA campus], [a Step 4jn Allocate FU Overhead].subject, [a Step 4jn Allocate FU Overhead].[RA level];
Step 5: Allocate medical costs. Step 5a: Join medical enrollments with student enrollments, convert head count for cohort status Code R (repeater), T (trailer) and O (original cohort) to 15 SCH per term, pick up rank and major code from the SN. Join the Medical Funding Unit table to pick up the fund unit name. Finally, summarize SCH to funding unit, subject, and level. Build table "A Med SCH by FU Subj Lvl."	Step 5a SELECT dbo_med_enroll.inst_code, dbo_med_enroll.year_num AS FY, dbo_stud_enroll.yr_num AS Year, dbo_stud_enroll.term_code, dbo_med_enroll.OBRID, dbo_stud_enroll.rank_code AS Rank, dbo_stud_enroll.major_study_field_code AS Subject, "P" AS [Level], IIf([term_code]="SM" And [SM_enroll_code] In ("R","T","O"),15,IIf([term_code]="AU" And [AU_enroll_code] In ("R","T","O"),15,IIf([term_code]="WI" And [WI_enroll_code] In ("R","T","O"),15,IIf([term_code]="SP" And [SP_enroll_code] In ("R","T","O"),15,0)))) AS SCH FROM dbo_med_enroll INNER JOIN dbo_stud_enroll ON (dbo_med_enroll.OBRID = dbo_stud_enroll.OBRID) AND (dbo_med_enroll.inst_code = dbo_stud_enroll.inst_code) WHERE (((dbo_med_enroll.year_num)=[yr_num]+1) AND ((dbo_stud_enroll.term_code) In ("SM","AU"))) OR (((dbo_med_enroll.year_num)=[yr_num]) AND ((dbo_stud_enroll.term_code) In ("WI","SP"))); Step 5a1 SELECT [a Step 5a join med enrollwith SN].*, dbo_medical_fund_unit.fund_unit_id FROM [a Step 5a join med enrollwith SN] INNER JOIN dbo_medical_fund_unit ON ([a Step 5a join med enrollwith SN].term_code = dbo_medical_fund_unit.term_code) AND ([a Step 5a join med enrollwith SN].Year = dbo_medical_fund_unit.yr_num) AND ([a Step 5a join med enrollwith SN].inst_code = dbo_medical_fund_unit.inst_code) AND ([a Step 5a join med enrollwith SN].Level = dbo_medical_fund_unit.crse_level_code) AND ([a Step 5a join med enrollwith SN].Subject = dbo_medical_fund_unit.subject_code); Step 5a2 SELECT [a Step 5a1 join med FU name].FY, [a Step 5a1 join med FU name].inst_code, [a Step 5a1 join med FU name].fund_unit_id, [a Step 5a1 join med FU name].Subject, [a Step 5a1 join med FU name].Level, Sum([a Step 5a1 join med FU name].SCH) AS [FUSL SCH], Sum(IIf([rank] In ("JR","SR","NU"),[SCH],0)) AS [UD SCH], Sum(IIf([rank] In ("FR","SO"),[SCH],0)) AS [LD SCH], Sum(IIf([rank] In ("MS","NM"),[SCH],0)) AS [M SCH], Sum(IIf([rank] In ("DS","ND","NG"),[SCH],0)) AS [D SCH], Sum(IIf([rank] In ("PR"),[SCH],0)) AS [P SCH] INTO [a med SCH by FU subj lvl] FROM [a Step 5a1 join med FU name] GROUP BY [a Step 5a1 join med FU name].FY, [a Step 5a1 join med FU name].inst_code, [a Step 5a1 join med FU name].fund_unit_id, [a Step 5a1 join med FU name].Subject, [a Step 5a1 join med FU name].Level;
Step 5b: Join the Medical Funding Unit table with the course section table to select the course sections offered by the Medical Funding units.	SELECT dbo_medical_fund_unit.yr_num, dbo_medical_fund_unit.term_code, dbo_medical_fund_unit.inst_code, dbo_crse_section.campus_code, dbo_medical_fund_unit.fund_unit_id, dbo_crse_section.crse_id, dbo_crse_section.crse_sect_id FROM dbo_medical_fund_unit INNER JOIN dbo_crse_section ON (dbo_medical_fund_unit.term_code = dbo_crse_section.term_code) AND (dbo_medical_fund_unit.inst_code = dbo_crse_section.inst_code) AND (dbo_medical_fund_unit.fund_unit_id = dbo_crse_section.fund_unit_id) WHERE (((dbo_medical_fund_unit.yr_num)=[fiscal year]-1) AND ((dbo_medical_fund_unit.term_code) In ("SM","AU"))) OR (((dbo_medical_fund_unit.yr_num)=[fiscal year]) AND ((dbo_medical_fund_unit.term_code) In ("WI","SP")));
Step 5c and d: Join CN to pick up SCH and CI to pick up subject and level and SN to pick up rank.	Step 5c SELECT [a Step 5b select med course sections]., dbo_crse_enroll.OBRID, dbo_crse_enroll.crse_enroll_cr_hrs AS SCH FROM [a Step 5b select med course sections] INNER JOIN dbo_crse_enroll ON ([a Step 5b select med course sections].crse_sect_id = dbo_crse_enroll.crse_sect_id) AND ([a Step 5b select med course sections].crse_id = dbo_crse_enroll.crse_id) AND ([a Step 5b select med course sections].campus_code = dbo_crse_enroll.campus_code) AND ([a Step 5b select med course sections].term_code = dbo_crse_enroll.term_code) AND ([a Step 5b select med course sections].yr_num = dbo_crse_enroll.yr_num); Step 5d SELECT [a Step 5c join CN data]., dbo_crse_inventory.subject_code, dbo_crse_inventory.crse_level_code, IIf([a Step 5c join CN data]![term_code] In ("SM","AU"),[a Step 5c join CN data]![yr_num]+1,[a Step 5c join CN data]![yr_num]) AS FY FROM [a Step 5c join CN data] INNER JOIN dbo_crse_inventory ON ([a Step 5c join CN data].crse_id = dbo_crse_inventory.crse_id) AND ([a Step 5c join CN data].inst_code = dbo_crse_inventory.inst_code) AND ([a Step 5c join CN data].term_code = dbo_crse_inventory.term_code) AND ([a Step 5c join CN data].yr_num = dbo_crse_inventory.yr_num); Step 5d1 SELECT [a Step 5d join CI data].*, dbo_stud_enroll.rank_code FROM [a Step 5d join CI data] INNER JOIN dbo_stud_enroll ON ([a Step 5d join CI data].term_code = dbo_stud_enroll.term_code) AND ([a Step 5d join CI data].yr_num = dbo_stud_enroll.yr_num) AND ([a Step 5d join CI data].campus_code = dbo_stud_enroll.campus_code) AND ([a Step 5d join CI data].OBRID = dbo_stud_enroll.OBRID);
Step 5e: Finally summarize to funding unit, subject and level, and append to the same table that has the medical headcount SCH in it.	INSERT INTO [a med SCH by FU subj lvl] ( FY, inst_code, fund_unit_id, Subject, [Level], [FUSL SCH], [UD SCH], [LD SCH], [M SCH], [D SCH], [P SCH] ) SELECT [a Step 5d1 join SN data].FY, [a Step 5d1 join SN data].inst_code, [a Step 5d1 join SN data].fund_unit_id, [a Step 5d1 join SN data].subject_code, [a Step 5d1 join SN data].crse_level_code, Sum([a Step 5d1 join SN data].SCH) AS [FUSL SCH], Sum(IIf([rank_code] In ("JR","SR","NU"),[SCH],0)) AS Expr1, Sum(IIf([rank_code] In ("FR","SO"),[SCH],0)) AS Expr2, Sum(IIf([rank_code] In ("MS","NM"),[SCH],0)) AS Expr3, Sum(IIf([rank_code] In ("DS","ND","NG"),[SCH],0)) AS Expr4, Sum(IIf([rank_code] In ("PR"),[SCH],0)) AS Expr5 FROM [a Step 5d1 join SN data] GROUP BY [a Step 5d1 join SN data].FY, [a Step 5d1 join SN data].inst_code, [a Step 5d1 join SN data].fund_unit_id, [a Step 5d1 join SN data].subject_code, [a Step 5d1 join SN data].crse_level_code;
Step 5f: Summarize the combined table to funding unit (for the denominator of the cost allocation).	SELECT [a med SCH by FU subj lvl].FY, [a med SCH by FU subj lvl].inst_code, [a med SCH by FU subj lvl].fund_unit_id, Sum([a med SCH by FU subj lvl].[FUSL SCH]) AS [FU SCH] FROM [a med SCH by FU subj lvl] GROUP BY [a med SCH by FU subj lvl].FY, [a med SCH by FU subj lvl].inst_code, [a med SCH by FU subj lvl].fund_unit_id;
Step 5g: Allocate Medical Funding Unit costs by multiplying Other plus Academic Administration costs from the FX with Comp and College table for Medical Funding units by the ratio of SCH for the funding unit subject and level to SCH for the funding unit.	SELECT [a med SCH by FU subj lvl].FY, [a med SCH by FU subj lvl].inst_code, [a med SCH by FU subj lvl].fund_unit_id, [a med SCH by FU subj lvl].Subject, [a med SCH by FU subj lvl].Level, ([Unres Other]+[Unres AA])([FUSL SCH]/[FU SCH]) AS [Unr FU], ([Total Other]+[Total AA])([FUSL SCH]/[FU SCH]) AS [Tot FU] FROM ([a med SCH by FU subj lvl] INNER JOIN [a FX with comp and college] ON ([a med SCH by FU subj lvl].fund_unit_id = [a FX with comp and college].fund_unit_id) AND ([a med SCH by FU subj lvl].inst_code = [a FX with comp and college].campus_code) AND ([a med SCH by FU subj lvl].FY = [a FX with comp and college].yr_num)) INNER JOIN [a Step 5f sum SCH to FU] ON ([a med SCH by FU subj lvl].fund_unit_id = [a Step 5f sum SCH to FU].fund_unit_id) AND ([a med SCH by FU subj lvl].inst_code = [a Step 5f sum SCH to FU].inst_code) AND ([a med SCH by FU subj lvl].FY = [a Step 5f sum SCH to FU].FY);
Step 5h: Summarize to subject and level independent of funding unit. Build table "A Subject Level Med Cost."	SELECT [a Step 5g allocate med cost to FUSL].FY, [a Step 5g allocate med cost to FUSL].inst_code, [a Step 5g allocate med cost to FUSL].Subject, [a Step 5g allocate med cost to FUSL].Level, Sum([a Step 5g allocate med cost to FUSL].[Unr FU]) AS [Unres Med], Sum([a Step 5g allocate med cost to FUSL].[Tot FU]) AS [Total Med] INTO [a subject level med cost] FROM [a Step 5g allocate med cost to FUSL] GROUP BY [a Step 5g allocate med cost to FUSL].FY, [a Step 5g allocate med cost to FUSL].inst_code, [a Step 5g allocate med cost to FUSL].Subject, [a Step 5g allocate med cost to FUSL].Level;
Step 6: Calculate the unallocated part of the FX and CP and summarize it to campus for allocation. Step 6a and b: Sum FX and CP to campus.	SELECT dbo_fund_unit_expens.yr_num, dbo_fund_unit_expens.campus_code, Sum([fac_salary_expens]+[benefits_expens]+[other_fund_expens]) AS [Unr FX], Sum([fac_salary_expens]+[benefits_expens]+[other_fund_expens]+[restrict_fac_salary_expens]+[restrict_benefits_expens]+[restrict_other_fund_expens]) AS [Tot FX] FROM dbo_fund_unit_expens GROUP BY dbo_fund_unit_expens.yr_num, dbo_fund_unit_expens.campus_code HAVING (((dbo_fund_unit_expens.yr_num)=[fiscal year])); SELECT dbo_college_expens.yr_num, dbo_college_expens.campus_code, Sum(dbo_college_expens.college_expens) AS [Unr CP], Sum([restrict_college_expens]+[college_expens]) AS [Tot CP] FROM dbo_college_expens GROUP BY dbo_college_expens.yr_num, dbo_college_expens.campus_code HAVING (((dbo_college_expens.yr_num)=[fiscal year]));
Step 6c, d and e: Sum comp, funding unit overhead, and med to campus.	SELECT [a subject level comp cost].FY, [a subject level comp cost].[RA campus], Sum([a subject level comp cost].[Unr comp]) AS [Unr Cmp], Sum([a subject level comp cost].[Tot comp]) AS [Tot Cmp] FROM [a subject level comp cost] GROUP BY [a subject level comp cost].FY, [a subject level comp cost].[RA campus]; SELECT [a subject level FU cost].FY, [a subject level FU cost].[RA campus], Sum([a subject level FU cost].[Unr Ovh]) AS [Unr FU], Sum([a subject level FU cost].[Total Ovh]) AS [Tot FU] FROM [a subject level FU cost] GROUP BY [a subject level FU cost].FY, [a subject level FU cost].[RA campus]; SELECT [a subject level med cost].FY, [a subject level med cost].inst_code, Sum([a subject level med cost].[Unres Med]) AS [Unr Med], Sum([a subject level med cost].[Total Med]) AS [Tot Med] FROM [a subject level med cost] GROUP BY [a subject level med cost].FY, [a subject level med cost].inst_code;
Step 6f: Join the input FX and CP cost with the allocated cost (comp, funding unit overhead, and med) to calculate the remainder which is unallocated. Build table "A Campus Unallocated." Note a negative unallocated amount can result if there are instructors paid by Funding Units which have no FX. This case should not happen.	SELECT [a Step 6a sum FX to campus].yr_num, [a Step 6a sum FX to campus].campus_code, [a Step 6a sum FX to campus].[Unr FX], [a Step 6a sum FX to campus].[Tot FX], [a Step 6b sum CP to campus].[Unr CP], [a Step 6b sum CP to campus].[Tot CP], [a Step 6c sum comp to campus].[Unr Cmp], [a Step 6c sum comp to campus].[Tot Cmp], [a Step 6d sum FU to campus].[Unr FU], [a Step 6d sum FU to campus].[Tot FU], [a Step 6e sum med to campus].[Unr Med], [a Step 6e sum med to campus].[Tot Med], [Unr FX]+nz([Unr CP],0)-nz([Unr Cmp],0)-nz([Unr FU],0)-nz([Unr Med],0) AS [Unr Unallocated], [Tot FX]+nz([Tot CP],0)-nz([Tot Cmp],0)-nz([Tot FU],0)-nz([Tot Med],0) AS [Tot Unallocated] INTO [a campus unallocated] FROM ((([a Step 6a sum FX to campus] LEFT JOIN [a Step 6b sum CP to campus] ON ([a Step 6a sum FX to campus].campus_code = [a Step 6b sum CP to campus].campus_code) AND ([a Step 6a sum FX to campus].yr_num = [a Step 6b sum CP to campus].yr_num)) LEFT JOIN [a Step 6c sum comp to campus] ON ([a Step 6a sum FX to campus].campus_code = [a Step 6c sum comp to campus].[RA campus]) AND ([a Step 6a sum FX to campus].yr_num = [a Step 6c sum comp to campus].FY)) LEFT JOIN [a Step 6d sum FU to campus] ON ([a Step 6a sum FX to campus].campus_code = [a Step 6d sum FU to campus].campus_code) AND ([a Step 6a sum FX to campus].yr_num = [a Step 6d sum FU to campus].yr_num)) LEFT JOIN [a Step 6e sum med to campus] ON ([a Step 6a sum FX to campus].campus_code = [a Step 6e sum med to campus].inst_code) AND ([a Step 6a sum FX to campus].yr_num = [a Step 6e sum med to campus].FY);
Step 7: Allocate student services, academic support, and institutional support from the Campus Expenditure (CX) file and Funding Unit residual expenses from the Campus Unallocated table to all combinations of subject and level from course enrollments, with SCH, offered by the campus by multiplying each cost by the ratio of the number of SCH in the campus, subject, and level divided by the number of SCH on the campus. Identify the resulting table as the Subject Level SCH Cost table. Note that some Subject Level combinations for a campus may have cost and no SCH. Step 8: Allocate library from the Campus Expenditure (CX) file to all combinations of subject and level from course enrollments on the campus by multiplying the cost by the ratio of the number of SCH in the subject and level times three, if the level is graduate, divided by the number of undergraduate SCH plus graduate SCH times three on the campus. Identify the resulting table as the Subject Level Library Cost table.
Step 7a: Sum the joined CN and SN data to course section. Calculate SCH by student level.	SELECT [a course enroll].FY, [a course enroll].yr_num AS year, [a course enroll].term_code AS term, [a course enroll].inst_code AS inst, [a course enroll].campus_code AS campus, [a course enroll].crse_id AS course, [a course enroll].crse_sect_id AS section, Sum([a course enroll].crse_enroll_cr_hrs) AS SCH, Avg([a course enroll].crse_enroll_cr_hrs) AS CCH, Sum(IIf([rank_code] In ("JR","SR","NU"),[crse_enroll_cr_hrs],0)) AS [B SCH], Sum(IIf([rank_code] In ("MS","NM"),[crse_enroll_cr_hrs],0)) AS [M SCH], Sum(IIf([rank_code] In ("DS","ND","NG"),[crse_enroll_cr_hrs],0)) AS [D SCH] FROM [a course enroll] GROUP BY [a course enroll].FY, [a course enroll].yr_num, [a course enroll].term_code, [a course enroll].inst_code, [a course enroll].campus_code, [a course enroll].crse_id, [a course enroll].crse_sect_id;
Step 7b: Join CI to pick up subject and level.	SELECT [a Step 7a sum CN and SN data to section].*, dbo_crse_inventory.subject_code, dbo_crse_inventory.crse_level_code AS [CI Level] FROM [a Step 7a sum CN and SN data to section] INNER JOIN dbo_crse_inventory ON ([a Step 7a sum CN and SN data to section].term = dbo_crse_inventory.term_code) AND ([a Step 7a sum CN and SN data to section].year = dbo_crse_inventory.yr_num) AND ([a Step 7a sum CN and SN data to section].inst = dbo_crse_inventory.inst_code) AND ([a Step 7a sum CN and SN data to section].course = dbo_crse_inventory.crse_id);
Step 7c: Calculate level based on student level and campus. Note the special case for CYCC and SSCC.	SELECT [a Step 7b join CI subject and level].*, IIf([CI level] Not In ("M","D"),[CI Level],IIf([B SCH]<[M SCH] And [D SCH]<[M SCH],"M",IIf([M SCH]<[B SCH] And [D SCH]<[B SCH],"B","D"))) AS [RA Level], IIf([inst] In ("CYCC","SSCC"),[inst],[campus]) AS [RA Campus] FROM [a Step 7b join CI subject and level];
Step 7d: Sum SCH to subject and level. Build table "A Subj Lvl SCH."	SELECT [a Step 7c calculate Ra campus and level].FY, [a Step 7c calculate Ra campus and level].[RA Campus], [a Step 7c calculate Ra campus and level].subject_code, [a Step 7c calculate Ra campus and level].[RA Level], Sum([a Step 7c calculate Ra campus and level].SCH) AS [SL SCH] INTO [a subj lvl SCH] FROM [a Step 7c calculate Ra campus and level] GROUP BY [a Step 7c calculate Ra campus and level].FY, [a Step 7c calculate Ra campus and level].[RA Campus], [a Step 7c calculate Ra campus and level].subject_code, [a Step 7c calculate Ra campus and level].[RA Level];
Step 7e: Append the medical SCH.	INSERT INTO [a subj lvl SCH] ( FY, [RA Campus], subject_code, [RA Level], [SL SCH] ) SELECT [a med SCH by FU subj lvl].FY, [a med SCH by FU subj lvl].inst_code, [a med SCH by FU subj lvl].Subject, [a med SCH by FU subj lvl].Level, [a med SCH by FU subj lvl].[FUSL SCH] FROM [a med SCH by FU subj lvl] WHERE ((([a med SCH by FU subj lvl].Level)="P"));
Step 7f and g: Sum the combined SCH to campus, subject and level, and to campus.	SELECT [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code, [a subj lvl SCH].[RA Level], Sum([a subj lvl SCH].[SL SCH]) AS [CSL SCH] FROM [a subj lvl SCH] GROUP BY [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code, [a subj lvl SCH].[RA Level]; SELECT [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus], Sum([a subj lvl SCH].[SL SCH]) AS [C SCH], Sum(IIf([RA Level]="M" Or [RA Level]="D" Or [RA Level]="P",[SL SCH],0)) AS [Grad SCH], Sum(IIf([RA Level]="G" Or [RA Level]="T" Or [RA Level]="B",[SL SCH],0)) AS [UG SCH] FROM [a subj lvl SCH] GROUP BY [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus];
Step 7h and 8: Allocate campus costs by SCH. These include all of the CX cost except POM and the unallocated costs from the FX and CP. Build table "A Subject Level SCH Cost."	SELECT [a Step 7f sum SCH to CSL]., [a Step 7g sum SCH to campus].[C SCH], [stud_serv_expens]+[inst_support_expens]+[other_acad_support_expens]+nz([Unr Unallocated],0) AS [Unr Campus], [stud_serv_expens]+[inst_support_expens]+[other_acad_support_expens]+[Tot Unallocated]+[restrict_stud_serv_expens]+[restrict_inst_support_expens]+[restrict_other_acad_expens] AS [Tot Campus], [Unr Campus]([CSL SCH]/[C SCH]) AS [Unr Campus SCH Cost], [Tot Campus]([CSL SCH]/[C SCH]) AS [Tot Campus SCH Cost], [stud_serv_expens]([CSL SCH]/[C SCH]) AS [Unr SS SL Cost], [inst_support_expens]([CSL SCH]/[C SCH]) AS [Unr IS SL Cost], [other_acad_support_expens]([CSL SCH]/[C SCH]) AS [Unr OA SL Cost], nz([Unr Unallocated],0)([CSL SCH]/[C SCH]) AS [Unr UN SL Cost], ([stud_serv_expens]+[restrict_stud_serv_expens])([CSL SCH]/[C SCH]) AS [Tot SS SL Cost], ([inst_support_expens]+[restrict_inst_support_expens])([CSL SCH]/[C SCH]) AS [Tot IS SL Cost], ([other_acad_support_expens]+[restrict_other_acad_expens])([CSL SCH]/[C SCH]) AS [Tot OA SL Cost], nz([Tot Unallocated],0)([CSL SCH]/[C SCH]) AS [Tot UN SL Cost], [a Step 7g sum SCH to campus].[Grad SCH], [a Step 7g sum SCH to campus].[UG SCH], dbo_campus_expens.library_expens AS [Unr Campus Library], [restrict_library_expens]+[library_expens] AS [Tot Campus Library], [Unr Campus Library](IIf([RA Level]="M" Or [RA Level]="D" Or [RA Level]="P",[CSL SCH]3,[CSL SCH])/([Grad SCH]3+[UG SCH])) AS [Unr SL Lib Cost], [Tot Campus Library](IIf([RA Level]="M" Or [RA Level]="D" Or [RA Level]="P",[CSL SCH]3,[CSL SCH])/([Grad SCH]*3+[UG SCH])) AS [Tot SL Lib Cost] INTO [a subject level SCH cost] FROM (([a Step 7f sum SCH to CSL] LEFT JOIN [a Step 7g sum SCH to campus] ON ([a Step 7f sum SCH to CSL].FY = [a Step 7g sum SCH to campus].FY) AND ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step 7g sum SCH to campus].[RA Campus])) LEFT JOIN [a campus unallocated] ON ([a Step 7f sum SCH to CSL].FY = [a campus unallocated].yr_num) AND ([a Step 7f sum SCH to CSL].[RA Campus] = [a campus unallocated].campus_code)) LEFT JOIN dbo_campus_expens ON ([a Step 7f sum SCH to CSL].[RA Campus] = dbo_campus_expens.campus_code) AND ([a Step 7f sum SCH to CSL].FY = dbo_campus_expens.yr_num);
Step 9: Allocate POM at the campus level. Step 9a: Summarize NASF from the Area Inventory (AI) file to the campus level, grouping by Instructional (Functions 10 and 21), Student Services (Function 52), Libraries (Function 40), and Institutional Support (Functions 51 and 54). Also, summarize NASF from the Space Inventory table to the campus level, grouping Instructional Space (Functions 10 and 21) by Area Type, Classroom (Area Type 110), and 130), Lab (Area Type 210 and 215) and Other (all other Area Type values).	SELECT DISTINCTROW [yr_num]+1 AS [Fiscal Year], dbo_area_inventory.campus_code, Sum(IIf([function_code]="10" Or [function_code]="21",[area_sqft_num],0)) AS [Instruct NASF], Sum(IIf([function_code]="51" Or [function_code]="54",[area_sqft_num],0)) AS [IS NASF], Sum(IIf([function_code]="40",[area_sqft_num],0)) AS [Lib NASF], Sum(IIf([function_code]="52",[area_sqft_num],0)) AS [SS NASF], Sum(IIf(([function_code]="10" Or [function_code]="21") And [area_type_code]="110",[area_sqft_num],0)) AS [Classroom NASF], Sum(IIf(([function_code]="10" Or [function_code]="21") And ([area_type_code]="210" Or [area_type_code]="215"),[area_sqft_num],0)) AS [Lab NASF], Sum(IIf(([function_code]="10" Or [function_code]="21") And Not ([area_type_code]="110" Or [area_type_code]="210" Or [area_type_code]="215"),[area_sqft_num],0)) AS [Other NASF] FROM dbo_area_inventory WHERE (((dbo_area_inventory.yr_num)=[fiscal year]-1)) GROUP BY [yr_num]+1, dbo_area_inventory.campus_code;
Step 9b: Summarize NASF from the Area Inventory (AI) file to the state level, grouping by Instructional (Functions 10 and 21), Student Services (Function 52), Libraries (Function 40), and Institutional Support (Functions 51 and 54). Also, summarize NASF from the Space Inventory table to the Campus Level field, grouping Instructional Space (Functions 10 and 21) by Area Type, Classroom (Area Type 110), Lab (Area Type 210 and 215) and Other (all other Area Type values). These state totals will be used as allocators for campuses that have no NASF.	SELECT DISTINCTROW [yr_num]+1 AS [Fiscal Year], Sum(IIf([function_code]="10" Or [function_code]="21",[area_sqft_num],0)) AS [State Instruct NASF], Sum(IIf([function_code]="51" Or [function_code]="54",[area_sqft_num],0)) AS [State IS NASF], Sum(IIf([function_code]="40",[area_sqft_num],0)) AS [State Lib NASF], Sum(IIf([function_code]="52",[area_sqft_num],0)) AS [State SS NASF], Sum(IIf(([function_code]="10" Or [function_code]="21") And [area_type_code]="110",[area_sqft_num],0)) AS [State Classroom NASF], Sum(IIf(([function_code]="10" Or [function_code]="21") And ([area_type_code]="210" Or [area_type_code]="215"),[area_sqft_num],0)) AS [State Lab NASF], Sum(IIf(([function_code]="10" Or [function_code]="21") And Not ([area_type_code]="110" Or [area_type_code]="210" Or [area_type_code]="215"),[area_sqft_num],0)) AS [State Other NASF] FROM dbo_area_inventory WHERE (((dbo_area_inventory.yr_num)=[fiscal year]-1)) GROUP BY [yr_num]+1;
Step 9c: Allocate POM to instruction and the non-instruction categories at the campus level by multiplying POM from the Campus Expenditure (CX) file by the ratio of campus NASF for instructional, institutional support, library, and student services, divided by NASF for the campus. Also, allocate instructional POM to classroom, lab, and other categories at the campus level by multiplying instructional POM from the Campus field by the ratio of campus NASF for instructional classroom, lab, and other, divided by NASF for all three. For campuses without NASF, if there are any, use the state level totals.	SELECT DISTINCTROW dbo_campus_expens.campus_code, dbo_campus_expens.yr_num, dbo_campus_expens.pom_expens AS [Unr POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[pom_expens]([State Instruct NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[pom_expens]([Instruct NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Unr Instruction POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[pom_expens]([State IS NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[pom_expens]([IS NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Unr IS POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[pom_expens]([State Lib NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[pom_expens]([Lib NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Unr Lib POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[pom_expens]([State SS NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[pom_expens]([SS NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Unr SS POM], [pom_expens]+[restrict_POM_expens] AS [Total POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[Total POM]([State Instruct NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[Total POM]([Instruct NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Total Instruction POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[Total POM]([State IS NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[Total POM]([IS NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Total IS POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[Total POM]([State Lib NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[Total POM]([Lib NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Total Lib POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]),[Total POM]([State SS NASF]/([State Instruct NASF]+[State IS NASF]+[State Lib NASF]+[State SS NASF])),[Total POM]([SS NASF]/([Instruct NASF]+[IS NASF]+[Lib NASF]+[SS NASF]))) AS [Total SS POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]) Or [Instruct NASF]=0,[Unr Instruction POM]([State Classroom NASF]/[State Instruct NASF]),[Unr Instruction POM]([Classroom NASF]/[Instruct NASF])) AS [Unr Classroom POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]) Or [Instruct NASF]=0,[Unr Instruction POM]([State Lab NASF]/[State Instruct NASF]),[Unr Instruction POM]([Lab NASF]/[Instruct NASF])) AS [Unr Lab POM], IIf(isnull([a Step 9a sum NASF to campus by function]![Fiscal Year]) Or [Instruct NASF]=0,[Unr Instruction POM]([State Other NASF]/[State Instruct NASF]),[Unr Instruction POM]([Other NASF]/[Instruct NASF])) AS [Unr Other POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]) Or [Instruct NASF]=0,[Total Instruction POM]([State Classroom NASF]/[State Instruct NASF]),[Total Instruction POM]([Classroom NASF]/[Instruct NASF])) AS [Total Classroom POM], IIf(isnull([a Step 9a sum NASF to campus by function]![Fiscal Year]) Or [Instruct NASF]=0,[Total Instruction POM]([State Lab NASF]/[State Instruct NASF]),[Total Instruction POM]([Lab NASF]/[Instruct NASF])) AS [Total Lab POM], IIf(IsNull([a Step 9a sum NASF to campus by function]![Fiscal Year]) Or [Instruct NASF]=0,[Total Instruction POM]([State Other NASF]/[State Instruct NASF]),[Total Instruction POM]([Other NASF]/[Instruct NASF])) AS [Total Other POM] FROM (dbo_campus_expens LEFT JOIN [a Step 9a sum NASF to campus by function] ON (dbo_campus_expens.campus_code = [a Step 9a sum NASF to campus by function].campus_code) AND (dbo_campus_expens.yr_num = [a Step 9a sum NASF to campus by function].[Fiscal Year])) LEFT JOIN [a Step 9b sum NASF to state by function] ON dbo_campus_expens.yr_num = [a Step 9b sum NASF to state by function].[Fiscal Year];
Step 9d: Divide campus library POM into campus graduate library and campus undergraduate library by using the same weighted SCH as were used in the library cost allocation above; i.e., use the Campus Weighted SCH table. Identify the resulting table as the Campus POM Allocation table.	SELECT [a Step 9c allocate POM to Instructional and non]., [Unr Lib POM]([UG SCH]/([UG SCH]+3[Grad SCH])) AS [Unr Lib UG POM], [Unr Lib POM](3[Grad SCH]/([UG SCH]+3[Grad SCH])) AS [Unr Lib Grad POM], [Total Lib POM]([UG SCH]/([UG SCH]+3[Grad SCH])) AS [Total Lib UG POM], [Total Lib POM](3[Grad SCH]/([UG SCH]+3*[Grad SCH])) AS [Total Lib Grad POM] FROM [a Step 9c allocate POM to Instructional and non] INNER JOIN [a Step 7g sum SCH to campus] ON ([a Step 9c allocate POM to Instructional and non].campus_code = [a Step 7g sum SCH to campus].[RA Campus]) AND ([a Step 9c allocate POM to Instructional and non].yr_num = [a Step 7g sum SCH to campus].FY);
Step 10: Allocate campus POM for student services, institutional support, undergraduate library, and classrooms to subject and level based on SCH. Multiply the campus level cost for each individual cost category by the ratio of SCH from the Campus Subject Level SCH table divided by the SCH from the Campus SCH table. Identify the resulting table as the Subject Level POM Cost by SCH table.	SELECT [a Step 7f sum SCH to CSL].FY, [a Step 7f sum SCH to CSL].[RA Campus], [a Step 7f sum SCH to CSL].subject_code, [a Step 7f sum SCH to CSL].[RA Level], [a Step 7f sum SCH to CSL].[CSL SCH], [a Step 7g sum SCH to campus].[C SCH] AS [Campus SCH], [Unr IS POM]+[Unr SS POM]+[Unr Classroom POM]+[Unr Lib UG POM] AS [Unr Campus POM], [Total IS POM]+[Total SS POM]+[Total Classroom POM]+[Total Lib UG POM] AS [Total Campus POM], [Unr Campus POM]([CSL SCH]/[Campus SCH]) AS [Unr CSL POM], [Total Campus POM]([CSL SCH]/[Campus SCH]) AS [Total CSL POM] INTO [a subject level POM SCH cost] FROM (([a Step 9c allocate POM to Instructional and non] INNER JOIN [a Step 7f sum SCH to CSL] ON ([a Step 9c allocate POM to Instructional and non].campus_code = [a Step 7f sum SCH to CSL].[RA Campus]) AND ([a Step 9c allocate POM to Instructional and non].yr_num = [a Step 7f sum SCH to CSL].FY)) INNER JOIN [a Step 7g sum SCH to campus] ON ([a Step 9c allocate POM to Instructional and non].campus_code = [a Step 7g sum SCH to campus].[RA Campus]) AND ([a Step 9c allocate POM to Instructional and non].yr_num = [a Step 7g sum SCH to campus].FY)) INNER JOIN [a Step 9d lib POM campus level] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step 9d lib POM campus level].campus_code) AND ([a Step 7f sum SCH to CSL].FY = [a Step 9d lib POM campus level].yr_num);
Step 11: Allocate campus graduate library POM cost to subject and level based on instructors' salary as allocated by CCH. Step 11a: Summarize the CCH cost in the Section Instructor Comp Distribution table to campus subject and level. Step 11b: Sum the same as 11a, except to campus. Step 11c: Multiply the campus graduate library POM cost by the ratio of instructors' CCH cost divided by the CCH instructors' cost at the campus level from the preceding steps. Store the results in the table "A Subject Level Grad Lib POM."	SELECT [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].subject, [a section instructor comp distribution].[RA level], Sum([a section instructor comp distribution].[Unr CCH comp]) AS [Unr SL CCH Salary], [a section instructor comp distribution].[Tot CCH comp] AS [Total SL CCH Salary] FROM [a section instructor comp distribution] GROUP BY [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].subject, [a section instructor comp distribution].[RA level], [a section instructor comp distribution].[Tot CCH comp] HAVING ((([a section instructor comp distribution].[RA level]) In ("M","D","P"))); SELECT [a Step x11a sum comp to CSL].FY, [a Step x11a sum comp to CSL].[RA campus], Sum([a Step x11a sum comp to CSL].[Unr SL CCH Salary]) AS [Unr Campus CCH Salary], Sum([a Step x11a sum comp to CSL].[Total SL CCH Salary]) AS [Total Campus CCH Salary] FROM [a Step x11a sum comp to CSL] GROUP BY [a Step x11a sum comp to CSL].FY, [a Step x11a sum comp to CSL].[RA campus]; SELECT [a Step x11a sum comp to CSL].FY, [a Step x11a sum comp to CSL].[RA campus], [a Step x11a sum comp to CSL].subject, [a Step x11a sum comp to CSL].[RA level], [Unr Lib Grad POM]([Unr SL CCH Salary]/[Unr Campus CCH Salary]) AS [Unr CSL Grad Lib POM Cost], [Total Lib Grad POM]([Total SL CCH Salary]/[Total Campus CCH Salary]) AS [Tot CSL Grad Lib POM Cost] INTO [a subject level grad lib POM] FROM ([a Step x11a sum comp to CSL] INNER JOIN [a Step 9d lib POM campus level] ON ([a Step x11a sum comp to CSL].FY = [a Step 9d lib POM campus level].yr_num) AND ([a Step x11a sum comp to CSL].[RA campus] = [a Step 9d lib POM campus level].campus_code)) INNER JOIN [a Step x11b sum comp to Campus] ON ([a Step x11a sum comp to CSL].FY = [a Step x11b sum comp to Campus].FY) AND ([a Step x11a sum comp to CSL].[RA campus] = [a Step x11b sum comp to Campus].[RA campus]);
Step 12: Allocate campus lab POM to subject by NASF and level by SCH. Step 12a: Get list of subjects with SCH by summarizing the Subj Lvl SCH table by subject code.	SELECT [a subj lvl SCH].FY, [FY]-1 AS [AI Year], [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code, Sum([a subj lvl SCH].[SL SCH]) AS [Subject SCH] FROM [a subj lvl SCH] GROUP BY [a subj lvl SCH].FY, [FY]-1, [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code HAVING (((Sum([a subj lvl SCH].[SL SCH]))0));
Step 12b and c: Summarize NASF from the Area Inventory (AI) file for all subjects in rows with (Function Code 10 or 21) and (Area Type 210 or 215 LAB) and subject codes which have SCH from the preceding step. Also, capture SCH for these subject codes from the previous step. Summarize NASF and SCH from the preceding step by campus.	SELECT DISTINCTROW [yr_num]+1 AS FY, dbo_area_inventory.campus_code, dbo_area_inventory.subject_code, Sum(dbo_area_inventory.area_sqft_num) AS [Subject NASF], Sum([a Step x12a sum SCH by subject].[Subject SCH]) AS [Subject SCH] FROM dbo_area_inventory INNER JOIN [a Step x12a sum SCH by subject] ON (dbo_area_inventory.yr_num = [a Step x12a sum SCH by subject].[AI Year]) AND (dbo_area_inventory.campus_code = [a Step x12a sum SCH by subject].[RA Campus]) WHERE (((dbo_area_inventory.function_code)="10" Or (dbo_area_inventory.function_code)="21") AND ((dbo_area_inventory.area_type_code)="210" Or (dbo_area_inventory.area_type_code)="215")) GROUP BY [yr_num]+1, dbo_area_inventory.campus_code, dbo_area_inventory.subject_code; SELECT DISTINCTROW [a Step x12b sum NASF to subject].FY, [a Step x12b sum NASF to subject].campus_code, Sum([a Step x12b sum NASF to subject].[Subject NASF]) AS [Campus NASF], Sum([a Step x12b sum NASF to subject].[Subject SCH]) AS [Campus SCH] FROM [a Step x12b sum NASF to subject] GROUP BY [a Step x12b sum NASF to subject].FY, [a Step x12b sum NASF to subject].campus_code;
Step 12d: Allocate Lab Cost from the Campus POM Allocation table subject code by multiplying by the ratio of NASF at the subject level divided by NASF at the campus level.	SELECT [a Step x12b sum NASF to subject].FY, [a Step x12b sum NASF to subject].campus_code, [a Step x12b sum NASF to subject].subject_code, [a Step 9c allocate POM to Instructional and non].[Unr Lab POM], [a Step 9c allocate POM to Instructional and non].[Total Lab POM], [a Step x12c sum NASF to campus].[Campus NASF], [a Step x12c sum NASF to campus].[Campus SCH], [a Step x12b sum NASF to subject].[Subject NASF], [a Step x12b sum NASF to subject].[Subject SCH], [Unr Lab POM]/([Subject NASF]/[Campus NASF]) AS [Unr Lab Subject Cost], [Total Lab POM]/([Subject NASF]/[Campus NASF]) AS [Tot Lab Subject Cost] FROM ([a Step x12b sum NASF to subject] INNER JOIN [a Step 9c allocate POM to Instructional and non] ON ([a Step x12b sum NASF to subject].FY = [a Step 9c allocate POM to Instructional and non].yr_num) AND ([a Step x12b sum NASF to subject].campus_code = [a Step 9c allocate POM to Instructional and non].campus_code)) INNER JOIN [a Step x12c sum NASF to campus] ON ([a Step x12b sum NASF to subject].FY = [a Step x12c sum NASF to campus].FY) AND ([a Step x12b sum NASF to subject].campus_code = [a Step x12c sum NASF to campus].campus_code);
Step 12e: Select the SCH from the Subject Level SCH table for those subject codes that have NASF in the AI file.	SELECT [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code, [a subj lvl SCH].[RA Level], [a subj lvl SCH].[SL SCH] FROM [a subj lvl SCH] INNER JOIN [a Step x12b sum NASF to subject] ON ([a subj lvl SCH].subject_code = [a Step x12b sum NASF to subject].subject_code) AND ([a subj lvl SCH].[RA Campus] = [a Step x12b sum NASF to subject].campus_code) AND ([a subj lvl SCH].FY = [a Step x12b sum NASF to subject].FY);
Step 12f: Sum SCH to subject.	SELECT [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code, Sum([a subj lvl SCH].[SL SCH]) AS [S SCH] FROM [a subj lvl SCH] GROUP BY [a subj lvl SCH].FY, [a subj lvl SCH].[RA Campus], [a subj lvl SCH].subject_code;
Step 12g: Allocate Lab POM to subject and level by SCH. Build table "A Subject Level Lab POM Cost."	SELECT [a Step x12e select SCH for CSL].FY, [a Step x12e select SCH for CSL].[RA Campus], [a Step x12e select SCH for CSL].subject_code, [a Step x12e select SCH for CSL].[RA Level], [a Step x12d allo lab POM to subject].[Unr Lab Subject Cost], [a Step x12d allo lab POM to subject].[Tot Lab Subject Cost], [a Step x12f sum to Subj].[S SCH], [a Step x12e select SCH for CSL].[SL SCH], [Unr Lab Subject Cost]([SL SCH]/[S SCH]) AS [Unr Lab SL Cost], [Tot Lab Subject Cost]([SL SCH]/[S SCH]) AS [Tot Lab SL Cost] INTO [a subject level lab POM cost] FROM ([a Step x12e select SCH for CSL] INNER JOIN [a Step x12d allo lab POM to subject] ON ([a Step x12e select SCH for CSL].subject_code = [a Step x12d allo lab POM to subject].subject_code) AND ([a Step x12e select SCH for CSL].[RA Campus] = [a Step x12d allo lab POM to subject].campus_code) AND ([a Step x12e select SCH for CSL].FY = [a Step x12d allo lab POM to subject].FY)) INNER JOIN [a Step x12f sum to Subj] ON ([a Step x12e select SCH for CSL].subject_code = [a Step x12f sum to Subj].subject_code) AND ([a Step x12e select SCH for CSL].[RA Campus] = [a Step x12f sum to Subj].[RA Campus]) AND ([a Step x12e select SCH for CSL].FY = [a Step x12f sum to Subj].FY);
Steps 12h and i: For those campuses with no subjects that have both NASF and SCH, allocate to all subject, level combinations that have SCH based on SCH. First in Step 12h, identify those campuses that already have Lab POM allocated In Step 12i, allocate to the remaining campuses, campus wide based on SCH.	SELECT DISTINCT [a subject level lab POM cost].FY, [a subject level lab POM cost].[RA Campus] FROM [a subject level lab POM cost]; INSERT INTO [a subject level lab POM cost] ( FY, [RA Campus], subject_code, [RA Level], [Unr Lab Subject Cost], [Tot Lab Subject Cost], [S SCH], [SL SCH], [Unr Lab SL Cost], [Tot Lab SL Cost] ) SELECT [a Step 7f sum SCH to CSL].FY, [a Step 7f sum SCH to CSL].[RA Campus], [a Step 7f sum SCH to CSL].subject_code, [a Step 7f sum SCH to CSL].[RA Level], [a Step 9c allocate POM to Instructional and non].[Unr Lab POM], [a Step 9c allocate POM to Instructional and non].[Total Lab POM], [a Step 7g sum SCH to campus].[C SCH], [a Step 7f sum SCH to CSL].[CSL SCH], IIf(IsNull([a Step x12h select campuses with lab POM]![RA Campus]),[Unr Lab POM]([CSL SCH]/[C SCH]),0) AS [Unr Lab SL Cost], IIf(IsNull([a Step x12h select campuses with lab POM]![RA Campus]),[Total Lab POM]([CSL SCH]/[C SCH]),0) AS [Tot Lab SL Cost] FROM (([a Step 7f sum SCH to CSL] LEFT JOIN [a Step x12h select campuses with lab POM] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step x12h select campuses with lab POM].[RA Campus]) AND ([a Step 7f sum SCH to CSL].FY = [a Step x12h select campuses with lab POM].FY)) LEFT JOIN [a Step 7g sum SCH to campus] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step 7g sum SCH to campus].[RA Campus]) AND ([a Step 7f sum SCH to CSL].FY = [a Step 7g sum SCH to campus].FY)) LEFT JOIN [a Step 9c allocate POM to Instructional and non] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step 9c allocate POM to Instructional and non].campus_code) AND ([a Step 7f sum SCH to CSL].FY = [a Step 9c allocate POM to Instructional and non].yr_num) WHERE (((IIf(IsNull([a Step x12h select campuses with lab POM]![RA Campus]),[Total Lab POM]*([CSL SCH]/[C SCH]),0))0));
Step 13: Allocate Campus Other POM to subject by NASF and level by SCH. Step 13a: Get list of subjects with SCH by summarizing the Subj Lvl SCH table by subject code.
Step 13b and c: Summarize NASF from the Area Inventory (AI) file for all subjects in rows with (Function Code 10 or 21) and (Area Type other than 110, 210 or 215) and subject codes which have SCH from the preceding step. Also, capture SCH for these subject codes from the previous step. Summarize NASF and SCH from the preceding step by campus.	SELECT DISTINCTROW [yr_num]+1 AS FY, dbo_area_inventory.campus_code, dbo_area_inventory.subject_code, Sum(dbo_area_inventory.area_sqft_num) AS [Subject NASF] FROM dbo_area_inventory INNER JOIN [a Step x12a sum SCH by subject] ON (dbo_area_inventory.campus_code = [a Step x12a sum SCH by subject].[RA Campus]) AND (dbo_area_inventory.yr_num = [a Step x12a sum SCH by subject].[AI Year]) AND (dbo_area_inventory.subject_code = [a Step x12a sum SCH by subject].subject_code) WHERE (((dbo_area_inventory.function_code) In ("10","21")) AND ((dbo_area_inventory.area_type_code) Not In ("110","210","215"))) GROUP BY [yr_num]+1, dbo_area_inventory.campus_code, dbo_area_inventory.subject_code; SELECT [a Step x13b sum NASF to subject for other].FY, [a Step x13b sum NASF to subject for other].campus_code, Sum([a Step x13b sum NASF to subject for other].[Subject NASF]) AS [Campus NASF] FROM [a Step x13b sum NASF to subject for other] GROUP BY [a Step x13b sum NASF to subject for other].FY, [a Step x13b sum NASF to subject for other].campus_code;
Step 13d: Allocate Other Cost from the Campus POM Allocation table by multiplying by the ratio of NASF at the subject level divided by NASF at the campus level.	SELECT [a Step x13b sum NASF to subject for other].FY, [a Step x13b sum NASF to subject for other].campus_code, [a Step x13b sum NASF to subject for other].subject_code, [a Step 9c allocate POM to Instructional and non].[Unr Other POM], [a Step 9c allocate POM to Instructional and non].[Total Other POM], [a Step x13c sum NASF to campus].[Campus NASF], [a Step x13b sum NASF to subject for other].[Subject NASF], [Unr Other POM]([Subject NASF]/[Campus NASF]) AS [Unr Other Subject Cost], [Total Other POM]([Subject NASF]/[Campus NASF]) AS [Tot Other Subject Cost] FROM ([a Step 9c allocate POM to Instructional and non] INNER JOIN [a Step x13b sum NASF to subject for other] ON ([a Step 9c allocate POM to Instructional and non].yr_num = [a Step x13b sum NASF to subject for other].FY) AND ([a Step 9c allocate POM to Instructional and non].campus_code = [a Step x13b sum NASF to subject for other].campus_code)) INNER JOIN [a Step x13c sum NASF to campus] ON ([a Step 9c allocate POM to Instructional and non].yr_num = [a Step x13c sum NASF to campus].FY) AND ([a Step 9c allocate POM to Instructional and non].campus_code = [a Step x13c sum NASF to campus].campus_code);
Step 13e: Select the CCR Comp from the Section Instructor Comp Distribution table for those subject codes that have NASF in the Area Inventory (AI) file. Summarize by subject and level.	SELECT [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].subject, [a section instructor comp distribution].[RA level], Sum([a section instructor comp distribution].[Tot CCH comp]) AS [CSL CCR Comp] FROM [a section instructor comp distribution] INNER JOIN [a Step x12b sum NASF to subject] ON ([a section instructor comp distribution].subject = [a Step x12b sum NASF to subject].subject_code) AND ([a section instructor comp distribution].[RA campus] = [a Step x12b sum NASF to subject].campus_code) AND ([a section instructor comp distribution].FY = [a Step x12b sum NASF to subject].FY) GROUP BY [a section instructor comp distribution].FY, [a section instructor comp distribution].[RA campus], [a section instructor comp distribution].subject, [a section instructor comp distribution].[RA level];
Step 13f: Sum CCR Comp to subject.	SELECT [a Step x13e sum CCH Comp to CSL].FY, [a Step x13e sum CCH Comp to CSL].[RA Campus], [a Step x13e sum CCH Comp to CSL].subject, Sum([a Step x13e sum CCH Comp to CSL].[CSL CCR Comp]) AS [S CCR Comp] FROM [a Step x13e sum CCH Comp to CSL] GROUP BY [a Step x13e sum CCH Comp to CSL].FY, [a Step x13e sum CCH Comp to CSL].[RA Campus], [a Step x13e sum CCH Comp to CSL].subject;
Step 13g: Allocate Other POM to subject and level by CCR Comp. Build table "A Subject Level Lab POM Cost."	SELECT [a Step x13e sum CCH Comp to CSL].FY, [a Step x13e sum CCH Comp to CSL].[RA Campus], [a Step x13e sum CCH Comp to CSL].subject, [a Step x13e sum CCH Comp to CSL].[RA Level], [a Step x13d allo Other POM to subject].[Unr Other Subject Cost], [a Step x13d allo Other POM to subject].[Tot Other Subject Cost], [a Step x13f sum to Subj].[S CCR Comp], [a Step x13e sum CCH Comp to CSL].[CSL CCR Comp], [Unr Other Subject Cost]([CSL CCR Comp]/[S CCR Comp]) AS [Unr Other SL Cost], [Tot Other Subject Cost]([CSL CCR Comp]/[S CCR Comp]) AS [Tot Other SL Cost] INTO [a subject level othr POM cost] FROM ([a Step x13d allo Other POM to subject] INNER JOIN [a Step x13e sum CCH Comp to CSL] ON ([a Step x13d allo Other POM to subject].subject_code = [a Step x13e sum CCH Comp to CSL].subject) AND ([a Step x13d allo Other POM to subject].campus_code = [a Step x13e sum CCH Comp to CSL].[RA Campus]) AND ([a Step x13d allo Other POM to subject].FY = [a Step x13e sum CCH Comp to CSL].FY)) INNER JOIN [a Step x13f sum to Subj] ON ([a Step x13e sum CCH Comp to CSL].subject = [a Step x13f sum to Subj].subject) AND ([a Step x13e sum CCH Comp to CSL].[RA Campus] = [a Step x13f sum to Subj].[RA Campus]) AND ([a Step x13e sum CCH Comp to CSL].FY = [a Step x13f sum to Subj].FY);
Steps 13h and i: For those campuses with no subjects that have both NASF and CCR Comp, allocate to all subject, level combinations which have SCH based on SCH. First in Step 13h, identify those campuses which already have Other POM allocated. Then in Step 13i, allocate to the remaining campuses, campus wide based on SCH	SELECT DISTINCT [a subject level othr POM cost].FY, [a subject level othr POM cost].[RA Campus] FROM [a subject level othr POM cost]; INSERT INTO [a subject level othr POM cost] ( FY, [RA Campus], subject, [RA Level], [Unr Other Subject Cost], [Tot Other Subject Cost], [S CCR Comp], [CSL CCR Comp], [Unr Other SL Cost], [Tot Other SL Cost] ) SELECT [a Step 7f sum SCH to CSL].FY, [a Step 7f sum SCH to CSL].[RA Campus], [a Step 7f sum SCH to CSL].subject_code, [a Step 7f sum SCH to CSL].[RA Level], [a Step 9c allocate POM to Instructional and non].[Unr Other POM], [a Step 9c allocate POM to Instructional and non].[Total Other POM], [a Step 7g sum SCH to campus].[C SCH], [a Step 7f sum SCH to CSL].[CSL SCH], IIf(IsNull([a Step x13h select campuses with other POM]![RA Campus]),[Unr Other POM]([CSL SCH]/[C SCH]),0) AS [Unr Other SL Cost], IIf(IsNull([a Step x13h select campuses with other POM]![RA Campus]),[Total Other POM]([CSL SCH]/[C SCH]),0) AS [Tot Other SL Cost] FROM (([a Step 7f sum SCH to CSL] LEFT JOIN [a Step 7g sum SCH to campus] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step 7g sum SCH to campus].[RA Campus]) AND ([a Step 7f sum SCH to CSL].FY = [a Step 7g sum SCH to campus].FY)) LEFT JOIN [a Step x13h select campuses with other POM] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step x13h select campuses with other POM].[RA Campus]) AND ([a Step 7f sum SCH to CSL].FY = [a Step x13h select campuses with other POM].FY)) LEFT JOIN [a Step 9c allocate POM to Instructional and non] ON ([a Step 7f sum SCH to CSL].[RA Campus] = [a Step 9c allocate POM to Instructional and non].campus_code) AND ([a Step 7f sum SCH to CSL].FY = [a Step 9c allocate POM to Instructional and non].yr_num) WHERE (((IIf(IsNull([a Step x13h select campuses with other POM]![RA Campus]),[Total Other POM]*([CSL SCH]/[C SCH]),0))0));
Step 14: Allocate Law Library to Subject 220101, Level P.	SELECT dbo_campus_expens.yr_num AS FY, dbo_campus_expens.campus_code AS Campus, "220101" AS Subject, "P" AS [Level], dbo_campus_expens.law_library_expens AS [Unr Law Lib], [restrict_law_library_expens]+[law_library_expens] AS [Tot Law Lib] INTO [a subject level law library] FROM dbo_campus_expens;
Step 15: Allocate Law Library to Subject 511201, Level P.	SELECT dbo_campus_expens.yr_num AS FY, dbo_campus_expens.campus_code AS Campus, "511201" AS Subject, "P" AS [Level], dbo_campus_expens.med_library_expens AS [Unr Med Lib], [restrict_med_library_expens]+[med_library_expens] AS [Tot med Lib] INTO [a subject level med library] FROM dbo_campus_expens;
Step 20: Balance all input cost with all allocated cost.	See SQL for Steps 20 a - o.
Step 21: Combine all allocated cost into a single table summed by fiscal year, campus, subject, and level.	See SQL for Steps 21 a - m.
Step 22: Add instructional FTE to the main table of cost by campus, subject, and level. Note, nonprofessional medicine sections taught by a Medical Funding Unit are not included in the FTE.	SELECT [a course instructor]., IIf([appt_code]="GA","GA",IIf([fac_rank_code] In ("NR","OT","UK"),"AO",[fac_rank_code])) AS Rank FROM [a course instructor] INNER JOIN dbo_fac_demographics ON ([a course instructor].OBRID = dbo_fac_demographics.OBRID) AND ([a course instructor].inst = dbo_fac_demographics.inst_code) AND ([a course instructor].FY = dbo_fac_demographics.yr_num); SELECT [a Step x22a1 join instr rank to section instr table].FY, [a Step x22a1 join instr rank to section instr table].[RA campus], [a Step x22a1 join instr rank to section instr table].subject, [a Step x22a1 join instr rank to section instr table].[RA level], Sum(IIf([Rank]="PR",[CCH inst],0)) AS [PR CCH], Sum(IIf([Rank]="AP",[CCH inst],0)) AS [AP CCH], Sum(IIf([Rank]="AS",[CCH inst],0)) AS [AS CCH], Sum(IIf([Rank]="IN",[CCH inst],0)) AS [IN CCH], Sum(IIf([Rank]="LE",[CCH inst],0)) AS [LE CCH], Sum(IIf([Rank]="GA",[CCH inst],0)) AS [GA CCH], Sum(IIf([Rank]="AO",[CCH inst],0)) AS [AO CCH] FROM [a Step x22a1 join instr rank to section instr table] GROUP BY [a Step x22a1 join instr rank to section instr table].FY, [a Step x22a1 join instr rank to section instr table].[RA campus], [a Step x22a1 join instr rank to section instr table].subject, [a Step x22a1 join instr rank to section instr table].[RA level]; SELECT [a subject level total cost normalized]., [a Step x22b1 sum to csl].[PR CCH], [a Step x22b1 sum to csl].[AP CCH], [a Step x22b1 sum to csl].[AS CCH], [a Step x22b1 sum to csl].[IN CCH], [a Step x22b1 sum to csl].[LE CCH], [a Step x22b1 sum to csl].[GA CCH], [a Step x22b1 sum to csl].[AO CCH] INTO [a subject level total cost with CCH] FROM [a subject level total cost normalized] LEFT JOIN [a Step x22b1 sum to csl] ON ([a subject level total cost normalized].Level = [a Step x22b1 sum to csl].[RA level]) AND ([a subject level total cost normalized].subject = [a Step x22b1 sum to csl].subject) AND ([a subject level total cost normalized].[RA campus] = [a Step x22b1 sum to csl].[RA campus]) AND ([a subject level total cost normalized].FY = [a Step x22b1 sum to csl].FY);
Step 23: Add section size to the main table of cost by campus, subject, and level.	SELECT [a Step 1b2 join funding unit, subject,level].FY, [a Step 1b2 join funding unit, subject,level].year, [a Step 1b2 join funding unit, subject,level].term, [a Step 1b2 join funding unit, subject,level].inst, [a Step 1b2 join funding unit, subject,level].campus, [a Step 1b2 join funding unit, subject,level].course, [a Step 1b2 join funding unit, subject,level].section, [a Step 1b2 join funding unit, subject,level].Enrollments, [a Step 1b2 join funding unit, subject,level].subject, [a Step 1b2 join funding unit, subject,level].[CI level], IIf([CI level] Not In ("M","D"),[CI Level],IIf([B SCH]<[M SCH] And [D SCH]<[M SCH],"M",IIf([M SCH]<[B SCH] And [D SCH]<[B SCH],"B","D"))) AS [RA level], IIf([inst] In ("CYCC","SSCC"),[inst],[campus]) AS [RA campus] FROM [a Step 1b2 join funding unit, subject,level]; SELECT [a Step x23a convert section data to RA level and campus].FY, [a Step x23a convert section data to RA level and campus].[RA campus], [a Step x23a convert section data to RA level and campus].subject, [a Step x23a convert section data to RA level and campus].[RA level], Sum([a Step x23a convert section data to RA level and campus].Enrollments) AS [Total Enrollments], Count() AS [Total Sections], Sum(IIf([Enrollments]<4,0,[Enrollments])) AS [Non IS Enrollments], Sum(IIf([Enrollments]<4,0,1)) AS [Non IS Sections], Sum(IIf([Enrollments]<4,1,0)) AS [IS Sections], Sum(IIf([Enrollments]<11 And [Enrollments]>3,1,0)) AS [Small Class], Sum(IIf([Enrollments]<51 And [Enrollments]>10,1,0)) AS [Large Class], Sum(IIf([Enrollments]>50,1,0)) AS [Auditorius Class] FROM [a Step x23a convert section data to RA level and campus] GROUP BY [a Step x23a convert section data to RA level and campus].FY, [a Step x23a convert section data to RA level and campus].[RA campus], [a Step x23a convert section data to RA level and campus].subject, [a Step x23a convert section data to RA level and campus].[RA level]; SELECT [a subject level total cost with CCH]., [a Step x23b sum to csl].[Total Enrollments], [a Step x23b sum to csl].[Total Sections], [a Step x23b sum to csl].[Non IS Enrollments], [a Step x23b sum to csl].[Non IS Sections], IIf(IsNull([Total Sections]) Or [Total Sections]=0,0,[Total Enrollments]/[Total Sections]) AS [Avg Section Size], IIf(IsNull([Non IS Sections]) Or [Non IS Sections]=0,0,[Non IS Enrollments]/[Non IS Sections]) AS [Avg Non ISSection Size], [a Step x23b sum to csl].[IS Sections], [a Step x23b sum to csl].[Small Class], [a Step x23b sum to csl].[Large Class], [a Step x23b sum to csl].[Auditorius Class] INTO [a subject level total cost with CCH and sections] FROM [a subject level total cost with CCH] LEFT JOIN [a Step x23b sum to csl] ON ([a subject level total cost with CCH].Level = [a Step x23b sum to csl].[RA level]) AND ([a subject level total cost with CCH].subject = [a Step x23b sum to csl].subject) AND ([a subject level total cost with CCH].[RA campus] = [a Step x23b sum to csl].[RA campus]) AND ([a subject level total cost with CCH].FY = [a Step x23b sum to csl].FY);

Programming the Process

An Access file contains the SQL and test data for the Resource Analysis process.

Data Verification Displays

Step 1a: Student Credit Hours Retrieved From the Course Enrollment (CN) Table.

Fiscal Year	Campus	Year	Term	Institution	Enrollments	SCH
1998	BLTC	1997	SM	BLTC	9	33
1998	CYCM	1998	SP	CYCC	1	5
1998	OHSU	1997	AU	OHSU	2	8
					12	46

Note, the SCH should balance with the Course Enrollment (CN) file summary edits.

Step 1 b: Summarize by course section

Programming note: sort by campus and year ascending and term descending.

Fiscal Year Campus Year Term Institution Enrollments SCH SCH with non med FU Sections with non med FU Sect with level change

1998 BLTC
1997
SM BLTC
9

33

33

7

0

1998 CYCM
1998
SP CYCC
1

5

5

1

0

1998 OHSU
1997
AU OHSU
2

8

3

1

1

Step 1d: Join in Instructors.

Campus Year Term Section Instructors SCH for Instructors

BLTC

1997

SM

6

26

CYCM

1998

SP

1

5

OHSU

1997

AU

1

3

8

34

Step 1f: Allocate Instructor's Comp.

CampusSection Instructors Unr % Allocated Tot % Allocated

BLTC

1998

$212,000

$232,200

$151,000

$161,100

17

5

71%

69%

CYCC

1998

$35,000

$35,000

$35,000

$35,000

5

1

100%

100%

OHSU

1998

$40,000

$40,000

$30,000

$30,000

3

1

75%

75%

$287,000

$307,200

$216,000

$226,100

75%

74%

Step 2: Subtract Allocated Comp from the Funding Unit Expenses.

yr_num campus_code FX UR Comp FX UR Other FX UR Total FXC UR Comp FXC UR Ovh FXC UR Total FX Total FXC Total UR Allocated Comp Tot Allocated Comp

1998
BLTC
$283,002

$1,112,001

$1,395,003

$151,000

$1,244,003

$1,395,003

$1,612,206

$1,612,206

$151,000

$161,100

1998
CYCC
$120,000

$1,000

$121,000

$35,000

$86,000

$121,000

$121,000

$121,000

$35,000

$35,000

1998
OHSU
$300,000

$250,000

$550,000

$30,000

$520,000

$550,000

$550,000

$550,000

$30,000

$30,000

FX UR Total = FXC UR Total
FX Total = FXC Total
FXC UR Comp = UR Allocated Comp, or else some FF records relate to Funding Units for which there is no FX.

Step 3: Prorate College Academic Administration Cost to Funding Unit.

Year

Campus

Campus Prorated

Campus Input

1998 BLTC $30,000 $30,000

1998

CYCC

$0

1998

OHSU

$0

Total

$30,000

$30,000

Step 6: Calculate Unallocated Funding Unit Costs.

Total of Input (FX, CP) and Output (Comp, Funding Unit Overhead, Med, and Unallocated)

Year campus_code Unr FX Tot FX Unr CP Tot CP Unr Cmp Tot Cmp Unr FU Tot FU Unr Med Tot Med Unr Unallocated Tot Unallocated

1998
BLTC
$1,395,003

$1,612,206

$20,000

$30,000

$151,000

$161,100

$1,172,000

$1,281,100

$92,003

$200,006

1998
CYCC
$121,000

$121,000

$35,000

$35,000

$86,000

$86,000

$0

$0

1998
OHSU
$550,000

$550,000

$10,000

$15,000

$30,000

$30,000

$220,000

$220,000

$310,000

$315,000

$0

$0

$2,066,003

$2,283,206

$30,000

$45,000

$216,000

$226,100

$1,478,000

$1,587,100

$310,000

$315,000

$92,003

$200,006

Balance Input vs Output

yr_num	campus_code	Unr Input	Unr accounted for	Tot Input	Tot accounted for
1998	BLTC	$1,415,003	$1,415,003	$1,642,206	$1,642,206
1998	CYCC	$121,000	$121,000	$121,000	$121,000
1998	OHSU	$560,000	$560,000	$565,000	$565,000
		$2,096,003	$2,096,003	$2,328,206	$2,328,206

Step 7 and 8: Allocate Campus Expenses by SCH.
Remember, the input includes the CX, except POM, and the unallocated FX plus CP.

Input cost for SCH allocation is Suudent Services + Institutional Support + Academic Support + Unallocated from Funding Unit allocation

Fiscal Year Campus Total Input Cost allocated by SCH Total Cost allocated by SCH Total input Lib Cost Total Lib Cost allocated

1998

BLTC

$404,006

$404,006
$72,000 $72,000

1998

CYCC

$210,000

$210,000
$74,000 $74,000

1998

OHSU

$216,000

$216,000
$76,000 $76,000

$830,006

$830,006
$222,000 $222,000

Step 9c: Allocate POM at the Campus Level.

Campus_Code Yr_Num Unr POM In Total POM In Unr POM Allocated Total POM Allocated

BLTC

1998

$34,000

$74,000

$34,000

$74,000

CYCC

1998

$35,000

$35,000

$35,000

$35,000

OHSU

1998

$36,000

$36,003

$36,000

$36,003

$105,000

$145,003

$105,000

$145,003

Step 9d: Allocate Library POM at the Campus Level.

Campus_Code Yr_Num Unr Lib POM Total Lib POM Unr Allocated Total Allocated

BLTC

1998

$4,135

$9,000

$4,135

$9,000

CYCC

1998

$4,257

$4,257

$4,257

$4,257

OHSU

1998

$4,378

$4,378

$4,378

$4,378

$12,770

$17,636

$12,770

$17,636

Step 10: Allocate POM to Subject and Level Codes by SCH.

FY RA Campus Unr POM In Total POM In Unr POM Allocated Total POM Allocated

1998
BLTC
$16,541

$36,000

$16,541

$36,000

1998
CYCC
$17,027

$17,027

$17,027

$17,027

1998
OHSU
$13,135

$13,136

$13,135

$13,136

$46,703

$66,163

$46,703

$66,163

Step 11: Allocate Graduate Library POM.

FY RA Campus Unr In Tot In Unr Allocated Tot Allocated

1998

OHSU

$4,378

$4,379

$4,378

$4,379

Step 12d: Allocate Lab POM to Subject Code.

FY campus_code Unr Lab POM in Total Lab POM in Unr Lab POM to Subject Total Lab POM to Subject

1998
BLTC
$9,649

$21,000

$9,649

$21,000

Step 12i: Allocate Lab POM to Subject and Level Codes.

FY RA Campus Unr Lab in Tot Lab in Unr Lab allo Tot Lab allo

1998
BLTC
$9,649

$21,000

$9,649

$21,000

1998
CYCC
$9,932

$9,932

$9,932

$9,932

1998
OHSU
$10,216

$10,217

$10,216

$10,217

$29,797

$41,150

$29,797

$41,150

Step 13d: Allocate Other POM to Subject.

FY campus_code Unr Other POM In Total Other POM In Unr Other POM to Subject Total Other POM to Subject

1998

BLTC

$7,811

$17,000

$7,811

$17,000

Step 13i: Allocate Other POM to Subject and Level.

FY RA Campus Unr Other in Tot Other in Unr Other Allocated Tot Other Allocated

1998

BLTC

$7,811

$17,000

$7,811

$17,000

1998

CYCC

$8,041

$8,041

$8,041

$8,041

1998

OHSU

$8,270

$8,271

$8,270

$8,271

$24,122

$33,312

$24,122

$33,312

20. Balance all allocated cost to all input cost
In the Abbreviations:
1. Unr = Unresticted
2. Tot = Total

For Input Cost:
1. SS = Student Services from the CX
2. IS = Institutional Support from the CX
3. AS = Academic Support from the CX
4. Lib = General Library from the CX
5. LL = Law Library from the CX
6. ML = Medical Library from the CX
7. POM = Plant Operation and Maintenance from the CX
8. FS = Faculty Salary fromt he FX
9. FB = Faculty Benefits from the FX
10. OE = Other Expenses from the FX
11. AA = College Expenses (Academic Administration) from the CP.

For Allocated Cost:
1. Comp = Instructor's Salary and Fringes from Step 1
2. Ovh = Funding Unit Overhead (except Medical)from Step 4
3. Med = Medical Funding Unit Cost from Step 5
4. SCH = Campus Cost (including unallocated FX and CP) allocated by SCH from Step 7
5. Lib = General Library Cost from Step 8
6. LL = Law Library from Step 14
7. ML = Medical Library from Step 15
8. SCH POM = POM Cost allocated by SCH from Step 10
9. GL POM = POM Cost for library from Steps 10 and 11
10. LAB POM = Lab POM from Step 12
11. Other POM = Other POM from Step 13.

Step 20 c input cost

yr_num campus_code Unr SS Unr IS Unr AS Unr Lib Unr LL Unr ML Unr POM

1998
BLTC
$30,000

$31,000

$32,000

$33,000

$0

$0

$34,000

1998
CYCC
$31,000

$32,000

$33,000

$34,000

$0

$0

$35,000

1998
OHSU
$32,000

$33,000

$34,000

$35,000

$53

$100

$36,000

$93,000

$96,000

$99,000

$102,000

$53

$100

$105,000

Tot SS Tot IS Tot AS Tot Lib Tot LL Tot ML Tot POM

$66,000

$68,000

$70,000

$72,000

$15

$20

$74,000

$68,000

$70,000

$72,000

$74,000

$0

$0

$35,000

$70,000

$72,000

$74,000

$76,000

$54

$102

$36,003

$204,000

$210,000

$216,000

$222,000

$69

$122

$145,003

faculty salary (FX) faculty benefits (FX) other expenses (FX) college (CP)

Unr FS Tot FS Unr FB Tot FB Unr OE Tot OE Unr AA Tot AA Tot w/o POM

$225,001

$298,002

$58,001

$117,202

$1,112,001

$1,197,002

$20,000

$30,000

$1,918,241

$100,000

$100,000

$20,000

$20,000

$1,000

$1,000

$0

$0

$405,000

$300,000

$300,000

$0

$0

$250,000

$250,000

$10,000

$15,000

$857,156

$625,001

$698,002

$78,001

$137,202

$1,363,001

$1,448,002

$30,000

$45,000

$3,180,397

step 20 n allocated cost (except POM)

FY RA Campus Unr Comp Tot Comp Unr FU Ovh Tot FU Ovh Unr Med Tot Med Unr SCH Tot SCH

1998
BLTC
$151,000

$161,100

$1,172,000

$1,281,100

$0

$0

$185,003

$404,006

1998
CYCC
$35,000

$35,000

$86,000

$86,000

$0

$0

$96,000

$210,000

1998
OHSU
$30,000

$30,000

$220,000

$220,000

$310,000

$315,000

$99,000

$216,000

$216,000

$226,100

$1,478,000

$1,587,100

$310,000

$315,000

$380,003

$830,006

Unr Lib Tot Lib Unr LL Tot LL Unr ML Tot ML Tot w/o POM

$33,000

$72,000

$0

$15

$0

$20

$1,918,241

$34,000

$74,000

$0

$0

$0

$0

$405,000

$35,000

$76,000

$53

$54

$100

$102

$857,156

$102,000

$222,000

$53

$69

$100

$122

$3,180,397

Step 20 o allocated POM

FY RA Campus Unr SCH POM Tot SCH POM Unr GL POM Tot GL POM Unr Lab POM Tot Lab POM Unr Other POM Tot Other POM Tot POM

1998
BLTC
$16,541

$36,000

$0

$0

$9,649

$21,000

$7,811

$17,000

$74,000

1998
CYCC
$17,027

$17,027

$0

$0

$9,932

$9,932

$8,041

$8,041

$35,000

1998
OHSU
$13,135

$13,136

$4,378

$4,379

$10,216

$10,217

$8,270

$8,271

$36,003

$46,703

$66,163

$4,378

$4,379

$29,797

$41,150

$24,122

$33,312

$145,003

Step 21 Join Cost and FTE to a single table by Campus, Subject Code and Level

FY RA campus Unr Comp Tot Comp Unr FU Ovh Tot FU Ovh Unr Med Tot Med Unr SCH Cost Tot SCH Cost

1998
BLTC
$151,000

$161,100

$1,172,000

$1,281,100

$0

$0

$185,003

$404,006

1998
CYCC
$35,000

$35,000

$86,000

$86,000

$0

$0

$96,000

$210,000

1998
OHSU
$30,000

$30,000

$220,000

$220,000

$310,000

$315,000

$99,000

$216,000

$216,000

$226,100

$1,478,000

$1,587,100

$310,000

$315,000

$380,003

$830,006

Unr Lib Tot Lib Unr POM Tot POM

$33,000

$72,035

$34,000

$74,000

$34,000

$74,000

$35,000

$35,000

$35,153

$76,156

$36,000

$36,003

$102,153

$222,191

$105,000

$145,003

FTE LD FTE UD FTE M FTE D FTE P FTE

11.82

11.82

0.00

0.00

0.00

0.00

0.11

0.11

0.00

0.00

0.00

0.00

0.89

0.00

0.00

0.18

0.04

0.67

12.82

11.93

0.00

0.18

0.04

0.67

Return to Resource Analysis Files

http://regents.ohio.gov/hei/RA/archive/RAspecifications05271999.html
Last updated May 27, 1999

Aggregation						Allocated Cost per Student FTE
Fiscal Year	Institution Type	Institution	Campus	Subject (see Note 1)	Level (see Note 2	Compensation	Funding Unit Overhead	Medical Funding Unit	Student Services	Institutional Support	Academic Support	Funding Unit Rollup	Library	POM
9999	XX	XXXX	XXXX	XXXXXX	XX	$999,999	$999,999	$999,999	$999,999	$999,999	$999,999	$999,999	$999,999	$999,999
						Student FTE	Lower Division	Upper Division	Master's	Doctoral	Professional
						999,999	999,999	999,999	999,999	999,999	999,999
						Instructor CCH	Professor	Assoc. Professor	Asst. Professor	Instructor	Lecturer	GA	All Others	Total
						99,999	99,999	99,999	99,999	99,999	99,999	99,999	99,999	99,999
						Average Class Size (all sections)	Average Class Size (regular sections)		Number of sections by size 1 - 3	4 - 10	11 - 50	> 50
						999	999		99 9	99 9	99 9	99 9

Note 1: Subject may be a 6, 4, or 2 digit subject code, Subject Field Name, the word ALL, or a Subsidy Model Name
Note 2: Level May be a Level Code, the word ALL, or blank if Subject Code is a Subsidy Model Name.

CampusSection Instructors	Unr % Allocated	Tot % Allocated
BLTC	1998	$212,000	$232,200	$151,000	$161,100	17	5	71%	69%
CYCC	1998	$35,000	$35,000	$35,000	$35,000	5	1	100%	100%
OHSU	1998	$40,000	$40,000	$30,000	$30,000	3	1	75%	75%
		$287,000	$307,200	$216,000	$226,100			75%	74%

yr_num	campus_code	FX UR Comp	FX UR Other	FX UR Total	FXC UR Comp	FXC UR Ovh	FXC UR Total	FX Total	FXC Total	UR Allocated Comp	Tot Allocated Comp
1998	BLTC	$283,002	$1,112,001	$1,395,003	$151,000	$1,244,003	$1,395,003	$1,612,206	$1,612,206	$151,000	$161,100
1998	CYCC	$120,000	$1,000	$121,000	$35,000	$86,000	$121,000	$121,000	$121,000	$35,000	$35,000
1998	OHSU	$300,000	$250,000	$550,000	$30,000	$520,000	$550,000	$550,000	$550,000	$30,000	$30,000

Year	Campus	Campus Prorated	Campus Input
1998	BLTC	$30,000	$30,000
1998	CYCC	$0
1998	OHSU	$0
	Total	$30,000	$30,000

Year	campus_code	Unr FX	Tot FX	Unr CP	Tot CP	Unr Cmp	Tot Cmp	Unr FU	Tot FU	Unr Med	Tot Med	Unr Unallocated	Tot Unallocated
1998	BLTC	$1,395,003	$1,612,206	$20,000	$30,000	$151,000	$161,100	$1,172,000	$1,281,100			$92,003	$200,006
1998	CYCC	$121,000	$121,000			$35,000	$35,000	$86,000	$86,000			$0	$0
1998	OHSU	$550,000	$550,000	$10,000	$15,000	$30,000	$30,000	$220,000	$220,000	$310,000	$315,000	$0	$0
		$2,066,003	$2,283,206	$30,000	$45,000	$216,000	$226,100	$1,478,000	$1,587,100	$310,000	$315,000	$92,003	$200,006

Fiscal Year	Campus	Total Input Cost allocated by SCH	Total Cost allocated by SCH	Total input Lib Cost	Total Lib Cost allocated
1998	BLTC	$404,006	$404,006	$72,000	$72,000
1998	CYCC	$210,000	$210,000	$74,000	$74,000
1998	OHSU	$216,000	$216,000	$76,000	$76,000
		$830,006	$830,006	$222,000	$222,000

Campus_Code	Yr_Num	Unr POM In	Total POM In	Unr POM Allocated	Total POM Allocated
BLTC	1998	$34,000	$74,000	$34,000	$74,000
CYCC	1998	$35,000	$35,000	$35,000	$35,000
OHSU	1998	$36,000	$36,003	$36,000	$36,003
		$105,000	$145,003	$105,000	$145,003

Campus_Code	Yr_Num	Unr Lib POM	Total Lib POM	Unr Allocated	Total Allocated
BLTC	1998	$4,135	$9,000	$4,135	$9,000
CYCC	1998	$4,257	$4,257	$4,257	$4,257
OHSU	1998	$4,378	$4,378	$4,378	$4,378
		$12,770	$17,636	$12,770	$17,636

FY	RA Campus	Unr POM In	Total POM In	Unr POM Allocated	Total POM Allocated
1998	BLTC	$16,541	$36,000	$16,541	$36,000
1998	CYCC	$17,027	$17,027	$17,027	$17,027
1998	OHSU	$13,135	$13,136	$13,135	$13,136
		$46,703	$66,163	$46,703	$66,163

FY	RA Campus	Unr In	Tot In	Unr Allocated	Tot Allocated
1998	OHSU	$4,378	$4,379	$4,378	$4,379

FY	campus_code	Unr Lab POM in	Total Lab POM in	Unr Lab POM to Subject	Total Lab POM to Subject
1998	BLTC	$9,649	$21,000	$9,649	$21,000

FY	RA Campus	Unr Lab in	Tot Lab in	Unr Lab allo	Tot Lab allo
1998	BLTC	$9,649	$21,000	$9,649	$21,000
1998	CYCC	$9,932	$9,932	$9,932	$9,932
1998	OHSU	$10,216	$10,217	$10,216	$10,217
		$29,797	$41,150	$29,797	$41,150

FY	campus_code	Unr Other POM In	Total Other POM In	Unr Other POM to Subject	Total Other POM to Subject
1998	BLTC	$7,811	$17,000	$7,811	$17,000

FY	RA Campus	Unr Other in	Tot Other in	Unr Other Allocated	Tot Other Allocated
1998	BLTC	$7,811	$17,000	$7,811	$17,000
1998	CYCC	$8,041	$8,041	$8,041	$8,041
1998	OHSU	$8,270	$8,271	$8,270	$8,271
		$24,122	$33,312	$24,122	$33,312

Step 20 c input cost
yr_num	campus_code	Unr SS	Unr IS	Unr AS	Unr Lib	Unr LL	Unr ML	Unr POM
1998	BLTC	$30,000	$31,000	$32,000	$33,000	$0	$0	$34,000
1998	CYCC	$31,000	$32,000	$33,000	$34,000	$0	$0	$35,000
1998	OHSU	$32,000	$33,000	$34,000	$35,000	$53	$100	$36,000
		$93,000	$96,000	$99,000	$102,000	$53	$100	$105,000

		Tot SS	Tot IS	Tot AS	Tot Lib	Tot LL	Tot ML	Tot POM
		$66,000	$68,000	$70,000	$72,000	$15	$20	$74,000
		$68,000	$70,000	$72,000	$74,000	$0	$0	$35,000
		$70,000	$72,000	$74,000	$76,000	$54	$102	$36,003
		$204,000	$210,000	$216,000	$222,000	$69	$122	$145,003
		faculty salary (FX)		faculty benefits (FX)		other expenses (FX)		college (CP)
		Unr FS	Tot FS	Unr FB	Tot FB	Unr OE	Tot OE	Unr AA	Tot AA	Tot w/o POM
		$225,001	$298,002	$58,001	$117,202	$1,112,001	$1,197,002	$20,000	$30,000	$1,918,241
		$100,000	$100,000	$20,000	$20,000	$1,000	$1,000	$0	$0	$405,000
		$300,000	$300,000	$0	$0	$250,000	$250,000	$10,000	$15,000	$857,156
		$625,001	$698,002	$78,001	$137,202	$1,363,001	$1,448,002	$30,000	$45,000	$3,180,397
step 20 n allocated cost (except POM)
FY	RA Campus	Unr Comp	Tot Comp	Unr FU Ovh	Tot FU Ovh	Unr Med	Tot Med	Unr SCH	Tot SCH
1998	BLTC	$151,000	$161,100	$1,172,000	$1,281,100	$0	$0	$185,003	$404,006
1998	CYCC	$35,000	$35,000	$86,000	$86,000	$0	$0	$96,000	$210,000
1998	OHSU	$30,000	$30,000	$220,000	$220,000	$310,000	$315,000	$99,000	$216,000
		$216,000	$226,100	$1,478,000	$1,587,100	$310,000	$315,000	$380,003	$830,006
		Unr Lib	Tot Lib	Unr LL	Tot LL	Unr ML	Tot ML	Tot w/o POM
		$33,000	$72,000	$0	$15	$0	$20	$1,918,241
		$34,000	$74,000	$0	$0	$0	$0	$405,000
		$35,000	$76,000	$53	$54	$100	$102	$857,156
		$102,000	$222,000	$53	$69	$100	$122	$3,180,397
Step 20 o allocated POM
FY	RA Campus	Unr SCH POM	Tot SCH POM	Unr GL POM	Tot GL POM	Unr Lab POM	Tot Lab POM	Unr Other POM	Tot Other POM	Tot POM
1998	BLTC	$16,541	$36,000	$0	$0	$9,649	$21,000	$7,811	$17,000	$74,000
1998	CYCC	$17,027	$17,027	$0	$0	$9,932	$9,932	$8,041	$8,041	$35,000
1998	OHSU	$13,135	$13,136	$4,378	$4,379	$10,216	$10,217	$8,270	$8,271	$36,003
		$46,703	$66,163	$4,378	$4,379	$29,797	$41,150	$24,122	$33,312	$145,003