School of Music Class Cost/Revenue Modeling Tool
From Business Intelligence
Contents |
[edit] Project Contact
If you have questions or would like more information, please contact Robert Newcomb (rnewcomb@umich.edu).
[edit] Project Description
The School of Music, Theatre & Dance (SMTD) has developed a Class Cost/Revenue Modeling Tool to provide more accurate metrics for financial aid awards. The Xcelsius-based tool aids in the development of cost/revenue measurements for current and proposed curriculum.
[edit] Business Need
- Improve accuracy of metrics used for financial aid awards.
[edit] System/Application & Data Sources
The tool was built using Crystal Xcelsius (Standard Version 4.5) from Business Objects. Aggregate data is sourced from the following data sets in the U-M Data Warehouse: CRAS, Financial Aid, and Financials data and internal SMTD data is also included.
[edit] Project Purpose, Scope, & Deliverables
- Include variables such as percentage of an instructor's total appointment effort, multiple instructors, tuition attribution (75/25 and 50/50 splits), lab fees, and indirect costs associated with instruction such as staff support and facility overhead.
- Test the efficacy of the tool using data sets from three actual Fall 2007 classes.
- Refine the tol to provide requisite detail discovered during the tool testing process, including:
- Allowance for a varying number of student 'profiles' within a class population, covering attributes such as level, residency, percentage of financial aid provided, and number of SCH.
- A top level choice of 'Class Type' (Lecture/Lab or Performance Studio), which in turn presents alternative methods of building a Student Matrix for subsequent calculations.
- A variable in the Instructor Matrix for the 'Instructor Type' (Professor, Associate Professor, Lecturer, GSI, etc) with associated average salaries used for subsequent calculations, and the capability of factoring in instructor appointment cost allowances such as PFIP.
[edit] Duration/Timeline
In January 2007, an alpha version of the modeling tool was presented to the school's senior administrators.
In February 2007, a revised version of the tool built in Xcelsius was presented to the Small BAG group, which stimulated enthusiastic discussion among the other academic units about the applicability of the underlying rapid prototyping technology for their purposes. Possible modeling efforts using the Xcelsius technology that are under investigation include an Endowment Fund projection tool, a trending tool for overall Source/Use data, and a “morphing” tool for comparing Source/Use data across all U-M units.
The current tool is undergoing continual refinement through a vetting process with SMTD senior administrators. Planned expansion of the tool includes a scaling of its scope to provide cost/revenue modeling at the Degree and Departmental levels.