Plan Ledger Pivot Macro Instructions

Purpose: The steps in these instructions will provide college and departmental users with financial information that is budgeted to varying levels of detail.

Limitations:

  • The data in the Account Analysis Report reflects transactions that are posted to the General Ledger. Transactions that have not yet posted to the General Ledger, will not be reflected.
  • Budget data is manually entered by the pivot owner. Please remember that any budgetary transfers and/or adjustments will need to be manually updated on this report.
  • Users are encouraged to utilize this tool as one aspect of their financial management activities.

Instructions:

  1. Run and Save Account Analysis Report from Operations Ledger.
  2. Ensure that the date parameters include the entire fiscal or operating year.
  3. You may run many funding sources on one report by using a hierarchy or entering several or a range of departments or cost centers in the funding source parameters.
  4. Ensure that you are using funding sources that you enter budget for in step 3.
  5. Open “PlanLedgerPivot”.Note: Do Not Change the Name of the File.
  6. Click on PlanDataEntry Tab to enter or edit plan budget(s).
  7. Fiscal Year = YYYY (ie. 2018)
  8. Period = X (ie. July = 1, June = 12)
  9. Fund = XXXX
  10. Department = XXXXX
  11. Cost Center = XXXXXXX
  12. Natural Account = XXXXXX (You may use Budget Rollups or Specific Account Codes)
  13. Budget Rollups
  14. Commonly Used Account Codes
  15. Supplemental = XXXXXXXXXX (if this value is not used, enter 0)
  16. Project = XXXXXXXXXX (if this value is not used, enter 0)
  17. Net Amount = XX.XX
  18. Enter Revenue as a positive amount.
  19. Enter Expenses as a negative amount.
  20. Click Insert Plan to convert budget into AAR formatting.
  1. Click on Process Tab.

  1. Select either one or two reports to include.
  2. Navigate to reports saved in step one and select.
  3. The report will take a minute to run.
  1. Click on Report_Criteria_Information Tab.
  1. The period parameters will reflect for each report selected.
  2. The date and time the reports were delivered will reflect for each report selected.
  1. Click on CombinedData Tab.
  1. This includes all of the data from each report selected.
  2. This data is used in the pivot table.
  3. Do NOT change this data.
  4. There are six additional columns for organizing the pivot.
  5. MONTH: Converts the accounting period to a month.

Note: When a period is not entered in the Plan, it will show as “Not Entered”

  1. LEDGER: Identifies the ledger; budget, operations (actual), or plan
  2. CC&DESC: Combines the Cost Center and Description in one field
  3. ACCT: Combines the Account and Description in one filed
  4. ROLLUP: Specialized rollup for revenue, wages, other expenses, capital outlay, and surplus.
  5. BUDACCT: Identifies the budget rollup category for each account value
  1. Click on Summary Tab.
  2. This shows a summary of each cost center in the Account Analysis Report.
  3. You may use the filters to identify specific data.
  4. Click on PvtTblRpt Tab.
  5. Use the filters to isolate specific funding sources.
  1. Transaction Descriptions: Filter or Expand/Collapse each field
  2. ROLLUP = Expense Type
  3. BUDACCT = Budget Office Rollup
  4. ACCT = Account code and description
  1. Transaction Dates and Amounts
  2. FISCAL_YEAR = Fiscal Year
  3. PERIOD = Accounting Period
  4. MONTH = Conversion from Accounting Period to Calendar Month
  5. LEDGER = Ledger from Account Analysis Report
  6. Actual: Operations Ledger
  7. Plan: Entered on PlanDataEntry tab
  8. Budget: Budget Ledger
    NOTE: In order to get a remaining budget/variance ensure that only the budget or plan ledger are selected.
  9. Total =Variance Actual to Plan MTD
  1. Grand Total = Variance Actual to Plan/Budget YTD
  1. Double click on any dollar amount to drill to transaction detail in a new tab.
  1. There is a Remaining Balance Pivot at the top to filter to a cost center and delineate between Actual, Plan, and Budget as a Grand Total.
  1. Copy and paste the pivot table to additional tabs and use filters to set up other specific funding sources.
  2. Update tab names to reflect filter selections.
  3. Double-click tab.
  4. Change name.
  1. Refresh data as often as you would like by completing step four with updated reports.