LSCC Finance Functionality Report 12-1-05

Document Sample
LSCC Finance Functionality Report 12-1-05 Powered By Docstoc
					                                                           Finance Functionality Check-up Report
                                                                                December 1, 2005

                              Lake-Sumter Community College
              Follow-up Report for November 14, 2005 to November 16, 2005

Account Information
Project name:           Lake-Sumter Community College Finance Process Improvement Assessment
Prepared by:            Richard D. Gerry
                        Principal Functional Consultant - Finance
                        800-541-5330 ext 4419

Lake-Sumter CC          Richard Scott              Vice President for Administration
Lake-Sumter CC          John Froman                Director of Financial Services
SunGard SCT             Anne Ceccato               Account Manager


   1.   Validate staff knowledge and review current use of SCT Banner
   2.   Determine use of best business practices
   3.   Identify ways to increase accuracy and ease of access for staff
   4.   Increase LSCC’s utilization of delivered Banner tools

Progress Report

SCT Banner Finance Reviewed:


   1. Chart of Accounts roll up of funds and organizations – currently LSCC is using organization
      codes with campus locations as part of the organization (e.g. 62250L, 62250C, 62250S). The rational
      for this was when LSCC first implemented SCT Banner the college would budget by campus.
      However, the current administration has changed this and LSCC budgets by departments and tracks
      expenditures by locations. The budget is loaded in organization 62550 and the year to date
      transactions is in the lower organization 62550L for example. With this current set up an end user
      cannot readily see if they have enough money for the year in FGIBDST, and must go to FGIBAVL to
      see true budget availability.

        Since LSCC is also using location codes to track expenditures, in addition to the organization codes,
        my recommendation is that LSCC both records budget and expenditures in the higher organizations
        (62550) and terminates the lower organizations (62550L, 62550C, 62250S). By doing this, LSCC will
        make FGIBDST as well as any Finance Self Service Budget Query useful to see available budget
        without having to go to FGIBAVL. LSCC will continue to use location codes on its budget and
        expenditure transactions to track costs by location. This one change will reduce the number of
        organizations, improve usability of FGIBDST and Finance Self Service Budget Queries and reduce
        the number of approval queues needed based on organization.

 Lake-Sumter Community College                                                                          v1.1
 Banner Finance Functionality              SunGard SCT                                            2/14/2012
 Check-up                            Confidential and Proprietary                                Page 1 of 8
                                                          Finance Functionality Check-up Report
                                                                               December 1, 2005

      Making these changes to the organization codes will have an impact across the system. First, the
      changes to the organization codes should be made effective at the start of the new fiscal year so that
      they do not have to move the old transactions to the new organization during a mid-year change.
      Changes will need to be made in these areas that use organization codes: account indexes, defaults
      on FTMFUND, student detail codes, and HR Position Control set up.

      I also recommended that the organization hierarchy be changed. Currently the president and vice
      presidents have to look up a number of non-data enterable organizations to get a picture of what is
      happening at the college. If the hierarchy was changed to start at the president level and work down
      to the department level, then the high-level cabinet members can use FGIBDST and Finance Self
      Service Budget queries more effectively by keying in their organizations and seeing total budgets and
      expenditures, and not having to add up numbers from different organizations.

      I saw no need to make changes with the fund structure at LSCC but I did explain how the fund types
      are used to override FTMCOAS in year-end processing. If LSCC were going to use the Research
      Module, then they would want the grant fund types to roll the available balance at year-end. Rolling a
      grant’s available balance at year does two things. First, it eliminates the research accounting
      personnel from recording the available balance entry by hand in the new fiscal year. Second, it
      enables LSCC to use the inception to date query form FRIGITD. When SCT Banner rolls the
      available balance for a grant at year-end, FRIGITD does not pick up the year-end entry and add more
      money to the grant budget. For example, a grant with an original budget of $100,000 and
      expenditures of $20,000 would have an $80,000 available balance at year-end. When SCT Banner
      rolls the $80,000 at year-end during the FGRBDRL, FRIGITD will still show the grant having a budget
      of $100,000. Again, FRIGITD is programmed to ignore the entries from FGRBDRL. However, if LSCC
      makes a manual entry of $80,000, then FRIGITD shows that the grant has a budget of $180,000.

      LSCC did state that on the FGRBDSC report they were getting a lot of zeros on the report. In
      reviewing several different funds on the report it was found that the zeros are being created by
      transaction that have been washed out (net result zero - 100 in 100 out) or by the year end
      FGRBDRL. The zero dollar transactions occurring from FGRBDRL are old funds that have been
      washed out but the fund itself was never terminated in FTMFUND. Therefore, SCT Banner keeps
      rolling along the zero balance forward year to year. I recommend this old funds are terminated.

  2. Review Fund/Org Security set up – LSCC is using fund/org security in Banner but no rule code
     security at this time. The set up was in general adequate. I viewed only a handful of individuals and
     LSCC did not report big issues with the security.

      I did recommend that LSCC add the user’s organization and default ship to address on each profile to
      default this information in when the end-user is keying in a requisition. I also suggested that they
      remove the ability to process invoices to those people that are not in accounts payable. I explained
      that the research accounting people should have the ability to post journal vouchers during the trail-
      out period with the grant. I also showed LSCC how to remove people that are set up in FOMPROF
      but have since left the college. In addition, I explained that since the organizations will be changing
      then the organization security would have to reflect those changes and the quickest way to make
      those changes would be to use sql.

      Finally, I suggested that the accountants that process year-end transactions be given access to post
      in the accrual period. I found out that the accrual period was not being utilized during the year-end
      process. LSCC does not close period 12 until the auditors have left in August, which means that the
      accrual period is never utilized. This also means that individuals may back date transactions to June
      even in August and adjust year-end numbers that were once thought confirmed. I showed LSCC that

Lake-Sumter Community College                                                                          v1.1
Banner Finance Functionality              SunGard SCT                                            2/14/2012
Check-up                            Confidential and Proprietary                                Page 2 of 8
                                                         Finance Functionality Check-up Report
                                                                              December 1, 2005

      they should close period 12 after the year-end invoices are finished around the second week in July.
      The accrual period, period 14, will be open since they run FGRGLRL on July 1 and that processes
      opens the accrual process. From the second week in July to when the auditors leave the accountants
      that have the ability to post to the accrual period may do so. Thus, keeping a better control of the
      year-end process.

      In addition to the changes to the fund/org security I reviewed the set up FOASYSC, FTMCOAS and
      FTMFSYR. All three were set up well, with the exception of fiscal year 2000 and 2001 in FTMFSYR
      and no NSF checking for Purchase Orders. The fiscal years still had their accrual periods open and
      the accrual periods need to be closed using sql. There should be NSF processing on Purchase
      Orders since purchase orders can be created by themselves and not through requisitions, which does
      have NSF checking.

  3. Review of Approval Queues – LSCC has approval queues set up for invoices and requisitions.
     There are 36 approval queues that are working and 12 that are not being used. I showed LSCC how
     to delete old or invalid queues by first removing the queue criteria and then the queue itself. For the
     36 approval queues that are working, many of them have do not have established back-up approvers
     in the various approval levels. LSCC has indicated that this has been an issue when people are on
     vacation or sick and key documents are not being more forward through the queues. In reviewing the
     queues, we also came across a number of individuals who are still active approvers but no longer
     with the school.

      I spent time with LSCC showing them how to properly build an approval queue that utilized back up
      approvers and the best way to set up the queue criteria. I have attached a set of example queues that
      LSCC can use a guide. This document was discussed at LSCC and was built in the training instance.

      With the changes to the organization codes, the number of queues with old approvers and without
      back-up approvers I recommended that LSCC build new queues. These new queues would replace
      the old queues with the old organization codes, have back-up approvers and have criteria that utilize
      the new organization codes. I also suggested the LSCC use sql to maintain the approvers within the
      queues. That way when a person leaves the college who is an approver, they may use sql and
      replace that person with the new individual. This process keeps the queues clean and easy to see
      who is in the queue. Currently there are queues with 10 records in them and only 2 records are the
      active records and they are not always at the top.

  4. Reviewed Accounts Payable and Purchasing set up – I reviewed the set up of the accounts
     payable and purchasing tables, all of these table are in good working order. LSCC wanted to make
     some changes to the FTMSHIP and I was able to show them how to change the contact person on
     the records.

      LSCC was having issues with open invoices report, FAROINV. LSCC processes invoices that are
      cancelled from the vendor in order to track these cancelled invoices in SCT Banner. The advantage is
      it prevents that invoice from ever being paid. The result of this process is a large number of invoices
      that are open and for zero dollars. These invoices will never be paid through the check process and it
      is cluttering up FAROINV report. While the invoices can be marked paid through sql they do not seem
      to be removed from the report. I suggest that LSCC mark the zero invoices as paid to clean up the
      open invoice listing and to still use the FAROINV report; but, download the report to an excel file and
      omit the zero invoices.

      LSCC was interested in online receiving and I showed them how FPARCVD worked. LSCC matches
      their invoices to the receiving document by hand and would not want to key the receiving information
      in the FPARCVD. However, I did show them that could put the receiving information on the invoice in

Lake-Sumter Community College                                                                          v1.1
Banner Finance Functionality              SunGard SCT                                            2/14/2012
Check-up                            Confidential and Proprietary                                Page 3 of 8
                                                            Finance Functionality Check-up Report
                                                                                 December 1, 2005

      FAAINVE and if they turned on the document matching process, they could prevent invoices from
      being paid that have not had receiving done on them.

      LSCC wanted to see how the SCT Banner bank reconciliation worked. After it was shown they
      decided they would continue to use their access database process to do the bank reconciliation
      process. The database works the same as the SCT Banner process, and they would not have to
      clean up the years of transactions that have already been reconciled in their database.

      Finally, LSCC was shown why they had to do NSF overrides for both requisitions and invoices. The
      budget transfers that were doing did not always cover the NSF status of the account distribution. So
      when the invoice was processed the account distribution was still in a NSF status. LSCC will have to
      review the budget availability form, FGIBAVL, more closely to see how much budget in reality needs
      to be transferred.


  5. Reviewed Budget Development Practices – LSCC currently is using spreadsheet budgeting to load
     the college’s budget into the budget development module. They also feed HR budgets into budget
     development. LSCC wanted to have the departments enter their budget directly into the budget
     development module in baseline SCT Banner.

      I recommended that LSCC do not do this since the budget development module in Finance Self
      Service was much easier for the end-user to use and could track the changes to budget request that
      the baseline module could not and they can lock down the budget requests so no further changes
      may be submitted. With the end-user entering their budgets requests in the Finance Self Service
      Budget Development module, then the budget office can download all the requests into the
      spreadsheet budgeting; make institutional changes, and upload the new data back into baseline
      budget development. These steps are more efficient for LSCC.

      Moreover, LSCC can use spreadsheet budgeting to download year to date activity and last year
      budgets and load them into separate phases in the baseline SCT Banner Budget Development
      module and the end-users can view the data in the Finance Self Service Budget Development
      Module for comparative purposes.

  6. Reviewed Fixed Asset Procedures – LSCC is using the fixed asset module correctly and I saw no
     issue with how they process fixed assets. They did have some clean up work to do in some of the
     tables were they are no longer using numbers to represent values. For example, they once used a
     number to represent the asset was a motor vehicle. Where today they use a code like, MV, for motor
     vehicle. LSCC cannot simply remove the numbers from the tables since converted assets are still
     using them. I suggested that they use sql to change the appropriate numbers to the new values on
     the old assets. Once that was completed they could then remove the numbers from the tables.

      LSCC uses the FFVSDAT table with some state reporting values. Typically the FFVSDAT table is
      used for user-defined values to further identify information regarding a fixed asset. A few of the
      original assets in SCT Banner have some of the FFVSDAT values attached to them, but the majority
      of the assets do not. LSCC is going to investigate if this table is still being used for the state reports
      and should the values be attached to the fixed assets.

      LSCC also has 28 account codes for fixed assets that are being used. These account codes will not
      be used according to LSCC and should be terminated. I have attached the list of account numbers
      with this report.

Lake-Sumter Community College                                                                              v1.1
Banner Finance Functionality               SunGard SCT                                               2/14/2012
Check-up                             Confidential and Proprietary                                   Page 4 of 8
                                                           Finance Functionality Check-up Report
                                                                                December 1, 2005

      LSCC needed help in entering buildings into the Fixed Asset module. I was able to walk them through
      the process with a test building and was successful in running depreciation correctly for it as well. The
      IT folks would have to SQL in the accumulated depreciation number onto the asset record in

      LSCC had limited knowledge on how Dtags were resolved and they had a good deal of dtags created
      already in the system. I was able to share the current Fixed Asset workbook that handled all the
      common Dtag issues and I also present a document that showed how to handle the uncommon Dtag
      issues. I was able to resolve a couple of Dtags in training to demonstrate the handouts. Most of the
      Dtags at LSCC are ones that wash out and those can and will be fixed in short order. The rest
      requires research about the transaction and the related fixed asset before it can be resolved, but the
      documentation covers all scenarios LSCC would encounter.


  7. Trained staff on the functions of Finance Self-Service – LSCC has had Finance Self Service
     installed for a while but has never used its functionality. I was able to train the key finance personnel
     how the use the following functions in Finance Self Service: FOMPROF
     Self Service Code Look ups
     Self Service Requisition
     Self Service Purchase Order
     Self Service Budget Transfer
     Self Service Multiply Budget Transfer
     Self Service View Document
     Self Service Budget Queries
     Self Service Encumbrance Query
     Self Service Approve Documents
     Self Service Budget Development Worksheet
     Self Service Budget Development Query
     Self Service Budget Development Organizational Lock

      LSCC expressed much interest in getting end users to use Finance Self Service, but it would not be
      until after the organization codes are changed. If LSCC rolled out self service any sooner they end-
      user would face the same issue they do now, the budget and year to date expenditures are in
      separate organizations.

  8. Reviewed Accounts Receivable processing – LSCC is using the Student Accounts receivable
     module to track their student receivables. They are feeding the student transactions over to finance
     on a daily basis and reconciling on a daily basis. I spent most of my time further explaining how the
     application of payment processed work and why they were having individuals with application of
     payment issues. One reason was they did not set up their priority codes on their detail codes
     correctly. In some instances the charge and payment detail codes were set up with ‘000’ priority code
     when only payment codes should have ‘000’.

      In addition, some of the application of payment issues are related to financial aid being on for like aid
      year. This means the aid only applies to charges in the current year and would not pay off older
      balances. I should them some tips, like turning off like aid year over a weekend and running
      application of payment to clear out some older payment. I also explained that application issues could
      be due to timing and some time payments needs to split out to pay charges from different terms.

Lake-Sumter Community College                                                                            v1.1
Banner Finance Functionality               SunGard SCT                                             2/14/2012
Check-up                             Confidential and Proprietary                                 Page 5 of 8
                                                            Finance Functionality Check-up Report
                                                                                 December 1, 2005

        With these suggestions, fixing the priority codes on the detail codes, removing the like aid year on a
        weekend and running application of payments, and splitting out payments. LSCC will find that the
        number of outstanding application of payments issue will decrease, but will always occur in SCT
        Banner one time or another.

        When LSCC start to close their months in FTMFSYR quicker, there is a strong potential that the feed
        documents created in the Student module will contain back dated transactions. If the period is closed
        for the older dated transaction the document will get suspended and an accountant will have to
        reprocess the document with a date in the current month. To prevent this issue I recommend that
        LSCC change the feed document to feed by system date instead of transaction date as indicated in

        LSCC needs to remove the cashiers that have left the college in TGAUPRF.

        LSCC also has a feed document stuck in GURFEED. They will need to contact the UDC solution
        center to ask how to remove this document out of GURFEED since I could not functionally.

        Finally, LSCC needs to remove journal vouchers that are being posted to the AR accounts. This is
        throwing off their AR reconciliation. Only feed docs show be posted to these accounts.

    9. Additional Training – In addition to the above topics I reviewed I also showed LSCC how the finance
       rule codes worked in SCT Banner and how to build custom rule codes if they needed too. I also
       showed them how the CR05 rule code worked and how it should be used to record deposits or wire
       transfers in SCT Banner. Currently LSCC is using a JE16 rule code and hitting the bank code directly
       causing the controller to sign off on deposits. Using the CR05 rule code the data entry person only
       keys in where the deposit is going for the revenue and the rule code will process the bank code side
       of it through G021 process code. The controller was happy about this rule code since it cut down on
       the number of journal vouchers that needed to be signed off on.

Progress Report
Recommended Timeline to implement improvements/corrections

Completed by December 31, 2005
  Resolve Dtags
  Fix FOMPROF – Add user organizations, ship to codes, remove invoice capabilities, add research end
   date capabilities to those that need to, and add accrual posting capabilities for those that need it
  Close accrual periods for Fiscal Years 2000 and 2001
  Fix FTMSHIP records with new contact people
  Contact UDC solution center for the AR document stuck in GURFEED and why SCT Banner is allowing
   budget transfers to create negative budgets.
  Terminate the old funds that are no longer used that have rolled forward every year zero general ledger
  Read Training Materials delivered during the week
  Terminate the un-used fixed asset account codes – list attached.
  Test and start using the CR05 rule code for deposit and wire transfers
  Decide if Document level matching will be utilized
  On TGACTRL change the feed documents to use system date instead of transaction date
  Have a supervisor remove people in TGAUPRF that have left the college
  Mark purchase orders in FOASYSC with NSF checking

 Lake-Sumter Community College                                                                            v1.1
 Banner Finance Functionality               SunGard SCT                                             2/14/2012
 Check-up                             Confidential and Proprietary                                 Page 6 of 8
                                                            Finance Functionality Check-up Report
                                                                                 December 1, 2005

Completed by February 28, 2006
  Complete the correction of the priority numbers on the student detail codes
  Load building data into Fixed Asset
  Review state reporting process to see if why the FFVSDAT table is being used. Does it need to be
   attached to fixed assets
  Clean up Fixed Asset master records by converting values that used numbers to the new alpha
  Remove the numbers from the Fixed Asset Validation tables

Completed by April 30, 2006
  Complete the review of the organization chart to see what new orgs need to be built to support new
   methodology and hierarchy
  Add new organizations into FTMORGN that support new methodology with a start date of May 1, 2006.
   This will allow budgets to be posted to them for the new fiscal year.
  Future terminates organization that end with a letter to terminate after the audit is completed. (Between
   July and August these organization may be needed to make adjusting entries for the audit so they cannot
   be terminated in July)

Completed by May 30, 2006
  Have new approval queues tested and then built in production

Completed by July 1, 2006
  Change the organization codes in the detail codes and HR to the new organization codes using sql

Completed by the second week in July
  Convert account indexes to use new organization codes
  Close period 12
  Change FOMPROF records and organization security to use the new organization codes by using sql

Completed by September 2006
  Provide end user training for Finance Self Service

Progress Report
Recommendations for Additional Training/Consulting

Virtual Classrooms

Attend SunGard SCT Ed Center GASB virtual classroom training:
January 18, 2006, 1-4 p.m. cost $175

Please see Course Description attachment in cover e-mail.

On-site Visits

Please arrange for all on-site consulting visits with Anne Ceccato, Account Manager:

 Lake-Sumter Community College                                                                         v1.1
 Banner Finance Functionality              SunGard SCT                                           2/14/2012
 Check-up                            Confidential and Proprietary                               Page 7 of 8
                                                           Finance Functionality Check-up Report
                                                                                December 1, 2005

April 2006 – Consulting visit to provide additional guidance on approval queue set up, organization code
changes, FUPLOAD training and follow-up on action items from the finance functionality Check-up

September 2006 – Grant Billing and Research Accounting training in a 3.5 day visit.

November 2006 – Grant Billing/Research Accounting follow-up consulting visit

          Name                       E-Mail Address                             Attendance
                                                                    Day 1           Day 2           Day 3
  April McGuire                           X               X               X

  Debra Vizsnor                            X               X               X

  Doug Guiler                              X

  Loretta Campman                          X               X               X

  Sue Fagan                                  X               X               X

  Allison Rehbaum                          X               X               X

  John H. Froman                            X               X               X

  Tammy Spencer                                            X

  Ann McLellan                                             X

  Regina Peeples                                           X

  Daulyn Buchanan                                                          X

Supplemental Documents Attached

List of Accounts no longer used for Fixed Assets.doc
Example Queues for Lake-Sumter Community College.xls
GASB Reporting Overview.doc

Hours Billed

Preparation                2
Travel                     8
On-Site Consulting         24
Follow-Up                  10
TOTAL                      44

 Lake-Sumter Community College                                                                          v1.1
 Banner Finance Functionality              SunGard SCT                                            2/14/2012
 Check-up                            Confidential and Proprietary                                Page 8 of 8

Shared By: