What You Should Know
Spreadsheet Journals are a method of mass Journal creation used by certain departments. Special access and training is required to perform Spreadsheet Journals.
Learn how to Create Spreadsheet Journals.
- To create Spreadsheet Journals, three excel files are required and must be stored all in the same folder:
- Dept_Journal_Spreadsheet.xls - journal workbook used to create/validate
- JRNLMCRO_Dept.xla - Visual Basic code library and dialog control
- GLLOG.xlt - message log template
- Create a new spreadsheet journal from the template
- Validate spreadsheet journals in Prime Financials after creating the journal in Excel
- Correct/update your spreadsheet journal until no errors are found
- Email the validated spreadsheet journal to the Controller’s office
Spreadsheet Journals are used to:
- Process bulk (large number of rows) journals.
Spreadsheet Journals are not used to:
- Make Budget changes. Contact the Budget Office for Budget changes.
- Correct Labor Accounting journals. These changes must be made in LA.
- Departments who are not set up in the Departmental Charges system will use Spreadsheet Journals to bill for internal goods and services provided (e.g. Facilities bills for work orders and Dining Services bills for events)
All approvals will be made by the Controller's Office.
Spreadsheet Journal Navigation Information
Menu Path: General Ledger > Journals > Import Journals > Spreadsheet Journals
Spreadsheet Journal Activities:
Line Descriptions for Spreadsheet Journals
Do not exceed 30 characters in the Journal Line Description. If more than 30 characters are entered, when your spreadsheet journal is loaded, your unique description will be replaced by the standard Account description.
How to Name Your Spreadsheet Journal File
Use a naming convention to create a unique file name that allows the file to be easily identified. Example: Journal Class_Date_version (J00003_20140331_1)
Note: The name must NOT have spaces and use the underscore (_) in place of spaces.
Also, do not exceed 29 characters in the file name. The file name will not validate in PeopleSoft. Be sure to include the journal reference number in the file name.
Validating Spreadsheet Journals
Once the journal(s) have been created, they need to be validated to ensure the journals balance, have no errors, and meet PeopleSoft chartstring combination criteria.
To fully validate a spreadsheet, the Run Status column must indicate "Success" and the Distribution Status column must indicate "Posted". If an error occurs, you will still be able to view the Details for the journal file, but additional validation is required.
Next, review the .LOG file to check for any additional errors and confirm your journal can be submitted to the Controller's Office.
Key areas to for you to review in the .LOG file:
- Total Balance Amount - This should equal zero when all the journal lines (debits and credits) balance.
- Total Debit Amount - This should be the same amount as the Total Credit Amount; the Total Debit Amount must be positive; the Total Credit Amount must be negative.
Once the journal has no errors and has been successfully validated, the next step is to send a confirming email to the Controller's Office.
How to Modify the Journal Header
- At line 6 of your journal, select the pencil icon .
- Highlight the chartstring that appears.
- Click OK. Many users forget to highlight the string and then think the header can’t be modified.
Common Journal Errors
- Invalid Chartstring or ChartFields: Account, Department, Fund, etc.
- Closed ChartFields are invalid
- Non-existent values are invalid
- Invalid User ID value
- User ID must be entered in all caps
- User must be set up in PeopleSoft
Spreadsheet Journal Email Process
The confirmation email process has not changed from previous versions of Prime Financials.
During the “write file” process, two .txt files are created. The .txt file that contains your unique file name is used for validation. After successfully validating your file/journal:
1. Copy and paste the .LOG file contents into an email.
- Non Finance and Treasury staff should attach the .txt file to the email.
- Internal Finance and Treasury staff should copy the .txt file onto the Financial Reporting server
2. Send the confirming email to email@example.com.
Posting the Journal
Once the Controller’s Office receives the submission, they load the journal, match the control totals and journal class, and post the journal.