General Ledger - PowerPoint PPT Presentation


PPT – General Ledger PowerPoint presentation | free to view - id: 7e431a-NmQ4Y


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

General Ledger


MaineStreet Financials 8.4 General Ledger Excel Journal Entry – PowerPoint PPT presentation

Number of Views:269
Avg rating:3.0/5.0
Slides: 90
Provided by: rsh88
Tags: general | ledger


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: General Ledger

General Ledger
MaineStreet Financials 8.4
Excel Journal Entry
  • 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

Excel Journal Entry Process
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)

PeopleSoft Delivered Excel Files
  • Files can be downloaded from
  • http//
  • Need to be installed locally on your computer
  • We recommend saving them in a single folder on
    your computers C drive

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

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

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

Spreadsheet Journal Import Screen
  • Will need to adjust the window to view entire

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
Spreadsheet Journal Import Screen
  • Each section provides different options
  • Lets review the options in each section

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
  • Not linked to any specific Journal Sheet

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
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
General Defaults
  • From the Spreadsheet Journal Import screen,
    select the Setup button
  • Define Options and Defaults screen appears

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

General Defaults General Options
  • General Options
  • Sets English as default language

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

General Defaults Document Sequencing
  • Document Sequencing
  • Leave both boxes blank

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

Journal Sheets Defaults
  • Now lets set defaults for our Journal worksheets
  • Select Configurate

Journal Sheets Defaults
  • Two items open
  • A ChartField configuration dialog box
  • An empty Journal sheet

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
  • Changes to configuration settings apply to all
    Journal Sheets opened after the configuration is

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

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

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

New Journal Sheet
  • From your Spreadsheet Journal Import screen
    select New
  • New Journal Sheet dialog box appears

New Journal Sheet Info
  • Each Journal Sheet represents a Journal Entry
  • 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

New Journal Sheet
  • Enter your Journal Sheet Name
  • Select OK

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

Journal Sheet
Left-End of Journal Sheet
Right-end of Journal Sheet
Journal Header
  • Separate sections of the Journal Sheet for the
    Journal Header and Journal Lines

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
Journal Header
  • Now we can edit the Journal Header
  • In the Header section of your Journal Sheet,
    select the button in Row 5

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

Journal Header
  • Journal Date defaults to todays date. You may
    use default date or enter a different date
  • Leave Journal Class box blank

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

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

Journal Header
  • Leave Adjustment Type box blank

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Journal Line Open Item Key
  • Leave Open Item Key column blank

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

Save Your Work!
  • Remember to save your work by selecting the SAVE
    button on your Excel menu bar

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

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
  • May not add lines between existing lines

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

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

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
  • Take note of information entered into File name
    section since you will refer to it later

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

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

Upload Journal Entry-Run Control ID
  • To create Run Control ID, select Add a New Value
  • Enter name for Run Control ID
  • Helps to apply name that relates to process
  • Will only see Run Control Ids you create
  • Select Add

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

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

Upload Journal Entry - Browse
  • When icon selected, following dialog box
  • Select Browse to open Choose File dialog box to
    browse your computer for previously saved JE file

Upload Journal Entry - Upload
  • File name will appear in Browse box
  • Select Upload button to return to Spreadsheet
    Journal Import page

Upload Journal Entry - Run
  • Select Run and Process Scheduler Request page

Upload Journal Entry Process Scheduler
  • Complete Process Scheduler Request page as
  • Do not need to enter any information on this page
  • Select OK button when ready

Upload Journal Entry Process Scheduler
  • Spreadsheet Journal Import page opens with
    Process Instance number assigned
  • Number indicates JE file has been submitted to
  • Do not need to select Run button. When Process
    Instance number appears, file is already in
    process queue

Upload Journal Entry Process Monitor
  • Select Process Monitor link to check progress
  • Process List page opens

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
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

Upload Journal Entry Process List
  • Process Detail page opens
  • Select Message Log link to find out if Journal

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

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

Upload Journal Entry View Log/Trace
  • On View Log/Trace page, select second Message Log
  • Message opens that describes error/s in Excel JE

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

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

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

Edit Journal Sheet
  • After your Journal Sheet opens, select the
  • You can now apply changes to your Journal Header
    and Journal Lines
  • Make certain to Save your changes!

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

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
  • To delete all Journal Sheets, select All and then

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

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

Copy Journal Sheet
  • It is exactly like the Journal Sheet you copied
  • Select Edit button to create a new Journal ID and
  • 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