Microsoft Word - L1-T2 ReviewingandUploadingSpreadsheetJournalEntries.doc by Ohio


									                                                                              Quick Steps

Reviewing and Uploading Spreadsheet Journal Entries

   1.     To begin the process of uploading spreadsheet journal entries, you need a copy of the
          GL Spreadsheet Topics folder on your PC. Your agency should provide you with the
          GL Spreadsheet folder. In addition, your agency decides where you should save your
          files. Please seek guidance from your agency on this process.
   2.     Double-click the GL Spreadsheet Topics (Zip) folder to go to the Actuals folder.
   3.     Double-click the Actuals folder to display the files in it.

   4.     The Actuals folder contains four files. Each file serves an important purpose for the
          Spreadsheet Journal Entry process. The only file you use to create journal entries is
          the JRNL1.xls.

          Note: Do not open or change the other files. You do not use these files in this process.
   5.     Double-click the JRNL1.xls file to begin reviewing your spreadsheet journal entries.

   6.     When you open the JRNL.xls file, you may see a Security Warning.

          Click the Enable Macros button in the Security Warning window to open the
          JRNL.xls (spreadsheet journal) file.

   7.     Next, you need to review the information that currently exists in the journal

          Click the Edit button to open the journal spreadsheet for your review or to make
          changes to your journal.

   8.     In the Edit Journal Sheet window, select the journal sheet you wish to review and
          upload. In this scenario, you need to select and review the "test" spreadsheet that you
          created for upload in the previous topic in this course.

          For this scenario, click "test".

   9.     Click the OK button to open and edit the test spreadsheet.

Date Created: 4/12/2007                                                                    Page 1
Quick Steps

   10.   Once you have opened the test spreadsheet, review all the journal lines in the
         spreadsheet for accuracy. Verify that:

         1. All journal lines balance
         2. Account coding is correct

         Note: Use the ChartField Crosswalk job aid to verify that you enter valid information
         into the Chartfield fields.
   11.   If errors exist, you have two options to correct the journal lines:
         1. Locate the correct information and update the field.
         2. Send an e-mail to the GL Journal Processor asking for revisions.

         When you have corrected and reviewed all the journal lines, click the Save button to
         save your changes.

         In this scenario, all journal lines are correct.

         Click the Save button.

   12.   Click the Home button to go to the main menu of the journal spreadsheet where you
         can begin to upload this journal.

   13.   Now that you have reviewed the spreadsheet in Excel, you need to upload it into
         OAKS for further processing. Uploading the spreadsheet shows that you have
         reviewed and approved it, so only upload it after you have approved it.
   14.   Click the Write File button in the Import Journals field area to upload the
         spreadsheet into OAKS.

   15.   Use the Write Journals to File window to convert the journal spreadsheet into a
         format that you can upload into OAKS.

         You can select one or several spreadsheets to save to your computer.

         To save all of the spreadsheets listed in the Select Sheet field, click the ALL button.
   16.   Note: Your agency decides where you should save your spreadsheet files. Please seek
         guidance from your agency on their current process.
   17.   Selecting “test” saves the spreadsheet to your My Documents folder.

         For this scenario, click “test”.

   18.   Check the File Name displayed near the bottom of the Write Journals to File
         window. The journal spreadsheet program saves your journal spreadsheet in its upload
         format to this location. Verify with your agency if the file address provided is the
         correct location for saving spreadsheets.

Page 2                                                                  Date Created: 4/12/2007
                                                                           Quick Steps

   19.    Click the OK button so your computer converts the journal spreadsheets you selected
          into a format that you can upload into OAKS.

   20.    Click the Internet Explorer button to log in to OAKS.

   21.    Next, you need to log in to OAKS so you can continue to upload this journal

          Enter your OAKS User ID and Password and then click the Sign In button to go to
          the OAKS Main Menu.
   22.    Begin by going to the Spreadsheet Journal Import page.

          Click the General Ledger link.

   23.    Click the Journals link.

   24.    Click the Import Journals link.

   25.    Click the Spreadsheet Journals link.

   26.    Click the Collapse Menu button to minimize the side menu so you can see more of
          the OAKS page.

   27.    A Run Control ID, along with your User ID, uniquely identifies your preferences for
          running a report or process in OAKS.
   28.    Use the Find an Existing Value page to search for a Run Control ID that you have
          already saved.

          Use the Add a New Value page to add a new Run Control ID.
   29.    Click the Add a New Value tab so you can import your journal spreadsheet (a “new

   30.    When creating a Run Control ID, enter a short descriptive name, maximum 30 letters
          and numbers with no spaces.

          Enter the desired information into the Run Control ID field.

          For this scenario, enter a valid value e.g. "ReclassJCB".
   31.    Click the Add button to import your spreadsheet file.

Date Created: 4/12/2007                                                                 Page 3
Quick Steps

   32.   Use the Spreadsheet Journal Import Request page to attach the spreadsheet
         journal(s) you wish to upload into OAKS.
   33.   OAKS automatically provides the information in the *Character Set and *If Journal
         Is Invalid fields.
   34.   OAKS automatically displays Skip in the *If Journal Already Exists field.
         Sometimes you may need to upload a corrected spreadsheet from Excel to OAKS. If
         so, change the *If Journal Already Exists field to Update. When you select
         “Update,” OAKS replaces the previous file with your new file.

         For this scenario, use the *If Journal Already Exists “Skip”.
   35.   Click the Add button to import your spreadsheet file.

   36.   Notice that OAKS asks you which file you want to import. OAKS does not yet know
         which journal spreadsheet file you want to import.

         For this scenario, you saved this spreadsheet in your My Documents folder. This
         location is defined by your agency.

         Click the Browse button to search for the journal spreadsheet(s).
   37.   OAKS displays the Choose File window. Use this window to find the JRNL1.txt file
         that Excel created for you to import.

         Click the scrollbar to go to the right of the window.

   38.   Note: You do not import the JRNLx.xls file. Only import the JRNL1.txt file.

         For this scenario, click the “JRNL1.txt” file, which is located in the My Documents

   39.   Click the Open button to select this file and return to the OAKS Browse window.

         Click the Open button.

   40.   Click the Upload button to inform OAKS to pull the spreadsheet journal information
         from your Excel file into OAKS.

   41.   OAKS returns you to the Spreadsheet Journal Import Request window. Notice
         your file name, JRNL1.txt, in the Attached File field. This shows that you have
         prepared OAKS to upload this particular file.

         Click the Save button to store these settings in OAKS.

   42.   Click the Run button to open the Process Scheduler Request page.

Page 4                                                                 Date Created: 4/12/2007
                                                                               Quick Steps

   43.    Use the Process Scheduler Request page to set up how you want to run a process.

          You can also use this page to select process output types and output formats.
   44.    Note: Do not change the Server Name, Recurrence, and Time Zone fields. If you
          change these fields, the process may not run when expected.
   45.    Click the OK button to begin the upload process.

   46.    Notice that OAKS assigns a Process Instance number when you run a process. Use
          this to track the status of your spreadsheet upload process.
   47.    Click the Process Monitor link to display the upload status of your spreadsheet

          Click the Process Monitor link.

   48.    The Process List page displays all the processes that you have requested. Use the
          process name or process instance number to find your process.
   49.    Continue clicking the Refresh button until the Run Status displays “Success” and the
          Distribution Status displays “Posted”.
   50.    If OAKS displays “Success”, you must now run the journal edit batch process.

          See the Running the Journal Edit Batch Process topic for instructions.
   51.    To verify the OAKS system successfully uploaded your journal click the Details link.

          Click the Details link.

   52.    Click the Message Log link.

   53.    Note: To verify that the spreadsheet journal has successfully uploaded, you must
          confirm in line three of the message log all journals were imported. In this case, the
          one journal you created imported successfully.
   54.    Congratulations! You have successfully reviewed the journal lines, uploaded the
          spreadsheet journal into OAKS, and verified that the process has run successfully.

          Next, you are going to edit these journal lines to verify that they have no errors.

Date Created: 4/12/2007                                                                         Page 5

To top