Let's make this example simple, however one that could have a very powerful impact that can also prove a point.
You first need to either befriend an individual in IT or have available the use of an MCIF system. The MCIF system will be the best tool for data for with most historical data is saved within a time frame. Most legacy systems will only provide you with a point in data. I don't want you to discount this process if you do not have an MCIF, however it will take much longer, years of data collection to produce the same results.
The information we will be trending will be Checking Accounts. Although we will look at only non-interest checking at this point, my suggestion to you is to build these examples into as many product categories as you would like to review. Your portfolio should include both Business and Retail Checking, interest and non-interest accounts, Consumer Loans, Home Equity Loans and Lines, Money Market accounts, Internet banking, Online Bill Pay, etc.
My suggestion would be to focus on checking first then once you become familiar with the process bring additional products online.
Automation by the use of queries, batch files, Vlookup functions in Excel can remove the time constraints on the collection and displaying of information. However for this example how to collect the data will be the focus.
The data that we will be trending will be all new accounts opened in each month in each of the categories. So to collect data within your MCIF go to your current month and select all non-interest checking account that were new to the institution for the month ended. Once ran you will want to push out a report of those new accounts by branch. If at first you want to review the information by company that is fine, however what we are looking for are trends so the best level for now will be branch.
Here is a component that confuses some marketers, we really shouldn't care about how well we acquire new accounts from month over month, however year over year month to month. So if in July a branch had 6 new non-interest checking accounts the comparison will be July of the prior years for the trend.
The philosophy is that we live our lives based on cycles. Schools start the same time each years, season begin and end on cycles, holidays come and go, however these cycles will also predict buying patterns. Now there are the occasional anomalies that occur that one must predict or take in account for adjustments.
If you can obtain historical data go back as far as you can. I'm sure you will not be able to do this all in one sitting, however over time collect and log the information.
 |
| Although this example only shows a few years of data you can begin to see a cycle. The more data the better you are able to see the trends |
If you are proficient at using your MCIF set up a batch to run the data, however make sure you select the correct historical time frame. With the Harland Touche' system this process is easy, however run the data and have it drop into an Excel file. Once it is finished before running the next time frame grab it rename it or something, for each time you re-run the batch it will just overlay the data.
If you are a little more proficient at Excel you can build a main table to display all of the information that will allow you to graph the data, which becomes very helpful.
I pushed this process so that data collection through batch was automated each month dropping all of my monthly category data into Excel folders, then I build an extractor tool using macros in Excel that grabbed the Excel files put them into a proper format so that all I did was copy and paste the data into my product report. By using Vlookup tables and other functions of Excel a end user would only have to select their branch and all of their new account data would be shown in both number a graph forms. I provided the user with both the number of new accounts and total dollars for comparison.
Now you might say that this looks like a lot of work. Yes it was, however after I had built all of the tools it took a matter of seconds to build each. Because this data is so important it is my opinion it should be built into the MCIF systems that you run. However up until now these systems are more focused on the direct mail component of their services.
Now once you have a number of years worth of data, what is it telling us? First you will be able to see where most of the accounts are being opened it and at what balance. Do you have locations that produce a large number of account, however low balances? Second if you are going to set goals for the upcoming year this is one great place to start. You can quickly tell if a location could handle 45 new accounts a month, or which months the goals should be increased or decreased.
You will be able to even tell which managers are doing a better coaching job as it pertains to sales.
I was able to obtain 5 years of trended data and I began predicting the future "In my terms" as to what I should expect from a location. Our finance group spent months throwing darts, however really when it came down to it they took a balance increased it by 10% then divided it by 12. In the end when the first comparison months were review against actual, the finance group was off hundreds accounts accounts and I was off in one category by 1 account and others far closer by almost 2/3rds than their forecast.
It is just one step closer to predicting the future of account acquisition. As a marketing individual if locations are held accountable for unattainable goals or goals that are set far below their expectations it was my position that someone needed to be aware of potential pit falls. If the goals were set far above expectations then it was my job to develop the programs to get them there.
I'll leave you with one more category that will always be good to review monthly. Utilizing the same categories, obtain the information for the total number of accounts at the end of the month. What you are hoping for is a trend upward of additional accounts, however today what you may see is the overall run-off as a whole. Finding those locations where the trend is a decrease in overall accounts will be one to review further. These are normally personnel issues, however a number of other factors could be found as to the decline. In either event it becomes a great coaching opportunity.
Next weeks post will focus on how to build a retention report and why having knowledge of branch retention is so important to the overall stability of an organization.
No comments:
Post a Comment