General Ledger

1
General Ledger
MaineStreet Financials 8.4
Excel Journal Entry
2
Overview

• A Journal Entry is used to update the General
  Ledger for many types of transactions, including
  cash receipts, transfers of revenue or expense,
  and budget changes
• PeopleSofts custom Excel interface provides an
  easy-to-follow menu for entering data and
  specifying defaults

3
Excel Journal Entry Process
4
PeopleSoft Delivered Excel Files

• PeopleSoft provides the following required Excel
  files for files
• JRNLUMS.xls Excel workbook you will use to
  create and maintain journal sheets
• JRNLMCRO.xla macro file that supports your
  activities when creating and importing Excel
  Journal entries (will not open)
• GLLOG.xlt Message log template that assists in
  writing Excel Journal Entries to PeopleSoft (will
  not open)

5
PeopleSoft Delivered Excel Files

• Files can be downloaded from
• http//www.maine.edu/system/oft/psfinancial/journa
  l_entry.php
• Need to be installed locally on your computer
• We recommend saving them in a single folder on
  your computers C drive

6
Macro Security Level

• Excel Macro Security Level must be set to
  Medium rather than High
• From Excel menu, select Tools gt Macro gt Security

7
Macro Security Level

• Medium security level will allow you to open
  macros that support Excel JE process
• Set level to Medium and then select OK

8
Setup Excel Journal Entry File

• From Excel menu select File gt Open
• Locate file JRNLUMS.xls and open it
• Dialog box regarding macros will appear

• Select Enable Macros
• Spreadsheet Journal Import screen opens

9
Spreadsheet Journal Import Screen

• Will need to adjust the window to view entire
  screen

10
Spreadsheet Journal Import Screen

• Provides links to other screens used to setup of
  Excel Journal worksheet
• Comprised of three main sections

- General - Journal Sheets - Import Journals
11
Spreadsheet Journal Import Screen

• Each section provides different options
• Lets review the options in each section

12
General Options
Setup -

• Used to setup general defaults for your workbook
  (Business Unit, Ledger Group, Source and User ID)

Notes -

• Worksheet you can use for miscellaneous notes or
  calculations
• Not linked to any specific Journal Sheet

13
Journal Sheets Options
New Inserts a new journal sheet
Edit Enables you to edit selected journal sheet
Copy Copies selected journal worksheet to a
new worksheet with new name
Delete Enables you to delete selected journal
sheet/s
14
Import Journals Options
Import Now We will not use this option
Write File Will use this option to save
Journal Sheets files that will be uploaded to
PeopleSoft
15
General Defaults

• From the Spreadsheet Journal Import screen,
  select the Setup button

• Define Options and Defaults screen appears

16
General Defaults - Header

• Header Defaults
• This is where you set header defaults that will
  appear on Excel Journal Entries
• Header contains information that uniquely Journal
  Entry

17
General Defaults General Options

• General Options
• Sets English as default language

18
General Defaults Message Options

• Message Options
• Relates to messages displayed following JE import
  process
• Select Log Successful and Error Message radio
  button
• Check Display Messages Online box

19
General Defaults Document Sequencing

• Document Sequencing
• Leave both boxes blank

20
General Defaults Online Import Control

• Online Import Control
• Settings determine how Excel Journal Entries are
  delivered to PeopleSoft
• Will be pre-set

21
Journal Sheets Defaults

• Now lets set defaults for our Journal worksheets

• Select Configurate

22
Journal Sheets Defaults

• Two items open

• A ChartField configuration dialog box
• An empty Journal sheet

23
Journal Sheets Defaults

• ChartField configuration screen assists in
  re-ordering Excel Journal Sheet columns
• Project Enterprise technical team configured
  Journal Sheet columns for you
• Can adjust column order to suit your needs
  further
• Changes to configuration settings apply to all
  Journal Sheets opened after the configuration is
  set

24
Journal Sheets Defaults

• ChartField configuration box

• Buttons on Column screen work as follows

• Moves blue highlight vertical selection bar to
  the LEFT
• Moves blue highlight vertical selection bar to
  the RIGHT
• Moves blue highlighted column to the LEFT of the
  column on its LEFT
• Moves blue highlighted column to the Right of
  the column on its RIGHT
• Inserts New column to the LEFT of blue
  highlighted column
• Deletes highlighted column

25
Journal Sheets Defaults

• Field Format screen shows Field Name, Label and
  Format of each column description required to
  properly transfer Excel Journal Entries to
  PeopleSoft
• Do Not Make Any Changes to Field Format Settings!

26
Journal Sheets Defaults

• To return to ChartField configuration screen,
  select Column tab on ChartField configuration box
  and then select Exit
• After you return to Spreadsheet Journal Import
  screen, save work by selecting icon Excel
  toolbar

27
New Journal Sheet

• From your Spreadsheet Journal Import screen
  select New

• New Journal Sheet dialog box appears

28
New Journal Sheet Info

• Each Journal Sheet represents a Journal Entry
  (JE)
• Journal Sheet name should be same as required
  10-character (or less) Journal ID you will apply
  to JE
• Do not enter more than 10 characters or include
  blank spaces in your Journal ID!
• Will prevent successful import of Journal Sheet
  to PeopleSoft

29
New Journal Sheet

• Enter your Journal Sheet Name
• Select OK

30
Journal Sheet

• Journal Sheet is wide so need to scroll right to
  view entire sheet
• Columns appear in the default order or order set
  during ChartField configuration process
• Journal Sheet name does not display on Journal
  Sheet

31
Journal Sheet
Left-End of Journal Sheet
Right-end of Journal Sheet
32
Journal Header

• Separate sections of the Journal Sheet for the
  Journal Header and Journal Lines

33
Journal Control Buttons

• The following control buttons are available to
  you on Row 5

We recommend you not use these buttons except for
the Add Header Edit Header button
Add Header Opens New Journal Header screen
Edit Header Opens Edit Header selection
box
34
Journal Header

• Now we can edit the Journal Header
• In the Header section of your Journal Sheet,
  select the button in Row 5

35
Journal Header

• New Journal Header screen appears

• Displays the default items you previously entered

• Enter name you applied to Journal Sheet into
  Journal ID box

36
Journal Header

• Journal Date defaults to todays date. You may
  use default date or enter a different date

• Leave Journal Class box blank

37
Journal Header

• Foreign Currency, Rate Type Exchange Rate boxes
  remain blank in Currency Information section
• Leave blank

38
Journal Header

• Enter same date entered for Journal Date in
  Effective Date box

39
Journal Header

• Leave Adjustment Type box blank

40
Journal Header

• Adjusting Entries and Reversals primarily handled
  by Campus Accounting Office, so will not cover
  those tasks here

41
Journal Header

• In Description box, enter header description for
  JE
• Can enter up to 254 alpha/numeric characters

42
Journal Header

• After entering all information on Journal Header,
  select OK
• Will return to Journal Sheet and see information
  you entered into Header

43
Edit Journal Header

• If you need to change Journal Header, select
  icon in Row 5
• Edit Journal Header selection box appears

• Select Journal Sheet Edit Journal Header box
  appears

44
Journal Lines

• In Row 9 of Journal Sheet Lines, select
  button to add first line of JE

• Journal Header Description appears in first line
  of JE

45
Journal Line Business Unit Ledger

• Enter 5-digit Business Unit for first line of JE
  in Unit column
• Must enter Business Unit in UPPER-CASE text

• Enter ACTUALS into Ledger column in UPPER-CASE
  text

46
Journal Line Description

• In Description column, briefly but precisely
  explain what this line JE

• Supervisor and others will refer to information
• Limit Description to thirty alpha/numeric
  characters

47
Journal Line Amount

• Enter all credit values as negative numbers
  (include - sign before number) and all debit
  values as positive numbers (do not enter sign
  before number)
• Do not enter dollar signs or commas
• If cents are other than zero, enter a period
  after the dollar amount

48
Journal Line Dept ID

• Every line of every a JE requires Department
• Enter 7-digit Department for first line of JE
  into Dept ID column

49
Journal Line Account

• Every line of every JE requires Account
• Enter 5-digit Account for every line of JE into
  the Account column

50
Journal Line Class

• If using 2-digit Class ChartField in JE, enter
  number in Class column

51
Journal Line Fund

• Every line of every JE requires Fund
• Enter 2-digit Fund for first line of JE in Fund
  column

52
Journal Line Program

• If using 5-digit Program code, enter it into
  Program column

53
Journal Line Project

• If using 7-digit Project code, enter it into
  Project column

54
Journal Line Operating Unit

• If using 3-digit Operating Unit code in addition
  to Department, enter it into Oper Unit column

55
Journal Line Reference

• Reference will be helpful in tracking source of
  JE line
• Can enter up to ten alpha/numeric characters
  Reference column

56
Journal Line Open Item Key

• Leave Open Item Key column blank

57
Journal Line Currency, Rate, Rate Type Base
Amount

• Do not need to enter information into last four
  columns of Journal Line, so leave columns blank

58
Save Your Work!

• Remember to save your work by selecting the SAVE
  button on your Excel menu bar

59
Add Journal Lines

• Two options to add lines to JE
• Add One Line at a Time In Line section of
  Journal sheet, select button in Row 9 to add
  one line
• Information from previous line copies into new
  line for those columns checked in Row 8

60
Add Journal Lines

• Add More than One Line at a Time In Lines
  section of your journal sheet, click
  button to open Insert Multiple Lines dialog box

• Enter number of lines you wish to add
• By default, new lines will add under existing
  lines
• May not add lines between existing lines

61
Add Journal Lines

• New lines will be blank unless you check the Copy
  data from line box
• Another box will appear where you enter the
  number of the lines you want to copy
• Select Insert to add new lines

62
Upload Journal Entry - Write File

• Now ready to prepare JE for upload to PeopleSoft
• From the Journal Spreadsheet Import screen,
  select the Write File button

• Write Journal to File dialog box opens

63
Upload Journal Entry - Write File

• Select Journal Sheet you want to upload
• In File name section, enter path and file name of
  file you will upload

• Files uploaded to PeopleSoft should be in .xml
  format
• Take note of information entered into File name
  section since you will refer to it later

64
Upload Journal Entry PeopleSoft Login

• Click OK
• Excel JE is now saved to computer and is ready
  for upload to PeopleSoft

• Must access MaineStreet Financials to upload
  Excel JE
• After login, follow this path
• Enterprise Applications gt Financials gt General
  Ledger gt
• Journals gt Journal Entry gt Import Journals gt
  Spreadsheet Journals

65
Upload Journal Entry- Run Control ID

• Spreadsheet Journal Import page appears with Find
  an Existing Value page open

• First time you upload Excel JE to PeopleSoft,
  must create Run Control ID
• Run Control ID is used whenever a process is run
  in PeopleSoft

• Can save Run Controls for frequently used
  processes

66
Upload Journal Entry-Run Control ID

• To create Run Control ID, select Add a New Value
  page

• Enter name for Run Control ID
• Helps to apply name that relates to process
• Will only see Run Control Ids you create
• Select Add

67
Upload Journal Entry- Run Control ID

• When you add new Run Control ID, Spreadsheet
  Journal Import page opens
• In the future, will select Run Control ID from
  Find An Existing Value page

68
Upload Journal Entry Attach File

• All necessary information will appear on page
• In Attached File section, select icon to
  browse for file you saved to computer

69
Upload Journal Entry - Browse

• When icon selected, following dialog box
  opens

• Select Browse to open Choose File dialog box to
  browse your computer for previously saved JE file

70
Upload Journal Entry - Upload

• File name will appear in Browse box

• Select Upload button to return to Spreadsheet
  Journal Import page

71
Upload Journal Entry - Run

• Select Run and Process Scheduler Request page
  opens

72
Upload Journal Entry Process Scheduler

• Complete Process Scheduler Request page as
  follows

• Do not need to enter any information on this page

• Select OK button when ready

73
Upload Journal Entry Process Scheduler

• Spreadsheet Journal Import page opens with
  Process Instance number assigned

• Number indicates JE file has been submitted to
  run
• Do not need to select Run button. When Process
  Instance number appears, file is already in
  process queue

74
Upload Journal Entry Process Monitor

• Select Process Monitor link to check progress
• Process List page opens

75
Upload Journal Entry Process List
Find Process Instance number listed
Initially, Run Status will display as Queued
Select Refresh button to update Run Status display
If all goes well, Run Status will display as
Posted and then as Success
76
Upload Journal Entry Process List

• A Run Status of Success refers to Run process
• Does not necessarily mean Excel Journal
  successfully imported into PeopleSoft

• Select Details link on Process List page

77
Upload Journal Entry Process List

• Process Detail page opens
• Select Message Log link to find out if Journal
  imported

78
Upload Journal Entry Message Log

• Message Log page provides details of process
• Verify process completed successfully with 1
  journals imported

• Select Explain button for additional details

79
Upload Journal Entry Errors

• If Excel Journal did not import, following
  message will appear

• Possible errors might include
• Invalid ChartField errors
• Invalid User ID
• Already existing Journal Entry with same Business
  Unit/Journal ID/ Journal Date combination
• To identify errors, return to Process Detail page
  and select View Log/Trace link

80
Upload Journal Entry View Log/Trace

• On View Log/Trace page, select second Message Log
  link

• Message opens that describes error/s in Excel JE

81
Upload Journal Entry View Log/Trace
Incorrect text case

• In this example, there was an User ID error
• To correct error/s, return to Excel JE, correct
  error, save file and then initiate file upload
  process starting with Write File from Spreadsheet
  Journal Import screen

82
Edit Submit Journal Entry

• After Excel Journal Entry successfully imports
  into PeopleSoft, run it through same processes
  used for Journal Entries created online
• Edit process
• Submit for Approval

83
Edit Journal Sheet

• To edit an existing Excel JE, open PeopleSoft
  Excel file and select Edit button from
  Spreadsheet Journal Import screen

• When Edit Journal Sheet dialog opens, select
  Journal sheet file and choose OK

84
Edit Journal Sheet

• After your Journal Sheet opens, select the
  button
• You can now apply changes to your Journal Header
  and Journal Lines

• Make certain to Save your changes!

85
Delete Journal Sheet

• To delete a Journal Sheet, open the Spreadsheet
  Journal Import screen and select the Delete
  button
• The Delete Journal Sheets dialog box will open

86
Delete Journal Sheet

• To delete one Journal Sheet, select sheet you
  want to delete and then select Delete button
• Hold Ctrl key down on keyboard to select multiple
  files
• To delete all Journal Sheets, select All and then
  Delete

87
Copy Journal Sheet

• To copy a Journal Sheet, open Spreadsheet Journal
  Import screen and select Copy button
• Copy Journal Sheet dialog box opens

• Copy option enables you to copy an existing
  Journal Sheet and use it to set up new worksheet
  with a new name

88
Copy Journal Sheet

• Select Journal Sheet you want to copy
• Enter a new name in New Journal Sheet box

• Select Copy and a new Journal Sheet opens

89
Copy Journal Sheet

• It is exactly like the Journal Sheet you copied
• Select Edit button to create a new Journal ID and
  description
• New description does not carry through to
  existing lines
• You add lines for new departments that are to
  receive charges and delete lines for departments
  that have no charges