NUFinancials: Creating a Spreadsheet Journal Training Guide

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

Creating a Spreadsheet Journal

The spreadsheet journal is an Excel file used to record actuals journal lines that users can upload for processing in Portal Actuals Journal. A spreadsheet journal must contain only transactional lines that share the same purpose. Acceptable journal purposes are agency transactions (AGY), adjustments to balance sheet (BAL), correction transactions (COR), internal sales (ISJ), or transfer of actuals dollars (TFR). Spreadsheet journals may not be used for budget journals.

*Important: The spreadsheet journal may easily become corrupted. To avoid having issues, use a new spreadsheet each time. Download the spreadsheet template from https://www.northwestern.edu/financial-operations/policies-procedures/finance/spreadsheet-journal-template.xlsm .

What happened when you opened the spreadsheet?

Respond by taking this action:

Spreadsheet opens without any warning or messages.

Continue to step 2: Save the file with a new name - macro enabled.

Some type of warning appears that the macros have been disabled.

Configure Excel macros security level:

  1. Click the File tab, click Options, and then click Trust Center.
  2. Click Trust Center Settings, and then click the Macro Settings category.
  3. Click Enable VBA macros. This allows all macros to run.
  4. Click OK twice.

Continue to step 2.

Some type of option appears allowing you to enable the macros.

Macro warning

Enable the macros and continue to step 2.

  • Save the spreadsheet journal file as macro-enabled workbook. (example: Bookstore Charges Nov 2021.xlsm)
  • Save each file to an easy to access location & with a unique file name.

Spreadsheet header

Enter your NETID.

Enter the Journal Date - determines posting date.

Enter a unique Header Description - 30 character max.

Choose a journal purpose from the drop down list. Journal Purpose Options
  • Required spreadsheet fields are indicated.
  • Use the Header Description box to identify the spreadsheet contents (limited to 30 characters). This field may be viewed when searching for journals.
  • 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.

Journal Line Entry

Not sure whether to use a + or - sign?

To Increase this account: To Decrease this account:
Expense Enter positive amount (debit) Enter negative amount (credit)
Revenue Enter negative amount (credit) Enter positive amount (debit)
  • Journal Line Description (required field) - description will appear on the GL008
  • Journal Line Reference (optional field) - can be used for additional reporting or to reference journal ID related to this line activity.
  • Journal Total should equal Zero & Required Fields Check should say OK.

Tips:

  • Check field alignment. This may help you spot typos where department or project numbers have too few or too many digits
  • Observe accounting rules for different journal types: https://www.northwestern.edu/financial-operations/policies-procedures/finance/financejournalprocessing.html

*Important: 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 cell
  • Do not delete entire rows or columns
  • Do not change the Amount column format. Amounts are formatted with two decimal points and no $ signs
  1. Click the "Output to Text File" button and save the resulting text file in an easily found place, Documents or Desktop.
  1. Log into NUFinancials: https://nufin.northwestern.edu/
  2. Navigation: NUFinancials > NavBar > Navigator > General Ledger > Journals > Import Journals > External Flat Files
  3. You will need a Run Control, which can be reused. Add a new Run Control value or find an existing value

    Attach text file
  4. Click Attach button
  5. Click Browse to select the output text file from where you saved it. *Note: the .xlsm Excel template cannot be uploaded directly, only the output .txt text file may be used.
  6. Click + to add additional journal files (if you would like to upload multiple journals at once.)

  7. Click Run

    Run Import

  8. Click OK
  9. Click Process Monitor to review process status

    Process Monitor
  10. Click Details

    Process Details
  11. Click View Log/Trace

    View Log
  12. Click .LOG file

    Log File
  13. Copy or note journal ID (example highlighted above)

Once a journal has been uploaded, it must be completed in Portal Actuals Journal and submitted for approval*
  1. Navigation: NUFinancials > NavBar > NU Actuals Journal > Add/Update Actuals Journal
  2. Click Find an Existing Value to search for your journal
  3. Enter your the journal ID recorded above, or search for your journal

    Find Journal
  4. Click Modify button
  5. Complete additional steps as needed:
    • When prompted to enter the Original Journal ID for Correction/Internal Sales journals, enter the oldest Original Journal ID if you are correcting multiple journals, and use the Line Reference column to record each original journal ID(s).
    • Confirm you are in compliance with the 90-day rule and complete and attach 90-day forms when applicable per: http://www.northwestern.edu/financial-operations/policies-procedures/forms/90-Day_Memo_Form_and_Instructions.pdf
    • Attach supporting information and documentation to the journal submission (i.e., include description of what you are doing and attach support for calculations. This is needed for journal approval and audit purposes
    • Complete any additional details required for that journal type
  6. Click Save & Submit

When successfully submitted, the workflow status will show “Pending Approval”
Journal Pending Approval

If errors are identified, click “Error” and correct the issue identified

Journal Error

*Central users may complete and submit journals via:
  • Navigation: NUFinancials > NavBar > General Ledger > Journals > Journal Entry > Create/Update Journal Entries

Top 5 reasons spreadsheets must be revised:

  1. The format has been adjusted or a corrupted spreadsheet has been reused (use a new one each time).
  2. Required fields have not been entered. (Verify the Required Field Check box in the header section shows 'OK')
  3. The macros have been disabled.
  4. Chart strings are invalid or do have budget errors ('Budget exceeds tolerance' or 'No budget exists').
  5. Amounts are longer than 2 decimal points, have rounding errors, or contain dollar signs or formulas.

Troubleshooting:

  • Doublecheck you are uploading the output text file (.txt) and not the excel (.xlsm) file into NUFinancials.

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




Keywords:"journal spreadsheet" "spreadsheet training" "journal train" "excel journal" "upload journal" "spreadsheet journal"   Doc ID:111019
Owner:ESAF GL Team .Group:Northwestern
Created:2021-05-24 17:13 CSTUpdated:2022-08-10 09:41 CST
Sites:Northwestern
Feedback:  0   0