Financial reporting

The financial report is prepared as an Excel spreadsheet. The latest copy of the report is available in Excel format in the Financial Report subfolder of the Financial Records folder in Wild Apricot. (Note: you must be logged in to Wild Apricot and in Admin view to use this link.)

If this is the first financial report for a new fiscal year, start a new Excel spreadsheet. The format and proposed budget should be worked out with the ASURA Treasurer, but you can use the latest report as a starting point for creating the new report.

Change the FY in the header of the spreadsheet if this is a new fiscal year. Change the "As of" date in the header to the last day of the month you are working on.

For each non-scholarship account:

• In Quicken, create a Banking Summary report by choosing Reports-Banking-Banking Summary. Customize the report by clicking on the "customize" button in the upper right, then on the Accounts tab to select only the current account is selected. Also customize dates to include everything from the start of the fiscal year through the end of the month for which you are reporting.
• Copy figures from the Banking Summary to the Actual YTD columns. The categories in the Banking Summary should match the account categories in the financial report, so you know which figures to copy where. Special for the Operations account:
  • Use the figures for individual events in the "Event Registration Fees Collected" and in "Paid Event Costs" to create or update the  figures on the Travel Report (second tab on the spreadsheet). You will need to review the detail for each event to see the management fee associated with that event.
  • The total Net Gain/Loss figure on the Travel Report should be automatically used on the Financial Report under Event Costs - Paid Events - net".
• Compare the Actual Unexpended amount in the spreadsheet to the balance as of the end of the month for which you are reporting in Quicken (the Quicken figure should have been already reconciled to the Foundation account). These figures should match, except that for the Operations Account in FY 2024, you will need to match to an adjusted figure:
  • Subtract the Book Sales Net Income and add the Gifts in Kind per Foundation to the Unexpended ASURA Operations Fund figure in Quicken,
  • This because those two entries refer to books donated, and do not directly affect the actual funds available. Beginning part way through FY 2024 the book donations are reported in the Special Projects account, and so will not affect the Financial Report beginning in FY 2025.
• If the figures don't match, make sure that you have set the parameters in the Banking Summary Report correctly and that you have copied everything correctly.

For the Scholarship Endowment Principal portion of the Financial Report:

• In Workday, generate a Summary Balance Sheet for the Endowment account. Parameters:
  • Display by: Gift.
  • Company: Prefilled with ASU Enterprise Partners Consolidation. Leave it alone.
  • Period: The month you are working on, selected from Period-All-Periods and the current fiscal year. Note that month numbers start with the first month in the fiscal year, i.e., July.
  • Fund Hierarchies: Leave blank,.
  • Fund: Select both FD400 and FD401.
  • Gioft Hierarchies: Leave blank.
  • Gift: G03616.
  • Note that you can save these parameters as Filters, which you can then use in future, so you would need only to change the date.
• Copy figures from the Total column of the Summary Balance sheet to the YTD column of the Financial Report:
  • If this report is for February, in the Summary Balance sheet click on the figure for "Other Net Investment Return in fhe FD401 column. This will open a window that contains detail for what is in the total figure. Enlarge the window and scroll down until you find an entry with an Accounting Date in late February. If necessary, scroll right to view the Line Memo and the amount. You are looking for a line memo of "FYnn Endowment Payout Accrual". Copy the amount on this line to the "Payout to Endowment Spending" line in the Financial Report -- enter it as a negative figure.
  • For the "Net Investment Return" line in the Financial Report, calculate the sum of the line in the Foundation report labeled "Other Net Investment Return" and the Payout to Endowment Spending figure in our report. This is because the figure in the Foundation report is net of the payout.. 
  • Contributions to Current-Year Contributions
  • Transfers In to Transfers from Operations
• Make sure the Total Endowment Principal figure in the Financial Report matches the Ending Net Assets figure in the Total column of the Summary Balance Sheet. If it doesn't, review your work -- there is an error either in your calculations or in you copying.

For the Scholarship Endowment Spending portion of the Financial Report:

• Generate a Summary Balance Sheet for the Schoarship Spending Account. Parameters are the same as for the Endowment account, except that the fund is FD320.
• Copy figures from the Total column of the Summary Balance sheet to the YTD column of the Financial Report:
  • Other Net Investment Return to Payout from Endowment Principal.
  • If Transfers In is not 0, click on the figure to reveal detail. Any non-zero figure here would be very unusual (since closing the Special Projects account in FY 2023), and should be something the Treasurer could explain if you don't recognize it.
  • ASU Operations Expenses to the ASU Scholarship figure. This should be entered as a negative figure.
• Make sure the "Total Scholarship Endowment Spending" figure in the YTD column of the Financial Report matches the "Ending Net Assets" figure in the "Total" column of the Summary Balance Sheet. If it doesn't, review your work to find the problem.

Save the report on your computer with a name of YYYYMMDDFinancial Report.xlsx, where YYYYMMDD is the date that the report is "through".

To create the the For Fee Travel report:

• Download the latest version from the Financial Report subfolder of the Financial Records folder in Wild Apricot. (Note: you must be logged in to Wild Apricot and in Admin view to use this link.).
• If this is a new fiscal year, change the FY in the header of the spreadsheet. Change the "As of" date in the header to the last day of the month that you are reporting through.
• In Quicken, create a Banking Summary report by choosing Reports-Banking-Banking Summary. Customize the report by clicking on the "customize" button in the upper right, then on the Accounts tab to select only the Operation account. Also customize dates to include everything from the start of the fiscal year through the end of the month for which you are reporting.
• Use information from the Banking Summary report to list events for the current year, together with their associated fees paid and fees collected. 
• Save the report with a name of YYYYMMDDForFeeTravel.

Submit the completed reports to the Treasurer for review, and adjust the report as needed as a result of the review.

When the reports are final, upload them to the Financial Report subfolder of the Financial Records folder in Wild Apricot. (Note: you must be logged in to Wild Apricot and in Admin view to use this link.). Delete the previous versions of the reports from that folder.

If there is a Board meeting coming up in the next couple of weeks, save a copy of each report as a PDF file, and submit them to the Secretary when the call for Board items is issued. (This step might be done instead by the Treasurer.)

You will only need to do this once. After setting it up, you can use it whenever you want to generate a Donations Report.

\Use the "Find Journal Lines with Worktag Columns - End User" report in the EP Workday system to select donations. Then save your selections as a filter so you can use it each time the Membership Operations Manager requests a list:

• In the selection panel for the above-mentioned report, set fields as follows (leave all others alone):
  • Company = ASU Enterprise Partners Consolidation (the default)
  • Amount Type = Ending Balance (the default)
  • Time Period = Current Period YTD
  • Period = Use the current month. You will change this as needed.
  • Cost Center = CC00300 ASUF Gifts
  • Gift = add all of the ASURA accounts
  • Journal Source = Gift System
• Save this selection by scrolling all the way to the bottom and clicking on "Save". Give the filter a name such as "Donations Report"

Set up report filter

This needs to be done just once. After that you will use the saved filter you create here to select the data you need for the Donations Report.

\Use the "Find Journal Lines with Worktag Columns - End User" report in the EP Workday system to select donations. Then save your selections as a filter so you can use it each time the Membership Operations Manager requests a list:

• In the selection panel for the above-mentioned report, set fields as follows (leave all others alone):
  • Company = ASU Enterprise Partners Consolidation (the default)
  • Amount Type = Ending Balance (the default)
  • Time Period = Current Period YTD
  • Period = Use the current month. You will change this as needed.
  • Cost Center = CC00300 ASUF Gifts
  • Gift = add all of the ASURA accounts
  • Worktags = Revenue Category: RC5001 Gift Income
  • Journal Source = Gift System
• Save this selection by scrolling all the way to the bottom and clicking on "Save". Give the filter a name such as "Donations Report"

Set up report columns (optional and one-time)

If you would like to view the data you have selected in a usable format, e.g. to make sure that you have what you expected::

• Click on the "Expand Rows" icon in the top right corner of the Details list to get a view that wraps the long Line Memo fiel.
• Use the "Edit Grid Preferences" icon  in the top right corner of the Details list to hide all columns except:
  • Journal Number
  • Accounting Date
  • Gift
  • Revenue Category (just to make sure you are getting only gift income)
  • Line Memo
  • Ledger Credit Amount

Now whenever you run a "Find Journal Lines..." report, this is the format you will see. You can always change it  by unhiding columns if you need to see something different.

Download data to your computer

In Workday, run the "Find Journal Lines..." report with the filter you created and saved in the previous step, adjusting Period and Starting and Ending Accounting Dates to select transactions requested by the Membership Operations Manager.

While looking at a list of journal lines that you selected, click on the Export to Excel icon that is in the blue stripe at the top of the screen. Don't use the one that is in the same row with the grid preferences, etc.

When the "Export Document" prompt opens, click on "Download". This will put a file called "Find_Journal_Lines_with_Worktag_Columns_-_End_User.xlsx on your computer. For Windows computers, it will be in your Downloads folder (no matter where you have your browser set to put downloads). The file will contain all fields (not just those you selected for display) for the transactions you selected.

Select desired data fields in Excel

Double-click on the file to open it in Excel. It will open in Protected View - click on the "Enable Editing" button. Delete all of the lines that precede the headings for the data fields.

Delete all of the columns except:

• Journal Number
• Accounting Date
• Gift
• Line Memo
• Ledger Credit Amount

Select all data. On the Home tab, in the Cells block, in the Format pull-down, choose "Autofit row height".

Format for printing

If you have previously set up an Excel spreadsheet for this report, you can use it instead of re-creating all of the formatting each time. Or you can use the example report that is in the Donation Report folder of the Financial Records folder in Wild Apricot

• Open the formatted file
• Delete all the data
• Copy the new data that you downloaded and adjusted above to it
• Update the custom heading.
• Save under a new name.

If you are doing this for the first time, or don't want to re-use your previous formatting:

• Set page layout to landscape orientation. Adjust the width of the columns to maximize the Line Memo field while still fitting within the landscape space available.
• Set other page layout options:
  • Scale: 100%
  • Gridlines View and Print,
  • Headings View and Print
  • Sheet
    • Print titles Rows to repeat: 1:1
    • Print gridlines 
  • Page Setup: create a custom Header that appropriately labels the report, and choose "Page 1 of ?" from the footer pull-down.
  • Save the report (probably not on top of any previous reports you have prepared and sent).