Reporting Budget Ledger Upload Process

Overview

The Reporting Budget Ledger (RBL) provides a method for units to add and/or update non-sponsored Funds revenue and/or expense budget data for reporting purposes. The benefit of using the RBL is in having one system capturing both the budget and actuals data. This allows you to:

  • Compare budget data to actual revenue and expense data (variance reporting).
  • View budget data using standard reports.

To use the RBL, complete each of the following parts of the process:

  1. Download the RBL Template
  2. Create the Data File Using the Template
  3. Upload the Data File
  4. Verify the Upload Results

Important Information

Non-sponsored fund budget lines are entered for a ChartField combination and a particular Fiscal Year and Accounting Period. Once entered, adjustments to the budget amount can be handled one of two ways:

  • Increasing (or decreasing) the existing budget amount by uploading an adjustment transaction (positive or negative) to the existing budget lines.
  • Checking the Replace Budget Amount With Amount From File box when performing the load. The upload process overwrites the existing budget line(s) with the new transaction amount.

Posted budget lines cannot be deleted, but can be hidden or zeroed-out by adjusting the dollar amount to be $0.00 using one of these two methods.

The following reports display RBL data. Each displays the detailed budget and actuals data by the Account Group roll-ups based on the UM_ACCT_GROUP tree.

  • CMB Reporting Bdgt Ledger NonSponsored Reconciler by Fund/Dept/Prgm (Business Objects)
  • CMB Reporting Bdgt Ledger NonSponsored Reconciler by PG (Business Objects)
  • Income Statement Variance by Dept/Dept Group (MGLN075D) (nVision)

Download the RBL Template

  1. Download the Report Budget Ledger Template from the M-Pathways Reporting Budget Ledger website.

    Note: The file is an Excel spreadsheet that can be reused each time you submit non-sponsored Fund budget lines or adjustments to existing budget lines.

  2. Click Save as.
  3. Save the spreadsheet to the desired location to use as a template file.

Create the Data File Using the Template

Budget lines can be entered for annual, quarterly, or monthly budgeting purposes depending on the Accounting Period you select. Enter revenues as negative numbers (e.g., -2,000.00) and expenses as positive numbers (e.g., 3,000.00).

  • For an annual budget, enter the net total amount of the budget line with an Accounting Period of 1.
  • For a quarterly budget, divide the net total amount of the budget between four budget lines. Using the University’s Fiscal Year as an example, the first budget line has an Accounting Period of 1 and subsequent budget lines for the quarters have Accounting Periods of 4, 7, and 10, respectively.
  • For a monthly budget, divide the net total amount between 12 budget lines each with the applicable Accounting Period.

Existing budget lines cannot be deleted, but can be incrementally adjusted. “Zeroing out” results in a net amount of $0.00 for the budget line, which “deletes” it for reporting purposes, but doesn’t remove the ChartField combination from the Reporting Budget Ledger.

When you upload a completed spreadsheet, the Reporting Budget Ledger identifies which ChartField combinations have already been loaded and adjusts the amount associated with those budget lines by the amount in the Posted Total Amt field. The table below indicates the action you would take to adjust an existing budget line.

Type of Budget Line Change Action
Revenue "Zero Out" Enter the total amount of the budget line as a positive value.
Increase Enter the difference between the existing total and the desired total as a negative value.
Decrease Enter the difference between the existing total and the desired total as a positive value.
Expense "Zero Out" Enter the total amount of the budget line as a negative value.
Increase Enter the difference between the existing total and the desired total as a positive value.
Decrease Enter the difference between the existing total and the desired total as a negative value.

Open the Reporting Budget Ledger spreadsheet template that you stored on your computer.

  1. If applicable, click Enable Content.

    2. Security Warning.

  2. Click OK.

    3. Required fields and field formats are noted on the template.

    Important Information

    • Do not re-format columns.
    • Leave unused fields blank.
    • Enter full values, including leading zeros (if applicable).
    • When you create the data file, the macro process overwrites the previous file, unless you uniquely name each file. The default name for the data file is BUDLOAD.DAT.
    • Data files must have a file extension of .DAT.
    • Save the completed spreadsheet to process any error corrections from the upload process.
  3. Verify UMICH displays in the Business Unit field.

    Note: UMICH is the only acceptable value for this field.

    4. Reporting Budget Leadger Template

  4. Verify RPT_BUDGET displays in the Ledger field.
  5. Enter applicable ChartField information for the budget line:
    • Account
    • Fund
    • DeptID
    • Program
    • Class
    • Project/Grant

    Note: Applicable revenue Account values begin with 4. Applicable expense Account values begin with 5 or 6.

  6. Identify when the budget line is in effect using the following fields:
    • Fiscal Year (e.g., 2009).
    • Accounting Period (e.g., 2)
  7. Enter the dollar amount associated with the budget line in the Posted Total Amt field (e.g., 11500).

    Note: This field is formatted to automatically apply the comma(s) and decimal point to the value you enter.

  8. Copy the Business Unit and Ledger field values into the next row(s) for any subsequent budget line(s).

    Note: Repeat steps 6 – 8 for each row of data in the spreadsheet.

    9. Reporting Budget Leadger Template (cont)

  9. Save the spreadsheet.

    Note: Do not use special characters in the name of the file.

  10. Click Create Budget Upload File.
  11. Verify or enter the desired location for the data file and its name in the file path and name field.

    12. Reporting Budget Leadger Template (cont)

  12. Click OK.

    13. Continue to the Upload the Data File section.

Upload the Data File

The upload process verifies whether the data file contains errors and results in status report.

  • If no errors are found, the budget lines are loaded.
  • If one or more errors occurred, none of the lines are loaded.

Refer to the Verify the Upload Results section to learn how to identify and correct errors.

Navigation: Main Menu > General Ledger > Ledgers > Load Reporting Budget Ledger

  1. Click Search.

    Notes:

    • If you do not have a Run Control ID, refer to M-Pathways Run Control ID Information for more information.
    • If you only have one Run Control ID, the system automatically selects it when you click Search.
    • If you have multiple Run Control IDs, select the applicable value from the Search Results.

    2. Find an Existing Value Page

  2. Click Attach.

    3. Parameter's Page

  3. Click Choose File.

    4. Choose File

  4. Navigate to and select the file.
  5. Click Open.
  6. Click Upload.
  7. Click Run.

    8. Run

  8. Select PSUNX from the Server Name drop-down list.

    9. Process Scheduler Request Page

  9. Select the Type.
    • MAC users: select Web.
    • Windows users: select Window.
  10. Click OK.

Windows Users:

  1. The following status pop-up windows appear:
    • Queued
    • Processing
    • Success (or Error)

    2. Status Pop-Up Window

  2. Go to step 16.

Mac Users:

  1. Click Report Manager.
  2. Click the Administration tab.
  3. Click the Load Reporting Budget Ledger link in the Description column to open the report.

The report displays in a separate window. It is also saved in and can be retrieved from your Report Manager for 60 days.

  1. Review the report, using the field descriptions in the next section, Verify the upload results, for reference.
  2. Click Print on your browser’s toolbar to print the report or Save to save the report to any folder on a computer drive to which you have access.

Verify the Upload Results

The RBL upload process results in a status report. Review the report to determine if corrections need to be made.

When the upload process is successful, the Report Budget Ledger Update report displays high level information: the file name, the total number of budget lines in the file, the number of existing budget lines updated (i.e., adjusted), and the number of new budget lines inserted (i.e., loaded).

When the upload process fails, the Report Budget Ledger Update report contains specific error information. To correct errors:

  • Review each error message in the report.
  • Open your saved (completed) Report Budget Layout Template and fix the incorrect budget line data as applicable.
  • Create the new data file.
  • Upload the data file.

Status Pop-Up Window

Letter Field Description
A Input file name Displays the name of the data file (e.g., BUDLOAD.DAT).
B Row# List each budget line that contains an error by its row number in the data file.
Note: The Accounting Period displays as a three-digit number in the Per field.
C Error Show the reason the budget line is incorrect and explains why the budget upload process failed (see the list of upload errors below for more information).
D Load Summary No budget lines are loaded from the data file when the system encounters an error during the upload process.
  • Total Lines indicates how many budget lines were in the data file.
  • Error Lines show the number of budget lines that contained an error.

The following table describes the possible errors that may appear on the Report Budget Ledger Update report.

Field Error Message Error Description/Correction
Ledger No Ledger value on Ledger row The required Ledger field is blank. Enter RPT_BUDGET in the field.
Account No Account value on ledger row The required Account field is blank. Enter an applicable value in the field.
Fund No Fund value on ledger row The required Fund field is blank. Enter an applicable value in the field.
Dept No Dept value on ledger row The required DeptID field is blank. Enter an applicable value in the field.
Fiscal Year No Fiscal Year value on ledger row The required Fiscal Year field is blank. Enter an applicable value in the field.
Accounting Period No Accounting Period value on ledger row The required Accounting Period field is blank. Enter an applicable value in the field.
Amount No Posted Total Amount value on ledger row The required Amount field is blank. Enter an applicable value in the field.
Business Unit Business Unit is Not Valid The value in the Business Unit field is incorrect. Enter UMICH in the field.
Account Account is Not Valid The value entered in the Account field is not an active value or not found in the Account table. Enter a different value.
Account Account Type is Not Valid (must be Revenue or Expense) The value entered in the Account field is allowed for the RBL process. Enter an applicable value beginning with a 4 (Revenue), 5 or 6 (Expense).
Fund Fund is Not Valid The value in the Fund field is not an active value or not found in the Fund table. Enter a different value.
Dept Department is Not Valid The value in the DeptID field is not an active value or not found in the Department table. Enter a different value.
Program Program is Not Valid The value in the Program field is not an active value or not found in the Program table. Enter a different value.
Class Class is Not Valid The value in the Class field is not an active value or not found in the Class table. Enter a different value.
Project/Grant Project_ID is Not Valid The value in the Project/Grant field is not an active value or not found in the Project/Grant table. Enter a different value.