Run & Save Reports
Monthly Reports
Run & Save Reports
Save a Monthlies Checklist
-Find a blank checklist in R:\Funds\Monthly Reports\Monthlies Checklist - New Process.doc
-In the monthly back up folder, create a folder for the new month to save your reports
-Go to R -> Funds -> Monthly Reports -> Monthlies Back Up -> 2015 Monthlies -> Month -> Fund #
Find theCost Roll Forward
-Interns run this report, but you can rerun it if necessary
-Jump To: “COSTROLLRPT”
-From date: prior month end calendar day of the month being audited
-To date: current month end calendar day of the month being audited
-It will be saved in R:\Funds\Monthly Reports\Cost Rollforwards
-Locate your fund by searching by Year > Month > Fund #
-Don’t run the proof until the Cost Roll Forward issaved out there
Run the Monthly Proof
-Use the template to run the Monthly Proof; do not use a prior month’s proof
-Find the monthly proof template in:
R:\Funds\Monthly Reports\Monthly Proofs\MonthlyProof-Acct#__FiscalYear.xls
-Fill inthe fund number
-Beginning date: last day of the fiscal year
-Ending date: last day of prior month calendar day
-Next to the ending date, click on Import Monthly Data
-This transfers the cost roll forward reports to the monthly proof spreadsheet
-Check any red cells for errors
-Save as a monthly proof in the monthlies back up folder as MonthlyProof-Fund#_FY(YYYY)
Run the Detail Trial Balance
- Jump To: “BatchRpt”
-Run a trial balance (R122)
-From date: prior month end calendar day of the month being audited (ex: 5/31/14)
-To date: current month end calendar day of the month being audited (ex: 6/30/14)
-Save in the monthlies back up folder as R122
Run the Adhocrpt Reports
-Jump To: “Adhocrpt”
-In the Monthlies – Automated Cost Roll Forward, run the Monthly Purchases, Monthly Sales, Monthly Short Term Purchases, Monthly Short Term Sales, Monthly Shorts, and Monthly Covers reports
-The Adhocrpt reports can be run together or separately
-From date: first calendar day of the month being audited
-To date: last calendar day of the month being audited
-Report Output Type: XLS
-Save in the monthlies back up folder
When completed, the Adhocrpt Reports will look like this in the monthlies back up folder:
Run the CSTROLLLTMV Report
-Jump To: CSTROLLLTMV
-Date: last calendar day of the month being audited
-Save in the monthlies back up folder as CSTROLLLTMV
Report Composition
-Move the above files into a“Paperless” folder
-Slim the CSTROLLLTMV report
-Hide from columns F to T
-Hide rows 6 to the bottom
-Change the print area to 1 page x 1 page
-Right click on all of the files in the paperless folder and choose “Combine in Acrobat”
-Move the reports around either before you combine the files or in Adobe, so they are in this order:
-Checklist
-Cost roll forward
-Monthly proof
-Detail trial balance
-Portfolio turnover
-Portfolio turnover YTD
-CSTROLLLTMV
Save Monthly
-Save as “Monthly_Year.Month.Date_Fund #”
-Example: Monthly_2014.06.30_6901
Verification of the Cost Roll Forward Report
Cost Roll Forward:
-The Cost Roll Forward is divided into three sections: Long Term, Short Term, and Short Positions
-Make sure that the miscellaneous amounts under each section are zero
-If they are not, notify your supervisor/lead senior; this may be due to new transaction code,
new asset group, or missing reporting category
-All material differences must be researched and corrected
-Miscellaneous balances can occur from the exercise of options during the period and should offset in the Long Term and the Short Positions columns; these are acceptable balances
-Scan the Cost Roll Forward for any bucketing issues
Monthly Proof:
-The Monthly Proof is a macro-based report that aggregates the Cost Roll Forward reports
-Check the numbers on the Cost Roll Forward to the Monthly Proof for long term, short term, and short positions for the appropriate month
Detail Trial Balance:
-Check for balance differences at the end of the Trial Balance
-If there are, notify your Supervisor/Senior Lead immediately
-All material differences must be researched and corrected
-Check for any uncategorized items on the Trial Balance
-Add up the Long Term Costs (equity, fixed Income, and derivative) and check to the Ending Long Term Balance on the Cost Roll Forward
-If needed, make a tape in Excel
-Copy the tape
-In Adobe, go under edit and choose paste
-Check the short term cost(cash equivalent) to the ending short term balance on the Cost Roll Forward
If your fund has shorts positions:
-Add up the long term costs and check to the total long term market value for the appropriate month on the Portfolio Turnover YTD
-Check the short positions at value cost to the ending short position balance on the Cost Roll Forward
Purchases and Sales with Less Than One Year:
-On the Cost Rollforward Report, there will be three additional tabs:
-Purchases Less Than One Year
-Sales Less Than One Year
-Tax Lot Less Than One Year
-Check that the securities listed on the Purchases Less Than One Year and Sales Less Than One Year tabs are reasonable based on the purchase or tax lot open date and maturity date of the security
Verification of the Ad-Hoc Reports
-Pull up the Long Term Purchases report and check the principal base column to the total purchases under the Long Term column on the Cost Roll Forward
-Pull up the Long Term Sales report and check the principal base column to the Total Sales under the Long Term column on the Cost Roll Forward
-Tenders should be included in the Total Sales figure
-If there are trades using the reversal flags (ex: SELL representing RBUY), then the net of
purchases and sales in the Cost Roll Forward should match the net of purchases and sells in the
Ad Hoc Reports
-Pull up the Short Term Purchases report and check the principal base column to the purchases total under the short term column on the Cost Roll Forward
-Pull up the Short Term Sales report and check the principal base column to the sales total under the short term column on the Cost Roll Forward
-Pull up the Shorts report and check the principal base column to the shorts total under the short position column on the Cost Roll Forward
-Pull up the Covers report and check the principal base column to the covers total under the short position column on the Cost Roll Forward
-If there are discrepancies between the ad-hoc reports and the Cost Roll Forward, research the difference and notify Operational Development if a change needs to be made
Verification of the Portfolio Turnover
Portfolio Turnover:
-Portfolio turnover is a measure of how frequently assets within a fund are bought and sold
-Portfolio turnover is calculated by taking either the total amount of new securities purchased or the amount of securities sold, whichever is less, over a particular period,divided by the total net asset value (NAV) of the fund
-On the current month Portfolio Turnover, check the Long Term Market Value, Purchases at Cost, Securities Purchased with <1 year, Sales at Cost, and Securities Sold to the Portfolio Turnover YTD
Portfolio Turnover YTD:
-On the Portfolio Turnover YTD, ensure that all data is calculated correctly:
-Check that subtotals add up correctly for the purchases and sales
-Check that the sub-total for the Total Long Term Market Value matches the End Market Value, and do the same for the rest of the sub-totals
-Check that the divisor is the month + 1
-Check that the Lesser of Purchases or Sales equals the Lesser of Total Purchases and Total Sales
-Check that the portfolio turnover calculation is calculated correctly (the calculation is in a percentage)
CSTROLLLTMV:
-Check the Long Term Market Value to the Total Long Term Market Value for the month on the Portfolio Turnover YTD
-Check the Long Term Market Value to the Long Term Market Value on the Trial Balance
Approving the Packet
-Sign as the preparer and put anapproved stamp (Comment -> Stamp -> Dynamic -> Approved)
-Sign off on the task completion matrix
Reviewing the Packet
-Once the packet is reviewed, sign as the reviewer and put a reviewed stamp
-Certify the packet (Sign -> Work with Certificates -> Certify)
-Save as the monthly report with “_FINAL” at the end
-Sign off on the task completion matrix