Creating and Uploading a GL Journal Spreadsheet

Body

Overview

For departments or units that have a large number of transactions, it may be easier to record the transactions on a spreadsheet and upload to the general ledger. They are typically used by departments and units who enter their information into other systems and extract it to be uploaded to the UGA Financial Management System. It is best to structure your spreadsheet with columns in the same order as they are on the template you will use to upload the entries to the general ledger.

There are two steps in this process.

Create the spreadsheet journal and upload it to the UGA Financial Management System.
Log in to the UGA Financial Management System and run the Edit Process and submit the journal so it can be approved and posted.

The OneSource Knowledge Base at https://onesource.uga.edu/resources/gl_journal_spreadsheet. The template consists of three (3) files which all must be saved to dedicated folder on your computer. If the files are not in the same folder, the macros will not work correctly. It is recommended that you check the knowledge base often to ensure that you have the most recent version of the spreadsheet.

The file JRNL_WS GL obtained from the Knowledge Base is the template where you will key in your data. In this example, we will assume that the most recent version of the spreadsheet is JRNL_WS ver 9.2.36a.xslm Once you have entered the data, you will need to upload the file and then go to UGA Financial Management System to budget check the journal, edit the ChartFields and submit for approval.

You should be familiar with the topics in this section before using the template.

Supporting documentation must be attached to all General Ledger Journals and should reflect the entry being made. A good rule of thumb is to include an electronic copy of what was used to create the journal entry. For intra-university charges (tickets), invoices should be attached that equal the revenue amount being credited. For correcting/moving a transaction, provide supporting documentation that shows where the transaction originally posted. Remember to be concise with journal entries and combine documents when reasonable; reviewing fewer documents can be helpful for your approvers! Documents included are also available during auditing and historical review. Examples of supporting documentation:

UGA_GL_LEDGER_DETAIL query results can be included to show what transaction is being corrected/moved.
Any analysis/spreadsheet that was used to determine the amounts included in the journal entry.
Invoices and agreements for Intra-University charges.

In this article, you will learn how to create a new spreadsheet to enter a group of journal entries. You will copy or enter the transactions using a spreadsheet template and upload the entire group into the UGA Financial Management System.

Be sure that you have the correct templates and have loaded them to the appropriate folder. Templates may be obtained from the OneSource Service Desk by emailing OneSource@uga.edu. UGA is currently using JRNL1_WS ver 9.2.32a .

1. Step 1

First, email OneSource Service Desk (onesource@uga.edu) to obtain the appropriate files.

The JRNL1_WS ver 9.2.32a spreadsheet is the macro-enabled spreadsheet template that you will use to load your data.

Open the folder where your templates are stored. Then, double-click on the JRNL1_WS ver 9.2.32a file to open it.

2. Step 2

The first time you use the template, you may see a Security Warning that the macros have been disabled. You need to enable them.

Click the Enable Editing button in the statusbar.

3. Step 3

Click the Enable Content button if prompted.

4. Step 4

When the file opens, it opens to the Control page. This page has multiple buttons that control how you set up defaults for your import, how you create and edit your journals, and how you import the journals.

5. Step 5

Controls to create and edit the journal spreadsheets are found in the Journal Sheets section.

Click the New Sheet button.

6. Step 6

A dialog box is displayed and asks for the name of your new spreadsheet.

Enter a value that helps you to locate it in your Spreadsheet Uploader folder when you are ready to upload.

Example shown here: " 20190520 ".

7. Step 7

Click the OK button.

8. Step 8

Click the " + " sign in the Journal Header section to begin entering header information for the journal.

9. Step 9

The New Journal Header dialog box is displayed. Most of the values default, but can be changed here if needed.

The first default value is for the Business Unit which should always be 18000, which is assigned to the University of Georgia.

10. Step 10

The Journal ID will say NEXT and should not be changed (or typed in). The default value will allow the system to assign the next number when it is imported.

11. Step 11

The Journal Date will default to the current date. Leave the default value.

12. Step 12

The next field that requires a value is the Ledger Group. Leave the default of ACTUALS.

The ACTUALS ledger group is where financial transactions will be posted. There are different options for commitment control uploads.

13. Step 13

The source defaults to ONL, which indicates it is an online entry submitted by a person and not coming from an interface. Leave this value in the field and do not edit it.

14. Step 14

The value of CURRENT_USER in the User ID field will associate this journal with the ID of the person who logs in to upload the file. This field is greyed out and cannot be changed.

15. Step 15

The Journal Class and Transaction Code should be left blank. UGA is not using these fields.

16. Step 16

Enter a description as you want it to appear on the journal header.

Example shown here: " Correct postage charges ".

17. Step 17

Leave the defaults in the remaining fields in this section.

18. Step 18

Leave all defaults in the Currency Information and Reversal sections.

19. Step 19

The Header is complete.

Click the OK button.

20. Step 20

The New Journal Header dialog box closes and you are ready to enter information for the journal lines.

Click the " + " sign in the Journal Lines section to open the lines for entry.

21. Step 21

Begin entering your Chartfields with the (Business) Unit cell. This should be 18000 (University of Georgia) for most transactions.

Enter "18000" into the Unit cell.

22. Step 22

Continue to enter the ChartField values.

Enter the appropriate information into the Ledger cell. This is typically "ACTUALS".

Enter "ACTUALS ".

23. Step 23

Enter the appropriate information into the Account cell.

Example shown here: "714101".

24. Step 24

Enter the appropriate information into the Fund cell.

Example shown here: "10000".

25. Step 25

Enter the appropriate information into the Dept ID field.

Example shown here: "34001100".

26. Step 26

Enter the appropriate information into the Program cell.

Example shown here: "11140".

27. Step 27

Enter the appropriate information into the Class field.

Example shown here: "11000".

28. Step 28

The budget reference is the budget year in which the transaction takes place.

Enter the appropriate information into the Budget Ref cell.

Example shown here: "2019".

29. Step 29

Enter the amount you are charging to this account by adding a positive number to create a debit entry.

Enter the appropriate information into the Amount cell.

Example shown here: "58.00".

30. Step 30

Enter the appropriate information into the Description cell.

Example shown here: "reclassify to postage".

31. Step 31

You now need to add one or more entries to balance the debits and credits on your spreadsheet.

Click the " + " sign in the Journal Lines section to begin entering the journal.

32. Step 32

The previous line is copied into the new entry. This gives you the opportunity to only change what you need to change.

Example shown here: you only need to change the account entry.

33. Step 33

Change information as needed in the ChartField cells.

In this example, only the Account ChartField will change. Example shown here: " 714110 " into the Account field.

34. Step 34

Because the transaction must balance, the amount should be changed to a negative number to create a credit entry on the line(s) that balance the first transaction.

Enter the appropriate information into the Amount field.

Example shown here: " -58.00 ".

35. Step 35

When your entry is complete, you are ready to upload the file into the UGA Financial Management System.

Click the Upload icon.

36. Step 36

The Import Journals Now dialog box is displayed and asks for your login information. These are the credentials you use for the UGA Financial Management System.

Enter your credentials into the User ID and Password fields.

37. Step 37

After entering your credentials, click the OK button.

38. Step 38

A status message is displayed to tell you whether or not the import was successful. If it is not successful, the message will try to guide you to the error to be corrected.

Note: Beneath the description there is Journal Header information. The 10-digit number is the Journal ID you can use when you search for your ID in the system.

Click the OK button.

Click the Don't Save button.

Creating and Uploading a GL Journal Spreadsheet

Summary

Body

Overview

Details

Details