WHAT IS GUS?
GUS is a custom database system for managing and maintaining funding source, purchasing, lien, and other financial information for departments and research organizations across the UCSB campus. The primary goal of developing GUS was to simplify the time consuming and laborious tasks that are essential to managing intramural and extramural funds, expenses, and reconciliation, while minimizing errors. Some of the features are:
- monthly and annual reports;
- monthly reconciliations with the campus ledger;
- communication with clients;
- carry forward; and
- statistics collection
Since its inception in 1998, the user base has grown from one research organization to more than 70 units across the UCSB campus.
There are many things to consider as you determine whether GUS is the right tool for your unit. For example:
- Do you currently track finances with project codes?
- Are you considering GUS for a research-intensive unit?
- Do you have a staff member that is familiar with GUS?
It is recommended that you reach out to colleagues for a demonstration of GUS features to see how it is used at other departments. The experiences of other GUS users can be extremely helpful as you evaluate whether the functions meet your departmental business processes. If you conclude that you would like to implement GUS for your unit, the next step is to contact the GUS Executive Committee with the request and find one or more mentors to help support you during the transition process.
4D is a programming language with a set of tools that can be used to create and manage database systems. GUS is a custom database system developed at UCSB using 4D. Although the programming requires 4D developer expertise, the customer interface was designed to work with minimal IT staff support. The role of the departmental GUS Administrator is generally designated to the departmental business officer or financial manager.
There are occasional upgrades for both 4D and GUS. The latest versions released for the campus are 4D v18.3 and GUS 8.1.
INSTALLATION AND ADMINISTRATION
Access to GUS is based on 4D licenses. In the past, departments were responsible for license procurement and contract maintenance with 4D. In August 2020, we completed the purchase of a 4D site license centrally to support existing use cases across the campus, primarily GUS.
The latest version implemented for GUS at UCSB is 4D v18. The minimum system requirements for the local computers are:
The installers are as follows.
- The installer for Windows machines is: https://download.4d.
com/Products/4D_v18.3/ Installers/4D_v18.3_Windows_ 64-bit.exe
- The installer for Mac machines (not on Big Sur) is: https://download.4d.
The web application is a compelling way to provide 'read-only' access to individuals who need to review records or balances. Users can login to GUS through a web browser rather than having to install 4D.
Authenticated users can access one, two, or all three of the categories below.
- Financial: summary of projects, financial statements, audit trails, outstanding liens, unreconciled reports, and award conditions.
- Payroll: payroll summary, payroll by project, and payroll by person.
- Purchasing: order history, new purchase order, duplicate order, and edit order.
The departmental GUS Administrator can activate the web server and also set permissions and access.
To activate the web server, go to Utilities > Preferences > Web > check the box for "Activate Web Server."
GUS was designed to work with minimal IT staff support. Generally, the departmental business officer or financial manager is the designated GUS Administrator.
The "Administrator" account is used to serve functional purposes, such as permissions, access management, and password reset. For day-to-day business use, the best practice is for individuals to login with their individual accounts that were created and authorized by the departmental GUS Administrator.
To manage user accounts, login with the Administrator credentials > go to Utilities > Edit Access.
From the next screen, the Administrator can add, remove, or edit permissions, rename or duplicate accounts, or change passwords.
- To add an account: click the '+' button under the users list.
- To delete an account: select the username > click the '-' button under the users list.
- To edit an account: select the username > edit permissions by marking or unmarking the checkboxes on the right.
If you are the departmental GUS Administrator and need assistance with access management or resetting the Administrator password, please contact us through IT Services > Enterprise Business Applications > GUS.
The following are access management recommendations from experienced GUS Administrators.
- To create a new account with the same permissions as an existing account:
Select the existing account > click on the dropdown arrow next to the settings gear > select "duplicate."
- When an account will no longer be used, but the account should be retained:
Select the account > change the password > click on the dropdown arrow next to the settings gear > select "rename" > prepend 'x' on the username.
This will move all past accounts to the bottom of the list.
- To change your own password:
Go to the "utilities" menu > select "change password" > enter the information in the pop-up window.
|Budget||The Budget module is the core of GUS financial management, which is based on Projects.
|Contracts & Grants||The Contracts & Grants module is the starting point for many of the funds managed by Organizational Research Units (ORUs).
|Personnel||The Personnel module is used for payroll expenses and payroll projections.
The Recharges module can be used to track and manage departmental recharges and generate the financial journals.
BUDGET AND PROJECT
A Project is the basic financial unit in GUS. It is a means of organizing and tracking awards, including budgets, expenses, personnel, conditions, and other details necessary for managing awards from inception, progress, changes, to close. Each Project corresponds to a single account-fund, while a single account-fund may include multiple Projects.
In GUS, Projects are tracked by adding a field to the Central Accounting LAFS structure, as shown:
Location - Account - Fund - PROJECT - Sub
Using this methodology, the Project field allows for the management of distinct pools of monies within a single account-fund.
Create a Project
Depending on departmental activities, there are different strategies behind Project setup and different users have developed methods to meet their unique needs. Here, we present typical use cases.
- Scenario A
If a Project begins as an awarded proposal, you will be prompted to create a new Project from the Proposal screen.
- Scenario B
If a Project is similar to ones already established, you can select an existing Project and copy it to a new Project and edit/update the detailed information.
- Scenario C
If you are creating a new Project from scratch, go to the menu --> select 'Projects' --> click on the 'New' button --> 'Award Information' tab --> enter the information for the following fields.
Fund a Project
Projects are funded through project increments, which can be allocated to specific Subs or Cost Types. A Sub must have an associated Cost Type. In GUS, funding and transactions are tracked by adding fields to the Central Accounting LAFS structure, as shown:
Location - Account - Fund - PROJECT - Sub - COST TYPE - Object Code
Please refer to the sections on Subs and Cost Types for more details.
Delete a Project
DELETING A PROJECT IS PERMANENT!
If you are really sure that you need to delete a project, the steps below outlines the process.
- Login with the "Administrator" account credentials and go to Utilities > Admin Projects > select the Project to be deleted (you can use the Select tools to drill down to the appropriate records, or select a set containing the records to be deleted and choose them one at a time).
Note: All actions shown in the "Actions" column will be performed on the corresponding record when you click "Perform Actions." To prevent unintended actions from being executed on unrelated records, clear the actions on all visible records before proceeding.
- To clear the "Actions" columns for all visible records, click the "Actions" button > None > Set Action.
- To delete all shown Projects (if not applicable, skip to step 5), click the "Actions" button > Delete Project > Set Action.
- To delete individual Projects one at a time, double-click the Project to be deleted > Project Setup > Action > Delete Project > Set Action > OK.
Repeat this step for each Project you wish to delete.
- Log out of the "Administrator" account.
A Sub (abbreviation for subaccount) must be defined per Project and Subs must have an associated Cost Type.
To define a Sub, go to the menu --> select 'Projects' --> 'Budget Information' tab --> click on '+' --> 'New Sub' screen --> enter the information for the following fields:
The 'Allocation Level' is to indicate whether the allocation is to be received at the Sub or Cost Type level.
Cost Types provide another level of reporting. They are unique to the GUS Project hierarchy and are tracked by adding a field to the Central Accounting LAFS structure (in addition to the Project field), as shown:
Location - Account - Fund - PROJECT - Sub - COST TYPE - Object Code
Using this methodology, Cost Types allow for aggregated reporting of object codes. A Cost Type is required when defining a Sub for a Project.
Manage Cost Types
To add or delete Cost Types, go to the menu --> select 'Projects' --> click on 'Cost Types' --> update the information.
Select Existing Cost Types
To select existing Cost Types when defining a Sub for a Project, go to the menu --> select 'Projects' --> 'Budget Information' tab --> click on '+' --> 'New Sub' screen --> click on '+' --> select Cost Types.
Generally, fiscal year (FY) designations are not be used on Extramural funds because Projects are not limited by FY. A grant may span across 5 years, for example, and while funding increments are received annually, the Project as a whole is not associated with a particular fiscal year.
Departmental funds, however, are rolled up to sub 3 every year by the Office of Business and Financial Services (BFS), and must be re-allocated for the new FY. Some departmental funds, such as recharges, are collapsed into sub 8, and gift fund balances are rolled over to the next FY. Therefore, these types of funds should have FY designations.
Understanding the FY designations for Projects will make the annual accounting roll-up and carry-forward process in GUS much easier.
Overhead rates will sometimes change and updating the information is a 2-step process.
Step 1. Go to Project > Budget > change the overhead on the related subs
Step 2. Run the "Check Overhead" utility > click on "Check OH rates" and review the discrepancies > select "Update OH Rates."
CONTRACTS AND GRANTS
The C&G module is integrated with the basic GUS Budget module for the management of "Projects." This feature provides a means to track and manage Extramural funding proposals and awards.
Department users can list proposals and awards, run reports of current and pending support for individual PIs, and produce summary reports that include descriptions of proposal- and award-related activities. These reports are optimized to support the Office of Research and the Organized Research Units (ORUs) Annual Report requirements. Additionally, the award abstracts can be displayed on the web.
The workflow begins when PIs submit proposal(s) to funding agencies to perform some work. If a proposal is accepted, the funding becomes an "Award," and the "Award" will then be assigned as one or more "Projects." (See Budget Documentation section for more information on "Projects.")
When an agency accepts a proposal, the funding becomes an "Award," and the Award will then be assigned as one or more "Projects." (See Budget Documentation section for more information on "Projects.")
A Funding Agency is the entity that provides funds for an accepted proposal.
A proposal is a document (or a set of documents) that outlines the specifics of a proposed investigation. Research proposals can be documented in GUS and the advantages are: (1) there is a search feature available and (2) if a proposal recorded in GUS is accepted, GUS can automatically generate an "Award" record.
Individual PI and department-specific reports can be generated using the C&G reports feature.
The reports will show in the editor window. Copy and paste the results to the editor of your choice.
Sub Agreements can be considered as 'sub-contracts.' The sub agreement screen will only display the records and data. To edit sub agreement information, go to the "Proposal" or "Award" screen.
What Are Condition Codes?
Condition codes (also referred to as Award Conditions) are a type of flag which may be attached to an "Award" or "Project" to indicate restrictions or conditions. They are generally numeric values, although sometimes with an accompanying letter, classified by category, and includes a short description. GUS allows up to 6 characters in length.
There are several project areas in GUS that provides alerts: Project management, Purchasing, and Personnel.
Who Publishes Condition Codes?
The Office of Research (OR) publishes a set of condition codes from the ORBiT database. Accordingly, we provide a condition codes file in a format that can be imported to GUS. The import operation is idempotent, meaning that repeatedly importing the same file will not result in changes to the database.
In addition, departments can create and define custom condition codes to assist with award management.
For example, a particular grant has specific personnel limitations. The department can create a condition code "PR" and description "Changes in Senior Personnel or % time require prior approval," and check the "Alert Pers" box. Subsequently, when a distribution shows funding by the restricted project, the "Personnel" screen will display an alert.
Importing OR Condition Codes
Any GUS user with access to the Budget module can import the condition codes published by OR.
- Save the condition codes file to your computer.
- Login to GUS and navigate to Project > Award Condition Codes
- Choose the new menu item under Utilities > Import Award Conditions
- Select the condition code file that was downloaded in step 1 and a screen showing the condition codes will appear > choose "Import."
The management of personnel expenses (payroll) is handled per employee. The process starts with adding an employee and appointment. After the information is added to the GUS database and defined with related distribution(s), then projections can be made. The projections will serve as placeholders for future (committed) expenses and can be visible on the budget side of GUS.
Adding an employee requires: first name, last name, email, and UC ID number. Although the other fields are not required, the additional information is important to making reports meaningful.
If funds are to be placed on hold for a PI (e.g. beginning a search) and the employee is TBD, you can create a placeholder and update the employee information upon hire.
Once an employee has been defined, open the window for that employee record and select the "Appointments/Distributions" tab to manage the payroll information.
There are three types of distributions: regular, what-if, info only.
Payroll projects, which create payroll liens known as SP liens or Main SP liens, are dynamic in nature and can fluctuate over time. To maintain discretion, the Main Salary Projection (Main SP) Lien amount shown on the budget side is a lump sum of all projections and reconciled expenses. This amount is reduced by expenses as they are posted.
The following example is an over-simplification, with the intent of conveying the concepts.
If a person is paid $12,000 this year, a portion of the budget must be committed to account for that anticipated future expense ($12,000).
- When the year starts, we plan to pay $12,000 total for the year (actual expense $0 and $12,000 remaining liens.)
- After one month, we still plan to pay $12,000 total for the year (actual expense $1,000 and $11,000 remaining liens).
Q: Where does the benefits rate value come from?
A: The appropriate benefit rate for the individual employees, based on Composite Benefit Rate (CBR) structure, should be entered in the distribution.
Q: Where does the DOS code come from?
A: This is the Earn Code in UCPath.
Q: How are the monthly projection values calculated?
A: Monthly pay rate, benefits (% of rate), and distribution percentage.
Q: How to accommodate change in pay rate (percentage or projection dates)?
A: The past (reconciled) expenses remain and the future payroll transactions need to be re-calculated for the projected new rate (or dates). Ideally the distribution that needs to be changed (rate or percentage) should be projected through to the last date. A new distribution should be created with the new pay rate and/or percentage.
There are two methods of payroll reconciliation: Post/Reconcile and Assisted Reconcile. The former method is fully manual, whereas the latter method is semi-automated.
This approach was the standard prior to the creation of the Assisted Reconciliation tool. Using a print out of the department payroll ledger, a departmental staff member can navigate to the Payroll Post/Reconcile screen and proceed with the following steps.
- Enter the ledger date and project code
- Select an employee
- Select the corresponding payroll line
- Scan the entire record and make corrections where numbers differ
- Correct any boxes that are outlined in red
- Mark the item as 'Reconciled' if the records match and click on 'Save'
- Repeat for all records on the payroll ledger
Within the Assisted Reconciliation window:
- Click on the dropdown box for 'Import Options'
- Select 'Direct Import'
- Enter the ledger date
- Enter the Hyperion login credentials
- Click 'Import'
The records are now ready for reconciliation.
The Recharges module allows the entry, processing, and tracking of departmental recharges. It generates reports, including printed and electronic Financial Journals, which are sent to Accounting, and the backup documentation to PIs and other departments. It will also post the credits and debits associated with recharge activity to the Budget component in GUS.
Department A provides goods or services that other departments want and that are based on a recharge rate, which were approved by the Office of Budget & Planning.
- Enter recharge activities into GUS
- Recharge activities are associated with a receiving account/fund, project, sub, cost type, and object code that are already setup and managed by Department A in GUS. (Note: Cost type is utilized if recharge is internal to your department, blank if recharging an external department.)
Department B consumes the goods or services provided by Department A
- Department A enters Department B as an "External Department" in GUS
- Department A enters the recharge using one of the three methods below:
(1) Enter Recharges
(2) Bulk Recharge Entry
(3) Import Storeroom Recharges
Recharges are set to a journal with a journal date, and the journal recharges are posted to budget.
The initial setup for recharges include:
- Setting preferences for your department
- Defining acceptable recharge activities
- Defining external departments and accounts
- Associate recharge activities with an account/fund, project, sub, cost type (if internal to department), and object code
[Step 1] Enter New Recharges
There are three different ways to enter new recharges:
1. Manually, via the 'Enter Recharges' screen
2. Copy/paste from Excel into the bulk-recharge screen
3. Import from a file that has been created by a recharge service
[Step 2] Review/Edit the New Recharges
A. Review New Recharges
To review new recharges, go to the 'Recharges' screen.
B. Edit Recharge
To edit a recharge, go to the 'Recharges' screen, double click on the line item, and make changes in the 'Edit Recharge' pop-up window.
C. Associate Recharges with a Financial Journal
Search for recharges that have not been added to a journal (Financial Journal = "Not Set"). If further refinement in the selection is required, narrow the query set by entering other search criteria.
Click on 'Set Journal' when the recharges are displayed correctly, and affirm the journal date.
If there is not an existing journal to associate with the recharges, create a new journal by selecting 'Recharges' from the main menu > 'financial journals' > click on 'new.'
D. Review Funding Sources
It is important to review the information before sending recharges to Accounting. On the 'Recharge Activities' screen, recharges to external departments or are on open projects with funding will be highlighted, and an error message will be on display for internal projects that will result in overdrafts. Review and correct these items as necessary. Once you have accepted the appropriate projects, you can set the journal.
E. Best Practice Recommendation
Prior to submitting the Financial Journal, as best practice, we recommend that you save a copy of recharges to external departments in Excel, and provide the Excel file to those departments so they have a chance to review/correct account-funds.
[Step 3] Print and Review the Financial Journal
Navigate to the 'Print Recharge Reports' screen to generate the Financial Journal, either a hard copy or an electronic file.
If corrections are needed, return to step 2. If the information is correct, continue to step 4.
[Step 4] Submit the Financial Journal
To submit a Financial Journal, first upload the electronic version, then confirm with Accounting that there were no upload or edit issues, and lastly, provide a paper copy.
Accounting requests the hard copy includes the text: "DO NOT DATA ENTER - FILE SENT TO ACCOUNTING COMPUTER SUPPORT" along with text indicating the file name, the department, and the name of the submitter.
[Step 5] Post Recharges to the Budget Module
Once Accounting verifies the file upload was error free, it is time to post the journal. Navigate to 'Budget' on the menu > select 'Recharges' > click on the 'Post Recharges' button.
[Step 6] Documentation
Print a copy of the Financial Journal (electronic or hard copy) as backup documentation. It can also be distributed to PIs and other departments as needed.
Provided to Accounting. This report included information not normally contained in traditional financial journals. New information includes Project Codes, Cost Types, and additional PI information in the Account Names.
Project Statements and Other Department Statement
Internal/External Recharge Statements can be generated from GUS Reports, which can be used to obtain PI approval and/or serve as backup for the transactions.
The Purchasing module is integrated with the UCSB Procurement Gateway via regular automated data downloads. Equipment inventory, insurance information, requisition approvals, and split funding can also be tracked in GUS.
Gateway requisitions are integrated with GUS via regular automatic data downloads. In addition to purchase orders, the Gateway approval process is also integrated with GUS to record subs and cost types.
To start the approval workflow in GUS for Gateway requisitions, open the 4D client > menu > SciQuest > Approve Requisitions.
The UCSB Procurement Gateway will load into a web area within GUS, where you can log in with your UCSBnetID and password.
Accessing Gateway requisitions from the GUS view is the same as directly accessing it in Gateway. Go to the upper right of the webpage and pull down the menu "Action Items." The number on the icon reflects the number of requisitions pending approval. Select the menu and click "Requisitions To Approve."
Once the requisition has been approved in Gateway, a lien will automatically be created in GUS.
If there are any problems creating the lien in GUS, an alert will pop up with a note of any special circumstance. For example, in the case below, a lien is created but the overhead rate may be incorrect.
In cases where GUS still cannot identify the sub or cost type, a different alert will appear to ask if you want to proceed without a lien. If you choose to proceed, the requisition will be approved in Gateway, but a manual lien will need to be created in GUS.
The equipment module in GUS provides an inventory of UCSB equipment owned by your department and the insurance that was purchased for the equipment.
To access the equipment inventory, select "Equipment" on the menu bar, then "Inventory." To add a new item, click on the "New" button at the bottom of the screen.
To view details for an existing item, double click on the item of interest and three tabs will appear: Inventory, EIMR/Insurance, and Source Info.
Enter or edit inventory information in the "Inventory" tab.
If an equipment item has an Equipment Inventory Modification Request (EIMR), the details can be modified in the "EIMR/Insurance" tab. Add or remove EIMR or insurance entries by clicking the plus (+) or minus (-) buttons.
Quick access to the insurance entries added in the screen above is: Equipment > Insurance. This view allows filters at the top of the screen.
Funding source information is located in the "Source Info" tab.
Split Funding in Purchase Order Screen (orders not placed via Gateway)
Split funding is a feature that is available in both Gateway and GUS. When more than one funding source is associated with a purchase, the use of split funding may be appropriate.
A funding source is a unique combination of Project, Sub, Type, and Object Code. The process for establishing the split fund varies depending on how the vendor blanket is set up (LVPO, FLEX, SBPO, or VB).
If a Project exists in more than one fiscal year, each fiscal year will count as a separate funding source even though the Project, Sub, Type, and Object Code are the same.
Low Value POs may be split only among Projects with the same account and fund number.
To split an order, click on the "Split" button, then enter the new funding source information.
To add a funding source, click on the plus (+) button and enter the information in the subsequent screens.
If the order type is "VB," the additional funding type must be set up from the vendor blankets screen (Purchasing > Vendor Blankets).
Click on the plus (+) button in the Vendor Blanket screen and define the funding source.
By clicking on the plus (+) button above the funding sources screen again, this will allow additional fund source entries.
There are several open communication channels. For questions about your workstation or equipment, please contact the IT support group for your department. If you have questions about 4D or GUS, or to report issues, please contact us through IT Services > Enterprise Business Applications > GUS.
Additional resources are as follows.
The Executive Committee provides oversight to the direction of the GUS project.
|Jane Kittle (Co-chair)||8316||OR|
|Cecilia Sousa (Co-chair)||3488||ERI|
The GUS mentors are seasoned GUS users who have graciously offered to assist with business use questions.
The GUS mailing list is used to communicate general announcements and also open to subscribers for sharing knowledge, best practices, tips and tricks, and general business processes. If you would like to subscribe to the GUS mailing list for general announcements, please sign in to groups.google.com with your firstname.lastname@example.org account, search for "GUS Mailing List," and add the group.
In addition to the GUS Mailing List, there is also a Google Chat Room for GUS. To join the group, please sign in to chat.google.com with your email@example.com account and search for "GUS Users."
If the Chat feature is turned on from your Google inbox, you can search for the "GUS Users" group from the Chat navigation bar.