pennsylvania banner users group 2008 fall conference spreadsheet budgeting

Pennsylvania Banner Users Group 2008 Fall Conference

Spreadsheet Budgeting

General Announcements:

Please turn off all cell phones/pagers If you must leave the session early, please

do so as discreetly as possible Please avoid side conversations during the

session Questions will be answered at the end of

the presentation

Thank you for your cooperation

Dickinson College

Margaret Stafford, Budget Analyst Polly Willey, Application Support Analyst Contact Info:

stafford@dickinson.edu willey@dickinson.edu

Dickinson College

What is Spreadsheet Budgeting? An Add-in tool that allows Microsoft Excel to

interface with the Banner database An Add-in is a file that Excel can use to

provide additional functionality Once data is downloaded into Excel, all

Excel features are available

Dickinson College

Minimum System Requirements Microsoft Windows 95/98, Windows 2000,

Windows NT 4.0, Windows XP Microsoft Excel 97, 2002, 2003, 2007 Oracle database, version 9.2.0.3 or greater Oracle Net8 Client Software or greater One licensed copy of Visual Basic 6.0

(Professional Version)

Dickinson College

Installation Process DBA needs to install the Desktop Tool

(GODDTOP) on DBA machine only Install Desktop Tool onto client machines Add users to GORDSEC table – Security

setup Verify users are on FOMPROF Verify users have Oracle security

Dickinson College

Icon installed on Desktop:

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Note: You can chose more than one fiscal year. (Control Key)

Dickinson College

Dickinson College

Hint:Adopted Budget: BD01, BD03Budget Adjustment: BD02, BD04Temporary Budget: BD03, BD04

Dickinson College

Dickinson College

Dickinson College

Hint: On the FOAP screens, no selection means “Select All”

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Use the Validation Wizard to validate data before beginning an upload process

Dickinson College

Prior to running the validation process on a spreadsheet:

Undo any local Excel formatting (undo macro)

Must have a Budget ID and Phase established in the Budget Module

Each unique FOAPAL string may only be used once in a budget phase

No blank lines in the spreadsheet No formulas One header line

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Validation Notes: You can validate more than one worksheet at

a time You can choose only:

One chart of accounts One Budget ID One Budget Phase

Header Row must be first row, data must be in second row. No blank rows.

Dickinson College

Examples of Validation Messages: “Organization code not found” “Record already exists on Budget Line Table” “Record already exists on the Spreadsheet” “Fund is not defined as data entry” – Please

note that this validation only works for funds and organizations, not for accounts and programs. The defect has been reported to SunGard in Feb. 2008

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Dickinson College

Upload Notes: You must undo any local Excel formatting prior to

the upload You must have Budget ID and Phase already built

in the Budget Module Each unique FOAPAL string may be used only

once in a budget phase Validation process will automatically run when

running upload process Data will be uploaded to FBBBLIN table Only one header row; no empty rows

Dickinson College

Lookup Functionality: Separate data lookup is available for each of

the FOAPAL items. Also available are budget module and position control items.

You can do a lookup without having a Banner session open.

You can search by code or description using the wildcard (%).

Note: All active codes for the FOAPAL will be displayed despite the Banner Fund/Organization security defined for the user.

Dickinson College

Dickinson College

Dickinson College

Many Uses: Ability to upload several lines into a phase

without rekeying We downloaded the “Approved” phase prior to

rolling it to check the totals using excel functions

Ability to download from operating ledger allows one to build custom reports from actuals

Probably many others…

Dickinson College

Further Information: A Banner Finance Spreadsheet Budgeting

Handbook is available in the Bookshelf and the UDC Center

Setup and Technical specifications can be found in Chapter 10 of the Technical Reference Manual

SunGard does have a training CD on this application – contact your SunGard Account Consultant

Dickinson College

Final Notes: You can run this application on a MacIntosh

computer by using a PC emulator program. Information can be found in the General Technical Reference Manual

This tool does not incorporate the use of index codes.

We highly recommend this add-in tool

Open to the Floor

Questions Comments