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.
Step 1: Download and open spreadsheet
*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:
Continue to step 2.
Some type of option appears allowing you to enable the macros.
Enable the macros and continue to step 2.
Step 2: Save the file with a new name
- 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.
Step 3: Enter the required information
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.
- 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.
Step 4: Enter the journal lines
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.
- 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
Step 5: Create the Upload Text File
- Click the "Output to Text File" button and save the resulting text file in an easily found place, Documents or Desktop.
Step 6: Upload Output Text File to NUFinancials
- Log into NUFinancials: https://nufin.northwestern.edu/
- Navigation: NUFinancials > NavBar > Navigator > General Ledger > Journals > Import Journals > External Flat Files
- You will need a Run Control, which can be reused. Add a new Run Control value or find an existing value
- Click Attach button
- 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.
- Click + to add additional journal files (if you would like to upload multiple journals at once.)
- Click Run
- Click OK
- Click Process Monitor to review process status
- Click Details
- Click View Log/Trace
- Click .LOG file
- Copy or note journal ID (example highlighted above)
Step 7: Complete Journal and Submit for Approval
- Navigation: NUFinancials > NavBar > NU Actuals Journal > Add/Update Actuals Journal
- Click Find an Existing Value to search for your journal
- Enter your the journal ID recorded above, or search for your journal
- Click Modify button
- 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
When successfully submitted, the workflow status will show “Pending Approval”
If errors are identified, click “Error” and correct the issue identified
*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:
- The format has been adjusted or a corrupted spreadsheet has been reused (use a new one each time).
- Required fields have not been entered. (Verify the Required Field Check box in the header section shows 'OK')
- The macros have been disabled.
- Chart strings are invalid or do have budget errors ('Budget exceeds tolerance' or 'No budget exists').
- Amounts are longer than 2 decimal points, have rounding errors, or contain dollar signs or formulas.
- Doublecheck you are uploading the output text file (.txt) and not the excel (.xlsm) file into NUFinancials.