DEPARTMENT QUERY REFERENCE GUIDE

The Department Query displays real time data on a specific department for a selected fiscal year period. The data can be viewed in HTML format or downloaded into Excel. The data is broken down by Parent Pools and displays revenue and expenses. Data within the Budget, Actual, Encumbrance, and Pre-Encumbrance columns are hyperlinked to display additional data on the selected value.

  1. From within FMS, navigate to Main Menu > Reporting Tools > Query > Query Viewer.

  1. On the Query Viewer screen enter the name of the query to access in the begins with field.For the Department Query, enter MSU_DEPT_BALANCES_BY_ACCT, and click the Search button.

The system displays a list of all queries that match the searched on name.

  1. To view the results of the query, click the HTML link in the Search Results section. To download the query data to Excel, click the Excel link in the Search Results section.

The Department Balances Query screen display.

  1. Enter your Department number in the Dept. field, and the starting and ending fiscal year in the Fiscal Year From and Fiscal Year To fields.
  2. Click the View Results button.

The system displays the data for the selected department and fiscal year.

The results of the query are displayed by Pool Account and are totaled at the end of each Pool. The initial pool displayed is Revenue, if it is applicable to the department selected. The Expense Pools are listed next, and are sub-totaled at the Pool level for the accounts within that Pool. A grand total of all expenses is listed at the bottom of the query.

The hyperlinks provided in the Budget, Actual, Encumbrance, and Pre-Encumbrance columns provide additional details of the selected value. When you select a link, a new tab will open. To return to the original query results, click the tab with the address on it.

The Budget column displays the original budget amount and any adjustments which have been applied.

(Drill down into Budget link)

The Actuals column contains the Recognized Revenue for the Revenue pools and the Actual Expenses for the Expense Pools.

(Drill down into Actual link within Revenue Pool)

(Drill down into Actual link within Expense Pool)

Encumbrance shows the detailed transactions behind the balance which includes the encumbrance(increase) and liquidation of the encumbrance (decrease).

(Drill down into Encumbrance link)

Pre-Encumbranceshows the detailed transactions behind the balance which include pre-encumbrance (increase) and liquidation of the pre-encumbrance (decrease).

(Drill down into Pre-Encumbrance link)

In order to reconcile the data within the system query, download the data using the links in the upper left corner of the results screens.

  1. Click the Download results in: Excel SpreadSheet link.

A dialog box regarding opening the file displays.

  1. Confirm the Open with Microsoft Excel line is selected, and click OK.
  2. When the Excel file opens, click the Enable Editing button at the top of the screen, if it displays, to allow changes to be made within the spreadsheet.

Dollar values for the Budget, Actual, Encumbrance, and Pre-Encumbrance columns are in the far right column, entitled Transaction Amount, within the download. The transaction values are downloaded as text, and MUST be converted to numeric values before any analysis can be performed on these values.

CONVERT TEXT COLUMN TO NUMERIC VALUES

  1. Select the column to be converted by clicking on the letter above the targeted column.
  1. Click the Data tab in the Ribbon.
  2. Click the Text to Columns icon.

The Convert Text to Columns Wizard dialogue box opens.

  1. Confirm the Delimited option is selected and click the Finish button.

The data converts into numeric values.