Q&A: How Do I Get My Paper Statement and 822 file to Match?

Short Answer: 

You don't.

This does not depend on whether your Treasury Dept has an Account Analysis system or uses MS Excel.  It depends on which, the paper statement or EDI 822 file, is the official account of record.

The official account of record means one particular format in which the bank sends your Treasury department's invoice for bank fees due is the only record by which the bank considers "official".  For example, if the account of record for your company is the EDI 822 file, then what the paper statement includes or doesn't include is irrelevant because only the data sent via the EDI 822 file is the official record of what your bank says your company owes it in bank fees for that particular time frame.

This is why my short answer to how to balance one to the other is "you don't" because one must serve as the account of record while the other is merely an alternate copy of the same data (ideally).  But, hopefully, you are not paying for both!

Let's step back and define what is a Paper Statement...

A paper statement is the either monthly or quarterly often itemized paper invoice (not in an electronic format) companies receive from their bank(s).  It includes the account name, account number, account level, any ECR, any Earnings Allowance, list of all balances for the period, list of bank service activity and their fees, total net fees due, etc.  This statement tells you what services you've used at what rate and what you owe after any earnings credit is applied.  And, either monthly or quarterly, you pay those bank fees due.

Now, what is an EDI 822 file?

The 822 file is the exact same information but in an electronic format so it can be captured by an electronic Account Analysis system.  There is nothing you should receive on a paper statement related to your Account Analysis that you can't receive in an 822 file.  Ideally, one should be an identical representation of the other.  If there is data omitted from the 822 file, then you should contact your bank for a restated 822.  

Welcome to an Electronic World!

Despite the notion of an official account of record, this is still a frequently asked question mainly because some Treasury departments consider the paper statement to be their account of record (not sure this is official on their bank's end) so they frantically try to match every single dollar amount/charge seen on the paper statement to what was captured from the EDI 822 file.  When a balance/charge does not match, they explain how management will freak out at the discrepancy so an explanation has to be made somehow even if it means manually inputting data where it was not just to balance things.

With the popularity of Account Analysis systems and the EDI 822 file along with the two international BSB formats available; most Treasury departments who have the bank and account size to justify it should only receive an electronic Account Analysis file.

With an electronic system, what purpose does a paper statement really serve?  Making the EDI 822 file your account of record alleviates the desire to match one to the other and does hold the bank accountable.

Don't have an Account Analysis system?  Then the paper statement will be your only official account of record by default.  

As always, if you have an Account Analysis question please feel free to email me: GirlMeetsAA@gmail.com.

  

Happy Analyzing!

Balance Required (8/14)

Account Analysis consists of balances and fees which comprise a basic math calculation. There are 14 balances that I refer to as "Remember the Math". I will discuss all 14 in 14 different posts.

Balance Required

Sometimes referred to as Collected Balance Required or Balance Equivalent - Total Service Charges, this is the amount required to offset total analyzed service charges for a current period.

This differs from the Balance Required Multiplier which is the amount required to offset $1 of analyzed service charges.

In perfecting my calculations, I was confused as to which definition correctly fits which title.  If you Google, "Collected Balance Required", you may find the definition given isn't truly correct. But let's breakdown the math...

Calculating the Balance Required

Lets assume the following numbers are true:

Analyzed Service Charges (ASC) = $500.00
Earnings Credit Rate (ECR) = 0.2%
Days in Period (DP) = 31
Days in Year (DY) = 365
Reserve Requirement (RR)** = $0

Collected Balances (CB) = (ASC) / [(ECR * DP/DY) * (1 - RR)]

CB = 500 / [(0.002 * 31/365) * (1 - 0)] = $2,943,548.38

To offset the total analyzed charges of $500.00 for the current period, the collected balance required is $2,943,548.38.

That's a sizable balance required just to offset $500 in analyzed service fees!  But imagine (and calculate) how much less that Balance Required would be if, and I'm just dreaming here, the ECR was 2%.

Balance Required to Offset $1

There are 2 ways to calculate this balance:

1. Collected Balance Required / Analyzed Service Charges
2. (1/ECR) / (DP/DY)

Using the 1st option, $2,943,548.38 / $500 = $5,887.09.  This is the simplest way to calculate the Balance Required Multiplier.

However, if you do not already have the Collected Balances Required amount, you will use the 2nd option.

=(1/0.002) / (31/365) = $5,887.09

The "multiplier" part comes in when you multiply this number by Service Charges to get the Collected Balances Required:

$5,887.0968 * $500 = $2,943,548

I added the next two decimal places which would have otherwise skewed the total by $3.  In this example, the balance required to offset $1 in service charges (if the above assumptions still hold true) is $5,887.09.

Auditing your Balance Required and/or Balance Required Multiplier

If you can read the raw data of the EDI 822 file, these balances should be sent by your bank(s). In an ideal world, what is sent on the EDI 822 file would also be sent on the paper statement.

For example, one of the large financial institutions has a dummy Account Analysis statement online which includes the "Service Charge Multiplier".  Using this number, the account holder can multiply it by the total service charges to find their Collected Balances Required.

The best way to audit these balances is still via an Account Analysis system.  It allows one to budget and forecast related service charges to plan future account collected balances required esp. with the current very unappealing ECR offerings.

Remember The Math

In the math thus far, Balance Required is the 8th component on the Compensation side of the Account Analysis equation.  Only 6 more to go! Til then...

Happy Analyzing!


**Most banks no longer deduct 10% as a Reserve Requirement so I've plugged in $0.00

Q&A: Is the CTP Worth the Time, Money, and Effort?

These last few weeks have been a time to consolidate my thoughts and posts into topics I believe readers want and need to know regarding Account Analysis. Although this post isn't directly related to Account Analysis I think it's interesting the number of questions I've received regarding the CTP exam. As a result, I have decided to try to address majority of them from my perspective and experience.

1. How long did you study for the CTP exam?  
2. What parts of the BOK were on the exam?  
3. Were you asked to do long calculations?  
4. Are there any lists to remember?   
5. On a scale of 1 - 10, how difficult is the exam?   
6. Is the AFP Learning System worth the money?  
7. Is the TMExam subscription enough?  
8. Did passing the CTP help your career?  
9. No one has heard of the CTP exam so do you think it's worth it? 
10. Would the CTP complement my MBA/CPA/CFA?   
11. Is the CTP required to get a Treasury position?  

• How long did you study for the CTP exam?  I think the recommendation is about 6 months but it depends on your background.  I had zero significant background in Treasury so I gave myself roughly 9 months to go back over the BOK just to get most of the reading done and understood. This also gave me time to complete the questions at the end of each chapter.  I wasn't sure if this would help me during the exam but it did help reinforce the material.  Whether those questions in any form would appear on the exam was a risk worth taking because I'd rather have that knowledge and not need it than to need it and not have it.  For others who have years of experience working in Treasury (or in some significant part of  Treasury), you might not need to take 9 months.  Maybe you only need 3 months to feel confident.  One client has worked in Treasury for at least 6 years.  She put off studying for the exam until the last minute almost doing a major cram session while I had carefully scheduled out my weeks for the last few months to make sure I covered everything in bite size portions. She passed. Easily. On the first try.  So, the answer to this question depends greatly on your current Treasury knowledge base and how much time you have available to devote to studying regularly. There is no wrong answer here.

• What parts of the BOK were on the exam?  Since the CTP is now in its next phase of testing windows with a completely new edition of the BOK, my answer to this question may be very irrelevant. However, when the suggestion is made to study everything in the book, just in case...you should really study everything in the book, just in case.  I took the exam during the June/July 2013 testing window and felt there were a lot of ECR, Earnings Allowance (EA), and Collected Balance (CB) questions in the beginning of the exam.  Notice anything ironic about this observation?  It could very well be that I calculate ECR, EA, and CB on a nearly daily basis so it would stand out and I would feel like I breezed through the first 20 or so questions. In my opinion, there were some sections which didn't have a heavy presence on my exam which was a relief because they were all part of my "Weak Areas" which means I spent more time studying them, just in case. 

• Were you asked to do long calculations?  No.  I had to do several long annoying Future Value calculations just to get one answer to one question but there isn't anything I can recall which took more than a couple of minutes to work out.  Actually, my testing center had a very subpar calculator which could only do the very basic of functions and maybe take the square of something.  I was freaking but the guy said if I really needed an actual calculator to let him know and they will find one. Somewhere. Either I wasn't asked to do more than basic calculations or my math skills are insane because I didn't need anything more than what I had.    

• Are there any specific lists to remember?  I recall a lot of definitions.  When I found myself throwing a blank, it was really easy to eliminate the ridiculous options and narrow it down to two.  Most times it was basic "choose the best answer" but I remember these being very easy to answer if you knew the material well enough to not get tripped up.  Basically, if you weren't 100% sure which was the answer, at least 2 of the 4 were outrageous enough to eliminate themselves increasing your odds of guessing the correct answer.  

• On a scale of 1 - 10, how difficult is the exam?  In answering this question, I compared it to the CPA or CFA exams which I have not taken but have heard they are some of thee most difficult exams in the Finance/Accounting realm. I don't think the CTP is on the same difficultly level as these exams.  However, having taken the exam twice, I thought the first time around was indeed difficult.  The second time around was almost a breeze.  I contribute this difference in experiences to how prepared I was in both situations so, again, this can vary depending on the person.  An experienced Treasurer can think the CTP is easy just as an experienced Accountant could think the CPA wasn't much of a struggle at all.  For the sake of giving an answer, I'd say it was right in the middle as far as difficulty for someone with zero prior Treasury knowledge who had prepared well for the exam.  

• Is the AFP Learning System worth the money?  I didn't use the AFP Learning System either time to prepare for the CTP exam so I can't say whether it is or is not worth the cost.  

• Is the TMExam subscription enough to pass?  For me, TMExam was not enough.  I liked the crossword puzzles and quick quizzes over each section which I took repeatedly.  I found the most difficult and helpful practice questions and quizzes came from the Preparing for Treasury Management Certification book (which is sold out indefinitely).  The questions are probably more difficult than any you'd find on the actual exam but it helped get me into the mind frame I needed to be in to tackle the subject matters well.

• Did passing the CTP help your career?  Not yet.  My answer is more optimistic at this point for various reasons including the fact that it has been less than a year since I passed the exam.  I still have the same position at my company, the same pay (minus a pay increase mostly for cost of living inflation), and the same responsibilities.  My company didn't cover the study materials for the CTP, the exam fee, or the AFP membership which initially goes along with that.  Everything was paid out of my own pockets.  In this case, my company didn't require the CTP for my position but there are some companies who do require it.  I personally know CTPs whose employers covered everything and gave them a pay raise once they passed the exam.  In this situation, having the CTP did help their career.  Also, most career opportunities which require or even prefer the CTP are not in my area (Vermont) so I am automatically limited in how I can make the CTP work for me.  This may not be the case for a vast majority so the answer to this question depends on many variables

• No one I've asked has heard of the CTP exam so do you think it's worth it?  Another difficult question to answer because I had to ask the person(s) for more information regarding their background, future career goals, etc.  Since the question was asked of me, I assessed my own situation to determine if at this point in time it was worth my efforts (twice); the jury is still out.  As stated above, I had to pay for everything required to eventually pass the CTP.  As most CTPs know, you are required to complete 36 continuing education credits within 3 years to retain those 3 letters.  Unless my company approves and covers my attendance to one of the many AFP conferences held annually, I will be forced to decide whether the cost drowns the benefit of having the CTP.  In the meantime, I've been sitting in on every free usually 1.2 CTP credits webinars I can fit into my schedule.  In addition, there is a required Recertification fee so you can submit and have your credits processed. However, for someone else, these challenges might not be present at all so they may feel the CTP is definitely worth the time, money, and effort.  

• Would the CTP complement my MBA/CPA/CFA?  I think so.  It shows mastery in a different section of the overall pie but certifications are usually not a bad thing when it comes to setting yourself apart from other applicants.  I have a Masters in Accounting.  I like to think that adding the CTP shows a breadth or depth of knowledge that's not limited to one specifically small area. Also, I was told that it also shows an aptitude to learn and master concepts.  Whatever works.   

• Is the CTP required to get a Treasury position?  No, it's not required but for some positions it is or may be "preferred".  It depends on the company and what the Treasury department determines to be a require skill set or knowledge base to do the job well.

Hopefully, this answers most questions anyone may have regarding the CTP.  Real answers to real questions from a real person not in a conflict of interest position.  

I still welcome questions posted to the blog or emailed to me.  Never think you are being bothersome or bombarding me with dumb questions.  

Next week, I shall return with more lucid Account Analysis posts. 

Happy Studying!

Q&A: Can My Bank Produce an EDI 822 File?

When I thought to write Q&A posts I assumed I'd run out of questions to answer but, from outside the field, very few Bankers or Treasurers understand Account Analysis so it has given me more to write about and - hopefully - this space remains informative.

Can my bank produce an EDI 822 file?

The first thing I ask when I get this question is, "who is your bank?"  It is safe to assume that the big banks of the USA are all producing an EDI 822 file for their Account Analysis customers.   There are some smaller, when compared to the Big Four, banks who have yet to move to an electronic file format for their Account Analysis.  Then there are some banks who would prefer their customers never find out an EDI 822 file exists!  Some banks will attempt to charge the customer requesting an EDI 822 an exorbitant initial set-up cost and/or try to appease by sending a MS Excel spreadsheet in place of or in addition to the paper Account Analysis statement. Usually, neither are good options which I will explain below.

Should my Treasury dept request an EDI 822 file?

The answer to this question depends greatly on what you plan to do with the EDI 822 file.  If you have already purchased and implemented an Account Analysis system, then an EDI 822 file is a must have.  It makes the capture, translation, and analysis of your EDI 822 file and even your BSB files easier and thorough.  However, if your Treasury staff does not have a software program which can capture and analyse the 822 data then how much benefit will an 822 file offer?

The comment I hear frequently from Treasury staff regarding an EDI 822 file is, "I can't read this thing!"  If you can't decipher what the file says and you do not have an Account Analysis system or module as part of your Treasury Workstation, then continuing to receive either a .pdf or Excel spreadsheet from your bank is a low cost alternative.  Always weighing the cost-benefit of any financial decision is a smart thing to do.

When would you recommend receiving an EDI 822?

Since the goal of Treasury is to cut costs and use money efficiently, my first response is when it helps your company achieve those goals.  Some Treasury departments are not fully staffed.  Even then, some are not staffed with an expert analysis team.  I have seen some pretty elaborate spreadsheets and macros used in MS Excel to calculate bank pricing, charges, volumes, fluctuations of either, and historical trends.  Since adding a new row for a bank service and/or account signifies an addition, the staff is aware of new services and accounts as well.

When the expert Treasury staff is not available, when the resources are just not there, and/or making the time to thoroughly analyse your bank fees is the last thing you attempt to do before the invoice must be paid; this is when I'd recommend an EDI 822 file.  Coupled with an Account Analysis system, it offers an efficient use of time which allows less focus on manually keying in data into a spreadsheet but more focus on analyzing that data.  Better management of your bank fees and cutting costs so your Treasury department's annual budget actually decreases with each passing year, should be the ultimate goal of any Treasury department.  How you get there depends on the individual needs of each company.

What about the BSB file?

The same would apply to the BSB file as well.  If your company has only domestic bank accounts denominated in the local USD currency, then a BSB is unnecessary.

With currency conversions, conversion rates, possible taxes, etc the BSB is slightly easier to read in its .xml format.  However, not all Account Analysis systems or Account Analysis modules of Treasury Workstations are TWIST BSB or ISO BSB/camt.086 message compatible so make sure you ask the right questions if you are deciding this is the right path for your company.

As always, if you have an Account Analysis question please feel free to email me: GirlMeetsAA@gmail.com. 

Happy Analyzing!

BSB Currency Types

The Bank Services Billing (BSB) is the international Account Analysis file which allows for various currencies.  Most companies have been receiving the BSB for some time now but do we really understand what each currency type tells us?

Below are 4 currency types you will see in a BSB file (their actual tag names may differ) along with some example balances.

• Account Currency - the currency in which the final account balances are denominated.
• Ledger Balance
• Float Balance
• Net Collected Balance
• Balance Subject to Reserves
• Investable Balance
• Etc.

• Price Currency - the currency in which the account's services are priced.  
• Settlement Currency - the currency in which the account's final bank fees are denominated and due. 
• Services Charges - Balance Compensable
• Service Charges - Balance Non Compensable
• Service Charges Net Due This Statement
• Total Service Charges Invoiced 
• Etc.
• Host Currency - the currency in which the account's taxes are due.

Most are already familiar with the account currency which usually ties to the home country's currency in which the account resides.  All the above currency types are important but since most Treasurers are more interested in what they owe, this makes the Settlement Currency very important.  Without it, it may be difficult to determine in which currency your final fees are due and how much is due.  

Important to Note:

• All 4 currencies may not be denominated in the same currency. Usually, there are 1-2 currencies per account statement but this may not always be the case.  
• The Account Analysis file must include a conversion rate.  This conversion rate is sent by the bank letting you, the Treasury staff, know what rate was used to convert one currency into its final settlement currency.  
• There is a Settlement Currency for both the Compensation Balances and each individual Service Charge.  You may see a Settlement Charge for your services in one currency which should align with the Settlement Currency of your total service charges due.  
• For individual bank services, Price Currency and Settlement Currency are different.  The currency in which your bank services are priced may be different from the charge in which your bank service fees are due.  
• Although Host Currency does exist I haven't seen many banks send tax information on their files.

FAQs:

1. Now that my bank sends a BSB, how do I make sense of its data?  
• Capturing and processing the data using some form of an Account Analysis system is the best approach.  It will allow you to easily read the BSB data, see your account balances, check service activity, and a host of other things. 
2. Most of our accounts settle in various currencies, how do I convert them into a single currency for reporting?  
• The bank should always send a conversion rate when the Settlement Currency differs from the Price and/or Account Currency.  This allows you to audit your bank fees due.  Your Account Analysis system or manual calculations using an applicable conversion rate according to an as of date will allow you to convert your multiple currencies into one for reporting.  
3. We receive the TWIST BSB; will the data differ when we begin receiving the ISO BSB?
• No. The tag names or formatting of the file may differ but your Account Analysis system should already be ISO BSB or camt.086 message ready.  The same data points will be sent so there are no major differences or changes your Treasury Dept should make to their process(es).  

This subject matter may not concern many companies whose accounts are all domestic but for the others, there were enough questions to make the post worthwhile.

Happy Analyzing!  

Keyword Search: EDI 822 Sample File

Regularly, I like to check which keywords brought someone to my blog or to a specific post.  From these keyword searches I can get an idea of which questions people desire answers to.  The #1 keyword search (thus far) has been "822 file".

These are variations of this internet keyword search that I've seen:

• EDI 822 Account Analysis
• 822 Statement
• X12 822
• 822 File
• What is An 822
• Account Analysis Statement
• 822 Sample File

I guess it's no surprise that of my 12 blog posts (thus far) the one with the most hits has been ANSI X12 EDI 822 with a 265 view count.  Considering that was my 1st blog post almost 9 months ago I think I should probably focus more on this topic since there may be a demand for the knowledge.

The Bad

Unfortunately, there isn't much I can discuss or explain without giving away most of the formatting rules and correct ways to construct an EDI 822 file.  Of course there are many basics on the internet but the heart of the different versions of the 822 file can't be disclosed or else little ole me will be in deep trouble!

The Good

Fortunately, the guide is available for sale from the AFP (not a sales pitch).  Of course, I know most banks have their own 822 implementation guide which they follow and sample 822 file which they distribute to their employees/contractors.  However, this has only allowed people like myself to easily distinguish one bad file from another.

The Myths

1. How a bank decides to format their EDI 822 file is correct.  It most definitely is not correct and I wish more receivers of these 822 files would demand the correct format instead of accepting the explanations given as final.  Also, anything "bank-specific" is contrary to the definition of "industry standard".  
2. Knowing how each bank sends its EDI 822 file is important.  Knowing how and what data your bank sends on the EDI 822 file is important.  Also, knowing how it should be sent can be important whether you have an Account Analysis software program or not.  This info is important to know for you, the Treasury dept, who pays the bank fees each period.
3. The bank's EDI department is staffed with EDI 822 experts.  Due to my experiences, I have lowered my expectations because I've spent more time explaining basics to the very people who ought to know since they are the ones in charge of formatting decisions and its execution.  Always strive to know more than to put your trust in someone else.

It is good that people seek knowledge and answers to something which they do not have a thorough understanding.  So, I will continue to use those keyword searches to write something more informative to assist as best I can without crossing any lines.  Also, any 822 file questions I receive I will attempt to address directly.

Happy Analyzing!

I am a CTP!

This week I passed the Certified Treasury Professional (CTP) exam.

This was my 2nd attempt; my 1st was during the June-July 2011 testing window where I missed the mark by 5 weighted points.  Or, maybe it was 5 weighted average points.  Whatever the exact wording, I remember seeing the number 5...so close yet so far away.

What is the CTP?

The CTP is an Association for Financial Professionals (AFP) certification exam.  According to the AFP website, "The Certified Treasury Professional (CTP) certification serves as a benchmark of competency in the finance profession and is recognized as the leading credential in corporate treasury worldwide."

For me, why the CTP?

It can feel demoralizing when Treasurers, Asst Treasurers, and/or Treasury Analysts contact you for the most efficient ways to do their account analysis each month yet not view you as their equal.  Based on some comments, I knew that without the CTP I may always be viewed as inept so I wanted to change that. However, not all Treasury departments require the CTP.

Also, one of my best Accounting professors (Husam Abu Khadra, PhD, CFFA and IFRS certified) always stressed the importance of certification exams to our class.  You gain respect and an even footing when you earn a certification of those whom you aspire to follow in a field you desire to either get into or move up in.  A certificate in your ideal area is a great accompaniment to a degree.  For me, it was about respect and future career goals I have for myself.  Most do not understand Account Analysis but those in my field do recognize the significance of the 3 letters I've earned.

Lastly, David K. Waltz, author of the Treasury Cafe blog, has been very supportive.  His blog focuses on topics of Treasury that I have not reached yet so it's my education outside of the classroom.  He's been a great mentor and introduced me to many experts within the Treasury field.  

How did I Prepare?

I purchased the Essentials of Treasury Management, 3rd Edition from AFP but I didn't purchase any prep courses or the AFP Learning System.  In an effort to save money, I instead purchased a membership to TMExam and bought a copy of Preparing for Treasury Management Certification, 3rd Edition.  Both the Preparing for Treasury Mgmt book and the TMExam site were recommended to me by other CTPs. Also, repetition and writing works for me. Writing down concepts or formulas even multiple times helped me recall them because I could close my eyes and visualize the list or paper I'd grown accustomed to seeing.  

After failing the exam the first time around, I decided to take a break and finish my Masters in Accounting (MSA).  Surprisingly, this gave me a very good foundation on many concepts I needed to learn for the Treasury exam. You'd think Accounting would have nothing to do with Treasury but, at least for my program, we had to understand how the accounting of things can directly affect a business and its revenue decisions.

I found an overlap in these areas: types of bonds & stocks (and pros/cons to each), inventory accounting methods, aging schedules/DSO, COGS, financing options, collateral, constructing financial statements which led to financial ratios, auditing, etc.  My most influential courses were Cost Accounting, Asset Valuation, Income Determination, and my capstone class which required us to apply all we'd learned as Accounting students to make financial decisions operating our fictitious business.

Indefinite Study Break

I'm ecstatic I passed and I'm glad to have a break from studying.  I've both gained and strengthen several areas of understanding while feeling this was the best move for me at this point in my career. Sometimes I wish I was one of those people who got things right on the first try.  Other times, I know I have to try again just to prove something to myself.

Happy Analyzing!