NUFinancials: Creating a Journal Spreadsheet Training Guide

This article discusses the reasons for using and how to create a journal spreadsheet.


Creating a Journal Spreadsheet

The journal spreadsheet is an Excel file used to record actuals journal lines (not budget). The journal spreadsheet is an alternative to the Actuals Portal and is not subject to workflow approvals. Only users approved by Accounting Services (ASC) may submit the spreadsheet. The spreadsheet must contain only transactional lines that all use the same journal purpose. Acceptable journal purposes are limited to balance sheet, internal sales, corrections, or transfer transactions.
• If you have one of these purposes: to record internal sales (ISJ), correction transactions (COR), transfers of actuals dollars (TFR), or adjust a balance sheet (BAL).
• If you can record 15 or more transactional lines, where all lines share a journal purpose of ISJ, COR, TFR, or BAL.
• If you have less than 15 lines, use the Actuals Portal.
1. It does not contain 15 or more lines.
2. The format has been adjusted or a corrupted spreadsheet has been reused (use a new one each time).
3. It is incomplete or contains $ or formulas.
4. The macros have been disabled.
5. Chart strings are invalid or do not have budget.

To review the Journal Spreadsheet Upload Process:

http://www.northwestern.edu/financial-operations/policies-procedures/finance/financejournalupload.html

Warning: The journal spreadsheet may easily become corrupted. To avoid having it sent back to you, use a new spreadsheet every time. Visit http://www.northwestern.edu/financial-operations/policies-procedures/forms/PSJournalSpreadsheet.xltm to download the template.


JrnlSpreadsheet01

JrnlSpreadsheet02
  • When saving your Journal Spreadsheet, use the following format: your 3-character code (mask) plus today’s date. (example: ACS11082008.xls)
  • If you submit more than one spreadsheet per day, the format should be your 3-character mask plus today’s date plus an alpha character A, B, C, etc. (example: ACS11082008A.xls)
JrnlSpreadsheet03

  • Required spreadsheet fields are: Entered By (NetID), Journal Mask, Journal Date, Header Description and Journal
  • Ignore the gray fields; these are only for Accounting
  • Use the Header Description box to identify the spreadsheet contents. This 30-character field may be viewed when searching for
  • Warning: Do not alter the spreadsheet formatting! Use the right-click Paste Special option to paste data into the spreadsheet! Do not use the regular Paste option. Pasted data often contains unacceptable formatting or formulas.

Not sure whether to use a + or – sign? To increase expense, enter a positive amount (debit). To increase revenue, enter a negative amount (credit). To decrease expense, enter a negative amount (credit). To decrease revenue, enter a positive amount (debit).


JrnlSpreadsheet04

Warning: Do not reformat the spreadsheet in any way!

  • Line numbers auto-populate when you enter a fund Do not enter line numbers.
  • Do not enter formulas, or dollar signs in any

Do not change the Amount column format. Amounts are formatted with two decimal points and no $ signs.

The spreadsheet must be submitted by email. Make sure to do the following:

  1. Follow your department internal review
  2. Make sure you are in compliance with the 90-day rule.
  1. In the subject line of the email, type the file name of the journal
  2. Include the following information in the body of the email:
    • Journal purpose
    • The name of the accounting period to which the journal should post
    • Any other relevant information and supporting documentation where appropriate
  3. Attach the spreadsheet to the
  4. Send the spreadsheet with attachments to upload-journal@northwestern.edu.

 

Accounting Services did this:

Your spreadsheet status:

What should you do?

Sent the spreadsheet back to you asking for a new spreadsheet

Macros are not enabled or the spreadsheet is corrupted.

Download a fresh spreadsheet. Use Appendix A of this guide to reset your macro security. Enable the macros. Use Paste Special to copy your data into the new spreadsheet.

Resubmit to Accounting Services.

Sent an email asking you to  correct the errors

Incomplete.

Complete the spreadsheet according to the guidance received from Accounting Services. Open the email you received from Accounting Services and click Reply. Attach  the new spreadsheet and click Send.

Contains $ or formulas.

Fix the errors. Open the email you received from Accounting Services and click Reply. Attach the new spreadsheet and click Send.

Journal contains less than 10 journal line errors

The journal contains incorrect chart strings or budget errors.  Email new chart strings to Accounting Services or add budget if it is a budget error and communicate the change to Accounting Services. Accounting Services will make the changes to the journal.

 

Journal contains more than 10 journal line errors.

This journal contains incorrect chart strings or budget errors. Search for the journal using the Searching for Spreadsheet Journals job aid in myHR Learn.

Find the errors, create a new journal spreadsheet with  corrected data, and submit it to Accounting Services. The original journal with the errors will be deleted.

When your new spreadsheet is processed, you will receive a new journal ID.


For additional assistance please contact the IT Support Center at 847-491-4357 (1-HELP) option 2, or consultant@northwestern.edu.




Keywords:"journal spreadsheet" "spreadsheet training" "journal train"   Doc ID:111019
Owner:ESAF GL Team .Group:Northwestern
Created:2021-05-24 18:13 CDTUpdated:2021-08-06 09:01 CDT
Sites:Northwestern
Feedback:  0   0