Skip to Knowledge Base content

Account Analysis Report in Scheduled Processes and Adding Budget Information

Tags WyoCloud

This guide will cover:

  • How to navigate to and schedule the account analysis report from Scheduled Processes
  • How to format the report for use using the account analysis pivot macro.
  • How to run the Budget by Account report and include in the account analysis pivot macro.
  • Reading the Account Analysis Report.

Note: This report includes all transactions which have processed in WyoCloud including:

  • Requisitions/Purchase Orders/Invoices/Non-PO Invoices
  • Journal Entries, including IDTs (which have been posted by Accounting)
  • Reconciled Expense Reports (including p-card expenses)
  • Payroll and Fringe Benefits
  • Summary level integrations from Banner (including cashier’s/marketplace transactions), postal, telecom/IT Billing

This guide has three parts.  Steps 1-5 outline running the Account Analysis Report to show only actuals/encumbrances.  If you would like to also view budgeted amounts (including remaining budget authority), continue onto the remaining steps.  At the end is an addendum to aid in reading the completed report.

Step One

To log into WyoCloud and navigate to the Scheduled Processes area.

  • Navigate to the WyoWeb website and select Financial Management from the WyoCloud area.  The WyoCloud Home page is displayed.
  • From the WyoCloud Home page, click Navigator then Scheduled Processes.

  • The Scheduled Processes page is displayed.

 

Step Two

Access the Account Analysis Report.

  • Click on Schedule New Process.

  • In the Schedule New Process pop up window, click on the arrow to the right of Name.

  • Click Search.

Note: Although you will likely see a number of options within Scheduled Processes, the Account Analysis is the only process campus users are able to run.

  • Type account into the name field then click search.

  • Highlight Account Analysis and click OK.

  • Click OK in the Schedule New Process window.

 

Step Three:

Schedule the account analysis report. In this step you will specify the parameters you’d like to run the report by.

  • In the pop up window, complete the following fields:
    • Subledger dropdown: blank
    • From Accounting Period: Jul-17
    • To Accouting Period: Most recent closed accounting period.
    • Budget Type: Actual OR leave Blank.  If this is left blank, both actuals and encumbrances will be included.
  • In the same window, click on the blue filter icon next to Account Filter Conditions Defined.

Note: In order to be able to add budget information to the report using steps 6-7, the Account Analysis must be ran for the full fiscal year.

 

  • In the new pop up window, select Add Fields then Organization.
    • Although you can filter by additional fields it is recommended to just filter your Organization or range of Organizations at this step.  Please note that the other chart of accounts segments will also pull in when you run this report using this filter. 
    • Note: At least one filter must be entered.

  • Complete the fields per the Organization data you’d like returned. Click OK.

  • Click Advanced.

  • Click Output, then the plus sign.

  • In the Format drop down, CSV must be selected.
  • Click Submit.

  • In the confirmation window, click OK.

 

Step Four:

Retrieve and download the report.

  • From the Scheduled Processes window, click on the blue refresh button to check to see if the report has completed running.  You may have to click the refresh multiple times.

  • Once the Account Analysis Reprot appears with a Status of Succeeded, it has completed running.

  • Double click on Account Analysis Report to open additional details at the bottom of the screen.

  • Scroll down to the Output area on the lower half of the screen, then click on the Output Name.

  • Select Open with then confirm Microsoft Excel is showing in the drop down. Click Ok.

Note: If Microsoft Excel doesn’t show as an option in the Open with field, you will need to change your computer’s default settings for opening .csv files. For details on doing this see this Knowledge Base article.

  • Once the file is open in Excel on your computer, click File then Save As
  • Save the file to your desktop. Be sure to select CSV (Comma delimited) (*.csv) from the Save as type dropdown.
    • Note: This is just a temporary save and can be discarded after the macro is run in step five.

  • Be sure to close the file prior to proceeding to step five.

If you would like to include both actual/encumbrance AND budget data, skip to Step Six. If you would like to run the Account Analysis including ONLY actual and encumbrance data, continue to Step Five.

Step Five:

In this step you will download and run the Account Analysis Pivot Macro to properly format the report for use.

  • Download the Account Analysis Pivot Macro located in the files section on the right side of this webpage (link).
    • As a best practice, always download the macro file directly from the Knowledge Base each time you use it to ensure you are utilizing the most current version.

  • Upon opening the Account Analysis Pivot Macro, you will first need to select Enable Content in the yellow ribbon at the top.  Then click the grey Generate Pivot Account Analysis Report – 1 button (located in the Process tab).

  • In the Instructions pop up window, click OK.

  • Navigate to where you saved the exported Account Analysis Report from WyoCloud Financial Management Reports and Anyalytics on your desktop (obtained in step 1-4). Select it then click Open.

Note: It will likely take several moments for the macro to process.

  • Once complete, the report will look similar to this:

  • Use Save As to rename and save the report to your desired location for future reference.

If you would like only actuals and encumbrances (not budget information) included in your report, you may stop here.  Continue to Step 6 to add budget information to the report.

For tips on reading the Account Analysis Report, see the Appendix: Reading the Report.

Step Six

Run Budget by Account report.

  • In WyoCloud, click Navigator then Reports and Analytics.

  • The Reports and Analytics page is displayed.

  • Select the small double arrow above the search.

  • From the drop down, select Shared Folders.

  • All Shared Folders you currently have security access for will display.  To locate the Transactional Table of Contents, begin by clicking on the Custom folder (you may need to scroll to locate it).

  • Click Dashboards.

  • Click Table of Contents.

  • To open the Table of Contents, click on the title

Note: You can set the Table of Contents as a favorite for easier navigation in the future.  For directions see this Quick Reference Guide.

  • Select Budget to Actuals by Account, under the General Ledger and Budgetary Control header.

  • Select the same Organization which the Account Analysis was ran then select Apply.

  • The Budget By Account Report is displayed.

  • To add the report to the Account Analysis Pivot Macro, first export it to Excel.
  • Click on the view report icon in the upper right corner, then click Excel(*xlsx).

  • Once the file is open in Excel on your computer, click File then Save As
  • Save the file to your desktop and close the file.
    • Note: This is just a temporary save and can be discarded after it is added to the Account Analysis macro you started in step five.

 

Step Seven

The following steps outline how to run both the Account Analysis (actuals and encumbrances) and Budget by Account into the macro at the same time to view both data sets side by side.  If you would only like to review actual/encumbrance data, use Step Five.

  • Download the Account Analysis Pivot Macro located in the files section on the right side of this webpage (link).
    • As a best practice, always download the macro file directly from the Knowledge Base each time you use it to ensure you are utilizing the most current version.

  • Upon opening the Account Analysis Pivot Macro, you will first need to select Enable Content in the yellow ribbon at the top.
  • Then click the grey Run All button below the two brown boxes (located in the Process tab).

  • You will be instructed to first locate the Account Analysis report you exported in Steps 1-4, click OK.

  • Navigate to where you saved the exported Account Analysis report. Select it then click Open.

  • The macro will likely take several moments to process prior to showing a second instruction window. This window will instruct you to select the exported Budget by Account report.

  • Navigate to where you saved the exported Budget by Account Report from WyoCloud Financial Management on your desktop (obtained in step 6). Select it then click Open.

  • Once complete, the report will automatically open the BudgetSummary tab. This tab allows for viewing budget and acctual data side by side.

  • You may also review only actual/encumbrance information using the AASummary tab.

Note: For tips on reading the completed report, see the appendix below.

 

Appendix: Reading the Report: AASummary Tab

After running the macro, the report opens to the AASummary tab.  From this tab you can filter to specific segments of the chart string by using the filters at the top.

From the AASummary tab, you can also drill in to see the details of specific amounts by double clicking a natural account name in column D.  You can identify if a balance is an actual or encumbrance by looking at the balance type column.

You can expand the details of all transactions included within a specific amount by double clicking an amount within the Accounted DR, Accounted CR or Total columns.

The full details of what makes up that number will open into a new tab.  See Appendix: Column Definitions for details.

Note: For better understanding on how to utilize pivot tables such as this one, UWIT offers useful trainings.  To see offerings visit the UWIT Catalog on WyoLearn. For Excel/Pivot table questions you may also email computer.training@uwyo.edu.

 

Reading the Report: BudgetSummary Tab

Similar to the AASummary tab, you can click on any number in columns: Accounted DR, Accounted CR Budget or Variance and the full details of what makes up that number will open into a new tab. See the appendix for column definitions.

A summary of expenditure totals, revenue, and budget as well as remaining spending authority are included in the upper right corner.

 

Appendix: Column Definitions

After clicking on a value to expand full details or the CombinedData tab, the following columns are included:

Column Name

Data Included

P_Period_From

Reflects the starting month of the report (as input when scheduled).

P_Period_To

Reflects the ending month of the report (as input when scheduled).

Balance_Type

Will show if transaction represents an actual or encumbrance (if encumbrances were included when running the report).

Accounting_Code_Combination

Full accounting chart string.

Code_Combination_Description

Written version of the accounting chart string.

Period_Year

Year of the period.

Period_Number

Corresponds with the month of the fiscal year (ie: July = 1, August = 2).

Period_Name

Name of the period (month).

Period_Start_Date

First day of the period.

Period_End_Date

Last day of the period.

Balancing_Segment

Entity

Natural_Account_Segment

Natural Account

Cost_Center_Segment

Organization

Balancing_Segment_Desc

Written version of Entity.

Natural_Account_Segment_Desc

Written version of Natural Account.

EnTITY

Entity

Natural account

Natural Account

fund class

Fund Class

fund source

Fund Source

Expense class

Expense Class

program

Program

activity

Activity

future

Future

GL_Date

The date the transaction was posted to the General Ledger (GL).

Created_By

Who entered into the GL. Note if this column shows serviceuw.integration this is a feeder file such as Banner, Telecom, Copier, Postal or Payroll.

Creation_Date

When the transaction was initially entered into the system.

Last_Update_Date

When the transaction was last updated.

Header_ID

This is a system generated number.

Header_Description

Description which was entered at the header level when the user entered the transaction.  The exception is those that say Journal Import indicate integrations or journal entries.

JE_Category_Name

Type of entry the transaction was given when it was entered.

JE_Source_Name

The source of the journal entry.

GL_Batch_Name

This is the name of the batch in the GL which can be utilized to search for more details within the system.

Posted_Date

The date the transaction first occurred within the system.

GL_JE_Name

This is the kind of journal entry.

GL_Line_Number

Journal line number.

Line_Number

The line number of the journal entry.

Orig_Line_Number

Original journal or subledger line number.

Line_Description

Description which was entered at the line level when the user entered the transaction.

GL_Transfer_Date

Date when the journal entry is transferred to the GL.

Completed_Date

Subledger journal entry completion date.

Transaction_Number

The number of the transaction.

Application_Name

Indicates the application in the system which was used to process the transaction.  Ie: Payables: Invoices, Purchasing: Reqs and POs, Project Costing: PPM (grants).

Event_ID

This is system generated information.

Event_Date

This is system generated information.

Event_Class_Name

The accounting event that originated the journal entry (ie: credit memo, period end accrual).

Event_Type_Name

The action taken relating to the accounting event that originated the journal entry (ie: credit memo validated, invoice cancelled, invoice validated).

Accounting_Class_Name

Accounting class assigned to the subledger journal entry line.

Party_Number

Supplier Number.

Party_Name

Supplier Name.

Party_Site_Number

Number assigned to the supplier’s address.

Party_Site_Name

Supplier’s address. Note: this appears as Order / Remit for transactions paid electronically.

INCLUDE_ACCT_WITH_NO_ACT

This are the filter options you selected when you scheduled the report.

BALANCE_TYPE_DSP

This are the filter options you selected when you scheduled the report.

FILTER_CONDITIONS_ATT

This are the filter options you selected when you scheduled the report.

FILTER_CONDITIONS_OPT

This are the filter options you selected when you scheduled the report.

Accounted_DR

A debit posted to the General Ledger (GL).

Accounted_CR

A credit posted to the General Ledger (GL).

COST_CENTER_DESC

Written Organization name

ORG

Numerical Organization

Balance

Balance of Debits minus Credits.

 

 

Was this helpful?
33% helpful - 3 reviews

Details

Article ID: 54226
Created
Sun 5/20/18 1:46 PM
Modified
Mon 9/17/18 3:58 PM