Electronic Spreadsheet Budgeting

Overview

Budget and LRE data for expendables object codes, including non-pooled wage budgets, is gathered using Excel spreadsheets. These spreadsheets are used by budget managers for updating the current year LRE and requesting budgets for next year.

Network File Distribution

An Excel worksheet is generated for each FRS six-digit (SL) account. Worksheets are grouped by Responsible Person and placed in an Excel workbook. Workbook files are named for the Responsible Person and stored on the 'ourslu' Server in S:\X-Dept\Budget\. The files are stored in a number of folders organized to resemble the University administrative structure. There are Division and Responsible Person folders, and, in some cases, Mid-Level folders. The image below displays the network folder structure with selected folders expanded. 2FINANCIAL AID, 3YOUNG WRITERS, etc. are Mid-Level folders.

Responsible Person Folders

The Responsible Person Folder is the basic organizational level and contains an Excel file for each Responsible Person. Each file is in its own folder to allow for granting of network security access.

Responsible Person Files

Each Responsible Person File contains three or more worksheets; one for each account the person is responsible for, a summary of all of the accounts, and an Object Code table (described later). The image below displays an open Responsible Person File for someone who is responsible for three accounts.

Network Access

Each Responsible Person is granted access to their Responsible Person Folder. If an individual is responsible for accounts in more than one division or more than one Mid-Level group within a division, they will have multiple folders and will see them listed in the Excel File, Open... dialog box or in a Windows Explorer window.

Division Folders

Division Folders are organized based on the FRS accounting structure and will contain a number of files or folders.

Responsible Person Folders

The Division Folder contains Responsible Person Folders for any accounts that are not grouped in a Mid-Level Folder.

Original Workbook Folder

Copies of all of the Division's workbooks are placed in the Original Workbook Folder and may be used as a back-up by the Vice President if the files in other folders need to be replaced.

Approved Workbook Folder

After the data in a Responsible Person workbook file is complete and approved by the Vice President, it must be copied to the Approved Workbook Folder. Data is imported into FRS from the Approved Workbook Folders.

Mid-Level Folder

Mid-Level Folders contain the folders and files relating to the optional Mid-Level groups, if any. They are described in more detail below.

Working Workbook File

The primary purpose of the Working Workbook file is to provide a summary of the Division Level Responsible Person files. The file has a worksheet linked to the summary worksheet in each of the Division level Responsible Person files, and the first worksheet in the file is a summary of the Division Level files only, not the full division.

Actual Workbook File

The Actual Workbook file is similar to the Working Workbook file except that it is linked to the summary worksheet in each Responsible Person file in the division Approved Workbook Folder and provides a summary of the Division Level Approved Workbooks but not the full division.

Mid-Level Folders

Vice Presidents may choose to organize their division at a level between the responsible person and the division. This might be done simply to organize a long list of Responsible Person Folders or to provide a summary of activity for an operating unit, such as Facilities Operations, that has a number of Responsible Persons.

Responsible Person Folders

The Mid-Level Folder contains Responsible Person Folders for any accounts that are grouped in that Mid-Level.

Working Workbook File

The primary purpose of the Working Workbook file is to provide a summary of the Mid-Level data. The file has a worksheet linked to the summary worksheet in each of the Mid-Level's Responsible Person files. The first worksheet in the file is a summary of the Mid-Level based on the linked files.

Actual Workbook File

The Actual Workbook file is similar to the Working Workbook file except that it is linked to the summary worksheet in each Approved Workbook and provides a summary of the Approved Workbooks.

File Naming Convention

File names consist of three sections: version number, file name, and optionally, a location reference.

Version Number

Each file has a version prefix separated from the file name by a hyphen (-) in the format VERSIONTEXT-FILENAME.xls.

File Name

Responsible person files are named using the FRS responsible person name in the format LAST, FIRST.xls. Actual and Working Workbook files are named Actual.xls and Working.xls.

Location Reference

If the division uses mid-levels, Responsible Person folders and files and Actual and Working summary files may exist in more than one folder within a division. Mid-Level files are differentiated from division level files and other mid-level files by a Mid-Level suffix appended to the end of the file name separated by a hyphen (-) in the format VERSIONTEXT- FILENAME-LOCATION.xls

Excel File Structure

Account Worksheet(s)

There is one Account Worksheet in the Responsible Person Workbook for each FRS six-digit (SL) account. It is the basic building block of the system where individual account data is displayed and entered. Individual worksheets (Tabs) are identified by the account number.

Summary Worksheet

The Summary Worksheet is in the BudSummary tab of the workbook, which is the first worksheet in the workbook. It summarizes by object code the data in lower level worksheets. In Responsible Person workbooks, it summarizes the Account Worksheets in that workbook. In Division and Mid-Level Workbooks, it summarizes the data from the Account Worksheets in that level.

ObjCdTable

This worksheet is used primarily for validation of new object codes, but may be used as a resource for names and numbers of valid object codes. It is the last tab in each workbook.

Data Entry

Data entry is done directly in the Account Worksheet for each FRS account. Historic data is displayed in columns for the prior year actual and current year budget, actual, and current LRE. Data input columns are provided for the updated LRE and next year budget. Analysis columns display the dollar and percentage increases for the updated LRE versus the current budget and next year's budget versus the current budget and updated LRE.

There are rows of data for each object code in use as well as blank rows for additional object codes. Column totals are displayed at the top of the worksheet in addition to the column headers. The worksheet is protected and data may be entered only in the Description, Updated LRE, future Budget, and Comment columns and in the Object Code column for new Object Codes. The data input areas are validated, allowing only certain kinds of data.

Data Summary and Analysis

Summary and analysis is provided by the Budget Summary Worksheets at the various levels. Input is not allowed except in the Comment column. Totals may be updated by pressing the "Refresh Totals" button in the upper left hand corner of the worksheet. Caution should be used when refreshing totals for the Division and Mid-Level Actual worksheets, however. See the Data Upload section in the Step-By-Step Instructions for details.

Data Upload

Data is uploaded from the Approved Workbooks Folders after the data entry period ends. The Budget Office initiates this action.

Step-By-Step Instructions

Data Entry

Data entry is done in the Account Worksheets in the Responsible Person Workbook. By default, the Responsible Person has network security access to them as does the Vice President and anyone with access to the Division or Mid-Level Folders. If someone else needs access to the Responsible Person Folder, contact the Budget Office.

Opening Files

1. Start Microsoft Excel and select File, Open... from the menu or Toolbar.

2. In the Look in: box of the Open dialog box, drill down to S:\X-Dept\Budget

3. Folders are listed by division. If you have accounts in more than one division, you will also have folders and files in more than one division.

4. Continue drilling down through the folder structure until you reach the desired Responsible Person file and double-click on it or press Open. You may find your files in any or all of the three folder levels.

5. An Excel box may appear warning of macros. Press Enable Macros.

6. The workbook will open and you will see three or more worksheet tabs.

7. Select one of the account tabs to begin data entry.

Data Entry

1. Enter new data in the appropriate columns: G for Updated LRE or J for next year's Budget.

2. Initially the data input columns are blank, but data may be entered directly or copied from other columns as appropriate.

Note: Data must be entered whether it changes or not. Blank amounts will be treated as zeros when data is loaded into FRS.

3. Amounts entered must be in whole dollars. You will receive a data validation error if you enter decimals or fractions.

4. After new data is entered, the dollar and percent increase columns are automatically recalculated, as are the column totals.

5. Comments may be entered in column O for your use or others accessing the file. They will not be available at summary levels or uploaded to FRS however.

Adding Object Codes

New Object Code lines may be added by entering data in the appropriate columns in the rows below the existing data. Please make sure the new object code selected is appropriate for the type of expenditure. A valid object code is required, but an object description may or may not be entered. If a description is not entered, the default description will be used. If a description is entered, only the first 35 characters will be uploaded to FRS. Do not use single or double quotes (' or ") in the description. Only Updated LRE and next year's Budget data may be added. Formulas for the dollar and percent increase columns are pre-filled and the data will be included in the column totals.

Changing Object Code Descriptions

Object Code descriptions may be changed to provide more meaningful labels. Simply replace or modify the text in column B. If a description is entered, only the first 35 characters will be uploaded to FRS. Do not use single or double quotes (' or ") in the description.

Data Summary

Responsible Person Level

The BudSummary worksheet in the Responsible Person workbook maintains totals for all of the account worksheets in the workbook. It is linked by formulas to each account worksheet in the workbook but it is not recalculated automatically. To recalculate the worksheet, click the Refresh Totals button. The worksheet is also recalculated when the workbook is saved. All of the columns in this workbook except Comments are protected.

Mid-Level

The Mid-Level data summary is found in the Mid-Level Working workbook file. The Responsible Person worksheets in the workbook are linked to the summary worksheet in each of the Mid-Level's Responsible Person files. The BudSummary worksheet is a summary of those Responsible Person summaries. The worksheets are display only, data may not be entered.

Pressing the Refresh Totals button in the BudSummary worksheet will update the links to the Responsible Person files. Pressing the Link to Workbook button in a Responsible Person worksheet will open the Responsible Person file.

Opening Files

1. Drill down to the file using the file open instructions in Data Entry above.

2. An Excel box will appear warning of macros. Press Enable Macros.

3. An Excel box may appear asking if you want to update linked information. Press Yes.

4. An Excel box will appear asking if you want to Relink and recalc? Press Yes.

5. After the recalculation process, the BudSummary worksheet displays updated totals for the Mid-Level and the Responsible Person worksheet(s) display updated totals for the Responsible Person files.

Division Level

The BudSummary worksheet in the Division Working Workbook file is a summary of the Responsible Person workbooks in the Division Folder and is linked to the summary worksheet in each of the Division's Responsible Person files. There is no division-wide summary. The worksheets are display only, data may not be entered.

Opening Files

1. Drill down to the file using the file open instructions in Data Entry above.

2. An Excel box may appear warning of macros. Press Enable Macros.

3. An Excel box will appear asking if you want to update linked information. Press Yes.

4. An Excel box will appear asking if you want to Relink and recalc? Press Yes.

5. After the recalculation process, the BudSummary worksheet displays updated totals for the Division Level Responsible Person files and the Responsible Person worksheet(s) display updated totals for each Responsible Person file.

Data Upload

The Approved Workbooks Folder is key to this process. Division-Level budget managers are responsible for monitoring and coordinating the activity of their Responsible People. When the Vice President and the Responsible Person agree on the LRE and Budget data in the Responsible Person's accounts, the Vice President signifies approval by moving or copying the Responsible Person Workbook to the Approved Workbooks Folder. The decision of whether to move or copy the file may hinge on a number of considerations:

· If the file is simply moved, there will be no record of the approved data in the Responsible Person Folder.

· If the file is copied, it will not be obvious that the data is complete and has been approved when looking at the files in the folder.

· A compromise might be to copy the file to the Approved Workbooks Folder and rename the original file to indicate that it has been approved.

· If the file is moved or renamed, the link to the Working Workbook file will be broken. You will not be able to recalculate totals in the Working Workbook file.

The Actual Workbook File in the Division or Mid-Level Folder may be used to determine if all of the expected workbooks have been placed in the Approved Workbooks Folder. The Actual Workbook File contains links to each of the Responsible Person Workbooks that should be in the Approved Workbooks Folder. When the Actual Workbook File is opened, you will be asked if you want to update linked information. If yes is selected, the update process will look for each expected workbook and return a File Not Found window for each file missing form the folder. You must Cancel the window for each file, however, which may be a long and tedious process if a large number of files are missing. Clicking on the Refresh Totals button will also begin the link update process.

After the close of the data entry period, data is extracted from the approved workbooks and entered into FRS. The Budget Office initiates this process. The approved Responsible Person Workbooks remain in the Approved Workbooks Folder after the data upload as a resource for the Vice President.

Back-Up and Recovery

A copy of each workbook is placed in the Original Workbooks Folder. These files may be used as a reference to determine what the file looked like originally, as a back-up to replace a file that has been damaged, or as a means to revert to the original data. Copy the file from the Original Workbooks Folder to the destination folder, replacing the existing file. Be careful to copy, not move, so that a copy of the file is left in the Original Workbooks Folder. If the Responsible Person has files in more than one folder, make sure you have the correct file.

Working "Off-Line"

Responsible Person Workbooks may be copied to a disk or other location to allow work where the network is not available. Most, but not all files, will be small enough to fit on a 1.44 mb floppy disk. Care must be taken to copy the updated files back to their original locations with their original names. Totals in summary files will not be updated until the file is copied back to the original folder. Do not move files in or out of the folders.

Actual and Working Workbook files will be too large to copy easily and will be of limited use because they rely on linked data which will probably not be available.