Analytics User Guide

2.  Zoom in on a Dashboard Panel - The Dashboard Panel can be enlarged by clicking the magnifying glass with the “+” sign located at the top left of the panel.

• Click the magnifying glass with the “+”:

• After clicking the “+” icon, a new window will open with an enlarged version of the chart:

 

3. How to open values for transaction types – You can drill down on the transaction types within a Dashboard Panel to review the underlying accounts and associated dollar values.

• To drill down, double click on the bar of the transaction type you wish to view:

 

• The annual values will appear in a new window as follows:

 

• To expand the annual values to see the underlying accounts, select the triangle to the left of the transaction type name:

 

Note:  The underlying accounts use their general ledger natural sign. Revenue/income will show as a negative (credit) and expenses/losses will show positive (debit).

4. How to review the dashboard panel for a single fund:

• To review the dashboard panels for a single endowment fund (not at the default “Grouper” level) select the drop down menu next to the “Fund” field on the main dashboard window pane:

 

• To expand the Grouper to see the underlying endowment funds, select the triangle to the left of the Grouper code and name, and then select the desired endowment to view:

 

• Once the endowment has been selected it will appear in the “Fund” field and the dashboard panels will update automatically with the data for the individual fund selected:

 

Back to Top

Worksheets

Worksheet Contents

The Worksheets within Analytics are:

• Worksheets
  • Corpus Rollforward - Current View
  • Corpus Rollforward - Prior Years
  • Balances Worksheet - Current Year
  • Balances Worksheet - Prior Years
  • Payout Rollforward - Current Year
  • Payout Rollforward - Prior Years

The components of each worksheet are as follows (for definitions of each component, see the “Dashboard Panel Contents” section of this document:

• Corpus Rollforward – Current Year and Corpus Rollforward – Prior Years
  • Corpus Beginning Balance
  • Gifts and Pledge Payments FYTD
  • Transfers to Endowment Fund FYTD
  • Reinvestment Endowment Payout FYTD
  • Corpus Ending Balance
  • Corpus Units
• Balances Worksheet – Current Year and Balances Worksheet – Prior Years
  • Units
  • Book Value
  • Market Value
  • Unspent Payout
• Payout Rollforward – Current Year and Payout Rollforward – Prior Years
  • Unspent Payout Beginning Balance
  • Market Value Allocated to Payout FYTD
  • Spending Transfers – FYTD
  • Spending – Direct Payments FYTD
  • Reinvested Endowment Payout FYTD
  • Release of Restrictions FYTD
  • Adjustments to Unspent Payout FYTD
  • Accumulated Unspent Payout
  • Units

Note: The Current Year version of each Worksheet will include partial year data / data as of the most recent month end, whereas the Prior Year version of each Worksheet will include full year data / data as of fiscal year end.

Worksheet Data Refresh Timing

• Current Year Worksheets
  • The refresh timing schedule for the current year Worksheets is consistent with the Dashboard Panel refresh timing schedule (see “Dashboard Panel Refresh Timing”).
• Prior Year Worksheets
  • The prior year Worksheets will include full prior year activity (regardless of the month end through which data is included in the current year Worksheets).

Worksheet Functionality

1. How to expand data to fund level:

• To open a Worksheet, click the name of the Worksheet you would like to view under the “Worksheets” heading. When the Worksheet opens, the data will show in total by Grouper:

 

• To view the Worksheet at the individual endowment fund level, click the triangle to the left of the Grouper code name:

 

2. How to expand an individual column within a worksheet:

• To expand an individual column of the worksheet place your mouse on the line in between the columns until the double sided arrow appears:

 

• Then, drag to expand the column as necessary:

 

3. How to copy data and export data to Excel:

• To copy data from the Worksheet, highlight the data to export (or use CTRL + A to highlight all data), right click and select the option “Copy”. Then open Excel and paste the data. Using this method, the header row of the Worksheet will not copy.
• To export Worksheet data to Excel, highlight the data to export (or use CTRL + A to highlight all data), right click, select the option “Export to Excel”, and save the file to the desired location. Using this method, the header row will be copied.

 

 

 

 

4. How to calculate payout to be allocated for the remainder of the fiscal year:

• Payout to be allocated for the remainder of the fiscal year can be calculated by opening the “Payout Rollforward” and applying the following formula:

Units * (X/12) * Annual Payout Rate

• X = Number of remaining months in fiscal year
  If September data is the most recent data included in Analytics, X would equal “9” as there are 9 more months when payout will be allocated in the fiscal year (October – June). Refer to monthly refresh announcement email to determine the most recent data included in Analytics.
• For example, to calculate the payout to be allocated for EQ10007 for the remainder of the FY20 fiscal year, when data through September 2019 has been imported into Analytics, the calculation would be as follows: 32,423.00 * (9/12) * $3.24 = $78,787.89. Add this to the “Accumulated Unspent Payout” to arrive at the total amount available to spend during the remainder of FY20. $78,787.89 + $26,262.63 = $105,050.52
• Remember, if there is a threshold on the fund, payout will be automatically reinvested each month and therefore will not be available to spend.

5. How to review prior fiscal year data:

• Open the desired “Prior Years” Worksheet.
• At the top of the window, select the fiscal year you would like to review by selecting the drop down next to the field “TIME”.
• Once the desired fiscal year has been selected, the Worksheet will update with the selected year’s data.

Back to Top

Reports

Types of Reports

There are two reports included in Analytics. The reports and their contents are:

• Fund Information:
  • Fund Number
  • Fund Name
  • Designated Purpose
  • Type
  • Payout Threshold – Indicates payout is automatically reinvested monthly
  • Reinvest Payout (Yes/No) – Indicates whether unused payout will be automatically reinvested at fiscal year end
  • Grouper Code
  • Subgrouper Code – Not used for all funds, so may show as blank
• Transaction Report – Includes transaction‐level details of:
  • Gifts and Pledge Payments
  • Transfers to Endowment
  • Spending of Endowment Payout

Report Data Period

• The data contained in the Reports is on a real time basis and is not limited to the prior month end date like the Dashboard Panels and Worksheets.

To reconcile data contained in the Transaction Report to the Dashboard Panels and/or Worksheets, the transactions with a posting date later than the month that has been imported for the Dashboard Panels and Worksheets must be excluded. For example, if data through September 2019 has been imported into Analytics for the Dashboard Panels and Worksheets, exclude any transactions with a posting date after 9/30/2019 to tie the Transaction Report details to the Dashboard Panels and/or Worksheets.

Report Functionality

• Click on “Fund Information” under Report Viewer:

• The Fund Information Report can be limited to one or more specific “Fund Type” codes by using the checkbox next to each fund type in the drop down menu on the left:

• Once the desired Fund Types have been selected, click “Run Report” to generate a list of all
  endowments that match the Fund Types requested within your population of endowment
  funds:

• The Fund Information Report output will generate as follows:

2. How to run a Transaction Report:

• Select “Transaction Report” under Report Viewer:

• Select the Report Type on the left hand side of the window pane that generates:

• Once the Report Type is selected, click “Run Report”.

• The Transaction Report output Report should generate as follows:

Note 1: The “Amount” column in the Transaction Report uses the general ledger natural sign for the transaction. Revenue/income will show as a negative (credit) and expenses/losses will show positive (debit).
Note 2: To run another “Report Type”, close the Transaction Report window completely. Launch the Transaction Report again and select a new “Report Type”.

3. How to view the next page of Report data:

• The Fund Information Report and Transaction Report may contain multiple pages. To view additional pages, use the arrows at the top of the window pane to navigate:
  

4. How to export Reports to PDF and Excel:

• Reports can be exported to Excel or PDF by selecting the red arrow icon at the top left of the window and selecting Excel or PDF:

• You will then be prompted to choose a location where the report will be saved.

Back to Top

Users with Multiple Assignments

A limited number of users will have multiple Groupers assigned to them. In these cases the user must be aware of what data they are reviewing in the Dashboard Panels, Worksheets, and Reports.

1. Dashboard Panels

• To review the dashboard panels for a specific assignment select the drop down menu next to the “Fund” field on the main dashboard window pane:

• Select the desired Grouper assignment:

• Once the Grouper assignment has been selected it will appear in the “Fund” field and the
  dashboard panels will update automatically:

2. Worksheets

• When a Worksheet is selected all Grouper assignments will appear:

• To expand the Grouper to see the underlying endowment funds, select the triangle to the left of the Grouper code and name:

3. Reports

• For each Grouper assigned to the user there will be a Fund Information and Transaction Report. The Grouper will be noted in the report name as follows:
  • Report Viewer
    • GSPM Fund Information
    • VSTC Fund Information
    • GSPM Transaction Report
    • VSTC Transaction Report

Back to Top