FMLA Workbench User Guide
Descripción
City of Portland
Treasury Module User Guide Front Office and Back Office SAP Roles
This document created by: EBS Team in consultation with PF&T.
Page 2
This document last modified: 12/8/2014
Treasury Module
Table of Contents 1. Overview..................................................................................................................................... 5 1.1 TRM Roles ........................................................................................................................... 5 1.2 Information needed in order to enter transactions. ......................................................... 7 1.3 TRM Activity Checklist ...................................................................................................... 10 2. Create Security Transactions .................................................................................................... 12 2.1. Create Class (FWZZ)......................................................................................................... 12 A. Fixed-Interest Bonds ...................................................................................................... 14 B. Variable Interest Bonds-- ............................................................................................... 15 C. Zero Bonds, Commercial Paper, Bankers’ Acceptance .................................................. 18 2.2 Upload Market Prices (TBDM) ......................................................................................... 20 2.3 Edit Security Prices (FW17) .............................................................................................. 21 2.4 Create Transactions (FTR_CREATE) .................................................................................. 22 2.4.1 Create Fixed Interest Bond ........................................................................................ 22 2.4.2 Create Variable Interest Bonds .................................................................................. 25 2.4.3 Create Zero Coupon Bond, Commercial Paper, Bankers’ Acceptance ...................... 27 3. Create Money Markets (FTR_CREATE) ..................................................................................... 30 3.1 Variable Interest Money Market ...................................................................................... 30 3.2 Fixed Term and Interest and Repurchase Agreements Money Markets ......................... 35 4. Settle the Transaction ............................................................................................................... 40 4.1 Settle the Transaction (FTR_EDIT).................................................................................... 40 5. Daily Activities—Front Office (Beginning of Day) .................................................................... 41 5.1 Review Cash Flows for the Day (TPM13) ......................................................................... 41 5.2 Upload market prices (TBDM) See 2.2 for directions ...................................................... 42 5.3 Change the Interest Rate (JBIRM) .................................................................................... 42 5.4 Securities—Update Planned Records (FWUP) ................................................................. 43 6. Daily Activities—Back Office (End of Day) ............................................................................... 45 6.1 Agree security cash flow to custodian (TPM13) .............................................................. 45 6.2 Post to the GL: Post purchase amount (TBB1) ................................................................. 46 6.3 Post to GL: Post interest receipt (FWSO) ......................................................................... 48 6.4 Derived Business Transactions: Clear prepaid interest (TPM18) .................................... 49 6.5 Post Amortization and Update Market Value (TPM1) ..................................................... 49 7. Troubleshooting—Back Office .................................................................................................. 52 7.1 Reverse error flows (FTR_Edit)......................................................................................... 52 7.1.1 Reverse a Purchase .................................................................................................... 52 7.1.2 Reverse a Non-Position Flow for a Money Market.................................................... 52 7.2 Reverse TBB1 Errors-Reverse a purchase or non-position flow for a MM (TPM10) ....... 53 7.3 Reverse Valuation--TPM1 (TPM2) .................................................................................... 54 Page 3
Overview
FMLA Workbench
7.4 Reverse FWSO for all or one security (TPM_POSTAUTREV) ............................................ 55 7.5 Reverse TJ05 (Interest Rate Fixing for MM’s) .................................................................. 56 8. As Needed—Front Office .......................................................................................................... 59 8.1 Create Business Partner (BP)............................................................................................ 59 8.2 Change the Business Partner ........................................................................................... 63 8.3 View changes to Business Partner ................................................................................... 63 8.4 Delete a Business Partner Role ........................................................................................ 64 8.5 Maintain Treasury Administrative Fee ............................................................................. 65 9. As Needed-Back Office.............................................................................................................. 67 9.1 Sell/Call Securities (FTR_Create) ...................................................................................... 67 9.2 Sell Variable Interest Money Market (FTR_EDIT) ............................................................ 68 9.3 Adjust Interest Payment for MM Transactions (FTR_EDIT) ............................................. 68 9.4 Change the Interest Rate (JBIRM) .................................................................................... 70 9.5 Update transaction with new interest rate (TJ13) ........................................................... 71 9.6 Fix Adjusted Interest Rate for MM’s (TJ05)...................................................................... 72 9.7 Create Securities Account (TRS_SEC_ACC) ...................................................................... 74 10. Monthly—Back Office ............................................................................................................. 74 10.1 Accrue Interest (TPM44) ................................................................................................ 74 10.2 Verify all relevant flows have posted for the month (TPM13) ...................................... 75 10.3 Review Money Market Interest Receipts (FTR_Edit) ..................................................... 76 10.4 Add LGIP Fees (FTR_EDIT) .............................................................................................. 77 10.5 Calculate Month-End Earnings Allocation...................................................................... 79 10.5.1 Determine the Net Accrued Interest for the Month ............................................... 79 10.5.2 Determine the Actual Monthly Earnings Received.................................................. 81 10.5.3 Calculate Earnings .................................................................................................... 85 10.6 Reconciliation of Earnings .............................................................................................. 85 11. Cash Management ................................................................................................................. 86 11.1 Posting Journal Reconciliation Tool (TPM20)................................................................. 86 12. Reports ................................................................................................................................... 86 12.1 Table of Reports ............................................................................................................. 87 12.2 Treasury Custom Report (ZTR_INVEST_REPORT)........................................................... 89
Page 4
Treasury Module
Overview
1. Overview The SAP Treasury Module (TRM) includes investment tracking, calculation of interest income, realized gains/losses and unrealized gains and losses, amortization of premiums and discounts, general trade information, integration to the GL and basic analytics such as total rate of return, weighted average maturity, and weighted average maturity to callable date.
1.1 TRM Roles Role RP_S_TR_FRONT OFFICE
Transaction
Description
BP
Maintain Business Partner (create and change)
FTR_CREATE
Create Order
FTR_EDIT
Edit Financial Transaction: Display Settle History Order expiration
RP_S_TR_BACK OFFICE
FWZZ
Create Security Class
JBIRM
Maintain Table Views (update reference interest rates)
TJ13
Update adjustment dates
TBDM
Import Market Data
ztr_admin_fee
Create/Change/Delete admin fee table
TJ05
Automatic Interest Rate or Commodity/ Security Price Adjustment
BP
Maintain Business Partner (create and change)
FTR_CREATE
Create Financial Transaction
FTR_EDIT
Edit Financial Transaction: Change Display Reverse Roll Over History Order Expiration
Page 5
Overview
Treasury Module
Role
RP_S_TR_DISPLAY
Page 6
Transaction
Description
FW17 FWSO FWZZ JBIRM TBB1
Give Notice Execute Order Enter Market Prices Securities: Automatic Debit Position Create Security Class Maintain Table Views (update reference interest rates) Treasury: Post Flows
TDBM
Import Market Data
TJ13
Update adjustment dates
ztr_admin_fee
Create/Change/Delete admin fee table
TJ05 TPM_POSTAUTREV TPM1 TPM10 TPM13 TPM18 TPM2 TPM44 TPM45 TRS_SEC_ACC
Automatic Interest Rate or Commodity/ Security Price Adjustment Reverse Automatic Debit Position Run Execute Valuation Fix, Post, Reverse Business Transactions (reverse TBB1) Subledger Cash Flow Fix and Post Derived Business Transactions Reverse Valuation Execute Accrual/Deferral of Expenses and Revenues Reverse Accrual/Deferral Create/Edit Securities Account
BP
Display Business Partner
BPCD FTR_EDIT FWZZ S_ALN_01001149 S_ALN_01001150 S_ALR_87015218 S_PLN_16000293 TBCD TIS1 TJ07 TJ10 TJ12
Change Documents (Business Partner) Display Financial Transaction Display Class Remaining Term Statistics Share of Position Posting Journal Total Commitment Treasury: Change Documents for Transaction Overview - Positions Rate / Price Adjustment Schedule Treasury: Summary Journal for MM, Securities, etc. Journal: Transactinos with Cash Flows
Treasury Module
Role
Overview
Transaction TMR1 TPM12 TPM13 TPM20 TPM40 TPM40a TPM51 ZTR_INVEST_REPORT
Description Money Market: Position List Subledger Positions Subledger Cash Flow Posting Journal Display Cash Flow for Security Account Display Securities Account Class Position List Treasury Ledger: Periodic Reporting Treasury Ledger: Periodic Reporting
1.2 Information needed in order to enter transactions. When creating, reviewing or changing transactions in the Treasury Module (TRM), it is important to have all the necessary information available from the Trade Ticket. The table below gives a description of common SAP fields in (TRM). SAP Field Description/Example Comment Bond Classification
Business Partner
Close Date:
US Treasuries US Agencies US Guaranteed Corp Bonds Municipal Bonds Corporate Bonds Counter Party: Broker/dealer or Click on Match box bank. Partners entered in field. Use Search Tab transaction Partners by BP Roles Issuer: Organization who issues the bond for securities. Entered on the Security Class. Depository Bank (Custodian Bank): Custodian of the Securities. Union Bank of California UB is the security account entered on the transaction. Trade Date
For Money Markets this is on the structure tab.
Page 7
Overview
Treasury Module SAP Field
Description/Example
Company Code Date Details: Pos. Value Date Calculat. Date Payment Date End of Term Fund
COP1 All are the settlement date
Gen Val Class
Short Term = up to and including 1 year Long Term = 1 year + Not-relevant (always) CUSIP # USD 1st Settlement Date Unless 1st interest accrual date is before interest start date—then use 1st interest accrual date. Issuer of the Security Example: RBS Securities Inc. One year + Par Value COP1 Market Price determination date Market Price as a % of PAR Example: 100.1% 04H-Variable rate bonds 04I-Fixed-interest bonds 04J-Zero bonds 51A-Fixed Term Deposit 51B-Portland Posterity 51C-Repurchase Agreements
Grant ID Number Issue Currency Issue Start Date
Issuer Long Term Nominal Amounts Portfolio Price Date Price/Rate Product Type
Page 8
Maturity Date 850400
Comment For Securities this is on the Trading Data tab. Can set to default
Except for: LGIP5073—850301 Portland Posterity-850208
Treasury Module SAP Field
Securities Acct. Short Term
Overview Description/Example 04B-Banker’s Acceptance 04C-Commercial Paper 55A-Variable interest MMs 55C-LGIP
Comment
Custodian--UB Up to and including one year
Page 9
Overview
Treasury Module
1.3 TRM Activity Checklist
Daily Front Office: Beginning of Day TPM13—Review cash flows for the day TBDM—Upload market prices (FW17 for manual input or review) JBIRM—Update Variable Bond Interest Rates Back Office: (End of Day) TPM13—Agree security cash flow to custodian TBB1—Post flows to accounting FWSO—Post security interest and repayment flows TPM18—Post derived business transactions TPM1—Post amortization and update market value Trouble-shooting: FTR_Edit—Reverse error flows (note—must execute TPM10 after reversal) TPM10—Reverse TBB1 errors (note must adjust transaction first) TPM2-Reverse TPM1—Reverse all or one security TPM_POSTAUTREV-- Reverse FWSO for all or one security TJ05_REV—Reverse TJ05 (MM interest rate fixing)
As needed: Front Office: FWZZ—Create Security Class FTR_Edit—Settle transactions created by back office BP—Create Business Partner ZTR_ADMIN_FEE—Maintain Treasury Administrative Fee Back Office: FTR_Create—Create transactions and sell/call FTR_Edit—Adjust interest flows, increase principle, decrease principle, roll-over fixed term deposits, reverse transactions or individual non-position flows. JBIRM—Update variable money markets interest rates (must run TJ13 and TJ05 to fix new rates) TJ13—update transaction with new interest rate (after JBIRM for money markets) TJ05 – fix interest adjustments for MM’s (after TJ13 update) TRS_SEC_ACC—Create or edit custodian (contact EBS) ZTR_ADMIN_FEE—Maintain Treasury Administrative Fee
Page 10
Treasury Module
Overview
Monthly: Back Office: TPM44—Execute accrued interest TPM13—Verify all relevant flows have posted for the month Calculate month-end earnings allocation Reconcile earnings distribution FTR_Edit--Review money market interest receipts FTR_Edit—Add LGIP Fees Cash management: Review LGIP and Custodian Clearing accounts are zero for the month TPM20—Posting Journal Reconciliation tool Reporting: ZTR_INVEST_REPORT – Summary and detailed data in City custom format TPM13—Review flow details by transaction/Security ID TPM12—Standard Position List TPM20—GL Posting journal, shows all TRM postings to the GL with account and document number. Create Security: FWZZ FTR_Create FTR_Edit (Settle) Create MM: FTR_Create FTR_Edit (Settle)
Page 11
2. Create Security Transactions
Treasury Module
2. Create Security Transactions To Enter Security Transactions there are three steps: 2.1. Create Class—Transaction Code FWZZ 2.2. Upload Market Prices—Transaction Code TBDM (this is an upload from a .txt file). (FW17 can be used for manual input or review) 2.3. Create Transaction—Transaction Code FTR_Create
2.1. Create Class (FWZZ) Transaction FWZZ is used to create financial instruments such as bonds. Step 1: Enter the transaction code FWZZ in the SAP Easy Access field. Note: All of these transactions can be set up as favorites! Step 2: Open the transaction. Step 3: Click on the Create
button.
Step 3: Complete the following: ID Number: CUSIP Number Product Type: 04H: Variable Bonds O4I: Fixed Interest Bonds O4J: Zero bonds (Discount bonds) O4B: Banker’s Acceptance O4C: Commercial Paper Shrt: Use Treasury convention (15 character limit) Long: Long description of the security (50 character limit) Make sure the Without reference radio button is checked.
Page 12
Treasury Module
2. Create Security Transactions
Step 4: Click on the Create button. Step 5: On the Search Terms Tab select the correct Bond Classification. The Bond Category is used to determine the correct Balance Sheet G/L account.
Step 6: Click on the Basic Data Tab. The Basic Data Tab contains the main information of the security. The data is the same for all product types. Enter the following information: Issuer: Issuer of the Security Issue Currency: USD Issue Start Date: 1st Settlement Date of Security. (Use 1st interest accrual if comes before 1st Settlement Date.) End of Term: Maturity date Interest Incl. Indicator: Select Inclusive Indicator if maturity date is included in Interest calculation, which is not usually the case. (It is unchecked by default.)
Step 7: Click on the Conditions Tab. Page 13
2. Create Security Transactions
Treasury Module
The directions below are for all three types of securities and Banker’s Acceptance and Commercial Paper. When you have completed the conditions for the class you are creating, go to STEP 8. A. Fixed-Interest Bonds Enter the following information: Int. Calc. Method: Use the Interest Calculation Method of the Bond. Use the following SAP methods depending on the Security information: Security SAP Calculation Method Information 30/360 360/360 Act/Act Act/Act(P) Act/360 Act/360 Repayment Type: Interest Condition:
Maturity Int (Perc-quoted) Field Description Eff. from The Issue Start date. Percent Calc. Date Calendar Rule Due Date Frq MEID
Final Repayment:
Page 14
Coupon / Interest Rate End Date of the Interest Calculation Payment working day shift usually 1 Interest Payment Due Date Frequency
Example Issue date: 11/28/11; Effective Start Date: 11/28/2011 Day before the 1st Coupon date Next business day convention = Next working day shift 1st Coupon date 6 = Interest Payable every 6 months
If checked, interest payment always on month end
Final Repayment Field Description Eff. from Issue date (1st Settlement Date)
Example Issue date: 11/28/11;
Treasury Module
2. Create Security Transactions Calc. Date
Calendar Rule Due Date MEID
Day Before Maturity Date if inclusive indicator unchecked. Maturity Date if inclusive indicator is checked. Payment working day shift usually 1 Maturity Date If checked, interest payment always on month end
Next business day convention = Next working day shift
1 1
B. Variable Interest Bonds--The Conditions Tab contains the Interest Calculation Method, Interest rate, Frequency and Due Dates. Enter the following information: Int. Calc. Method: Use the Interest Calculation Method of the Bond. Use the following SAP methods depending on the Security information: Security SAP Calculation Method Information 30/360 360/360 Act/Act Act/Act(P) Page 15
2. Create Security Transactions Act/360 Repayment Type: Variable Interest:
Treasury Module Act/360
Maturity Variable Interest Rate Field Description Eff. from Usually issue start date Reference Rate
Choose the Reference Rate
Calc. Date Calendar Rule Due Date
Day before maturity Working day shift rule if any Interest Payment Due Date Frequency
Frq MEID
Example Issue date: 11/28/11; Effective Start Date: 11/28/2011 The reference rate is created in configuration. Example: LIBORUSDO3—3 month Libor 1st Coupon date Next business day convention = Next working day shift 1st Coupon date 6 = Interest Payable every 6 months
If checked, interest payment always on month end
Int. Rate Adjustment: This dictates the conditions when the interest rate changes. Note: May need prospectus to determine these dates.
Page 16
Field Eff. from
Description Usually issue start date.
Calendar Rule Interest Rate Fixing Date
Working day shift rule if any Usually (-) x number of days from interest rate adjustment date.
Example Issue date: 11/28/11; Effective Start Date: 11/28/2011
Treasury Module
2. Create Security Transactions
Double click to update the condition details
Note: If specific dates or Regular is set, the Interest rate has to be maintained at these dates using transaction JBIRM.
Final Repayment:
Final Repayment
Field Eff. from Calc. Date
Due Date
Description Issue date (1st Settlement Date) Day Before Maturity Date if inclusive indicator unchecked. Maturity Date if inclusive indicator is checked. Maturity Date
Example Issue date: 11/28/11
Page 17
2. Create Security Transactions
Treasury Module
C. Zero Bonds, Commercial Paper, Bankers’ Acceptance The Conditions Tab contains the Interest Calculation Method, Interest rate, Frequency and Due Dates. Enter the following information: Int. Calc. Method: Use the Interest Calculation Method of the Security. Use the following SAP methods depending on the Security information: Security SAP Calculation Method Information 30/360 360/360 Act/Act Act/Act(P) Act/360 Act/360 Discounted: Repayment Type:
Discounted on exponential basis (always) Repayment of Security
Final Repayment:
Final Repayment Field Description
Page 18
Example
Treasury Module
2. Create Security Transactions Issue Date (1st Settlement Date) Day Before Maturity Date if inclusive indicator unchecked. Maturity Date if inclusive indicator is checked. Maturity Date
Eff. from Calc. Date
Due Date
Step 8: Click on the Exchanges Tab. Step 9: Click on the Insert Row
button. Enter NYSE in the Exchange field.
Page 19
2. Create Security Transactions
Treasury Module
Step 10: Click on the Notice Tab. For Callable bonds enter the Notice dates. Note: In the case of a premium, the “notice From date” is used to calculate the amortization for premiums. Step 11: Review Cash Flow. Verify dates are accurate and amounts make sense. If not, condition details need to be updated.
Step 12: Click the Save
button.
2.2 Upload Market Prices (TBDM) This is the automated way to upload market prices from a .txt file. The format is very specific for the file to properly upload. For a manual process and/or review you can use transaction Step 1: Enter the transaction code TBDM in the SAP Easy Access field. Step 2: Enter the following: a. Browse for the File name where your .txt file is stored. b. Leave Manual Entry selected c. Error file Check the Generate error file and Download checkboxes File name: Browse to where you’d like SAP to generate and save an error file once the transaction is updated. Page 20
Treasury Module
2. Create Security Transactions
d. Output control Check Test Run. Select All
Click on the Execute
button.
If no errors, uncheck the test run box and execute again. If errors, check output results, correct errors, and execute again. Note: You can verify Market Prices have been uploaded by checking FW17 (2.3)
2.3 Edit Security Prices (FW17) FW17 can be used for manual input or review. Step 1: Enter the transaction code FW17 in the SAP Easy Access field. Step 2: Enter the Id number and Exchange. (If you have just created the Class the ID # will default). Click on the Change button. Step 3: Enter the Price date. Step 4: Price type will always be Closing.
Page 21
2. Create Security Transactions
Treasury Module
Step 5: Enter the Price/Rate (always the rate).
Step 6: Click the Save button. NOTE: This is the Price TPM1 refers to when updating Market values.
2.4 Create Transactions (FTR_CREATE) 2.4.1 Create Fixed Interest Bond Step 1: Enter the transaction code FTR_CREATE in the SAP Easy Access field. Step 2: Open the transaction. Step 3: Enter the following on the Create Financial Transaction screen: Transaction Type: 100 Partner: Enter the appropriate business partner (broker from trade ticket). ID Number: CUSIP# Note: These fields will infer if you go to the transaction after FW17-Editing the Security Prices or FWZZ-Creating the Class. Step 4: Hit Enter or the button. Step 5: Enter the following on the Structure Tab based on the Trade Ticket:
Page 22
Treasury Module
2. Create Security Transactions
Securities Acct: UB (Custodian) Gen Val Class: Short Term or Long Term Date Details: All are settlement dates. NOTE: Calculat. Date is the date used to calculate accrued interest. Nominal Amount: Par Value Price: Price Step 6: Hit Enter or the button. Note: Check that the Payment Amount equals the Principal on the Trade Ticket and the Accrued Interest Payment Amount equals the Accrued Interest on the Trade Ticket. Step 7: Click on the Trading Date Tab: Step 8: Enter the following: Close Date: Trade Date Trader: Name of Trader Step 9: Click on the Administration Tab: Step 10: Enter the following: Portfolio: COP1 Fund: 850400 Grant: Not-Relevant
Page 23
2. Create Security Transactions Step 11: Click on the Position cash flows
Page 24
Treasury Module button.
Treasury Module
2. Create Security Transactions
Step 12: Select Valuation Area: Operative Valuation Area – US Gaap
Step 11: Select Create Automatically.
NOTE: Review flows for all dates and amounts to agree with trade ticket. Step 13: Click the green back arrow . Step 14: Click on the Cash Flow tab. You should see payment flows for the transaction. Step 15: Click the Save
button.
2.4.2 Create Variable Interest Bonds Step 1: Enter the transaction code FTR_CREATE in the SAP Easy Access field. Step 2: Open the transaction. Step 3: Enter the following in the Create Financial Transaction screen: a. Company Code: COP1 b. Transaction Type: 100 Page 25
2. Create Security Transactions c. Partner: Enter the appropriate business partner number. d. ID Number: CUSIP # Step 4: Hit Enter. Step 5: On the Structure Tab enter the following: Securities Acct: UB Gen Val Class: Short or Long Term Date Details: All are settlement dates Uncheck the Incl. check box. Nominal Amount: Par value Price Percent: Premium or Discount % Step 6: Hit Enter or the button. Note: Check the payment amount to make sure it calculated correctly. Note: Leave the Int.CalcMethod field blank as it pulls from the class. Step 7: Click on the Trading Date Tab: Step 8: Enter the following: Close Date: Trade Date Trader: Name of Trader
Page 26
Treasury Module
Treasury Module
2. Create Security Transactions
Step 9: Click on the Administr. Tab. Enter the following data: Portfolio: COP1 Fund: 850400 Grant: Not-Relevant Step 10: Click on the Position Cash Flows button.
Step 11: Click on Valuation Area; Operative Valuation Area – US Gaap Step 12: Click on Create Automatically. Step 13: Click on the green back button . Step 14: You should see payment flows for the transaction.
Note: If you double-click on the interest flow you can see the number of days on which the interest calculation is based. Step 15: Click the Save button. 2.4.3 Create Zero Coupon Bond, Commercial Paper, Bankers’ Acceptance Step 1: Enter the transaction code FTR_CREATE in the SAP Easy Access field. Step 2: Open the transaction. Step 3: Enter the following on the Create Financial Transaction screen: Transaction Type: 100 Partner: Enter the appropriate business partner ID Number: CUSIP# Note: These fields will infer if you go to the transaction after FW17-Editing the Security Prices or FWZZ-Creating the Class. Step 4: Hit Enter or the button. Step 5: Enter the following on the Structure Tab based on the Trade Ticket: Securities Acct: UB Gen Val Class: Short Term or Long Term
Page 27
2. Create Security Transactions
Treasury Module
Date Details: All are settlement dates. Nominal Amount: Par Value Price: Price
Step 6: Hit Enter or the
button.
Note: Check that the Payment Amount equals the Principal on the Trade Ticket. . Step 7: Click on the Trading Date Tab: Step 8: Enter the following: Close Date: Trade Date Trader: Name of Trader
Page 28
Treasury Module
2. Create Security Transactions
Step 9: Click on the Administration Tab: Step 10: Enter the following: Portfolio: COP1 Fund: 850400 Grant: Not-Relevant Step 11: Click on the Position cash flows button. Step 10: Select Valuation Area: Operative Valuation Area – US Gaap
Step 12: Select Create Automatically.
NOTE: Review all dates and amounts for all flows. Step 13: Click the green back arrow . Step 14: Click on the Cash Flow tab. You should see payment flows for the transaction. Step 15: Click the Save
button.
Page 29
3. Create Money Markets (FTR_CREATE)
Treasury Module
3. Create Money Markets (FTR_CREATE) 3.1 Variable Interest Money Market Step 1: Enter the transaction code FTR_CREATE in the SAP Easy Access field. You can also create a favorite. Step 2: Open the transaction. Step 3: Enter the following in the Create Financial Transaction screen: a. Company Code: COP1 will infer. b. Product Type: 55A-Variable Interest, without maturity 55C-LGIP c. Transaction Type: 100 d. Partner: Enter the appropriate business partner number. Step 4: Hit Enter or the button.
Page 30
Treasury Module Step 5: Enter the appropriate information in the following: Amount: Enter the dollar amount Start: when account opens End: current fiscal year end (update each year before the end of current for the next fiscal year) Inclusive: Start Inclusive Interest form: Variable Condition type: 1150 Ref.Int.Rate: Select the appropriate interest rate 1st Int. Rate: Enter the first interest rate from bank statement Int. calc. method: Act/ActY (ISDA) Frequency: WrkgDaysDueDate: Select either “No shift” or “Previous Work Day” Repayment form: Final repayment Condition type: 1120 Contract Date: Trade Date Trader: Name of Trader
3. Create Money Markets (FTR_CREATE) fields on the Structure Tab. Note the
Page 31
3. Create Money Markets (FTR_CREATE)
Treasury Module
Step 6: Enter the appropriate information in the
Step 7: Click on the Conditions
Page 32
fields on the Administr. Tab.
button. Double click on Condition Type 1150.
Treasury Module
3. Create Money Markets (FTR_CREATE)
Step 8: Check the indicated boxes on the Condition Details: Interest screen:
Page 33
3. Create Money Markets (FTR_CREATE)
Treasury Module
Step 9: Click the green back arrow . Step 10: Double-click on Condition type 1210. Step 11: Change Interest Rate Adjustment to “For Reference interest rate entry.”
Step 12: Click the green back arrow twice. Step 13: Click on the Cash Flow tab. You will see all related cash flows for the transaction based on the end term entered: investment, month-end interest payments and final repayment.
Page 34
Treasury Module
3. Create Money Markets (FTR_CREATE)
Note: You can double-click on the interest flow to see the number of days on which the interest calculation is based.
Step 14: Click the Save
button.
3.2 Fixed Term and Interest and Repurchase Agreements Money Markets Step 1: Enter the transaction code FTR_CREATE in the SAP Easy Access field. You can also create a favorite. Step 2: Open the transaction. Step 3: Enter the following in the Create Financial Transaction screen: e. Company Code: COP1. f. Product Type: 51A-Fixed-Term Deposit 51B-Portland Posterity 51C-Repurchase Agreements g. Transaction Type: 100 h. Partner: Enter the appropriate business partner number.
Page 35
3. Create Money Markets (FTR_CREATE) Step 4: Hit Enter. Step 5: Click on the Structure Tab. Step 6: Enter the appropriate information in the Payment Amount: Enter the par amount Term Start: Term End: Percentage Rate: % Int. calc. method: Act/ActY (ISDA) Frequency: At end of term Check “Shift due date back to end of term” box Note: Capitalized interest means interest isn’t paid out but added to the value of the investment. Click on the button. Enter appropriate shifts and indicate whether on rollover, interest is paid out (payment) or rolled into new balance (capitalization), or deferred to a particular paid out date (deferral). Contract Data: Trade Date Trader: Name of Trader
Page 36
Treasury Module
fields on the Structure Tab.
Treasury Module
3. Create Money Markets (FTR_CREATE)
Step 6: Click on the Administr. Tab. Enter the following: Portfolio: COP1 Fund: 850400 (Portland Posterity: 850208, LGIP5073: 850301 Grant: Not-Relevant
Step 7: Click on the Conditions
button. Double click on Condition Type 1200. Page 37
3. Create Money Markets (FTR_CREATE)
Treasury Module
Step 8: Check the appropriate boxes on the Condition Details: Interest screen for End of Interest Period and Due Date. For Working Day, Click in the field and hit F1 for information to determine the shift.
Step 9: Click the green back arrow
Page 38
.
Treasury Module
3. Create Money Markets (FTR_CREATE)
Step 10: Click on the Cash Flow tab. You should see all related cash flows for the transaction.
Note: If you double-click on the interest flow you can see the number of days on which the interest calculation is based.
Step 11: Click Save
.
Page 39
4. Settle the Transaction
Treasury Module
4. Settle the Transaction When a Transaction has been created, someone with the Front Office Role must review what has been entered and then Settle the transaction.
4.1 Settle the Transaction (FTR_EDIT) Step 1: Enter the transaction code FTR_EDIT in the SAP Easy Access field. You can also create a favorite. Step 2: Enter the transaction number. Step 3: Click on the Settle button. Step 4: Review the transaction.
Step 5: Click the Save
Page 40
button.
Treasury Module
5. Daily Activities—Front Office (Beginning of Day)
5. Daily Activities—Front Office (Beginning of Day) 5.1 Review Cash Flows for the Day (TPM13) This is a detailed listing of all flows related to a transaction; both those that post to the General Ledger (GL) and those that don’t (i.e.—accrued interest). This report can be used to reconcile to the custodian daily, review all flows have posted/fixed at month end, and other detailed review at the flow level. Step 1: Enter the transaction code TPM13 in the SAP Easy Access field. Step 2: Enter/delete/check the following: Product Groups: Check both Securities and OTC Trans.
Securities: Securities Account: Delete UB
Selection of TRL flows TRL Date: Enter today’s date Step 3: Click on the Execute button.
Page 41
5. Daily Activities—Front Office (Beginning of Day)
Treasury Module
Note: You can drill into a transaction by clicking on the TRL Date.
Once the report has executed there is a toolbar to point out:
Below are three buttons from the above toolbar and descriptions of their use: 1. Use this to refresh the report. This is useful if you’re executing transactions and want to update your results without having to leave the report. 2. Use with a particular flow highlighted and you can drill into the posting journal (TPM20) and see the posting details. 3. Use with a particular flow highlighted and you can press list and drill into the position list (TPM12) based on that flows date.
5.2 Upload market prices (TBDM) See 2.2 for directions 5.3 Change the Interest Rate (JBIRM) This will be done on a daily basis to update the Variable Bond Interest Rates and on an as needed basis for other interest rates. Note: Subsequent to this transaction you will need to run tcode FWUP. See 5.4 Step 1: Enter the transaction code JBIRM in the SAP Easy Access field. You can also create a favorite.
Page 42
Treasury Module
5. Daily Activities—Front Office (Beginning of Day)
Step 2: Select Ref. interest rate. Click on the green check or hit the enter key. Step 3: Click in the Ref. interest rate From field and use the matchbox dropdown menu to choose the appropriate reference interest rate. Step 4: Click on the green check Step 5: Click the Display>Change button. Step 6: Click the New Entries button.
Step 7: Enter the Reference interest rate, the beginning validity date and the actual rate.
Step 8: Click the Save
button.
5.4 Securities—Update Planned Records (FWUP) This transaction updates planned records between the security ID and the Security transaction. Run after interest rate adjustments, but also would need to be run if the City ever needed to add or change conditions on the security once transactions are created. Step 1: Enter the transaction code FWUP in the SAP Easy Access field. Page 43
5. Daily Activities—Front Office (Beginning of Day)
Treasury Module
Step 2: Input security ID and click on execute
You will see the Results Log with the message: “Update was carried out successfully.”
Page 44
Treasury Module
6. Daily Activities—Back Office (End of Day)
6. Daily Activities—Back Office (End of Day) 6.1 Agree security cash flow to custodian (TPM13) This is a detailed listing of all flows related to a transaction; both those that post to the General Ledger (GL) and those that don’t (i.e.—accrued interest). This report can be used to reconcile to the custodian daily, review all flows have posted/fixed at month end, and other detailed review at the flow level. Step 1: Enter the transaction code TPM13 in the SAP Easy Access field. Choose the Cash Flow Layout. Step 2: Enter/delete/check the following: Product Groups: Check Securities
Securities: Securities Account: Delete UB
Selection of TRL flows TRL Date: Enter today’s date Step 3: Click on the Execute button.
Page 45
6. Daily Activities—Back Office (End of Day)
Treasury Module
Note: You can drill into a transaction by clicking on the TRL Date.
Once the report has executed there is a toolbar to point out:
Below are three buttons from the above toolbar and descriptions of their use: 1. Use this to refresh the report. This is useful if you’re executing transactions and want to update your results without having to leave the report. 2. Use with a particular flow highlighted and you can drill into the posting journal (TPM20) and see the posting details. 3. Use with a particular flow highlighted and you can press list and drill into the position list (TPM12) based on that flows date.
6.2 Post to the GL: Post purchase amount (TBB1) Step 1: Step 2: Step 3: Step 4:
Enter the transaction code TBB1 in the SAP Easy Access field. Enter the transaction number. In the Up To And Including Due Date field enter the Date for flows you want to post. Check the Test run box.
Page 46
Treasury Module
Step 5: Click on the Execute
6. Daily Activities—Back Office (End of Day)
button.
Step 6: Review results. If all is correct, Click the green back arrow
, uncheck the Test Run
box and Click on the Execute button. Note: You should see purchase amount and appropriate GL accounts. Note: Click on the select layout button and choose the \EBS_1 layout.
Page 47
6. Daily Activities—Back Office (End of Day)
Treasury Module
6.3 Post to GL: Post interest receipt (FWSO) Step 1: Enter the transaction code FWSO in the SAP Easy Access field. Step 2: Enter the following: Company Code: COP1 ID Number: CUSIP# Up To and Including Due Date: First interest payment Step 3: Check the Test run box. Step 4: Click on the Execute button. Step 5: Review results. If all is correct, Click the green back arrow , uncheck the Test Run box and Click on the Execute
button.
Note: This posting will be the entire amount of the interest receipt offset to Deposits payable. The next step (TPM18) removes the portion that was in prepaid interest to clear out the prepaid interest. Note: Related TBB1 or FWSO flow must post before TPM18 will post.
Page 48
Treasury Module
6. Daily Activities—Back Office (End of Day)
6.4 Derived Business Transactions: Clear prepaid interest (TPM18) This needs to be run every day! Step 1: Enter the transaction code TPM18 in the SAP Easy Access field. Step 2: Enter the following: ID Number: CUSIP# TRL Date: First interest payment Step 3: Check the Test run box. Step 4: Click on the Execute button. Step 5: Click on the Fix button.
Step 6: Review results. If all is correct, Click the green back arrow box and click on the Execute
, uncheck the Test Run
button.
6.5 Post Amortization and Update Market Value (TPM1) This transaction is only relevant for the Securities Product Group as Money Markets don’t amortize or record market prices. Step 1: Enter the transaction code TPM1 in the SAP Easy Access field. Step 2: Enter or choose the following: Product Groups: Always select Securities Securities Account: Delete UB Valuation parameters: Key Date for Valuation: Date you are valuing Valuation Category: Always select Mid-Year Valuation Without Reset Posting Control: Check Test Run box.
Page 49
6. Daily Activities—Back Office (End of Day)
Step 4: Click on the Execute button Step 5: Click on Execute valuation button.
Step 4: Click the green check at the bottom of the messages window.
Page 50
Treasury Module
Treasury Module
6. Daily Activities—Back Office (End of Day)
Step 5: Click on Valuation messages to see any errors. Click on Logs + Messages to see accounting postings. Use the green back arrow screens.
to toggle between the two message
Note: You can drill into the accounting document by clicking on the document number. The document number will always start 37##########.
Step 6: Review results. If all is correct, Click the green back arrow box and click on the Execute
, uncheck the Test Run
button.
Page 51
7. Troubleshooting—Back Office
Treasury Module
7. Troubleshooting—Back Office 7.1 Reverse error flows (FTR_Edit) This transaction must be done to reverse a purchase or a non-position flow for a money market. Note: Must execute TPM10 after reversals to post to accounting. 7.1.1 Reverse a Purchase Note: If you are trying to reverse an entire transaction and dependent transactions have already posted, they will need to be reversed prior to this action. Step 1: Enter the transaction code FTR_Edit in the SAP Easy Access field. Step 2: Enter the transaction number Step 3: Select Reverse Step 4: Select Reversal Reason Step 5: Click on the Execute button. This puts the transaction into “to-be-reversed” status. Step 6: Run transaction TPM10 (7.2) 7.1.2 Reverse a Non-Position Flow for a Money Market Step 1: Enter the transaction code FTR_Edit in the SAP Easy Access field. Step 2: Enter the transaction number Step 3: Select Change Step 4: Go to Cash Flow Tab. Step 5: Change from edit to reverse mode by using the drop down on the edit/reverse icon. Step 6: Select the flow you’d like to reverse. Step 7: Select Reverse from the dropdown on the Flows icon. Step 8: Click on the Save button.
Page 52
Treasury Module
7. Troubleshooting—Back Office
7.2 Reverse TBB1 Errors-Reverse a purchase or non-position flow for a MM (TPM10) To reverse a purchase you have to reverse the entire transaction. Step 1: Enter the transaction code TPM10 in the SAP Easy Access field. Step 2: Enter the following information: Product Groups: Securities and/or OTC Transaction General Selections: Company Code: COP1 (will default) Limit by Transaction Number: Check the Limit by Transaction Number box and enter the transaction you want to reverse. Limiting of Bus. Transactions: Box must be checked Posting Control: Reason for Reversal: Select the reason using the match code Test Run: Leave checked for first run to check for errors and correct Output Control: Check Display and Select Business Transactions
Page 53
7. Troubleshooting—Back Office
Treasury Module
Step 3: Click on the Execute button. Step 4: Review Posting Log. If no errors, uncheck test run and execute. If errors, troubleshoot and correct errors, rerun as test run. When no errors, uncheck test run and execute.
7.3 Reverse Valuation--TPM1 (TPM2) Use this transaction to reverse all or one security. Step 1: Enter the transaction code TPM2 in the SAP Easy Access field . Step 2: Enter the following information depending on what you need to reverse: Product Groups: Securities and/or OTC Transaction General Selections: Company Code: COP1 (will default) Securities: ID Number: Enter the ID # only if you are reversing particular securities. If reversing all, leave blank. Securities Account: Delete UB Valuation Parameters: Key Date for Valuation: Date you want to reverse Posting Control: Reason for Reversal: Select the reason using the match code Test Run: Leave checked for first run to check for errors and correct Output Control: Check Display Positions
Page 54
Treasury Module
7. Troubleshooting—Back Office
Step 3: Click on the Execute button. Step 4: Review Posting Log. If no errors, uncheck test run and execute. If errors, troubleshoot and correct errors, rerun as test run. When no errors, uncheck test run and execute.
7.4 Reverse FWSO for all or one security (TPM_POSTAUTREV) Use this transaction to reverse FWSO postings. Step 1: Enter the transaction code TPM_POSTAUTREV in the SAP Easy Access field. Step 2: Enter the ID Number. Step 3: Securities Account: Delete UB Step 4: Position Value Date—Enter the date range of desired reversals. Note: Related GL Accounting period must still be open for this to post. Step 5: Select the Reason for Reversal using the match code. Step 6: Check Test Run Box.
Page 55
7. Troubleshooting—Back Office
Treasury Module
Step 7: Click on the Execute button. Step 8: Review Posting Log. If no errors, uncheck test run and execute. If errors, troubleshoot and correct errors, rerun as test run. When no errors, uncheck test run and execute.
7.5 Reverse TJ05 (Interest Rate Fixing for MM’s) This transaction is used when you determine that your variable interest MM had a different interest rate then you thought and you must reverse the interest rate fixing. Note: You must first use transaction JBIRM and correct the rate for the most current date. Even though you’ve done this, the transaction referencing that rate won’t update because you’ve already fixed the interest rate. Once you’ve completed the reversal you will need to run TJ05 again to fix the new rate before you are able to post the related interest payment (TBB1). Step 1: Enter the transaction code TJ05_REV in the SAP Easy Access field. Note: Use FTR_Edit and the Int.rate.adj tab get the necessary information to complete the reversal if you don’t already have it. See screen shot below:
Page 56
Treasury Module
7. Troubleshooting—Back Office
Step 2: Complete the following: a. Choose Interest Rate Adjustment b. Enter Fixing Date c. Enter Reference Interest Rate d. Uncheck All Transactions e. Enter Single Transaction # f. Check Test Run
Page 57
7. Troubleshooting—Back Office
Treasury Module
07
Step 3: Click on the Execute
button.
Step 4: If there are no errors, click the green back arrow click on the Execute
Page 58
button.
, uncheck the Test Run box, and
Treasury Module
8. As Needed—Front Office
8. As Needed—Front Office (See Sections 2, 3 and 4 for instructions on Creating Security Class and Settling Transactions)
8.1 Create Business Partner (BP) Business Partner Business Partners are created for each of the organizations with whom Treasury does business, such as counterparties, issuers, and custodial banks. Treasury will have its own unique group of business partners to maintain. These business partners will be numbered externally, meaning Treasury will enter the number when creating the business partner. The business partner numbering scheme is TRXXXX and will be used in numerical order (e.g. TR001, TR002, etc). Business partners are assigned roles based on the business process. All business partners start with the General Business Partner role. Step 1: Enter the transaction code BP in the SAP Easy Access field. Step 2: Open the transaction. Step 3: Click on the Organization button. Step 4: Complete the following: Business Partner: Enter the next available Treasury BP number (TRXXXX). Grouping: Treasury Create in BP role: Business Partner (Gen.)
Step 5: Complete the following on the Address Tab: Name: (1st line): Name of organization Name: (2nd line): Name of contact person Search Term: House No.: Street City/State/Zip Language: English Fax: E-mail: Website: Click Other communication, double-click on URl, enter the website. Page 59
8. As Needed—Front Office
Treasury Module
Step 5: Click on the Control Tab. Step 6: Enter the Authorization Group: TR01 (Treasury). Step 7: Select the appropriate role in the Create in BP role field by selecting from the dropdown list: A. Counterparty B. Depository Bank C. Issuer Step 8: Click on Save when the Change to another BP role in create mode window appears.
Page 60
Treasury Module
8. As Needed—Front Office
Step 9: Complete the following based on the role selected: A. Counterparty a. Click on Company Code b. Click on the SI:Authorizations tab Page 61
8. As Needed—Front Office
Treasury Module
c. Enter Company Code: COP1 d. Hit Enter e. Check the four authorization boxes
f. Click the Save
button.
B. Depository Bank Click the Save button. C. Issuer a. Click on the Ratings Tab b. Select the appropriate rating agency using the match code in the Rat. Proc. Column. c. Select the appropriate rating using the match code in the Rating Column. d. Enter the date the rating was issued in the Rated on field. Note: The Valid from and Valid to dates will default when you hit Enter.
Click the Save
Page 62
button.
Treasury Module
8. As Needed—Front Office
8.2 Change the Business Partner Step 1: Enter the transaction code BP in the SAP Easy Access field. Step 2: Click on the Open BP icon.
Step 3: Enter the Business Partner number. Click on the green check or hit Enter. Step 4: If in Display mode, switch to Change mode by clicking on the pencil icon . Step 5: Change the fields that need to be changed. Step 6: Click the Save
button.
8.3 View changes to Business Partner Step 1: Enter the transaction code BP in the SAP Easy Access field. Step 2: Click on the Open BP icon.
Step 3: Enter the Business Partner number. Click green check or hit Enter.
on the
Step 4: From the top menu bar, click Extras>Change History>For this Partner
Page 63
Treasury Module Step 5: Click on the Select All icon. Click on the green check mark or Hit Enter.
All changes to the business partner will be displayed.
8.4 Delete a Business Partner Role Step 1: Enter the transaction code BP in the SAP Easy Access field. Step 2: Select the relevant Business Partner. Step 3: Click on change display Step 4: Click on the Role detail
Page 64
button. button.
Treasury Module Step 5: Highlight the role you want to delete. Step 6: Click on the delete button.
8.5 Maintain Treasury Administrative Fee The Treasury Administrative Fee is tracked in SAP via a custom table that is maintained by Treasury. The fee is captured on a daily basis and is included in the SAP Treasury Investment Report, where it adjusts the Interest/Amortization per Day totals (producing a “Net” amount). The amount is also used when calculating the monthly earnings to be allocated to the bureaus. Procedure: Transaction code: ZTR_ADMIN_FEE The admin fee table includes three columns: Date: This field acts as an “effective” date. When a date and fee is entered, the fee will remain effective until a new date is entered. Therefore it is not necessary to enter a fee amount for every single day. This is true for both the Fixed Expenses and the Admin Fee Adjustment. Fixed Expense: This is the daily Treasury Administrative Fee. Admin Fee Adjustment: The adjustment field is used to increase or decrease the admin fee and is used when truing-up the fee to actual expenditures.
Page 65
Treasury Module
To add a new Fixed Expense amount: 1. Click 2. Enter the effective date 3. Enter the amount in the Fixed Expenses column 4. Click Save This amount will be used as the daily admin fee from this date forward. To add an Admin Fee Adjustment: 1. Click 2. Enter the effective date 3. Enter the appropriate Fixed Expense Amount—if you do not enter a fixed expense amount, the system will consider this amount as 0.00. 4. Enter the amount of the adjustment in the Admin. Fee Adjustment column. Add a positive amount to increase the fee or a negative amount to decrease the fee. 5. Click Save The system will sum the Fixed Expenses and Admin Fee Adjustment amounts and include the total amount in the Treasury Investment Report under Treasury Admin Fees.
Page 66
Treasury Module
9. As Needed-Back Office
Note: This procedure can also be done by those with the Back Office role.
9. As Needed-Back Office (See sections 2 and 3 for directions for create transactions)
9.1 Sell/Call Securities (FTR_Create) The steps to sell/call securities are the same as create transactions (sections 2 and 3) except for the following: Transaction Type: 200 Click on the Position Cash Flows button The Manual Assignment to Single Positions window will open. If only one lot, click on the green check mark. If multiple lots, as in the screen shot below, the assigned nominal column will be blank. You will need to allocate the amount from the lot you are selling. Then click on the Copy button and review.
Page 67
9. As Needed-Back Office
Treasury Module
Review flows for correct calculations of gain or loss, amortization, accrued interest and sale amount.
9.2 Sell Variable Interest Money Market (FTR_EDIT) Step 1: Step 2: Step 3: Step 4: Step 5:
Enter the transaction code FTR_EDIT in the SAP Easy Access field. Enter the transaction #. Click on Change. Change End Date (maturity date) to Sell Date. Click on the Save button
9.3 Adjust Interest Payment for MM Transactions (FTR_EDIT) Step 1: Enter the transaction code FTR_EDIT in the SAP Easy Access field. Step 2: Enter the Transaction #. Step 3: Click on Change. Step 4: Click on the Cash Flow tab.
Page 68
Treasury Module
9. As Needed-Back Office
You will see all related cash flows for the transaction based on the end term entered: investment, month-end interest payments and Final repayment.
Step 5: Step 6: Step 7: Step 8:
Click the Flows button. Go to Edit Flow Change Pmnt amount to actual bank receipt. Click the Copy button.
Page 69
9. As Needed-Back Office
Treasury Module
9.4 Change the Interest Rate (JBIRM) Step 1: Enter the transaction code JBIRM in the SAP Easy Access field. Step 2: Select Ref. interest rate. Click on the green check or hit the enter key. Step 3: Click in the Ref. interest rate From field and use the matchbox dropdown menu to choose the appropriate reference interest rate.
Step 4: Click on the green check Step 5: Click the Display>Change button. Step 6: Click the New Entries button.
Page 70
Treasury Module
9. As Needed-Back Office
Step 7: Enter the Reference interest rate, the beginning validity date and the actual rate.
Step 8: Click the Save
button.
9.5 Update transaction with new interest rate (TJ13) This is required after interest rate updates (JBIRM) to money market transactions so the transaction recognizes that the new rate is available. Note: This step can be substituted for an individual transaction by going into FTR_EDIT in change mode and saving. Step 1: Enter the transaction code TJ13 in the SAP Easy Access field. Step 2: Enter or select using the match code the relevant Reference Interest Rate. Step 3: Leave All Transactions box checked. Step 4: Check Test Run Box.
Page 71
9. As Needed-Back Office
Treasury Module
Step 7: Click on the Execute button. Step 8: Review Posting Log. If no errors, uncheck test run and execute. If errors, troubleshoot and correct errors, rerun as test run. When no errors, uncheck test run and execute.
9.6 Fix Adjusted Interest Rate for MM’s (TJ05) Every time an interest rate is changed, the interest flow needs to be adjusted. Step 1: Enter the transaction code TJ05 in the SAP Easy Access field. Step 2: Uncheck the All Transactions box. Step 3: Enter the transaction you want to adjust in the Single Transactions field. Step 4: Check the Test run box and Click on the Execute
button.
Step 5: If successful, remove the check from the Test run box and Execute
Page 72
Treasury Module
9. As Needed-Back Office
Page 73
Treasury Module
9.7 Create Securities Account (TRS_SEC_ACC) This is the transaction used to create a new custodian. If a new custodian needs to be created, contact EBS as creating a new custodian necessitates other changes in TRM.
10. Monthly—Back Office 10.1 Accrue Interest (TPM44) Step 1: Enter the transaction code TPM44 in the SAP Easy Access field Step 2: Enter the following: Product Group: Check Securities and OTC Transaction Company Code: COP1 ID Number: CUSIP# Securities Account: Must be blank (sometimes an account will default; delete it). Transaction: Transaction # Accrual/Deferral Key Date: Enter Check Including Key Date box and Test Run box. Check Immediate Posting Step 3: Click on the Execute
button.
Step 4: Review results. If all is correct, click the green back arrow , uncheck the Test Run box and click on the Execute button. Note: Run TPM13 to see subledger report with accrued interest updates types with Status fixed.
Page 74
Treasury Module
10. Monthly—Back Office
10.2 Verify all relevant flows have posted for the month (TPM13) This is a detailed listing of all flows related to a transaction; both those that post to the General Ledger (GL) and those that don’t (i.e.—accrued interest). This report can be used to reconcile to the custodian daily, review all flows have posted/fixed at month end, and other detailed review at the flow level. Step 1: Enter the transaction code TPM13 in the SAP Easy Access field. Step 2: Enter/delete/check the following: Product Groups: Check both Securities and OTC Trans.
Securities: Securities Account: Delete UB
Selection of TRL flows TRL Date: Enter the beginning and end date of the entire month Uncheck the Fixed Flows box.
Page 75
10. Monthly—Back Office
Treasury Module
Step 3: Click on the Execute button. Step 4: Review for scheduled flows that haven’t posted during the month.
10.3 Review Money Market Interest Receipts (FTR_Edit) Step 1: Enter the transaction code FTR_EDIT in the SAP Easy Access field. Step 2: Enter the Transaction #. Step 3: Click on Display.
Step 4: Click on the Cash Flow tab. You will see all related cash flows for the transaction based on the end term entered: investment, month-end interest payments and Final repayment. Page 76
Treasury Module
10. Monthly—Back Office
Step 5: Verify that SAP scheduled interest payment agrees with the actual bank receipt. If not, see Adjust Interest Payment for MM Transactions (section 9.3)
10.4 Add LGIP Fees (FTR_EDIT) At the end of the month LGIP charges fees for administering the account. Use this transaction to recognize the fees. Step 1: Enter the transaction code FTR_EDIT in the SAP Easy Access field. Step 2: Enter the transaction number. Step 3: Click on Change.
Page 77
10. Monthly—Back Office
Step 4: Click on the Other Flows Tab. Enter the following: FTYPE: 1901 (always) Amount: Amount of the fee Payment Date: Date the fee hit the City LGIP account.
Step 5: Click the Save Page 78
button.
Treasury Module
Treasury Module
10. Monthly—Back Office
10.5 Calculate Month-End Earnings Allocation The majority of the City of Portland’s investment earnings are pooled and allocated to bureaus each month-end based on an average-daily-cash balance. This is tracked in a Special Purpose Ledger (SPL) in SAP. Treasury calculates the amount to allocate from a combination of reports. Note: There are two streams of earnings that currently aren’t pooled with the other City earnings, but instead are individually allocated through separate Journal Entries by Treasury—Portland Posterity and LGIP-Multnomah Transient Tax. These can be distinguished for exclusion in the pooled earnings allocation by: Portland Posterity: Product Type: 51B Trans# 103 LGIP—Transient Tax: Trans#: 120 External Reference: #5073 There are three steps to determine the amount to allocate: 10.5.1 Determine the net Accrued Interest for the month 10.5.2 Determine the actual Monthly Earnings received 10.5.3 Calculate Earnings 10.5.1 Determine the Net Accrued Interest for the Month The calculation to determine the Net Accrued Interest for the month is to compare the accrued interest-to-date on the first day of the month to the accrued interest-to-date on the last day of the month; the difference between these two numbers is the Net Accrued Interest for that particular month. Step 1: Enter the transaction code TPM13. Step 2: Product Groups: Select Securities and OTC.
Step 3: Selection of TRL flows: TRL date: Enter the 1st and last day of the relevant month. Note: Use the Multiple Selection screen to enter the dates, rather than a date range. The report will run faster this way.
Page 79
10. Monthly—Back Office
Treasury Module
Select Fixed and Reversed Flows Step 4: Layout: /earnAccInt
Note: This layout filters for update types AD1000 and AD1001 and excludes product type 51B-Portland Posterity and trans#120—LGIP Transient Tax. Step 5: Click on the Execute
Page 80
button.
Treasury Module
10. Monthly—Back Office
Step 6: Calculate the net accrued interest by taking the subtotal for AD1000 (net accrued interest-to-date at the end of the month) and reducing it by the subtotal for AD1001 (net accrued interest-to-date at the start of the month). Example: Accrued interest-to-date at end of month AD1000 1,835,990.08 Accrued interest-to-date at beginning of month AD1001 (1,496,196.35) Net Accrued Interest for the month 339,793.73
10.5.2 Determine the Actual Monthly Earnings Received A. To determine all earnings except LGIP: Step 1: Enter the transaction code TPM20 Step 2: Product Groups: Select Securities and OTC.
Page 81
10. Monthly—Back Office
Treasury Module
Step 3: General Selections: Enter Fund 850400-Treasurer’s Interest Step 4:
Posting Data FI: Posting Date: First day to last day of relevant month (Enter the date range) G/L Account: 281100-Deposits Payable Step 5: Other Selections: Select Posted and Reversed Business Transactions Select Layout /EARNINGS
Page 82
Treasury Module
10. Monthly—Back Office
Step 6: Click on the Execute button. Step 7: Copy total amount into accrued interest spreadsheet. B. To determine LGIP earnings: Step 1: Enter the transaction code TPM20 Step 2: Product Group: Select OTC Step 3: OTC Transactions: Transaction 119 Step 4: Posting Data: Update Type: MM1150Step 5: Posting Data FI: Posting Date: First day to last day of relevant month (Enter the date range) G/L Account: 120060-Local Govt Investment Pool clearing account Step 6: Other Selections: Select Posted and Reversed Transactions
Page 83
10. Monthly—Back Office
Page 84
Treasury Module
Treasury Module
10. Monthly—Back Office
Step 7: Click on the Execute button. Copy total amount into accrued interest spreadsheet.
10.5.3 Calculate Earnings In the Accrued Interest Spreadsheet: Accrued Interest + Monthly Earnings + LGIP Earnings – Admin Fee Fiscal Year-to-Date= Monthly Earnings
A. Once the earnings amount is calculated, the earnings are allocated using transaction ZFICM01. Debit: 137100 Accrued Interest Receivable-year end Credit: Interest Revenue B. Once fund 850400 has enough cash balance, the allocated interest is distributed to the bureaus Debit: 281100 Deposits Payable, fund 850400 Credit: 137100 Accrued Interest Receivable-Year End C. At each month-end, the balances undistributed and allocated is matched in the GL is compared to the amount outstanding in the Treasury Module. Any differences must be researched by the Treasury Division. Differences are usually erroneous or unknown entries effecting earnings amounts.
10.6 Reconciliation of Earnings See section 10.5 for description of earnings allocation process. To do monthly reconciliation: Sum1: Calculate the GL available accrued interest using the following amounts: Outstanding accrued interest, including current month NOTE: This is interest allocated but not distributed. It should be the balance of account 137100 for all funds unless erroneous entries have been posted.
Page 85
11. Cash Management
Treasury Module
Add: Treasury Admin Fee fiscal year- to-date Subtract: Current month-end balance for GL Acct 281100, fund 850400 Note: This can be obtained by running t-code FAGLB03 YEAREND NOTES: Treasury Admin Fee: Add/subtract the yearend true-up fee/rebate amount. GL Acct 281100, fund 850400 balance: Determine if Treasury Division has reduced this balance by allocating to the Treasury Division Cost Center and add the amounts of those entries. Next: Sum2: Identify the available accrued interest from the Treasury Module. Follow the steps from 10.5.2, and note the subtotal for update type AD1000. Next: Sum3: Calculate the difference between the GL available accrued interest (Sum1) and the available accrued interest from the Treasury Module (Sum 2). Note: This difference should be s/b zero. If there is a difference the analyst must research and correct.
11. Cash Management 11.1 Posting Journal Reconciliation Tool (TPM20) This report shows all the flows that have already posted to the GL based on a particular date range and product group, or transaction, or Security ID #. Results show the posting date, document number, and debits/credits of the posting. Transactions which have not yet posted will not show on this report. Step 1: Enter the transaction code TPM20 in the SAP Easy Access field. Step 2: Enter the appropriate data in the following fields to filter the report: ID Number or Transaction Number (See text field from GL document) G/L Account Posting Date Step 3: Click on the Execute button or hit Enter.
12. Reports On the initial input screen of most reports in TRM you will see Product Groups at the top of the screen. The City of Portland uses two Product groups: Securities and OTC Transaction (Money
Page 86
Treasury Module
12. Reports
Markets). When you select a product, all transactions in that group show in the report. Without selecting a product no matter what selections you make in the selections below, no transactions will populate the report.
From here you enter data in the initial input screen fields based on the results you want to see and the type of report.
12.1 Table of Reports Below is a table of available reports, a brief description, and the page # where you can find information on running the report, if available in guide. Report Description Page # TPM13—Detailed Flows
TPM20—Posting Journal
TPM12—Position List
Detailed listing of all flows related to a transaction, both those that post to the General Ledger (GL) and those that don’t (i.e.-accrued interest). This report can be used to reconcile to the custodian daily, review that all flows have posted/fixed at month end, and other detailed review at the slow level. This report shows all flows that have already posted to the GL based on a particular date range and product group, or transaction, or Security ID #. Results show the posting date, document number, and debits/credits of the posting. Transactions that have not posted will not show on the report.
P. 40 and P. 43
P. 71
This is a position list, meaning it is executed based on a particular date, key date, and amounts are summarized to the transaction level for activity relative to that key date.
Page 87
12. Reports Report
Treasury Module Description
ZTR_INVEST_REPORT- This is a custom Treasury Module report and is a Treasury Investment multiple tab report. It is executed based on a key date, Report like TPM12. It contains yield calculations, weighted maturity, reporting on Treasury admin fee, interest per day, issuer details by investment type, maturity details by transaction, and then detailed tabs based on the type of investment: Bond positions, commercial paper, bankers’ acceptance, repurchase agreements, LGIP, and deposits. The final tab, ratings, is a detail of ratings by issuer based on the key date. The goal of this report is to present a consolidated view of Treasury activities, meeting the needs of daily users as well as high level decision makers. Summary Tab This is fairly static tab where the rows are fixed. It shows a summarized view based on the category. This is currently the only report showing: Net Portfolio Yield w/o Admin Fee, Net Portfolio Yield, WEIGHTED AVERAGE MATURITY, and AVERAGE EFFECTIVE MATURITY, in the Treasury Module. Maturity Overview This is detail by maturity date. All Variable Interest Money Markets, maturities have been overridden from the SAP default to always be one day, with a maturity the day before the key date the report is executed. Issuer Shows a summary by amount, based on market value, and category, with a total and percentage by issuer.
Page 88
Page # Screen shots below P. 73
Treasury Module
12. Reports
12.2 Treasury Custom Report (ZTR_INVEST_REPORT) This report is primarily a position based report. It reports a snapshot of the City’s investment portfolio based on a give date. It includes yield calculations and weighted maturity calculation that can only be found in this report. The data is on actual postings for all values except for Interest per Day and Accrued Interest. Step 1: Enter ZTR_INVEST_REPORT in the SAP Easy Access Screen Step 2: Enter the date in the Date field. Step 3: Click on the Execute
button.
Page 89
12. Reports
Treasury Module
Summary tab—Displays data as of the date entered. Int/Amort per Day: Total (Gross): This is a combination of Daily Interest Earnings and net Amortization Earnings. NOTE: this does include LGIP Transient Tax and Portland Posterity earnings. Total (Net): Gross – Treasury Admin Fee Net Portfolio Yield w/o Admin Fee : (Int/Amort per Day-Total (Gross)*365) / Book Value – Total (Gross) Net Portfolio Yield : (Int/Amort per Day-Total (Net)*365) / Book Value – Total (Gross) Weighted Average Maturity: (Each Trans#-BookValue * Days to Maturity)/Total Book Value Average Effective Maturity: (Each Trans#-BookValue * Days to Call Date(or Maturity if no call)/Total Book Value
Page 90
Treasury Module
12. Reports
Maturity Tab Days to Maturity: securities = maturity date from security class – key date Money markets = maturity date from trans – key date. ~for product type 55a and 55c maturity is always key date + 1 day Days to call date: only securities, Next Call Date – key date ~Make Whole Bonds= if First call date=Next call date=Maturity date then days to next call date = 1day
Page 91
12. Reports
Treasury Module
Issuer Tab: This tab is used to validate compliance with issuer limits set in the City Investment Policy. Amounts are based on the Market Value of the investment.
Ratings Tab: This tab presents current (based on key date) ratings of all business partners with the Issuer Role. Other Tabs: Bond Positions Commercial Paper Bankers’ Acceptance Repurchase Agreement (REPO) LGIP Deposits
Page 92
Lihat lebih banyak...
Comentarios
