Fund Equity & Cost Center Verification

Purpose:  The reconciliation of department cost centers are needed to minimize the risk of unauthorized use of funds and to ensure that revenues are properly recorded. Therefore, entries in the PeopleSoft General Ledger (PSGL) must be monitored monthly. For this reason, MAPP has asked that each department develop procedures for monthly verification of each cost center in PSGL with departmental records to ensure accuracy and propriety of the entries.
References:  MAPP 05.04.02, SAM 03.G.01, SAM 03.G.03
PT. I: Step by Step Reconciliation Using VNet Administration Database
PT. II: Cost Sharing
PT. III: Maintaining PS Cost Centers
PT. IV: Budget Request
PT. V: Request to Budget New Revenue/Fund Balance
PT. VI: Non-Payroll Correction
PT. VII: List of Reports
PT. VIII: Reviewing Fund Equity
Review and Responsibility: 
  • Responsible Party: Director, NSM Business Operations
  • Review: Every two years, on or before August 31
  • Director, NSM Business Operations - Fred McGhee
  • Dean, NSM - John Bear
  • Date of Dean's Approval: 09/01/2008

I: Step by Step Reconciliation Using FileMaker Pro VNet

Worksheet Name Transactions To Reconcile
Verification Worksheet Adjustments that show the difference in PS and FileMaker transactions
Asset_Liab_Fund_Equity Equity transactions 3xxxx and equity journals
Revenue_Expense Expense transactions 5xxxx and Revenue transaction 4xxxx and 5xxxx
Budget Journals Budget Journals BDXxxx or Journal ID
Open Commitments Verify the open commitments. Compare to FMP. Release if necessary.
Soft Commitments Check FMP to ensure these soft commitments belong to your cost center.
  1. Run 1074 reports with verification worksheets and chose the XML Verification Sheet

    Example: Department Verification Report
  2. Once the file opens the entire workbook containing the various worksheets would appear. Click on each of the tabs to reveal the information on each worksheet.

    Example: Department Verification Worksheet
  3. Reconciling with the Worksheets
    • Begin with the Asset_Liab_Fund_Equity tab:
      • Scroll all the way to the bottom of the worksheet to find "Fund Equity" transactions or use the auto filter feature in the Account type column to find "Fund Equity" lines only.
      • Reconcile each transaction as follows:
        • Locate the transaction by equity journal or budget journal # in FMP Quick Entry. (If there is a corresponding budget transfer, the budget journal is normally referenced in the description line).
        • Verify the data, then select "Equity to Budget" button to post it to the FMP Budget Input screen.
        • On the Verification Worksheet, place an "X" in the Verified column to indicate that it has been reconciled and enter the expenditure date in FileMaker Pro.
        • If both the equity and the journal do not post in the same period, follow the instructions in the box below.
    • Next go to the Budget Journals tab:
      • First identify the budgets that correspond to the equity posted in the Asset_Liab_Fund_Equity tab.
        If both the Equity Journal and the Budget Journal do not post in the same period:
        • If Equity Only Posts: Duplicate the record after it has been posted to the Budget Input layout. The "Equity Amount" and the corresponding "Start Amount" will populate in each field. Expend the first record, reflecting Equity posting. Remove the "Start Amount" and "Equity Amount" within the duplicated record and use the duplicated FMP record to note within the "Description" field the pending budget to be posted. Note if it was the intent to transfer equity only, the first record is complete.
        • If Budget Only Posts (most likely senior): Duplicate the record before positing it to the Budget Input layout. Then post one transaction to reflect posting of "Budget Only". Enter the intended Budget Amount in the "Equity" field (nothing in "Start Amount"). Leave the other record as an outstanding Budget Journal in Quick Entry and make a note in the "Description" field indicating that "Budget Posted".
        • Each Budget Input record should be balanced, never one sided.
        • Adjust the corresponding FMP Budget Input information to reflect this entry (i.e. move amount from Equity to a Budget Node if applicable).
        • On the Verification Worksheet, place an "X" in the Verified column to indicate that it has been reconciled.
        • Enter the expenditure date on the transaction in FMP.
        • If budget only post when both equity and budget was transferred, follow the instructions in the box below
        • If it is a budget transfer only, enter the positive and negative amounts in the appropriate budget categories on the budget input record. (Initially when the budget journal was done a budget entry record should have been created with the information in description only.
        • Prior Year Budgets
          • Prior Year Budgets, identified by CBBA or CFWD (highlighted below), should not be listed in FMP. These transactions will only appear on the first month of the fiscal year. When verifying equity and budget transactions as indicated above, do not leave these transactions within the spreadsheet for Budget Totals.

            Example: Prior Year Budgets | Prior Budget References

        • Prior Year Expenditures
          • Prior Year Expenditures should be expended and listed on a Budget Input screen in FMP
          • List the expenditure in the "Start Amount" and in the "Equity" field as a negative figure then expend.
          • The prior year expenditures can be identified by the budget ref column in the Revenue Expense worksheet.

            Example: Prior Year Expenditures

          • Enter or bring forward transaction from prior year in the usual fashion on Quick Entry screen; be sure to include the amount in Encumbrance Amt field. In this way it would not affect the current FY budget amount but it would reduce equity as it was intended to.

            Example: Post Prior Year Expenditures

          • Transaction list will show it as normal.
          • When not expended in FMP the account summary will show it as reducing the unbudgeted and it would be listed as a Prior Year encumbrance and reduce the current balance.

            Example: Reducing the unbudgeted

          • When expended in FMP, do not change the screen or remove the account code 4xxx, this is needed for the account summary to show it in Expenses (Prior BRef) and it should reduce PS accumulated funds. Don't forget to remove the amount from the encumbrance in Quick Entry when expended.

            Example: Expenses (Prior BRef)

        • Continue on to the Revenue Expenses tab:
          • First use the filter feature to find only Expenses under the Account Type column.
          • Then use the filter feature in other columns to find document numbers, vendors or amounts. The find feature in Excel can also be used.
          • If the transaction matches FileMaker Pro's transaction:
            • place an "X" in the Verified column of the Verification Worksheet
            • enter the expended date and amount in FMP
          • Second use the filter feature to find only Revenue. If revenue was deposited by the department, the document would have already been entered in FileMaker Pro Quick Entry Screen, find these and expend them. If revenue is deposited by the system (ABC) then enter into quick entry and expend.
          • For payroll transactions, print the PeopleSoft Labor Distribution Rpt (History) or the Payroll Account Rpt (History) and expend the transaction in FileMaker Pro accordingly.
          • Revenue when entered with correct account codes in FileMaker Pro Quick Entry screens will be reflected on the Account Summary screen under Revenue. It will also add to the Equity Balance.

            Example: Revenue Deposits

          • The total salary and fringe on the expenditure tab from the salary and fringe filter should match amount on FileMaker Pro "Payroll" layout under the month being reconciled.

            Example: Payroll Distribution

        • Open Commitments tab:
          • Compare open commitments in FMP to the Verification Worksheet.
          • Determine whether the commitment balance needs to be released or if liquidations are pending.
          • Identify the differences between the FMP commitment amounts and the Verification Report
          • Use the green section of the Verification Worksheet to input the adjustments. These totals will populate to the 1074 Verification Worksheet.
        • Soft Commitments tab:
          • Compare the FMP outstanding vouchers, SCR's, etc (items appearing in the "Outstanding" column of the FMP Account Summary) to the Soft Commitment documents listed on the Verification Worksheet.
          • Verify that they are valid.
          • Identify the FMP transactions that are not on the worksheet.
            • Use the green section of the Verification worksheet to input outstanding items in FMP that do not appear on the verification worksheet. These totals will populate to the 1074 Verification Worksheet.
            • Note: Remember to follow up on the outstanding item if it is over 60 days old and still not expended.
        • Verification Worksheet tab
        • Verification Worksheet vs. Account Summary

          Example: Verification Worksheet | Prior Budget References

        Continue to PT. II: Cost Sharing...