UNIVERSITY OF CENTRAL FLORIDA Finance & Accounting

UNIVERSITY OF CENTRAL FLORIDAFinance & AccountingOffline Journal EntryOverviewThe Spreadsheet Journal Entry feature facilitates rapid data entry using MicrosoftExcel giving you the ability to arrange and configure the data you wish to use in yourspreadsheet.The Spreadsheet Journal Entry streamlines journal processing and simplifies journaldata entry. The interface provides you with an easy-to-follow menu for entering data,specifying defaults, and choosing import options.Flair vs. PeopleSoftAccount/Org (seven digits) Department/Project (eight digits)Department Fiscal Year-to-DateProject Life-to-DateObject code (six digits) Account code (six digits)Expenditures begin with a seven (7xxxxx)Revenue begins with a six (6xxxxx)Setting Up Your WorkstationImporting spreadsheet journal entries requires three files in the same directoryanywhere on your hard drive (DO NOT SAVE ON YOUR DESKTOP). The firstfile, UCF JournalTemplate.xls, is the control page you use to create and maintainjournal sheets. The two other files are the Message Log Template and the CompiledMacro Sheet for creating and importing journal lines and headers. These three filesare located on the F&A website athttp://www.fa.ucf.edu/auxiliary/offline journal entry/offline journal entry.cfm.F&A Form 41-910 (09/06)1

OFF-LINE JOURNAL ENTRIESThis form should be downloaded to your local drive - do not open it directly from yourbrowser. Downloading spreadsheet journal entries requires three (3) files in the samedirectory on the local drive of your computer. The first file, UCF Journal TemplateProd.xls, is the control page you use to create and maintain journal sheets. The two otherfiles are the Message Log Template and the Compiled Macro Sheet for creating andimporting journal lines and headers.To download, right click on each link on the Web page and then click Save Target As:File NameDescriptionUCF Journal Template Prod.xlsJournal WorkbookGLLOG.XLTMessage Log TemplateJRNLMCRO.XLACompiled Macro SheetCopy each of the three files onto the same directory on your local drive by right clickingon the link and choosing “save target as” for each file, one at a time.NOTE: IT IS VERY IMPORTANT THAT THE FILE EXTENSION FOR EACHOF THESE FILES BE SAVED CORRECTLY AS FOLLOWS (if your computerdoes not show the correct file extension (.xxx), change it as shown and then click onsave):UCF Journal Template Prod.xlsGLLOG.xltJRNLMCRO.xlaClose the ‘save as’ window after you get the message “Download Complete” (this shouldonly take a few seconds). Repeat the same process for the other two files.DO NOT OPEN ANY OF THESE FILES FROM THE WEB – close the F&Awebsite (or save the instructions on your hard drive if you desire by clicking on the linksaving the instructions and then close):Click here to view the Off-line Journal Entries User's Manual.F&A Form 41-910 (09/06)2

Setting Workbook DefaultsThere is an initial setup that only needs to be performed once.1. Open Microsoft Excel on your workstation. Then, from the toolbar, clickTools Add-Ins, then click the Browse button.2. Navigate to the directory where you stored the JRNLMCRO file. Selectthis file and click OK and Yes.3. Click OK again.4. From the toolbar, click Tools Macro Security to display the Securitydialog box:F&A Form 41-910 (09/06)3

5. Select the Medium radio button and click OK (above) to close thiswindow.You are now ready to create some entries.F&A Form 41-910 (09/06)4

Refreshing Chartfield MacrosIf the speed type or account code is not valid in your Offline Journal spreadsheet,your macros may need to be updated (this information is refreshed each eveningon the web).1. Open the UCF JournalTemplateProd.xls file from your directory to display thefollowing page:2. In the section marked “Update” click on each of the Account Crosswalkmaking sure that you have internet access. The above screen should appear:Right click on the link to download the Account Crosswalk:Account CrosswalkAfter downloading, please click the button below to close this window.F&A Form 41-910 (09/06)5

3. Right click on Account Crosswalk then ‘save target as’ and save (accept the filename in the window.)4. Click yes to save. You will get a message Download Complete, click on closetwo times and yes to close.5. This should take you back on the Spreadsheet Journal Import main page youwill need to click on the Dept Crosswalk and Project Crosswalk and begin againin stop # 2 for each.6. Finally click on the Update Speed Types and the following message willappear:7. Click on OK, to make sure your spreadsheet is using the most current speedtype information available.Entering JournalsAfter setting your options and workbook defaults, you are ready to enter journalsusing a copy of the Template Worksheet that is part of the journal workbook.The buttons on this worksheet are described in the table below:1. Open the UCF JournalTemplateProd.xls file from your directory to displaythe following page:2. Use the following worksheet buttons to perform tasks governed by theControl Worksheet. These buttons are divided into three groups asshown in the following table:F&A Form 41-910 (09/06)6

ButtonDescriptionGeneralSetupActivates the Define Options and Defaults dialog box. Using thisdialog box, you can set journal header defaults, import options,message logging options, online import controls for the workbook,and general options.NotesDisplays the Notes worksheet in the workbook for “scratch paper” toenter instructions, calculations, notes etc.Journal SheetsNewInserts a new journal sheet. You can add as many journal sheets asnecessary and enter as many journals on each sheet as you like.EditSelects a specific journal sheet from a list of journals sheets in theworkbook to edit.DeleteSelects one or more journal sheets in the workbook to delete.CopyCopies a selected journal worksheet to a new name.Import JournalsImport NowImports one or more journal sheets.Write FileUCF will not use this functionality.3. Click the Setup button to display the Define Options and Defaults page.Leave BlankF&A Form 41-910 (09/06)7

4. The Business Unit, Ledger Group, and Source values should default.5.6.7.8.9.10.The Date will default to the current date and your PeopleSoft User ID. Ifyou enter the letters in lower case, they will be converted to upper casewhen the journal is uploaded.Accept the defaults for the Enable Multibook (as shown above alreadyselected) and AutoGen Lines (selected) checkboxes.Accept the language default under General Options.Accept the defaults under Message Options for the Log Successful andError Messages and Display Messages Online checkboxes (as shown).Leave the Document Sequencing section blank. UCF does not use thisfunctionality.In the Online Import Control section, accept the default Address, Donot enter your PeopleSoft User ID, and accept the default option ofKeep import status as Import. This option will allow you the ability tore-import your journals. Note that you can override journal import statusat the sheet level for all journals on that worksheet or for journals selectedfrom a list.Click OK. The following warning message displays:11. Click Yes. You will be brought back to the Control page of theSpreadsheet Journal Import. Click the New button. The followingdialog box displays:F&A Form 41-910 (09/06)8

12. Enter a Journal Sheet Name (generally this is the same name as the journalid) and click OK. The following displays:Naming convention:XXXXMM DD01st1 four digits of Dept/Proj., Month, Day, Sequence #Note: If you have more than one person in your department processing anyIDIs, assign sequence numbers, for example 01-30, 31-60, 61-99.The buttons on this worksheet are described in the table below:Button*DescriptionReturns you to the Control Worksheet.Opens the Import Journals Now page.*(header section)Displays the New Journal Header page which you use to add a newjournal header with its own defaults.Allows you to select the journal header you wish.*Allows you to edit a journal header. Select the journal header(highlight it) and click Ok.Allows you to copy a journal.*(header section)Allows you to delete a journal.Changes the import status of a journal.(line section)Adds a journal line.(line section)Deletes the journal line where the cursor is currently located.Allows you to copy a block of lines for the journal whose header isdisplayed at the top of the pageAllows you to delete several lines within a range at one time.F&A Form 41-910 (09/06)9

ButtonDescriptionRun TotalSelect to view a running total of the journal which will appear next tothe Speed Types button. An Out of Balance or Balance Entrymessage will appear next to the total.AccountSelect to view a drop-down box of valid values.Alt AccountWhen Account is populated from the drop-down box, thecorresponding Alternate Account value will be populated under thisbutton.DeptID/ProjectSelect to view a drop-down box of valid values.Speed TypesWhen DeptID or Project is populated from the drop-down box, thecorresponding Fund and Program values will be populated underthis button.13. Under the Header section, click on plus sign () button. The JournalHeader will display with the information that was entered on the DefineOptions and Defaults page.Required fields are the Journal ID, User ID, Effective Date (will default)and Description.F&A Form 41-910 (09/06)10

14. Enter a Journal ID, your PeopleSoft user ID and Description.The Journal ID is a 10 digit alphanumeric field. The first 4 digits are thebeginning prefixes of your department/project series. The next four digitswill be the current date MMDD. The last two digits will be the journalnumber that you are generating for that day. For example, HumanResources would generate a Journal ID 0510070101. IMPORTANT: Donot duplicate the same number within the same day or the system willoverwrite your original entry.The Description field will be used to identify the individual sending thejournal - begin by entering your GroupWise User ID, then describe thereason (what and why) you are creating the journal. This field is 70characters so you can be specific on your details. If your description lacksdetail, your journal could be sent back for additional information. DONOT USE ANY SYMBOLS.15. Click OK.16. Under the Line section, click the plus sign () button. A line willappear.17. Enter the Account number, then click the Alt Account tab to display theUnit (UCF01), Ledger (ACTUALS), and Alt Account Number in theirrespective fields.18. Enter the Dept/Project number in the respective column. Click the SpeedTypes button to display the Fund and Program numbers.Note: All charges to a Dept/Proj. need to recorded as a positive amount. All benefitingamounts for a Dept/Proj. (including revenue) are recorded as a negative amount.F&A Form 41-910 (09/06)11

19. Click the plus sign () button again to add another line.20. If you want to add a block of lines, click.21. Enter the desired number of lines. For example, if you have a separatespreadsheet with 50 lines, enter 50 to open up 50 lines and copy theinformation from your other spreadsheet over (column by column).Repeat the entry of data until the journal is complete (maximum 200 lines perjournal) and check the following:The journal header contains a business unit (UCF01) and ledger group (Actuals).Data is entered in the proper fields and in the proper format.All lines have a valid department or project (clear the program and fund, then click onthe speed type – any rows with blank data are invalid) and account code (click on thealternate account).All entries equal zero (click on the ‘Run Total’ button to make sure the entry is inbalance).When you are finished, save the worksheet in a desired directory on yourcomputer. Be sure to save the worksheet under a different name so the templateis not changed.F&A Form 41-910 (09/06)12