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:
- Download the RBL Template
- Create the Data File Using the Template
- Upload the Data File
- 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
- 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.
- Click Save as.
- 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.
- If applicable, click Enable Content.
- Click OK.
- 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.
- Verify UMICH displays in the Business Unit field.
Note: UMICH is the only acceptable value for this field.
- Verify RPT_BUDGET displays in the Ledger field.
- 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.
- Identify when the budget line is in effect using the following fields:
- Fiscal Year (e.g., 2009).
- Accounting Period (e.g., 2)
- 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.
- 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.
- Save the spreadsheet.
Note: Do not use special characters in the name of the file.
- Click Create Budget Upload File.
- Verify or enter the desired location for the data file and its name in the file path and name field.
- Click OK.
Required fields and field formats are noted on the template.
Important Information
Reporting Budget Leadger Template Screenshot
Reporting Budget Leadger Template (cont) Screenshot
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
- 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.
- Click Attach.
- Click Choose File.
- Navigate to and select the file.
- Click Open.
- Click Upload.
- Click Run.
- Select PSUNX from the Server Name drop-down list.
- Select the Type.
- MAC users: select Web.
- Windows users: select Window.
- Click OK.
Find an Existing Value Page Screenshot
Process Scheduler Request Page Screenshot
Windows Users:
- The following status pop-up windows appear:
- Queued
- Processing
- Success (or Error)
- Go to step 16.
Status Pop-Up Window Screenshot
Mac Users:
- Click Report Manager.
- Click the Administration tab.
- 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.
- Review the report, using the field descriptions in the next section, Verify the upload results, for reference.
- 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.
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.
|
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. |