Setting Up Spreadsheets for Budget Journals

Overview

This process is used for the agency budget processor to enter the adjustment budget journals into OAKS FIN using an Excel spreadsheet.

Downloading files

Steps

• The files necessary to create spreadsheet journal entries are available from the OBM Forms page under "Spreadsheet Journal Upload Files."

Click here for file installation instructions.

1. For each file listed, right click on its link and select "Save target as ..." Then choose where the files should be saved (for example, go to "My Documents" and then create a new folder called "Spreadsheet Journal Upload Files" where they are placed).

• All of the files (except the two PDFs) are necessary to make the application run properly; and, the files must be stored together in one folder for the spreadsheet journal entry to work. However, for this process, only open and make changes to BudgetJournalUpload.xlsm.
  
  Do not open or change the other files. Agencies should also not change these files.

• When a spreadsheet journal entry is created, it will be creating an Excel file. Each agency has a specific location to which files should be saved. Ask others in your agency where these should be saved.

Opening the spreadsheet

2. Find the Excel file named BudgetJournalUpload.xlsm and open it.

• Excel opens and the Budget Journal Upload control page displays.

• If Excel displays a Security Warning, choose to Enable Editing and/or Enable Content. This warning is caused by macros contained in the file to make it work properly.

When first opened, the Budget Journal Upload control page displays three sections:

• General - These buttons are used to set up journal header information. Data is entered a single time and then it automatically displays in the spreadsheet when creating new journals.

• Journal Sheets - These four buttons are used to create, edit, delete, and copy spreadsheet journals.

• Import Journals - Agency GL approvers use the Write File button to create a file to upload into OAKS FIN.

Setup

There are two ways (with different purposes) that agency users may set up header information for a spreadsheet journal.

• Define Options and Defaults - Allows the agency user to set up information which automatically populates each time the spreadsheet journal is used (e.g. BU-STATE, Ledger Group-ACTUALS). This is a one time task.

• New Journal Header - Allows the agency user to create a new journal. The information set up on the Define Options and Defaults window automatically displays on the New Budget Journal Header window. This information may be changed but additional information for each new journal must be set up.

The Business Unit for journals is always "STATE."

When using Define Options and Defaults, do NOT define a Date. If this is done, OAKS FIN automatically puts that date on every journal line entered.

The Budget Journal Upload page contains two main sections:

• Header - The information entered in the Header section uniquely identifies an agency‘s journal. It contains buttons (on row 5) to add, delete, or copy budget journal header information.

• Line - The information entered in the Lines section is the budget journal line information. Unit, Ledger, Account, Budget Period, ALI, Dept, and Amount are entered in this section. It contains buttons (on row 9) to add, delete, or copy budget journal lines information.

• See the Reviewing and Uploading Spreadsheet Journal Entries topic for a description of each button.

A check box displays under each ChartFields line. This check box is used to copy ChartFields distributions from one journal line to another. All check boxes except for Account and Amount are automatically selected. The Account and Amount fields should ALWAYS be manually entered. Other boxes may be deselected as well.

After the budget header information has been entered and the budget journal spreadsheet created, the header information for this particular spreadsheet journal must be created before entering any budget journal line information.

Once the journal spreadsheet is completed, the file is uploaded. If required information is missing from the spreadsheet, OAKS FIN creates an error for the budget journal. If the spreadsheet contains errors, it must be corrected and reprocessed.  See the Reviewing and Uploading Spreadsheet Budget Journal Entries topic for additional information on uploading spreadsheet journals.

Once uploaded into OAKS FIN, the agency GL approver checks the journals. If the approver finds errors in the spreadsheet journal, the spreadsheet can be corrected online.

• Step-by-step instructions for creating journal entries with spreadsheets are in the next topic: Creating Spreadsheet Budget Journal Entries.