Docstoc

Sample Spreadsheet for Home Finance - PDF

Document Sample
Sample Spreadsheet for Home Finance - PDF Powered By Docstoc
					                                                 BUSINESS PROCESS PROCEDURE
                     Title:                      Create Batch Journal Entry File Using SAP Data
                     Organization/Area:          FI - GL
 Responsibility:     Depts Creating JE’s         Solution/Release: 4.5B
 File:               create batchje.doc          Status:            Complete

                                    Business Process Procedure Overview

  Journal entries can be processed in batch using SAP data (i.e., cost center, business area, GL account)
  by entering the data in a spreadsheet and feeding the data/file to SAP using the program ZFIJRNL1.
  The data file should be formatted like the IFIS F60 record layout with the exception that SAP data will be
  used instead of IFIS data. This process typically can be used, but is not limited to, uploading some of the
  year end journal entries such as: College Work Study charge backs, Salary Prorates, SPS Benefit
  deductions, Endowment Market Adjustments, and Endowment Income Distribution.

  This procedure will outline the following:

  1. Formatting the Excel spreadsheet so that the F60 record layout is resembled.
  2. Populating the correct fields with the correct SAP data within the spreadsheet.
  3. Saving the spreadsheet file in the correct format so that it can be fed to SAP.




                                           Procedural Steps

1. Download the file Create Batch Journal Entries for F60 from the SAP Project
   Team home page at http://www.controller.cmich.edu/SAP/New_Folder/fi.htm.
   This file should contain the settings listed below when you enable the macro.
   If it does not, press Ctrl-shift-m to run a macro that will implement these
   settings.
                 OR
   Open a new Excel spreadsheet and format it as follows:
        a. Open a new spreadsheet within Excel.
        b. Click the blank button above the row 1 button to select the entire sheet.
        c. Change the font to Courier New 8.
        d. With the entire sheet highlighted, click the Align Left button. This
           ensures all the data is left justified within the cells.
        e. With the sheet still highlighted, select Format > Cells. Click the Number
           tab. Select Text, then click OK. This will format the cells to display
           exactly as the data is entered.
        f. Format the width of columns A-M by clicking once on the column letter
           to highlight the column. From there select Format > Column > Width.
           Enter the width (number of characters) for the column, then click OK.
           Format columns A-M with the following widths:

              A=1, B=3, C=29, D=6, E=6, F=6, G=10, H=11, I=6, J=11, K=50, L=36, M=1

                   Note: All of the columns for this file must be formatted even though
                   only a few of them will actually contain data.


Last changed on:       Last changed by: Dale Sandy              Version: 1.0                          Page:
11/19/07                                                                                              1 of 2
                                               BUSINESS PROCESS PROCEDURE
                   Title:                      Create Batch Journal Entry File Using SAP Data
                   Organization/Area:          FI - GL
 Responsibility:   Depts Creating JE’s         Solution/Release: 4.5B
 File:             create batchje.doc          Status:            Complete



2. Save the spreadsheet as an Excel file (with a .xls extension). This will serve
   as a template for entering journal entries.

3. Open the Excel template created in step 2. To create the journal entry,
   populate the following columns in the spreadsheet with the data indicated:

         B = F60
         D = Business Area (needed if entry is a Business Area/GL entry only)
         E = Cost Center
         F = GL acct
         G = Internal Order
         I = Document date (‘YYMMDD format)—you must start the entry with the
         hyphen (‘) to obtain proper filling of zeros
         J = Amount (whole number, no decimal, ex: enter $150.25 as 15025)
         K = Line item text
         M = D or C (debit/credit indicator)

         Note: All data should be in uppercase.

4. Save the file with a name different from that of the template created in step 2.
   This file should be saved as a PRN file. Proceed as follows:

    a. Click File > Save As
    b. Name the file, then select .prn file extension from within the Save As Type
       field.
    c. Click Save.
    d. Click OK on the window that appears.
    e. When closing the file, click Yes on the window that appears. The Save As
       window will re-appear. Click Save, then click Yes. Click OK. The file
       should now close.

5. The file can now be forwarded to Business & Finance Information Systems for
   testing and processing.

6. A sample spreadsheet template file prior to saving it as a .prn file is on the
   next page.




Last changed on:     Last changed by: Dale Sandy              Version: 1.0                      Page:
11/19/07                                                                                        2 of 2

				
DOCUMENT INFO
Description: Sample Spreadsheet for Home Finance document sample