FY2003-04 Budget vs - Columbia University



FY04/FY05 MANAGEMENT UNIT LEVEL VARIANCE ANALYSISOPERATING ACTIVITY QUARTERLY DETAIL FILE

CUMC Budget Units and Pre-Clinical and Clinical Management Units

User Guide

This user guide accompanies the Quarterly FY04/FY05 Operating Activity Detail File available to University financial officers via the Financial Management Reporting web site. The file is in Excel format and provides 10-digit account detail for the information summarized in the FY04/05 Operating Activity Reports accessible via same site.

Step 1: Download the Detail File

The Financial Management Reporting web site can be accessed by either of two methods:

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

from the “Reports” menu.

2. 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, choose report type “Quarterly Detail File” and follow the remaining instructions on-screen. Save the file you retrieve from this Web site to your own PC for further use.

If you are not able for any reason to access your Detail File, send e-mail to managementreporting@columbia.edu.

Step 2: Analyze Your Quarterly ResultsThis 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).

Contents Overview

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

|Tab |Description |

|COB – Total |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 |

|Detail – Direct |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) |

|Detail – Indirect |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 |

The Detail File allows you to identify the individual accounting entries that rolled into the summary data presented in your FY04/FY05 Operating Activity Reports.

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 in those reports, look for COB lines with significant % changes in year-over-yearYTD activity from FY04 to FY05 % change (column I), FY2003-04 budget to actual dollar variances, and significant % change from FY2003-04 to budgeted FY2004-05 and those with significant forecast variances from budget. 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 FY20024-035 YTD to FY20034-045 YTD, and Private Gifts on FY20034-045 budget to actualOriginal Budget, etc.

Also, review the Direct Expenses by Category at the bottom of the COB-Total Tab (on the last page if you printed it) of your reports. 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.

Those managers responsible for Budget Units that contain multiple Management Units should refer to the Addendum at the end of this document. It contains instructions for the use of the “COB Line – By Mgmt Unit” tab available in their Detail Files. This special tab can be used to isolate the contribution of each Management Unit’s performance to the overall Budget Unit as an initial step in analyzing quarterly results.

Step 3: 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 labeled “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 Eexcel. 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, creatinGg 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 |LabelColumn |Description |

|B |COB_Line |COB Line number and description |

|C |Management_Unit 1 |Management Unit number and description |

|D |Dep *2 |FAS Department Number |

|E |Sub_Dep 2* |FAS Sub-Department Number |

|F |Sub_Sub_Dep 2* |FAS Sub-Sub-Department Number |

|G |Acct_Number |6-Digit FAS account number |

|H |Acct_Description 2* |FAS account description. |

|I |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. |

|J |FY043_Sep__YTD 3Actual |Actual dollar value of total FY2002-03 FY2003-04 YTD activity. |

|K |FY04_BudgetFY05_Sep_YTD 3 |Actual dollar value of total FY2004-05 YTD activity.Dollar value of budgeted FY2003-04 |

| | |activity. |

| |FY04_Actual |Actual dollar value of total FY2003-04 activity. |

|L |FY04_Dollar_VarianceYTD_Dollar_Change |The differencechange between between FY2003-2003-04 budgetYTD and FY2004-05 YTD and |

| | |actual activity calculated as a whole dollar amount. |

|M |FY04_Pcnt_VarianceYTD_Pcnt_Change |The change between FY2003-04 YTD and FY2004-05 YTD activity calculated as aThe |

| | |difference between FY2003-04 budget and actual activity calculated as a percentage of |

| | |FY2003-04 budgeted activity of FY2003-04 YTD activity. |

|N |FY04_Actual |Actual dollar value of total FY2003-04 activity. |

|O |FY05_Orig_Budget |Dollar value of original budget for FY2004-05 activity. |

|P |FY05_Cur_EstimateBudget |Dollar value of updated current estimate budgetedfor FY2004-05 activity. The values in |

| | |this column will equal FY05_Orig_Budget unless modified in FFE by the unit. |

Step 3: Fields in Direct-Detail, , continued

|Column |ColumnLabel |Description |

|Q |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. |

|R |Direct_Expense_Sub_Category |Further subdivision of expense type allowing more refined analysis of activity |

| | |within certain Direct Expense Categories, particularly “Officers of Instruction.” |

|V |Acct_Fund_Category |Denotes the type of account, for example “Private Gifts” or “Government Grants and |

| | |Contracts”. |

1 Shown only in files for Budget Units with multiple Management Units.

2 * - Values provided are those as of the end of the FY2003-04 June 31FY05 quarter reporting periodend unless the account did not exist in the unit at that time, in which case the FY20023-043 year end values are provided.

3 Column label will change each quarter to reflect the appropriate month.

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 PQ). You will be able to see the accounts whowith activity that 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 labeled “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 |ColumnLabel |Description |

|B |COB_Line |COB Line number and description |

|C |Management_Unit1 |Management Unit number and description |

|D |Dep 2* |FAS Department Number |

|E |Sub_Dep 2* |FAS Sub-Department Number |

|F |Sub_Sub_Dep 2* |FAS Sub-Sub Department Number |

|G |Acct_Number |6-Digit FAS account number |

|H |Acct_Description 2* |FAS account description. |

|I |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. |

|J |FY04_Sep_YTD 3 |Actual dollar value of total FY2003-04 YTD activity. |

|K |FY05_Sep_YTD 3 |Actual dollar value of total FY2004-05 YTD activity. |

|L |YTD_Dollar_Change |The change between FY2003-04 YTD and FY2004-05 YTD activity calculated as a |

| | |whole dollar amount. |

|M |YTD_Pcnt_Change |The change between FY2003-04 YTD and FY2004-05 YTD activity calculated as a |

| | |percentage of FY2003-04 YTD activity. |

|N |FY04_ActualFY03_Actual |Actual dollar value of total FY2003-04 activity.Actual dollar value of total |

| | |FY2002-03 activity. |

|O |FY05_Orig_BudgetFY04_Budget |Dollar value of original budget for FY2004-05 activity. 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 |ColumnLabel |Description |

|P |FY05_Cur_Estimate |Dollar value of updated current estimate for FY2004-05 activity. The values |

| | |in this column will equal FY05_Orig_Budget unless modified in FFE by the |

| | |unit. |

|Q |Foreign_Acct_Number 4** |6-Digit FAS account number for the foreign, or offset, account. |

|R |Foreign_Acct_Desc 4** |FAS account description for the foreign, or offset, account. |

|S |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. |

|T |Foreign_Budget_Unit 4 |Foreign account Budget Unit number and description. |

|U |Foreign_Mgmt_Unit 4 |Foreign account Management Unit number and description. |

|V |Foreign_Dep 4** |Foreign account FAS Department Number. |

|W |Foreign_Sub_Dep 4** |Foreign account FAS Sub-Department Sumber. |

|X |Foreign_Sub_Sub_Dep 4** |Foreign account FAS Sub-Sub-Department Number. |

|Foreign_Acct_Central ** |Foreign account “Central” or “Non-Central” designation. |

|AC |Local_Acct_Fund_Category |Denotes the type of the local account, for example “Private Gifts” or |

| | |“Government Grants and Contracts”. |

1 Shown only in files for Budget Units with multiple Management Units.

2* - Values provided are those as of FY05 quarter end unless the account did not exist in the unit at that time, in which case the FY2003-04 year end values are provided.

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.

3 Column label will change each quarter to reflect the appropriate month.

4** - Values provided are those as of the end of the FY2003-04 June 31 of FY05 quarter end reporting period unless the only financial activity for the row took place in FY20023-034, in which case the FY20023-034 year end 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 was 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 FY2003-04 YTD, FY2004-05 YTD, and FY2003-04 Full Year ActualFY2002-03 and FY2003-04 on individual SL grant accounts, but FY2004-055 budgeted Original Budget and Current Estimate continued spendingforecast 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 FY2004-055 budgetforecasts, and it is also converted to a direct grant revenue estimate in your FY2004-055 budgetforecasts, 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 forecast 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).

One reason you may want to use this tab is to investigate just the type of direct grant revenue issue we described above. Let’s say that your Total COB Tab showed a decline in direct government grant revenue from FY2004 to FY2005. Knowing that direct revenue is a function of direct spending, you may come to the Grant Summary Tab for a summary of grant spending.

But first, before you even use this grant summary data, go to your Indirect Detail tab and filter by by Fund Category (Government Grant or Private Grant). If significant financial activity is found within the resulting subset of indirect activity, this indicates that there are either grant SLs mapping to your GLs (budget allocations TO another unit) or your SLs mapping to someone else’s GL (budget allocations FROM another unit)—either way, the net of this indirect activity will be reflected as a component of direct grant revenue for FY2003-04, but not for FY2004-05. The data on the Indirect tab will tell you the magnitude of the activity that is outside your Management Unit. Adjusting for that portion which is indirect, you can now go on to check the estimates you provided for just the direct spending on your own grant SLs using the Grant Summary Tab.

On the Grant Summary Tab, there are report filters to help you manage the data set of all of your accounts with grant activity. You can choose to look at Private Grants, Government Grants, or both (for both, choose “All”). You can look at those grant accounts that map to various COB lines (like “Research” or “Instruction”) or to any COB line (choose “All”). You should disregard the “Central” filter option.

|Report Filters |Description |

|Fund_Category |Type of grant activity, either “Private_Grants_And_Contracts” or “Government_Grants_And_Contracts” |

|Default_COB_Line |Default COB line for expenses on the account, for example “III.A.2 – Research”. In the COB –Total report, any |

| |activity in expense category Major Equipment will appear on line V.A.1. Any activity in the expense category |

| |Non-Tuition Financial |

| |Aid will appear on line III.A.1.d. All other expenses will be shown on the default COB line. |

|Central |“Central” or “Non-Central” (disregard—all your accounts are “Non-Central”) |

Addendum For Budget Units with Multiple Management Units:

Identify which Management Units contribute to the COB Lines Identified in Step 2

Start with the list of COB lines for further analysis that you identified in Step 2. Go to the COB Line-By Mgmt Unit tab. You will want to use filters to select a COB Line (column C) and then look in Column D for the management units’ contribution to activity on that COB line.

The fields of data that you will see on this tab are:

|Column |Label |Description |

|A |Activity_Type |Direct or Indirect |

|C |COB_Line |COB line number and description. |

|D |Management_Unit |Management Unit number and name. |

|E |FY04_Sep_YTD* |Actual dollar value of total FY2003-04 YTD activity. |

|F |FY05_Sep_YTD* |Actual dollar value of total FY2004-05 YTD activity. |

|G |YTD_Dollar_Change |The change between FY2003-04 YTD and FY2004-05 YTD activity calculated as a whole |

| | |dollar amount. |

|H |YTD_Pcnt_Change |The change between FY2003-04 YTD and FY2004-05 YTD activity calculated as a |

| | |percentage of FY2003-04 YTD activity. |

|I |FY04_Actual |Actual dollar value of total FY2003-04 activity. |

|J |FY05_Orig_Budget |Dollar value of original budget for FY2004-05 activity. |

|K |FY05_Cur_Estimate |Dollar value of updated current estimate for FY2004-05 activity. The values in this |

| | |column will equal FY05_Orig_Budget unless modified in FFE by the unit. |

* - Column label will change each quarter to reflect the appropriate month.

You can pose questions to your Management Units by COB line, and they can investigate further which accounts in their Management Unit contribute to the variance. Alternatively, if you want to dig into a COB-line variance yourself at the account level, you can use the Detail-Direct and Detail-Indirect tabs to do so—simply follow the guidelines above.

After you have chosen the report parameters, you will see grant accounts listed in rows on the left, expense categories going across in columns leading to a total for the account to the right, and a total by expense category across accounts at the bottom of each subcode column. Scroll down the page so you can see how data is also arranged by fiscal period.

To orient yourself, you may want to focus on the totals for the fiscal period, so scroll all the way to the left to the “Grand Total” column and the down the page to see the summary of actual activity in FY2003 and FY2004, and then proxy estimates in FY2005. Do these look reasonable? Do you understand the fluctuations? You can review the expense category detail or filter by account to get a more granular view of the characteristics of historical spending, and compare that to your “aggregate” proxy account estimates. Your conclusions will be helpful to share with the Office of Budget and Finance.

One important user note about this tab is that the size of the report is dynamic depending on the report filters you set, etc. The screen size “re-sets” to adjust to the size necessary. Always scroll to the top and to the left after changing report parameters to make sure you begin with a view of the entire data set.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download