Spreadsheet Format for ADI Upload

Spreadsheet Format for ADI Upload

Spreadsheet Format User Guide

StanfordUniversity

User Guide

Spreadsheet Format for ADI Upload

Financial Information Systems (FIS)

StanfordUniversity

Table of Contents

Objectives...... 2

Overview...... 2

Converting Text Files to Excel...... 3

Spreadsheet Format...... 8

Uploading Excel File to ADI Template...... 10

Loading Multiple Books...... 12

Objectives

  • Learn to create a Microsoft Excelspreadsheet that facilitatesthe journal upload process.
  • Get it right the first time to prevent unnecessary, duplicate effortby users as well as by Central Office staff.
  • Upload data in Excel spreadsheet to ADI template provided by Financial Information Systems, and ensure data is sent ready to be uploaded to GL.

Overview

By following the Spreadsheet Format guidelines, users will create spreadsheets that ensure the journal data is uploaded correctly and promptly for financial reporting.

Converting Text Files to Excel

The following screenshot is an example of a text file.

Open Microsoft Excel, and then open the file in Excel. A pop-up box (“Text Import Wizard”) will be displayed.

Once the 3 steps in the “Text Import Wizard” are completed, the spreadsheet is created.

Step 1

  • The default is usuallyset correctly to “Delimited”. If not, check the “Delimited” box.
  • Click the “Next” button to move to the next screen.

Step 2

  • Select the appropriate delimiters according to the data strings in the document (see example below).
  • Use the scroll bars to check the alignment of the columns, adjust where appropriate.
  • Click the “Next” button.

Step 3

  • Select “Text” for any date columns; this will ensure the data is formatted correctly.
  • Click on the “Finish” button.

A Microsoft Excel spreadsheet is created:

  • To properly upload the data, the Excel spreadsheet needs to be formatted according to the column headings in the body of the ADI templatewhich is provided by Financial Information Systems (see below example).

Spreadsheet Format

Format file for proper upload

  • File is not loadable when there are zeros in columns
  • Sort the debit column by ascending order.
  • Delete all zeros in any dollar columns.
  • Format number cells to:
  • 2decimal places
  • No commas –Do not use the 1000 Separator (,)
  • Organize columns in the Excel file so that they line up with the ADI template:
  • Ignore the “Upl” column in the ADI template.
  • Debits are on the left, credits on the right.
  • Enter a reason explaining the transfer in the “Description” column for auditing purposes.
  • The left side is comprised of the PFOO and the right of the PFO:
  • Captured Info DFF1 = Project
  • Captured Info DFF2 = Fund
  • Captured Info DFF3 = Organization
  • Two line transfers
  • Each outbound transfer needs to be immediately followed by the offsetting inbound transfer:
  • Verify balance
  • Ensure spreadsheet is in balance (debits = credits) before uploading to ADI template.
  • Delete any headings so that row 1 begins with a transfer line:

Uploading Excel File to ADI Template

  • Upload data to the ADI template:
  • Open the ADI template.
  • Enter the Batch Name in the white area (“To Fund Cost-Sharing Awards…” is the Batch Name in the below example)
  • Journal Name and Journal Description are the same as the Batch Name.
  • Unprotect the sheet and insert as many rows as needed:
  • Copy the information from the Excel spreadsheet and paste it on to the ADI template (C19).
  • Verify one more time that the debits = credits in the ADI file.
  • Send ADI file to FIS for upload to GL.
  • If there are any errors, the ADI file will be returned to the user for correction.
  • The error messages are displayed in the body of the file under “Messages”. See below example:

Multiple Books

If multiple books need to be uploaded:

  • Sort the file according to set of books.
  • Save each book in a separate Excel file.
  • Upload each set of books individually to an ADI template.

1

Top View