SPARC Dashboard Information

The University’s Strategic Planning Analytics and Reporting for Campuses (SPARC) dashboard is a management tool for campus decision makers to analyze financial resources at the department level. The product of over two years of work by the Advisory Working Group on Financial Planning, the dashboard provides detailed data in one convenient location, eliminating the need to manually run reports from different sources and systems.
The SPARC dashboard is used only for internal purposes and only for directional estimates to support decision making. This dashboard is not meant to be the sole source for decision making.

Access must be granted via email by the Chancellor of your campus; once granted Summit Team (summit@umassp.edu) can coordinate with your campus IT

Use this Smartsheet form created to collect information on issues users experience or to suggest changes

There are six sections of the dashboard in gray boxes at the top of the screen. They are the Key Ratios & Demographics, Payroll, Non-Personnel & Fringe Expenses, Student Revenue, Non-Student Revenue, and Financial Summary sections. The Key Ratios & Demographics and Financial Summary sections provide high level information and trend data, while the other tabs provide detail on each type of revenue or expense.

There are five filters at the top of each section: Fiscal Year, Campus, Functional Area, College/Division, and Department. The PeopleSoft tree described above is used to summarize by Functional Area, College/Division, and Department.

In the top right corner of each page is an “i” icon. Hovering over this icon provides additional information and a link to this document.

The Download Data button in the top right of each page enables the user to export data into Excel or CSV formats.

Use the checkboxes under the Department filter to select the departments you want to compare. Dashboards on the page will update to only show those departments. Some dashboards have toggles to switch between summary and department comparison views. Information on these toggles is described in sections below.

After selecting all of the departments you want to compare in the filters at the top of the page, scroll to the dashboard you want to view. Using the dropdown menu, select “department comparison” instead of “summary.” The dashboard will change to show multiple departments instead of a summary of them all.

Data is refreshed daily based on a live feed from each system. Prior year data is as of the last day of the fiscal year. Current year data is as of the last complete calendar day.

SPARC uses the campus RPT_DEPARTMENT trees for department roll ups.

The most current effective dated values are used on this tree on SPARC, which means SPARC always reflects how departments are currently set up ("As Is"). For example, if a child department A is moved to a new parent X, historical A data is now reported under X.

The following describe how changes and updates to the data tree affect reporting in SPARC:

*Campuses differ on how old departments are treated in the rollups. They may choose to group all closed departments in a "closed" rollup rather than keep in a college or functional area which would in turn move all the history out of the old functional area.

The SPARC data analytics team has conducted a data mapping exercise to document SPARC data flow architecture from sources.

Access the SPARC Student Data Lineage Document

Access the SPARC Finance/HR Data Lineage Document

• How is department determined?
  • Department is based on the department ID fields as they correspond to the RPT_DEPARTMENT tree in PeopleSoft. This is an existing tree utilized and maintained by each campus in financial reporting.
• How are employee FTEs shown?
  • Employee FTEs are taken as a snapshot in time on the last pay period in September. Current year employee FTEs are YTD as of the most recent pay period.
• Why am I seeing negative revenues or expenses in my department?
  • Some campuses transfer or distribute shared expenses or revenuse between departments. At a campus level, these transactions will net to zero, but may show up as a negative revenue or expense when viewing a specific department.
• Department Analysis Calculations:

• How are online and continuing education data categorized?
  • All expenses and revenues are categorized according to the PeopleSoft tree that drives the tool. Online and continuing education data are shown as inputted by the campus. In most cases, online and continuing education are separate departments, with revenues and expenses attributed to those departments.
• Enrollment by class - how are students that are >4 years categorized?
  • Students who have been enrolled for 4 or more years are categorized as seniors.

• What is included in the Payroll section?
  • There are five dashboards in the payroll section. Each provides a detailed view of a different way to analyze payroll. At the top is the total payroll and total employee FTE for all departments selected. The Department Detail dashboard shows the breakdown of payroll by internal and external funds for each department, as well as the employee FTE count. The Employee Detail dashboard shows payroll in all departments selected by employee type, FTEs, and internal/external funds. Faculty Detail shows faculty payroll and FTEs by tenure status. Student Employee Detail shows student payroll by undergraduate/graduate breakdown and job title for graduate students. Payroll by Earning Type breaks out total payroll by base salary, add comp, overtime, buyout, and other.
• What happens if the employee changes jobs throughout the year?
  • Payroll data is tracked based on the department from which funds are expended. Employee information is tracked using a combination of employee ID and employee record number, so changes to an employee’s information are captured as they change roles.
• Why are student employee FTEs "null?"
  • Student employee FTEs are not included in this version of the dashboard, as the focus has been on displaying FTEs for “typical” faculty and staff. Future enhancements may include student employee FTEs.

• Why is fringe included in the Non-Personnel tab and not shown on the payroll tab?
  • Fringe is shown on the Non-Personnel & Fringe Expense tab as a result of how fringe benefits are accounted for in the general ledger. Fringe expense is tracked as an expense type, available by department. Fringe is not tracked in available source systems at an individual employee level. Thus, fringe cannot be shown in combination with salaries on the Payroll tab, which are displayed based on individual employees.
• How are direct expenses different from indirect & other?
  • Direct expenses are related to general operations, while indirect & other are associated with grant-related costs.
• What type of expenses are included in the categories of "Other" and "Indirect and Other?"
  • There is not much going through Academic Affairs in the "Other" category. The main expense here is related to debt service. "Indirect and Other" includes debt service activity, depreciation, student loan activity, and transfers out.
• Are central administrative costs accounted for in the dashboard?
  • Central administrative costs are not accounted for in SPARC and no attempt is made to allocate them to departments.

• How are Dean's department budgets allocated in the dashboard?
  • Dean’s department budgets are not allocated to the departments that fall within their college. In some cases, this results in Dean’s departments with high administrative costs and low enrollment. This is a result of campus accounting trees and requires outside knowledge or manual adjustment.
• When filtered with Academic Affairs on the Financial Summary tab, Gross Tuition Revenue does not match Gross Revenue on Tuition Revenue tab. Why is this?
  • This is due to the change in the data source from 'Financial Planning - Payroll' to 'Department Security'

• In the Total Financial Aid Detail section, how are the percentages calculated?
  • The percentages in this section are calculated based on the percent of total aid awarded. This is a financial measure, not a student count. 
• Is state appropriation reflected in University Support?
  • No. Importantly, state appropriations are not included in the total revenues for any selection of departments. This is because state appropriations are not allocated to the department level. Because state appropriation makes up a significant portion of campus revenue, it is a significant portion of the undetailed University Support line.

Employee type is determined by EEO-6 code.

• FTEs in SPARC are allocated as a percentage based on the HR departments of the individual person
• Employee FTEs are taken as a snapshot in time on the last pay period in September
• Current year employee FTEs are YTD as of the most recent pay period
• An individual might be working in several departments, hence pay is spread across several HR departments
• Although pay and FTEs are allocated in several departments as a percentage, at campus level it all adds up to 1 FTE

External funds are considered to be any coming from a restricted source, such as from grants or contracts. These are indicated in PeopleSoft by having a fund code beginning with “53.” All other funds are considered to be internal.

Non-tenure-track faculty are sorted into two categories based upon feedback received from the campuses. Union code is used as a proxy for each category. Non-tenure-track faculty affiliated with a union are considered to be “permanent/continuing.” Those not affiliated with a union (or at UML in the Lowell Temporary Instructors - LTI code) are considered “temporary/non-continuing.”

Non-personnel expense categories are those utilized in the UM-Plan budget tool.

Gross tuition & fees include all student revenues except auxiliaries, allocated using the methodology above.

Net tuition & fees subtract financial aid from gross tuition & fees. Total financial aid includes grants, scholarships, and waivers. Loans are specifically excluded from aid shown here.

Credits taken include all credits taken by students in courses instructed by the selected department. For example, the Biology department would include students taking introductory level Biology courses, even if they are not Biology majors. This metric is meant to measure instructional output, not majors.

The winter semester includes any sessions offered between fall and spring. Because the summer semester falls between two fiscal years, the semester is defined as the summer before the fall. Some courses may start in the prior fiscal year, but for the purpose of analysis, all courses are counted in the fiscal year as the following fall term. By hovering over the term, the user can see the exact description of the term displayed, e.g. “2020 Summer.”

Grant revenue comes from external sources and has restrictions in its use. In PeopleSoft, these are indicated by a fund code beginning with “53.”

Non-student revenue categories are those utilized in the UM-Plan budget tool.

Students are considered a full financial FTE (FFTE = 1.00) if they are taking 12 credits or more. Otherwise, credits are divided by 12.

Auxiliary departments were identified through an analysis of departmental auxiliary fund usage, which were validated by the campuses and will be periodically reviewed and updated.

The majority of Other Revenues includes General Income, Gifts, and Commissions Income. Commissions incomes is generally used for food services, such as commissions received from Sodexo, while General Income is also used for food services, along with the Five College Program, graduate application fees, and other miscellaneous transactions.

Number of students enrolled in each course. Students enrolled in multiple courses are counted multiple times.

Number of students enrolled in any course. Each student is counted only one time regardless of the number of courses enrolled.

Gross tuition less institutional aid (auxiliary and other aid); includes aid remitted to the campus (federal & state and third-party aid)

Gross tuition less all financial aid; excludes all aid as this is the true price a student pays to the institution

Gross Revenue in the Tuition Revenue Tab is showing only the Functional Area of Academic Affairs (course contribution) whereas the Financial Summary tab also includes Functional Area of Auxiliary along with Academic Affairs.

Gross Tuition Revenue would reconcile if Academic Affairs filter is applied to the Financial Summary tab.

How is Gross Revenue per Credit calculated?

[Financial Planning - Finance - CLS VR2] * [Fin Sum Total Gross Revenue] / [Credits Taken]

How is Net Revenue per Credit calculated?

[Financial Planning - Finance - CLS VR2] * [Net Revenue] / [Credits Taken]

How is Expenses per Credit calculated?

[Financial Planning - Finance - CLS VR2] * [Fin Sum Total Expenses] / [Credits Taken]

How is Externally Funded Financial Aid calculated?

(sum([FED_AID_CONTRIB_TO_CRSE])) + (sum([STATE_AID_CONTRIB_TO_CRSE])) + (sum([PRIVATE_AID_CONTRIB_TO_CRSE]))

How is Internally Funded Financial Aid calculated?

(sum([Total Aid Contribution to Course]) * -1) + [Externally Funded Financial Aid]