CUMC Pre-Clinical and Clinical Management Units

USER GUIDE: FY04/FY05 MANAGEMENT UNIT LEVEL VARIANCE ANALYSISOPERATING ACTIVITY REPORTS

CUMC Pre-Clinical and Clinical Management Units

User Guide

This user guide accompanies an excel workbook called the “FY2003-04 Budget vs. Actual Analysis” provided to each Management Unit. Workbooks can be downloaded from the website:

The data in the workbook should enable the user to analyze, for any line in the Consolidated Operating Budget report (COB) and/or for the individual accounts that sum to any COB line, the following types of information:

1)changes between actual activity in FY2003 and actual activity in FY2004

2)variances between budgeted and actual activity for FY2004

3)comparison of most recent actual activity (FY2003 and FY2004) to the recently approved FY2005 budgets

The CUMC Office of Budget and Finance will engage in a dialogue with managers at the department level (or Business Unit level) on all three of these topics. For financial analysis purposes, some departments are a Business Unit with only one Management Unit (BU and MU are the same level of analysis), others have multiple Management Units within their Business Unit. This guide assumes a Management Unit level of analysis. Additional detail has been provided under separate cover to assist Business Units in analyzing multiple Management Units (see separate handout). the suite ofthree monthly FY04/FY05 Operating Activity Reports available to University financial managers via the Financial Management Reporting web site.

This User Guide contains the following sections:

• Accessing the Reports
• Brief Description of Reports
• Suggestions for Using the Reports
• FAQs

Accessing the reports

The site reports can be accessed by either of two methods:

1. In FFE, Choose “Financial Management Reports via the Data Warehouse”
   from the “Reports” menu.
   

1. Log in to the University portal at and
   access the “DWR Reports” tab.

After accessing the reporting web site via either of these methods, choose “Financial Management Reports” from the first menu and “FY04/FY05 Operating Activity Reports” from the second. Then, follow the instructions on-screen.

If you have any difficulty accessing the Operating Activity Reports, you can refer to a companion guide available on the Management Reporting website at

Summary Description of Reports

There are three distinct Operating Activity Reports:

Contents Overview

The variance analysis workbook for a given Management Unit contains the following four tabs:

ReportTab / Description
YTD Rate of Change Analysis ReportCOB – Total /
Displays actual percentage change between FY04 YTD and FY05 YTD. Also displays forecast percentage change between FY04 Full Year Actual and both FY05 Original Budget and FY05 Current Estimate.
Consolidated Operating Budget (COB) report that groups financial activity for all operating accounts in the management unit into distinct categories of revenue and expense (called COB lines). This tab includes 4 pages when printed:
Financial Overview: Summary of Sources, Uses and Change in Fund Balance
Summary of Sources: detailed lines of activity for Direct and Indirect Sources
Summary of Uses: detailed lines of activity for Direct and Indirect Uses
Direct Expenses by Category: distinct categorization of direct expense, by Personnel and OTPS
Extrapolation of YTD Actual vs. Full Year Forecasts ReportDetail – Direct /
Extrapolates FY05 Full Year Actual ending point based on YTD activity and displays dollar variances between this ending point and both FY05 Original Budget and FY05 Current Estimate.
Tabular data allowing the user to research the accounts that contribute to each DIRECT activity COB line (sections I, III, V, and VI of the COB) and each line of the Direct Expense by Category (Personnel and OTPS)
Prorated Forecasts vs. YTD Actual ReportDetail – Indirect /
Prorates the FY05 Original Budget and FY05 Current Estimate, transforming them into estimated YTD forecasts. Displays the dollar variances between these YTD forecasts and FY05 YTD actual.
Tabular data allowing the user to research the local and foreign accounts that contribute to each INDIRECT activity COB line (sections II and IV of the COB)
Grant Summary / A table listing accounts with actual grant spending in FY2002-03 and FY2003-04, and the total budgeted spending on proxy grant accounts for FY2004-05

Each of these reports is available monthly, at the Management Unit level and at the Business Unit level.

The three reports share the standard Consolidated Operating Budget (COB) report format that groups financial activity for all operating accounts into distinct categories of revenue and expense (called COB lines). These reports include 4 pages when printed:

• Financial Overview: Summary of Sources, Uses and Change in Fund Balance
• Summary of Sources: detailed lines of activity for Direct and Indirect Sources

• Summary of Uses: detailed lines of activity for Direct and Indirect Uses
• Direct Expenses by Category: distinct categorization of direct expense, by Personnel, OTPS, and Recoveries

Step 1: Review the COB Total Tab (Consolidated Operating Budget Report Tab)

As noted above, the format of the COB-Total tab is a four-page report that includes:

Financial Overview: Summary of Sources, Uses and Change in Fund Balance

Summary of Sources: detailed lines of activity for Direct and Indirect Sources

Summary of Uses: detailed lines of activity for Direct and Indirect Uses

Direct Expenses by Category: direct expense detail by Personnel and OTPS

The data you will see on each of these four pages includes FY2002-03 actual activity, FY2003-04 budgeted activity, FY2003-04 actual activity, and FY2004-05 budget data. Based on this data we have also calculated for you, for each line of financial activity:

FY2003-04 Budget to Actual dollar variance

FY2003-04 Budget to Actual percent variance

percent change from FY2002-03 actual to FY2003-04 actual

percent change from FY2003-04 actual to FY2004-05 budget

Any % values that are labeled “NM” = Not Meaningful, a designation we have assigned for calculation of % change when one value is a positive number and the other is a negative number, or when the first value is zero.

Step 2: Identify COB lines and Direct Expense Categories of Greatest Significance

As you become acquainted with the presentation of the data, look for COB lines with significant year-over-year % change (column I), FY2003-04 budget to actual dollar variances, and significant % change from FY2003-04 to budgeted FY2004-05. Determine which lines you want to explore further. Make your own record of which lines (over which time periods) you want to assess—for example, you may want to look at Med Fac Practice from FY2002-03 to FY2003-04, and Private Gifts on FY2003-04 budget to actual, etc.

Also, review the Direct Expenses by Category at the bottom of the COB-Total Tab (the last page if you printed it). This provides another lens through which to view your direct expense activity—by Personnel and OTPS. Go through the same process, and note those categories of spending that deserve further analysis.

Step 3: Conduct a Drill Down of COB Line Direct Revenue and Expense Activity

Start with the list of Direct Activity COB lines (from Step 2) that you believe require further analysis. You can use the second tab, called “Direct-Detail” to investigate activity on the account level that sums to each COB line of either direct revenue or direct expense (indirect activity is reflected at the account level on a different tab). The Direct-Detail tab contains a row for every SL subcode or GL control code with actual or budgeted direct activity on each account in your management unit.

In order to fully utilize this tab, you should be familiar with using filters in excel. Filters are a mechanism for segregating data to focus on subsets of a very large amount of information. Simply use the downward facing arrow in grey near the column label to select a parameter that you wish to zoom in on, and thus filter out all other data. When you select the criteria for filtering, the arrow will turn blue which means that the filter is “active.” We suggest you start by filtering on COB Line (column B). You will then see all of the accounts (and their financial data) that sum to the selected COB line. Click back on the arrow and set it to “All” if you wish to remove the filter. You can filter on multiple parameters, creatinG subsets of subsets of information. Just remember to turn off each filter if you want to go back to a view of all data. (Alternatively, you can access the menu item Data>Filter>Show All to remove all filters simultaneously.)

Here are the columns of data in the Direct-detail tab:

Column / Description
COB_Line / COB Line number and description
Dep * / FAS Department Number
Sub_Dep * / FAS Sub-Department Number
Sub_Sub_Dep * / FAS Sub-Sub-Department Number
Acct_Number / 6-Digit FAS account number
Acct_Description * / FAS account description.
Subcode_Or_Control / 4-Digit SL subcode or GL control code with description. Special values include “Fund Balance” to denote a beginning or ending GL fund balance, “Revenue Adjustment for Non-Zero Ending Balance” to denote a reduction in grant revenues for any unused portion of grant income, and “Revenue Calc’d from Proxy SL Expenses” to denote FY05 budgeted grant revenues calculated from the sum of Proxy SL grant expenses.
FY03_Actual / Actual dollar value of total FY2002-03 activity.
FY04_Budget / Dollar value of budgeted FY2003-04 activity.
FY04_Actual / Actual dollar value of total FY2003-04 activity.
FY04_Dollar_Variance / The difference between FY2003-04 budget and actual activity calculated as a whole dollar amount.
FY04_Pcnt_Variance / The difference between FY2003-04 budget and actual activity calculated as a percentage of FY2003-04 budgeted activity.
FY05_Budget / Dollar value of budgeted FY2004-05 activity.

Step 3: Fields in Direct-Detail, continued

Column / Description
Direct_Expense_Category / Subdivision of direct expenses activity identifying the type of expenditure, such as “Overtime” or “Supplies.” Useful for researching values within the Direct Expenses by Category section of the Consolidated Operating Budget report.
Direct_Expense_Sub_Category / Further subdivision of expense type allowing more refined analysis of activity within certain Direct Expense Categories, particularly “Officers of Instruction.”
Acct_Fund_Category / Denotes the type of account, for example “Private Gifts” or “Government Grants and Contracts”.

* - Values provided are those as of the end of the FY2003-04 June 31 reporting period unless the account did not exist in the unit at that time, in which case the FY2002-03 values are provided.

Step 4: Direct Expense Category Drill Down

Now that you have analyzed the COB lines, take another look at your direct expense activity and refer to your notes from Step 2. Were there any areas of the Direct Expense Category analysis (Personnel and OTPS) that deserve review?

Again, use the Direct-Detail tab, but this time instead of filtering by COB line, you can filter by Direct Expense Category (column P). You will be able to see the accounts who activity maps to various components of Personnel expense, etc.

Step 5: Indirect Activity Drill Down

The Indirect Activity drilldown is very similar to the analysis of Direct Activity, described above. Again, you should start with the list of COB lines (from Step 2) of Indirect Activity that you believe require further analysis. You can use the third tab, called “Indirect-Detail” to investigate activity on the account level that sums to each COB line of either indirect revenue or indirect expense.

You may recall that there are two main types of indirect activity—transfers and allocations. Transfers are GL to GL fund balance transfers, or similar funds flow carried out using SL accounts. Allocations represent the indirect relationship of another unit’s SL to your GL, or your SL to another unit’s GL. There are four categories of allocation activity:

1.Budget Allocation TO another unit: funding by your GL to cover another unit’s SL spending

2.Budget Allocation FROM another unit: funding by another unit’s GL to cover your SL spending

3.Revenue Allocation TO another unit: revenues from your SL providing funding for another unit’s GL

4.Revenue Allocation FROM another unit: revenues from another unit’s SL providing funding for your GL

Again, you will need to be comfortable with filters to take advantage of the data in the Indirect tab. Start by filtering by the COB line of interest to you, and then scroll to the right to see the accounts that contribute to that line. In addition, if you continue to scroll to the right, you will see the corresponding account in another management unit that is connected to your indirect activity.

Here are the columns of data you can select in the Indirect-Detail Tab:

Column / Description
COB_Line / COB Line number and description
Dep * / FAS Department Number
Sub_Dep * / FAS Sub-Department Number
Sub_Sub_Dep * / FAS Sub-Sub Department Number
Acct_Number / 6-Digit FAS account number
Acct_Description * / FAS account description.
Subcode_Or_Control / 4-Digit SL subcode or GL control code with description for transfers. This column is blank for allocations, which summarize activities across multiple SL subcodes.
FY03_Actual / Actual dollar value of total FY2002-03 activity.
FY04_Budget / Dollar value of budgeted FY2003-04 activity.
FY04_Actual / Actual dollar value of total FY2003-04 activity.
FY04_Dollar_Variance / The difference between FY2003-04 budget and actual activity calculated as a whole dollar amount.
FY04_Pcnt_Variance / The difference between FY2003-04 budget and actual activity calculated as a percentage of FY2003-04 budgeted activity.
FY05_Budget / Dollar value of budgeted FY2004-05 activity.

Step 5: Fields in Indirect-Detail, continued

Column / Description
Foreign_Acct_Number ** / 6-Digit FAS account number for the foreign, or offset, account.
Foreign_Acct_Desc ** / FAS account description for the foreign, or offset, account.
Foreign_SubCode_Or_Control / Foreign account’s 4-Digit SL subcode or GL control code with description for transfers. This column is blank for allocations, which summarize activities across multiple SL subcodes.
Foreign_Budget_Unit / Foreign account Budget Unit number and description.
Foreign_Mgmt_Unit / Foreign account Management Unit number and description.
Foreign_Dep ** / Foreign account FAS Department Number.
Foreign_Sub_Dep ** / Foreign account FAS Sub-Department Sumber.
Foreign_Sub_Sub_Dep ** / Foreign account FAS Sub-Sub-Department Number.
Foreign_Acct_Central ** / Foreign account “Central” or “Non-Central” designation.
Local_Acct_Fund_Category / Denotes the type of the local account, for example “Private Gifts” or “Government Grants and Contracts”.

* - Values provided are those as of the end of the FY2003-04 June 31 reporting period unless the account did not exist in the unit at that time, in which case the FY2002-03 values are provided.

** - Values provided are those as of the end of the FY2003-04 June 31 reporting period unless the only financial activity for the row took place in FY2002-03, in which case the FY2002-03 values are provided.

Key Points Relating to Grant Activity

A significant change in the presentation of grant activity has occurred due to the complex process required for loading fiscal year 2005 grant budgets into FAS, which is the source of the data in your workbook.

As you may recall, grant account budgets assigned by OPG to an account at its inception are project budgets, not fiscal year budgets. In order to create fiscal year budgets in FAS for FY2005 (this year), OMB and OTC created separate holding accounts called “proxy accounts” that contain estimates of all spending for government grant activity and private grant activity, respectively, for each COB line that has historically had activity for government grants or private grants. In other words, if a department has both private and government grant expenditures that are reflected on the “Instruction” expense line of the COB and only government grant expenditures that are reflected on the “Research” line of the COB, the department will be given a Private Grant proxy account to hold all private grant spending estimates associated with Instruction, a Government Grant proxy account to hold all government grant spending estimates associated with Instruction, and a Government Grant proxy account to hold all government grant spending estimates associated with Research.

What all of this means is that you will see historical spending activity for FY2002-03 and FY2003-04 on individual SL grant accounts, but FY2004-055 budgeted continued spending for that grant (plus all other government or private grant activity, plus all NEW government or private grant spending) will be summed together on the government or private grant proxy accounts.

Proxy accounts are representations of spending on only those grant accounts which are local to a management unit. Therefore, the estimates on your proxy accounts reflect only the SLs that you control (proxy accounts would not include spending by other management units on SLs that might map to one of your GLs). This proxy spending is reflected as direct expense activity in your COB in FY2005 budget, and it is also converted to a direct grant revenue estimate in your FY2005 budget, following the accounting rule that grant revenues are only recognized once related expenses have been incurred.

And here is the problem: historical presentation of direct grant revenue will reflect spending on SLs that you control as well as SLs that another unit uses if that foreign SL maps to one of your grant GLs. Going forward, the presentation of direct revenue budgets will NOT reflect the revenue to cover spending on an SL outside of your management unit, only the SLs that you control.

Thus, the treatment of proxy account estimates presents a comparability challenge at the direct revenue level for those management units that have funds flow with other management units on a grant(s)—i.e., if another unit has an SL that maps to one of your grant GLs, or if you spend on a grant SL that maps to another unit’s GL, there may be some challenges in analyzing historical direct grant revenue versus FY2004-05 budgeted direct grant revenue.

Your COB could show a decline in direct government grant or direct private grant revenue that is due to a shift in presentation only of the foreign SL’s activity.

We developed the Grant Summary Tab to summarize direct spending on grant SLs that belong to your Management Unit only (local SLs).