Gathering Endowment Information Agenda Provide information on how

Gathering Endowment Information

Agenda • Provide information on how endowment market value is tracked • Explain how BW Analysis can be used to display endowment information • Explain how the NRS website can be used to display endowment information

Endowment Sub-Ledger Accounting Johns Hopkins University has outsourced the task of calculating and maintaining endowment data to Northeast Retirement Services (NRS. ) NRS provides the following services: • Data feed to SAP for payout, buy-ins, and endowment market value • Endowment sub-ledger reports • Endowment history from SAP go live to present

Endowment Sub-Ledger Accounting The SAP general ledger does not provide a link between an endowment’s principal and income account. General Accounting and NRS maintain that crosswalk table.

Current Endowment Practices • Endowment requests and changes should be sent to General Accounting for approval. • Only General Accounting has the authorization to establish endowment principal funds.

Accessing Endowment Information in BW Analysis First, we’ll use BW Analysis to access historic and market values of an endowment principal fund.

Obtaining Historic & Market Value Using CUFS Prior to SAP, we used the ENDW function in CUFS to view historic book and market values. Historic book value is all gifts that have been made by donors. This is the corpus of the fund, which cannot be spent. Market value is all gifts plus undistributed earnings. It represents the endowment’s current total value.

BW Analysis Balance Sheet Comparison The Balance Sheet – Comparison report in BW Analysis is a quick and easy way to get endowment values. This report provides a current snapshot of an endowment’s historic book value and market value.

BW Analysis Balance Sheet Comparison The report also allows you to customize the data displayed and to download the data to Excel for further analysis. Note: Endowment market value is updated quarterly. You will only see changes in Periods 4, 7, 10, and 13, after the quarterly cycle for payout, buy-ins, and market value spread has been run.

Understanding the Balance Sheet – Comparison Report • There are two general ledger accounts within the balance sheet that provide the components of historic book and market value. – 163001 equals historic book value. – 163002 equals undistributed earnings, or the difference between historic book value and total market value.

Comparison of BW Analysis to CUFS From BW Analysis Black screens are from CUFS The total of GL accounts 163001 and 163002 is the endowment’s market value. Note: Only add GLs 163001 and 163002 to determine market value. In this example, market value is the same as the total assets in the endowment fund; this may not always be the case.

Accessing BW Analysis Balance Sheet Comparison Now that you have learned what the Balance Sheet – Comparison report does, let’s learn how to access the report.

Balance Sheet - Comparison To Access BW Analysis

Balance Sheet - Comparison On the Documents tab, navigate to Finance, Special Ledger and select the Balance Sheet – Comparison report.

Balance Sheet – Comparison Prompts This prompt screen should appear when you run the report. The next slides explain what to enter in each field. Note: Please contact us if you see an error indicating that you do not have authorization to run the report. We will give you the SAP role name and you will have to prepare an ISR to get access.

Criteria Selection Most recent period you want to view See next slides for fund criteria Enter your business area Once you have entered data in the required fields, click OK to run the report

Fund Option 1: Viewing all endowment funds in your division If you would like to view all the endowment principal accounts in your division, enter 35[your business area]* in the Fund field. All endowment principal funds begin with 35. The asterisk is a wild card. In this example, every fund that begins with 35160 will be displayed.

Fund Option 2: Viewing one endowment principal fund If you would like to view a single endowment, enter its principal fund in the Fund field.

Fund Option 3: Selecting more than one endowment fund Click the plus symbol to additional funds

Fund Option 3: Selecting more than one fund Make sure the drop down box is set to “Equal” for each endowment fund To additional funds, simply click the plus symbol again.

Fund Option 4: Selecting a range of endowment funds To select a range of funds, change the drop down value from “Equal” to “Between. ” The example above will display all endowment funds with numbers between 3516000001 and 3516099999.

Balance Sheet - Comparison Output The next slides show the report output. This report displays the entire balance sheet for a fund. We will take you through a series of commands to drill down on the endowment activity within the report.

Balance Sheet - Comparison Output After you execute the report, your initial screen should look like this.

Balance Sheet - Comparison Output If you executed the report with more than one fund and would like to see each fund separately, drag the Fund attribute to the Columns section of the report layout. Columns section of the layout Fund attribute

Balance Sheet - Comparison Output Your report will now have a column for every endowment fund you selected in the prompts.

Balance Sheet - Comparison Output To display the endowment principal fund number, right-click on Fund, Display as and left-click on “Key : Medium description” In BW Analysis terms, “Key” represents a GL or fund number.

Balance Sheet - Comparison Output The fund number should now be displayed alongside the fund name.

Balance Sheet - Comparison Output Remember that GL 163001 represents historic book value and GL 163002 represents undistributed earnings. An endowment’s total market value is the sum of GLs 163001 and 163002.

Export to Excel To send the report to Excel, click on the “Export” icon and choose the “XLSX (Excel 2007 and later)” file format.

Export to Excel Select the check boxes for “Repeat outer header members” and “Export Key and Text values as separate columns. ” Click “OK” to export the report to Excel.

Export to Excel Your Excel file should look like this. Now that the data is in Excel, you can manipulate and analyze it as needed.

Accessing NRS Sub-Ledger Reports The sub-ledger reports are available on the NRS website: https: //www. your-fundaccount. com/

Accessing NRS Sub-Ledger Reports The NRS login (“Web Account Name) is always your email address.

Accessing NRS Sub-Ledger Reports Detail report Summary report Click on “View Statement” to view a report. NRS provides detailed and summary reports for each quarter. You should only have access to the reports for your division.

NRS Detail Sub-Ledger Reports The detail report shows book value, market value, additions, and payout for each individual endowment.

NRS Summary Sub-Ledger Reports The summary report displays book value and market value for an entire division. Note: In this example, the totals have been removed for confidentiality purposes.

Conclusion You have been provided with two methods of accessing endowment information. We hope that this will be helpful to you. The next slide contains contact information for the General Accounting Investments group. Please contact us if you need any further information.

Contact Information • Nicole Smith 443 -997 -2321 Nicole. Smith@jhu. edu • Michael Lambright 443 -997 -4634 mlambright@jhu. edu • Vincent Shaughnessy 443 -997 -5004 vshaughnessy@jhu. edu