Creating and Maintaining Banking Structures

Report
Creating and Maintaining
Accurate Banking Structures
Solutions Summit 2014
Terri Sullivan
Handling Your Bank Information
• Keeping track of the bank setup is an important part of using
SmartAnalysis.
• From small banks to large banks, domestic banks to foreign banks,
SmartAnalysis can be your repository of information.
• From importing signers to tracking your negotiated fees, this
information is at your fingertips in the Bank Information module.
• A well-maintained bank setup allows SmartAnalysis users to produce
the most consistently accurate evaluations of bank fee information.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Banking Structure
• Contains the hierarchical
information about a bank:
• Relationships
• Accounts
• Statements
• Under each bank are folders
containing relationship accounts
and statements.
• Under Relationships are negotiated
fees, closed accounts and active
detail accounts.
• Under Statements is a listing of all
bank statements received.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Importing Banking
Information
Using an 822 file, a TWIST xml file or a CSV file, SmartAnalysis imports
the information into an easy to navigate database.
Let’s start at the beginning: Importing
• SmartAnalysis allows
users to import the
following formats:
• 822
• TWIST XML
• CSV – Service
charges
• ‘Produce complete
baseline from this
statement’ is ONLY
selected the first time a
file is imported for a
bank not shown in your
Banking Structure.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Importing a file for a new bank
• When users import a file for a bank which is not set up in the banking
structure, users need to select ‘Produce complete baseline from this
statement’.
• Producing the complete baseline will create:
• The Bank
•
•
•
•
Name and ABA/SWIFT NUMBER
Incoming Balance codes
Contacts
Address
• Relationship accounts
• Name and account number
• Negotiated fees (based on the pricing contained in the file)
• Detail accounts
• Name and account number
Chesapeake System Solutions, Inc. • Proprietary and Confidential
822 Files
• The 822 file format is an industry standard for electronic
bank statements provided by your bank each statement
cycle.
• 822’s have various format versions and elective options
within the version. Currently the preferred version is 4010
format.
• This data file contains the complete bank, account and
relationship information along with listed balances and
service transactions used per month.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Technical Components of an 822 file
There are three levels to a 822 transaction set:
 Interchange Control Envelope (ISA/IEA): The largest envelope, this
segment contains addressing, security and control information.
 Functional Group Envelope (GS/GE): The inner envelope encloses a
group of similar transaction sets.
 Transaction Set Information (ST/SE): The business document that is the
electronic data file equivalent of one or more account analysis
statements.
• A transmission of account analysis data must always start with ISA
and GS segments and conclude with GE and IEA segments.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
A Raw 822 File
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Unwrapping an 822 file
• Copy file into Word
• Replace – Special –
Paragraph Mark with
“blank”
• Replace - \ with Special
–Manual Line Break
Chesapeake System Solutions, Inc. • Proprietary and Confidential
ISA*00*
*00*
*01*006962435
*01*987123487
*100624*0951*U*00301*000084923*0*P*~
GS*AA*006962435*987123487*20100624*095147*84923*X*004
010
ST*822*84923
BGN*05*00000032568173*20100624*095147*LT
DTM*150*20100201
DTM*151*20100228
N1*BK*WELLS FARGO BANK, N.A.*13*121000248
N3*PO BOX 63020
N4*SAN FRANCISCO*CA*94163
PER*AA*MACMATH, JENNIFER*TE*949-251-4334
RTE*1*1
RTE*2*0*0
RTE*UF*0
DTM*151*20100228
ENT*1
N1*AO*TEST CUSTOMER
N3*TEST STATEMENT -- DO NOT MAIL
N3*TEST STATEMENT -- DO NOT MAIL
N4*GREAT MILLS*MD*206342585
ACT*00000032568173*OVERALL SUMMARY*13*121000248
LX*1
BLN*TE*000420*105.79
BLN*TE*000240*0
BLN*TE*000330*0
BLN*TE*000010*10579.01
BLN*TE*000040*10473.22
BLN*TE*000000*10579.01
BLN*TE*000400*0
BLN*TE*000030*0
BLN*TE*000350*2251.3
BLN*TE*000020*0
BLN*TE*000013*0
Unwrapped 822
File
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Unwrapped Test File – Service Lines
SER*TB*150511CUS015*361956.35*595.00*7.00*85*CASHIER'S CHECKS $7.00 EACH**A~
SER*TB*150514CUS104*30416.50*50.00*5.00*10.00*MONEY ORDER $5.00/EACH**A~
SER*TB*010400AAA001*15208.25*25.00*25.00*1.00*ACCOUNT MAINTENANCE $25.00/ACCOUNT**A~
SER*TB*000230AAA012*2313.63*3.8033*0.015*253.55*FDIC INS/FICO $.015/$1000**B~
SER*TB*010400AAA100*18249.90*30.00*30.00*1.00*ELECTRONIC ACCT ANALYSIS 822 $30.00/MONTH**B~
SER*TB*010402AAA103*12166.60*20.00*20.00*1.00*822 TRANSMISSION $20.00/TRANS**B~
SER*TB*100600ARP004*33458.15*55.00*55.00*1.00*DEPOSIT RECONCILEMENT - BASE $55 ACCT/MONTH**B~
SER*TB*100610ARP041*123643.07*203.25*0.15*1355.00*DEPOSIT RECONCILEMENT - ITEM $.15 PER DEP**B~
SER*TB*151342ARP108*36499.80*60.00*6.00*10.00*PHOTOCOPY SERVICE $6.00 EACH**B~
SER* - Functional Identifier code
TB* - AFP Standard Identifier (sometime TE which signifies “only” AFP Codes, normally shown on balances)
150511CUS015*- AFP Code (first 6 digits) & Bank Code
361956.35* - Compensating Balance (amount that must be maintained to offset service charges)
595.00* - Total Service Charge
7.00* - Per Unit Charge
85* - Number of Units
CASHIER'S CHECKS $7.00 EACH* - Service Charge Description
* - Fee Type Identifier (if blank = P*Q; THP –Threshold pricing etc..)
A - Payment Method Code ( A = common charge; B= Compensation By Balance etc..)
~ - End of line indicator
Chesapeake System Solutions, Inc. • Proprietary and Confidential
TWIST XML FORMAT
• The global electronic billing statement, known as TWIST
“BSB,” is now an international standard under ISO 20022.
The BSB can provide your international bank relationships
with a complete standardized picture, analysis and
electronic billing statements.
• This data file contains the complete bank, account and
relationship information along with conversion rates and
service transactions used per month.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Sample TWIST file
<?xml version="1.0" encoding="utf-8"?>
<Twist xmlns="http://www.twiststandards.org/3.1/ElectronicBilling"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.twiststandards.org/3.1/ElectronicBilling
TWIST3.1.Billing.TwistMsgElectronicBilling.200609.xsd"
xsi:type="ElectronicBillingMsg">
<header>
<messageId messageIdScheme="Electronic Billing"/>
<inReplyTo messageIdScheme="Statement"/>
<sentBy id="13021000089">
<typedPartyId>
<partyId>021000089</partyId>
<partyIdType>13</partyIdType>
</typedPartyId>
<partyName>Citigroup Inc.</partyName>
</sentBy>
<creationTimestamp>2013-02-25T12:00:00</creationTimestamp>
<expiryTimestamp>2013-02-25T12:00:00</expiryTimestamp>
</header>
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Sample TWIST file
<account>
<accountLevel>Detail</accountLevel>
<bban>91173</bban>
<statementStartDate>2013-01-01</statementStartDate>
<statementEndDate>2013-01-31</statementEndDate>
<statementProductionDate>2013-02-25</statementProductionDate>
<statementStatus>Original</statementStatus>
……………………………
<currencyTranslation>
<originalCurrency>USD</originalCurrency>
<targetCurrency>USD</targetCurrency>
<translationValue>1.000</translationValue>
</currencyTranslation> <accountName>CHESSYS</accountName>
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Service Charges TWIST XML file
<service>
<bankServiceID>30007</bankServiceID>
<subServiceQualifier>MutallyDefined</subServiceQualifier>
<subServiceCode>30007</subServiceCode>
<serviceDescription>Book Transfers Automated</serviceDescription>
<volume>1</volume>
<pricingCurrencyCode>EUR</pricingCurrencyCode>
<unitPrice>
<amount>1</amount>
<currency>EUR</currency>
</unitPrice>
<priceMethod>UnitPriced</priceMethod>
<paymentMethod>HardCharge</paymentMethod>
<originalChargePrice>
<amount>1</amount>
<currency>EUR</currency>
</originalChargePrice>
<originalChargeSettlement>
<amount>1.42832</amount>
<currency>USD</currency>
Chesapeake System Solutions, Inc. • Proprietary and Confidential
CSV service charge import
• Format specific CSV file can be used to import service charges. This
file can be used in cases where no 822 file is available.
• Example of required format:
FLAG
STM
Flag
SER
SER
SER
SER
SER
SER
Bank Name
BankABA
Start Date End Date Creation Date DataVersion
Budget Bank
123000000
2/1/2012 02/29/2012 03/11/2012
999
Relationship Account
Account Number Service Code Unit Price Volume
Total PriceBank Code Balance Requirement Type
DEFAULT RELATIONSHIP SUMMARY ACCOUNT
362356855
100200
0.5
272
136
1002 bbp
DEFAULT RELATIONSHIP SUMMARY ACCOUNT
362356855
100210
0.15
20
3
1003 bbp
DEFAULT RELATIONSHIP SUMMARY ACCOUNT
362356855
250000
35
1
35
2534 bbp
DEFAULT RELATIONSHIP SUMMARY ACCOUNT
362356855
250100
0.15
10
1.5
1010 bbp
DEFAULT RELATIONSHIP SUMMARY ACCOUNT
362356855
251050
25
1
25
2013 bbp
DEFAULT RELATIONSHIP SUMMARY ACCOUNT
362356855
400000
0.11
22
2.42
5004 bbp
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Creating Banks and
Accounts Manually
When 822 files or TWIST files are unavailable, users can manually create
banks, accounts, and statements.
Manual Creation of New Banks and Accounts
• When creating a new banking hierarchy, users will create a bank,
relationship account(s) and detail account(s).
• If the bank has monthly analysis charges, the negotiated fees can be
added to the relationship accounts.
• Monthly analysis statements can be created manually and processed
into the software.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Creating a Bank Manually
• If the bank does not have an 822 file available, users can create their
banks manually.
• From Banking
Structure, select
the down arrow
and choose New
and then select
Bank.
• An unnamed bank
tab will open for
the user to enter
the bank’s
information .
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of Banks
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of Relationship Accounts
• After setting up a new
bank, a new
Relationship Account
will need to be created
to accompany it.
• To create this new
Relationship Account,
highlight the new
bank, right click it,
select ‘New’, and then
‘Relationship for
selected Bank’.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of Relationship Accounts
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Adding Negotiated Pricing/Fees
• After inputting the
Relationship
Account
information, the
service charge fees
will need to be input
as well.
• To do so, select the
‘Negotiated Fees’
tab, then select
‘Add’, and then
input the Identity
Information and
Rate Details.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of Detail Accounts
• After setting up a
new Relationship
Account, the new
Detail Accounts will
need to be created.
• To create this new
Detail Account,
highlight the new
Relationship
Account, right click
it, select ‘New’, and
then ‘Account for
selected
Relationship’.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of Detail Accounts
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Creation of a Statement
When only a paper statement is available, users can manually
create a statement.
Manual Creation of a Statement
• Users will have two options:
• ‘Create Statement from bank setup’ should be chosen when the user is
creating the first statement.
• If statements already exist, the user can ‘Create Statement from latest
statement data’.
• It is best to input the Negotiated Fees at the Relationship level
prior to creating a statement.
• Should the user create a statement before inputting the Negotiated
Fees, the user will have to add the line items when creating the
statement for each Summary and Detail Account.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of a Statement
• To create a new
statement manually,
highlight
Statements, right
click and make
selection.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of a Statement
• Users are
required to
input the
statement
period on the
‘General
Information’
tab.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of a Statement
• The ‘Account Detail’ tab
displays:
• Statement Hierarchy
• Bank name
• Relationship Account
• Detail Accounts
• Relationship/Account
Detail
• Service Charges
• Balances
• Rates
• The service charges
information must be
input on the for each
Relationship and Detail
Account.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Manual Creation of a Statement
• The balance code
information must be
input on the Balances
tab for each
Relationship and
Detail Account.
• The first month users
will need to add the
balance codes:
• To add codes, select
add, input the service
code, description and
amount
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Processing of a Manual Statement
• Upon completion of the
manual statement, save
your entries.
• The statement will be
shown under the
statements folder with a
red circle, signifying the
statement needs to be
processed into
SmartAnalysis.
• To process the
statement, right click on
the statement and select
Reprocess Statement.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Enhancing and Maintaining
Bank Information
Bank Level Information
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Bank Level - Contacts
• Detailed contact
information for
bank
representatives is
entered on the
Contacts tab of the
Bank Editor located
in the bottom
corner of the Bank
Editor, second tab
from the left.
• Information can be
either manually
input or imported.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Authorized Associates Tab (RM)
• Authorized Associates are employees of your
organization that are trusted to make decisions
about and take action on the relationship with a
bank.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Bank Level - Balance Mappings
• Balance Mappings
aligns the
incoming balance
information from a
bank with the
SmartAnalysis
Comparison Codes.
• This allows
SmartAnalysis to
perform
meaningful
comparisons of
balances across
banks in reports
and queries.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Bank Level – Case Folder
• The Case Folder tab provides you with an area to record comments/case notes,
insert attachments to the file and to email those entries to a selected email address.
• The Case Folder tab displays the history for the bank.
• A prefect place to attach your negotiated price listing.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Relationship Level Information
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Relationship Level – Negotiated Fees
• The Negotiated Fees Tab of the relationship editor contains
information for the expected fees that define the baseline against
which the discrepancy process compares incoming service charges.
• When carefully maintained, the negotiated fee information provides
accurate rate, boundary and tolerance information.
• The more accurate the negotiated fee setup is, the more effective
SmartAnalysis can be in helping you to identify and eradicate
discrepancies.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Relationship Level – Negotiated Fees
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Relationship Level – General Ledger Export
• The GL Export Tab of the relationship editor sets the allocation
patterns for bank fees at the summary level.
• Earning credit balance detail can also be allocated.
• Radio button is used to export the allocated charges.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Detail Account Level Information
• At the account level, any information regarding negotiated fees,
tolerances or G/L export will supersede the summary level
instruction.
• The specialized information – Bank Confirmation is part of the
Relationship Manager (RM) module.
• Account signer information is maintained on the Signer tab.
Relationship Manager (RM) module required.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Detail Account – General Information
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Detail Account – Signer Information (RM)
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Detail Account – Bank Confirmation
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Detail Account – Earnings Credit Calculator
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Banking Structures
• The Banking Information Module allows users to maintain
their entire banking relationship.
• From opening to closing, all account information is
available for review or update with a click of your mouse.
Chesapeake System Solutions, Inc. • Proprietary and Confidential
Any Questions?

similar documents