Fund True Up to Balance to State GMS

Overview:

  • MUNIS > Tyler Menu > Departmental Functions > Account Inquiry
  • Please follow the directions exactly as laid out.
  • This is a very technical and detailed process. If you run into any issues or have questions please contact the Budget Department.
  • Account Inquiry allows you to view total accounts, individual accounts, as well as transaction level detail.
 

Instructions:

  1. Open Account Inquiry (Tyler Menu > Departmental Functions > Account Inquiry). Click SEGMENT FIND.

    Account Inquiry: Segment Find

  2. Enter Fund # # # and Account Type R|E. Click ACCEPT. Click YES CONTINUE.

    Account Search: Enter Fund Number and Account Type

  3. Click EXCEL.

    Account Inquiry: Click EXCEL

  4. Click SUMMARY, then OK. Wait for the report to process.

    Options: select SUMMARY

  5. The first time this Account Inquiry is made, click SELECT NONE. This will bring up all EXCEL categories to choose from. All future times of running an Account Inquiry, MUNIS will default to the selections made in step 6 (below).

    Make sure to click SELECT NONE

  6. Check the following categories. Then Click ACCEPT.

    Check the following categories

  7. The EXCEL document will open. Enable Editing. Make a copy of this tab. Keep the original for a reference of unchanged data. Name the duplicate copy ACCOUNTS.

    Click "Enable Editing" in Excel, then make a copy of this tab.

  8. This example shows what the ACCOUNTS tab looks like before any changes are made:

    ACCOUNTS tab in Excel, before changes.

  9. Copy the “Type” column to in front of column A by right-clicking and selecting INSERT COLUMN. In the original Type column use this formula: =MID(Full Account,18,1) Fill down, copy column A, right-click and paste as Value.

    Insert "TYPE" column

  10. Click in Column B2. Hold down CTRL, SHIFT and END. Hold down SHIFT key and arrow back to only have Columns that have the small green triangle in the upper left corner. Scroll back to the top of the page. Click the carrot for the pull down menu and select Convert to Number. Wait for this to process.

    Convert to Number

  11. Highlight the following columns: Object, Location, and Function. Right-click and select Format Cells. Choose CUSTOM. Under Type: enter 000 (3 zeros). Click OK. Highlight Program Column. Right Click, select Format Cells. Choose CUSTOM, Under Type: enter 0000 (4 zeros). Click OK.

    Format Cells: CUSTOM

  12. Select Object Column, Cut and Insert Cut cells in front of FULL ACCOUNT Column.
     
  13. For space purposes rename these titles as shown below:

    Rename the column Titles to match this image

  14. Insert a Copy of OBJ to Column A and rename SO (for State Object). Column titles should now look like this.

    Column Titles should appear this way

  15. Filter by Clicking SORT & FILTER, then selecting the Filter

    Sort and Filter

    1. Carrots will appear on the Row headings. Choose the carrot by TYPE and select E. Then Click OK. This will Filter by EXPENSE only.

      Filter by "E" to show EXPENSE only

  16. Click the carrot by SO and change roll up codes to reflect State codes. Use STATE ROLL UP CODES SHEET. In the example below, all checked boxes roll up into state code 310.

    Change roll up codes to reflect State codes.

    1. Type 310 in row 2 then fill down. Filter the next set of roll up codes and do the same until all are rolled up into the State code on the ACCOUNT tab. After ALL roll up codes have been changed to State Codes, Clear Filter.

      Enter state code and Fill Down

  17. Format CY ORIGINAL BUD, CY REVISED BUD, CY MEMO, CY ENC, CY REQ- by highlighting columns then Right Clicking and choosing FORMAT CELLS then CURRENCY and the drop down under SYMBOL: to choose $. Click OK.

    Format Cells as Currency

  18. Filter these same Columns one at a time by 0 or (-) blank. Check $0.00. Do this for each of the following columns: CY ORIGINAL BUD, CY REVISED BUD, CY MEMO, CY ENC, CY REQ.

    Filter columns by 0 or blank

    1. This will isolate all accounts with $0.00. Click in A2. Hold down CTRL, SHIFT, END, Right Click and select Delete Row

      Delete Row

    2. After all complete Clear Filter.

      Clear Filter

  19. Filter by TYPE Column B, EXPENSE (E) only.

    Filter by Type "E"

  20. After Column CY REQ. Type the below titles in the following 4 Columns and type formula’s as follows in each corresponding Row 2.
    YTD ACTUAL Row 2 =CY MEMO + CY ENC + CY REQ
    AVAILABLE Row 2 =CY REVISED - YTD ACTUALS
    ADJUSTMENT Row 2 =IF(AVAILABLE<0,-ROUND(AVAILABLE, 0),0)
    REVISED Row 2 =CY REVISED + ADJUSTMENT
  21. Filter by State Object. Example: Filter by 310, do step 22, then Filter by 320, do step 22. Do this for each State Object. 360 has a unique step, see step 25 before filtering it. NOTE: if there are Programs these will also need to be filtered by each program. See GMS sheet to see which programs are under one budget.

    Filter by State Object

  22. Look at the ADJUSTMENT Column. Highlight and see the SUM at the bottom, this is the amount that will need to be adjusted

    Highlight ADJUSTMENT column and view SUM

  23. Look at what account money is in. Use a negative to balance the amount. See example.

    Use a negative to balance the amount

  24. Clear Filters, Filter by TYPE E for Expense. Highlight CY Revised- note amount, Highlight Revised- note amount, these should equal.

    Filter by "E" and compare REVISED amount with CY_REVISED, should equal.

  25. When filtering SO 360 also filter by Union:
    Certified: 1,3,5,6,7,9
    Classified: 2,4,8

    Also filter SO by Union.

  26. Clear Filter and SAVE. Look over worksheet, in any blank cells type 0.00. Click in cell A1 Hold down CTRL, SHIFT and END. Right Click Define Name

    Clear Filter and SAVE

  27. Open new tab, name PIVOT from top tool bar choose INSERT tab, Insert Pivot Table.

    Create new PIVOT Tab

    Create Pivot Table dialogue box

  28. Drag SO to ROWS and Revised to Values. As shown below left. If there are Programs, then drag PROG under FILTERS as shown on right.

    Pivot Table Fields selections

    1. Your Pivot Table should look like this:

      Pivot Table

  29. Use GMS to compare totals on the Pivot. Make a Copy of the Pivot Table and Paste as Value two Columns over. Add GMS and DIFF as titles. See example:

    Compare totals from Pivot to GMS

  30. Fill in GMS Column with Documentation from State of Alaska
    1. Use Formula: =GMS-Sum of REVISED to see Difference. These should match. If not, go through each OBJ individually.
  31. Open a Previous Import to Copy Import and Budget Import Set Up tabs to this workbook.
    1. Use Import Tab as example. Copy columns from Account Tab, make sure that Account Tab filters are cleared.
      1. OBJ
      2. TYPE
      3. FULL ACCT
      4. ADJUSTMENT (copy as Value) Filter any zeros or (-) Use Find/ Replace to do this. Filter by zero and delete any that have only zero value. Delete by Right Click and Delete Row.
      5. D X100 ( no commas or decimals or $) Highlight Colum E, Right Click, Format Cells, Custom, Format with 13 zeros. See example below.
      6. =IF(E2<0,-E2,E2) this is get rid of all negatives.
      7. Copy Type Column
    2. Use this Formula to create Debit or Credit Columns
      Expense Account =IF(Adjustment>0,"D","C")
      Revenue Account =IF(Adjustment<0,"D","C")
      Fill down, Copy and Paste as Vales
  32. Add new tab, name Import Sheet Use the information from Import tab to fill in with directions from Budget Import File Format tab.
  33. Make sure no formulas, that Column I has 13 zeros and NO negatives. When copying Columns E, F and G do not use fill down. Check to make sure there are D and C in column H. Stay on Import Sheet make sure no rows or columns are highlighted and Save AS Formatted Text (Space delimited)

    Format Cells. Save AS Formatted Text (space delimited)

Details

Article ID: 63455
Created
Wed 10/3/18 3:44 PM
Modified
Thu 3/14/19 1:07 PM