Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > Partner Program >

Automated Bank Rules Engine

Started by Jordan Zoot -   in Partner Program

Executive Summary
We have successfully built out and deployed a practice level automated Bank Rules engine, that aggregates data, allocates to entities, codes to accounts, and pushes the data out to individual Xero engines where the data comes through pre-coded and transactions don’t have be approved individually through the reconciliation or in groups with the cash coder…..we jokingly call it the cash coding firehose. It processed 1.3 million transactions in ninety minutes this morning, and populated 74 new Xero entities. We have the data set for another 400+ but are waiting to see what happens -going to stop that thought here.-

Our conclusion is “WOW!!!!” four months ago when we started on the platform I would have bet against this working given our nightmare with 40K transactions choking the cash coder. We aren't interested in the blather about “beautiful accounting software”. Our needs are very unique, and a small business owner would not reach the point we are at. We have had three mid-level people working on this for three months. It is still a beta, so we aren’t sharing yet at all. The beautiful part about this is that it pushes the data out, but not into the entities. We upload the final import file one at a time. We deliberately chose the message because that many API calls in a short time window would probably make some Xero folks rather unhappy.

Background

Our primary banking platform is JP Morgan Chase Access which is their Commercial Banking distinguished from Consumer or Business Bank in cash management platform. The platform permits:

Funds transfer initiation -FTI-: Initiate and track wire transfers across the country and around the world.
Automated Clearing House -ACH- initiation: Create, store and send payment and collection information quickly and securely.

Liquidity initiation and reporting: Increase your return on excess balances with convenient short-term investments, including money market funds.

We uses debit cards off of our Operating Account for every expense that can possibly be paid with plastic -use is 87% debit cards vs. 13% credit cards.-

We maintain a Client Funds Account with sub accounts for every one of our Fund/Private Equity Clients at the Fund, mezzanine Consolidator, and Family Office levels. There are a number of additional Client Funds accounts for Tier 1 clients.

As part of our engagement letter process, each of the clients in this group is required to maintain a balance in the accounts which we bill monthly by invoice out of xero.com so we get reimbursed immediately from the Client Funds Accounts rather than having to invoice and chase.

The target balance every six months to maintain at 125% of the highest charge out over the past six months.

We have additional Client Funds Accounts -or clients have accounts at- both of which Middle Market platforms-
Citibank - CitiBusiness
Bank of America - CashPro

Our corporate credit cards managed through Expensify.com. We have a full enterprise implementation with Domain Control, and full corporate spending cards. The cards we use are:
American Express Green Corporate
Capital One Corporate Plus Card

Vendors where we allocate individual expense items on a pass-thru -no mark -up basis -we download transaction level activity in either .QBO or .CSV files- for
Shipping - FedEx, UPS and DHL
Office Expenses - amazon.com, staples.com, apple.com -iTunes and Apps-
Local Transportation - uber. com and a black car service in three cities.
Meals, Entertainment and Travel - from the Debit Cards
Research tools -allocatd based upon our yearly library cost-, others like Lexis, Westlaw, and Google Cloud Platform projects at cost.

-We do NOT charges out for what we refer to as normal overhead such telecommunications -fax, copier, telephone or internet- absent an extraordinary situation.-

Additional information as part of the year-end summary gathered from the client folder. The client folder includes:

Full substantive emails from GMail stripped of attachments, sensitive personal and financial email redacted, attachments from emails, copies of online chats from Skype, Google Hangout, etc which are client related, Notes from Salesforce and Desk.com cases from whatever interface they came in through.

All document to Adobe PDF and digitally signed to establish non-repudiation
.
Each document is Bates Numbered indexed.

We went paperless in 1998, and as of 12/31/2015 will have fully scanned every client file and practice related file since that date.

The files on a Box.com platform,fully integrated with our salesforce.com Service Cloud. We are currently testing dropbox for Business as a potential replacement or back-up for Box.com. We hope to test Google Drive in the same functions after the busy season.

We went through our 10.5 month year-end close-out this weekend where we:
Aggregated all of the source information -financial data that will get push through to client entities to update general ledgers, all of the emails, documents, etc. from varied sources to the client file -or - for a related entity-

The back-up detail for items that from vendors -e.g. FedEx, Uber rides- gets compiles with our monthly invoices provided to clients as part of year-end package-. This is the point that a Manager level review of the file to make sure we aren’t missing information or something needs to get captured in the year-end billing, and notes go to the top of files for year-end tax planning meetings with clients
We also put a list together of clients for review for continuation in the practice.

The next step is the tough one. Due to prior problems with the Xero Cash Coding with high transaction volume, and being on banking platforms that have two factor authentication, we don’t use the Yodlee bank feeds.

What have done, using the Jitterbit Data Loader is:
Deploy a standardized Chart of Accounts for all clients, with uniform account numbers and coding nomenclature.

Deploy a workflow such that individual transactions get coded to specific accounts by in the aggregate, and then pushed out to the individual Xero entities pre-coded such that we have essentially deployed “Bank Rules” at the client top entity level -while we attempt to use common vendors, each client is a bit different-

The goal being that one we “push the button” the bank data is pushed out to individual entities and once imported, the entity should be locked in for 10.5 months.

We then start a year-end review to make sure everything landed in the right places, true up with 1.5 months work the second week of January.

The transaction volume is approximately 60 transaction/month*12 months*1400 entities or roughly a million transactions. There are eight additional large entities that add roughly approximately 300,000 more transactions.

I pushed the button at 8AM this morning and around 9:45AM came back from breakfast and poof…...IT SEEMS TO HAVE ALL WORKED. Bottom line we have a “Bank Rules Engine” that handles substantially high volume through a workflow.

-Before anyone asks, NO we are not sharing it at least until May 2016. We still consider it in beta and are NOT willing to support anyone else using it-. If it does work, we are going to start thinking about what we might do. We would be interested in knowing if there is any interest in an app of this type. It was expensive > $50K in develop in payroll costs.