Wednesday, December 01, 2010
There are multiple ways to assign accounting information to your transactions using Treasury Sciences. This is the information that typically flows through to your accounting system when you use our Cash Management Operations product for straight through processing. Each one of these methods is potentially a blog post in themselves but we thought we'd take some time and give you a quick overview of options that exist.
I. Accounting Information using Forecasts.
This by far is the most round about way to apply accounting information to your transactions. Having said that this is also the most convenient way if you are used to the idea of entering accounting information when you are creating EFTs or when you are forecasting the transactions that will show up first in your current day file and eventually in your prior day files.
The implementation is fairly straight forward. You go into the Treasury Sciences Administrator and define the fields that you want to pass on to your accounting system. While creating the fields you define the basic meta data associated with the fields. For example, the data type of the field, the validations associated with the field etc. Creation of these fields is also an interesting topic that we will cover in a separate post but for now, lets just say that the creation of these fields is a one time task that you can undertake using the user interface the product provides you with.
Once these fields are created for a subsidiary, every time that subsidiary makes a wire (or you make a wire on behalf of that subsidiary) you are presented with a form which allows you to enter values for these fields. The same form also shows up when you are making a forecast which you plan on reconciling with the BAI line items when the actual transactions are reported by the bank using the BAI file.
Now, assuming that you have entered the right accounting information for the forecasts and the forecasts get reconciled with the BAI line items (this could be either auto reconciliation or manual reconciliation done by a cash analyst) we have a means to getting to the accounting information you entered while creating the forecast, finding out which actual transaction it belongs to and exporting it out with your GL export which you can then feed into your GL system.
In cases where GL export is being created using prior day files we also provide reconciliation facilities (both automatic and manual) between current day and prior day BAI files. This allows us to get to the accounting information in the forecast which is reconciled with a current day transaction which is in turn reconciled with a prior day transaction.
All of this sounds complicated, but the bottom line is if you are creating the wires using EFT or entering the accounting information while doing forecast, the system pretty much does the auto reconciliation logic and figures out the accounting information that should move to your GL export automatically in most of the cases.
II. Accounting Information using Prior Day Files.
Approach I. works perfectly and accurately if you are into the habit of using our EFT product for wires or entering accounting information while forecasting. However, if you do not forecast extensively or are not in the habit of entering accounting information with each forecast we allow you to directly enter accounting information for each transaction that you see in your prior day file.
When the GL export runs on prior day file, all the accounting information that you entered here flows through to your GL export as soon as you run it or every time you schedule it.
This is fairly simple, but the downside of this approach is that you end up entering this information manually.
III. Automated Accounting Information Using Rules On Prior Day Files
This is the best of both the worlds where you do not rely on extensive forecasting by your subsidiaries and you also do not end up entering accounting information for each transaction which shows up in the prior day file. Here is how it works:
You start out by creating basic rules for automatically creating the accounting information. E.g. In the below rule I am stating that for any transaction where the amount is 36,000$ and the transaction belongs to an account which in turn belongs to Campus 2, I want two accounting information rows created. One will have the value "Portfolio1" and the other will have value "Portfolio2" in the accounting information field called PortfolioID which I had created using the administration console.
Note that here complex rules containing multiple other Transaction fields are also possible. The rules can be created using the following fields: [TransID], [ABA_Number], [AsOfDate], [ValueDate], [AcctNo], [TransCode], [Amount], [FundsType], [Description], [SameDayAvail], [OneDayAvail], [OverOneDayAvail], [CustomerReferenceNumber] [currency] [CompanyID] and even company name. Of course you can also club them up using and / or conditions.
Once the rules are defined and when the BAI files start coming in, the system automatically starts applying accounting information to your BAI files. Here is a sample BAI file which has an amount field containing 36,000$.
As soon as you import the BAI file (either manually or through an automatic feed that you have configured with the Bank) the accounting information rules engine kicks in and evaluates every single transaction in the previous day file with the rules that you have configured. If the transactions match the rules, the relevant accounting treatment is applied. You are of course free to modify the automatically applied accounting treatment or add new rows to it if you wish.
The point: when we started off the product we started off with finite number of fields and one single way to getting your straight through processing to work. The first major step that we took towards making our accounting information piece stronger was to allow end users to define the fields that they wanted to flow through to their accounting system, themselves using a simple user interface. Then we allowed manual entry of values for accounting information fields (that the users had defined) while creating forecasts, EFTs and then manually as the previous day BAI files were imported.
Adding a full blown rules based component which allows you to define rules which in turn allow automatic application of the accounting information was the next natural step.
It is worth mentioning that besides supporting all these three methods we also allow you to mix and match these options. For example if you are doing a few forecasts we can continue to pick accounting information for those transactions from the forecast, for a few others that match the rules that you might have created accounting information gets automatically applied (which you can manually override) and for some transactions where none of the approaches are applicable you can continue entering the accounting information for transaction which show up in the prior day file.
We are continuously building cleaner, smarter and faster ways to get you straight through processing and a full blown rules based component which you can configure yourself to get the accounting information automatically entered, is our latest addition on this front.
Of course, we aren't stopping here. We plan on adding significant enhancements to all the three methods we discussed in this blog post and are continuously looking at newer and better ways to give you and accurate, hassle free and fast straight through processing that is not just powerful but fun to use.
Wednesday, November 03, 2010
We recently did a post on the Treasury Sciences Cash Management Dashboard where we described it at a high level. In this post we thought we would dive a little deeper and look under the hood of how Treasury Sciences cash management dashboard is different and the subtle features that are working behind the scenes to give you a wonderful user experience when you are working with it.
If you are used to working with a basic bank activity report using excel sheets, chances are that you might have already drawn similarities between the excel file and this web based cash management dashboard. One of our major focus was to keep the dashboard very similar to something that you might be already used to or something that is rather easy to understand.
The Flow-Codes on the Y-Axis and the Banks on the X-Axis was a quick but powerful way of letting cash managers monitor how the money is flowing through the banks. Do a wire from Bank A to Bank B and you can literally see the balance on Bank A go down and the balance on Bank B go up in near real-time with the ability to drill down and find out exactly what happened. Depending on which flow-codes you have used, you will also be able to see the transaction's impact on the flow-code in the dashboard itself.
For example, in the out of the box configuration on CMO, you will also be able to see the WOD (Wire out Domestic) figure on Bank A go up and the WID (Wire In Domestic) figure on Bank B go up, telling you exactly what happened at a very high level. Of course you could always drill down into the transactions level by clicking the Transactions button and see the transaction itself.
Even though the dashboard comes fairly close to looking like a simple excel based daily activity report it is much more powerful when it comes to features and gives you a layer of visibility that would be rather difficult to get using Excel spreadsheets. Here are just some of those features that are working silently and subtly behind the scenes when you are using the simple looking cash management dashboard:
Each forecast that you see is bolded on the dashboard. But then it automatically "un-bolds" itself and turns into normal font when a new BAI file comes in and it auto reconciles with the forecast, if the forecast is manually reconciled or if the forecast is suppressed. The underlying idea here is that the "bolds" on the dashboard are warning flags or things that you need to give attention to before you submit a projection.
There are multiple reasons why you might want to leave an non reconciled forecast in your projection, but the bolds are primarily targeted towards making you aware of these so that the decision is a conscious one rather than an overlooking error.
Besides, there is a certain visual appeal in being able to see the forecasts dim out in real-time as incremental BAI files are automatically sent by the banks. We have seen our clients monitor the dashboard for hours and they love the "un-bolding" of the forecasts in real time as the BAI files are coming in. It tells them that everything is on track and moving along as planned.
Behind The Scenes Calculations:
The mere fetching of Opening Available Balance, which is often reported by the bank in a BAI files seems like a simple task for a Daily Cash Position, but Treasury Sciences allows you to forecast and project for the future (for as far a day to about a month) and what this means is that the dashboards needs be highly intelligent and aware of a lot of additional items that are at play here.
A Classic example is if you were to do a projection for day after tomorrow. Given this scenario, you obviously do not have the bank statements or BAI files for day after tomorrow. Getting to the opening available balance for day after tomorrow and showing it you accurately does not just means we start with the balance of today and consider two day floats for today, but also three day floats from yesterday and all other long term floats (both variable and date based availability) as reported by the banks in the near past. To add to that we also need to look at future EFTs that you have scheduled using our EFT system.
If Debt payments, other payments or standing instructions in your organization have an impact on the opening available balance for the near future we need to consider these as well which is where we have a capability for writing additional plug-ins for your organization which impacts the short term future opening available balance.
The Float Adjustment line item that you see in the last line of the list, also has multiple calculations associated with it. Similarly every time you make a concentration wire we remember it as a concentration wire, create double entries for it and give you an option of suppressing both sides if you are suppressing a concentration wire.
Long story short, Every line that you see in the cash management dashboard has a high level of intelligence associated with it so that you don't have to scribble numbers in a notepad and remember them while doing your projections. We take our ability to project accurately rather seriously and have tweaked the calculations and the ability to reach out to multiple sources for years now and are constantly on the process of making these calculations smarter and better than most other cash management systems out there.
Subtle Visual Elements, Usability And Other Features:
Ability to see commas in the amounts, ability for a bank to get automatically added to the worksheet every time it is added to the Treasury Sciences system and reporting or forecasts begin on the bank, ability to see alternating colors for the grid to help you avoid parallax errors, ability to drill down by clicking on any figure and seeing how it was formed. These are just some of the countless features which are so subtle that you hardly tend to notice them but they all contribute towards giving you an overall better user experience with the system. We have also spent time considering the most readable fonts and sizes so that cash managers who spend hours looking at the dashboard do not have to strain their eyes to look at figures.
The cash management dashboard, reflects the overall thought process of the application, which is, handle the complexities behind the scenes and keep things as simple as possible for the end user. Give him clear visibility, ease of use and functionality that is most relevant in that context.
Even though it sounds like a Cliché one the core philosophies, one that our development team takes rather seriously, when it comes to the end user experience is, Simplify what you present, even if it involves multiple steps and complex calculations in the background.
After all, Simple is smart. Simple is intelligent. Simple is a whole lot of fun.
Monday, October 25, 2010
Ever since we announced the going live of BAI to Excel service last year we have had a fairly high number of people try it out.
For programmers who wanted to access our service using their own code and utilities we decided to make the API's for this service available for free so that folks can build on top of the service. This time around too we received better responses than what we had expected. There were folks who wanted to use the service using technologies ranging from their own .NET code to the VBS scripts.
For business folks who like the service but do not like the idea of logging in every day we provided a smart client application that sits on your machine and lets you convert BAI files to excel by calling these services literally at the click of a button.
Our single point focus with the Labs initiative has been to bring a little bit of 'fun' into the treasury space and make a dent in the universe of treasury workstations by genuinely trying to help folks who are not yet ready for a full blown treasury management system. This is one of the primary reasons why all our lab initiatives are free. Some, like our SQL Encryption engine, are even open sourced.
Taking this philosophy of introducing openness in the Treasury workspace, we have gone ahead and decided to bring all our free offerings under one roof. We are calling it Treasury Applications and this suite of free applications can be access online using the URL: https://applications.treasurysciences.com.
If you have been using BAI to Excel, BAI to Excel API or BAI to Excel Windows Client, you can use the email with which you registered for BAI to Excel and the same password that you picked while registering for BAI to Excel for accessing Treasury Applications.
All of these products are Free. Clubbed with these products you can even chose to try out Flagship Treasury Management product for a month but that is totally optional and if you just want to activate other products without trying out the trial of our Flagship Cash Management Operations product you can totally do that.
During registration (or any time later) you have the freedom of activating and deactivating the application you do (or do not) want to use.
We will continue to do more posts on some of these products, for example we have a post which introduces you to our free Bank Fee Analysis product right here, but here is a bird's eye view of the free products treasury sciences brings to the table using a single free web based signup.
We personally feel that there is a lot of free goodness here for small businesses who are still working with Excel sheets. You can start with basic BAI to Excel services, use our IBAN validator, import transactions into the system using BAI files and then use them for Bank Fee Analysis, use the transaction manager to manage imported transactions and above all use our Cash Management Operations product to do projections, investment positioning, forecasting, reporting and a lot more.
We take our free products fairly seriously and have a support email just in case you need help with any of them. For support on any of these products you can reach us at: email@example.com.
Go ahead. Give it a shot. Do a free sign up at: https://applications.treasurysciences.com or you can directly visit the sign up page at: https://applications.treasurysciences.com/TSApplications/Common/Registration.aspx and sign up there. Go on. Sign up, try the products out and let us know what you think.
We will be more than happy to answer your questions and help with any comments, ideas and suggestions that you might have.
Thursday, August 27, 2009
In one of my earlier posts I announced the BAI to Excel Service for free. BAI to Excel web based application was our attempt to make a small dent in the universe of treasury workstations.
Put simply; BAI To Excel is a web application by Treasury Sciences which allows you to turn messy and complicated BAI files into simple excel worksheets that you can analyze. If you would like to know more about the service you should click here.
In another one of my post I also announced the fact that the API of this service are also now free for you to use. While announcing this service; I had brushed against the fact that the API should open up a whole lot of possibilities for folks who want to build custom applications around the BAI to Excel Service.
BAI To Excel Desktop Client; who pre-beta version we are releasing today; is our attempt at showcasing the kind of applications that can be built by consuming the web service API.
BAI to Excel Desktop Client is a cute looking application that sits quietly on your desktop and allows you to convert BAI files to excel files really quickly without having to go to the BAI to Excel website.
You can either click the Add button on the application or drag and drop multiple BAI files into the application:
Once the files have been added just press the 'covert' button and the program will convert each BAI file into a corresponding excel file.
Once done you can either save each excel file you want in a desired location by clicking the save icon next to each file or you can click the "Save All" button and pick a folder where you would like to save all the converted excel files.
You can use the same tool to add zip files which in turn contain the BAI files you want to convert.
Doing this allows you to choose if you want to have all converted BAI files merge into one single Excel file or you want a separate excel file for each BAI file in the zip.
You can pick this preference by click on the preference tab and picking the preference based on what you would like to have --- one single excel file for all BAI files or each BAI file having it's corresponding excel files.
Note: The BAI to Excel client is built using Windows Presentation Foundation and .NET Framework 3.5. If you are running Windows Vista or above the application should just run out of the box. If however you are on an older operating system you may have to download .NET Framework 3.5 on your machine before you can run the client. You can get the .NET Framework 3.5 by clicking here.
Since the BAI to Excel desktop application consumes the same web-service based API that we expose; it will require internet access each time you try to convert files using it.
Also note that in order for this client to function you will have to change your preference and allow web service access to your BAI to Excel account. If you would like to know how to change this preference and enable web service access on your BAI to Excel account you can click here to find out more details.
To access the application and start using it, you can begin by clicking here.
As always; we would love hearing your ideas, opinions and feedback. If you have any comments or suggestions feel free to email me at firstname.lastname@example.org.
Special thanks to Rohit Jain working on this Treasury Sciences Labs initiative; Amit Ghosh for giving it a beautiful user-interface and to Syed Khader for giving it QA cycles.
Thursday, May 14, 2009
We've been looking at the treasury space for more than a couple of years now and turns out, there is very little free goodness available out there. Most products seem fairly closed. Ranging from their features, to their screens, to their pricing - most companies out there seem fairly reluctant to discuss anything openly.
What is even more discouraging is that unless you buy a treasury workstation from a specific vendor, there is very little value that vendor can add to your life as a cash analyst or a treasurer.
In my previous post we announced the free BAI to Excel service. Making the BAI to Excel service free was our humble humble attempt to make a small dent in the universe of treasury workstations and add a little bit of value to your life as a treasurer even if you do not need a professional solution like CMO.
A few folks have signed up for the service and have been trying it out. We have been talking to a few of those Beta signups and have been learning about how they would like to use the BAI to Excel service.
Most organizations out there who are using this service because they want to quickly scan BAI files during the course of the day and see what the files contain. Obviously, it makes sense to see a corresponding excel version rather than a cryptic BAI file. For them, the BAI to Excel service through it's user interface works just fine.
If your organization falls in this category and this is how you are (or plan on) using the BAI To Excel Service, the rest of this post may not apply to you.
A few other organizations however, have decided to be a little more adventurous with the service and have plans of using it programmatically so that they can automate the translation from BAI to Excel by building their own custom scripts and tools around this service.
We have been getting requests to make the BAI to excel service available programmatically primarily from folks who have in-house development teams and want to build small scripts or custom tools around this service. Classic cases that we have seen so far include:
- Teams who want to write a script which automatically downloads the BAI files, converts them to excel, zips the excel files up and sends them to the cash analyst every morning without needing any user intervention. The befit of this of course is that the cash analyst gets a consolidated zip containing human readable excel files every morning.
- Teams who want to write scripts that get BAI files, convert them to excel and post these excel files on their banking services intranet websites. Benefit is having human readable excel reports on your intranet site instead of going to the bank site manually and trying to read cryptic BAI files.
- Teams who want to build small desktop based tools which access this service programmatically.
We have decided to listen to the folks who have been giving us feedback and have decided to open up the BAI to Excel API so that you can do all of the above with your own in-house development team.
As of today, we are exposing the BAI to Excel API as a public, secured web service that anyone who signs up for the free BAI to Excel account can consume using his custom code or scripts.
Going ahead, we will be working on building some sample tools around this web service to demonstrate how you can build your scripts and tools around this service. In this post however, let's just go ahead and talk about how you can activate this service so that you can consume it using your own code or scripts.
To activate the service for programmatic access you will need to go through the standard registration process, which is completely free. Once registered and logged in to the BAI to Excel web application; start by clicking on the web service access hyperlink available in the header.
Once in this screen you should be able to activate web service access to the BAI to Excel service by clicking the enable button.
Clicking on the enable button should generate a security key for you:
This should also give you access to view the web-service WSDL specification. You can use the "click here" link to see the web service WSDL which contains the specifications you will need to consume the web-service programmatically. The web service requires that you pass the security key each time you call it programmatically.
We use the security key along with your user name to identify the request as a request coming from a valid user. Security keys are as confidential as passwords and should not be shared and distributed.
With your username and security key you can consume the web service based API, send your BAI files to the web service and get an excel file back using your custom code or scripts.
We are hoping that this will open up a whole lot of possibilities for folks who do not need a full blown treasury workstation yet and are just trying to build small tools or custom applications around BAI files.
Going forward we will be building additional tools and services around the API and will be giving you examples of how you can consume the API and do custom development using the API.
All of these sample tools that we build on this service will be released free of charge.
The underlying service, as usual, is also going to be free as well.
More free goodness to be announced soon.
We love hearing from you. If you like our services, please do continue to use them and spread the word.
We would also love to hear how you are using our free services.
Your feedback gives us an opportunity to improve the services based on your feedback.
If you are facing problems with services, please do let us know.
We would love to help.
As always, Ideas, thoughts, suggestions are always welcome.
Feel free to reach out to me at email@example.com.
Thursday, April 23, 2009
If you are a treasurer or a banker you probably work with BAI 2 files. Unless you have well established business relationships with the banks where you have your accounts, chances are, that you are downloading your BAI files by logging in to your bank’s website and grabbing a copy on a daily basis.
How do you analyze these files?
Unless you are a super hero or serious treasury professional who remembers the specification and every single BAI code off the top of your head, you probably struggle with these files as you try to make sense out of them.
The BAI to Excel is a free service from the labs of Treasury Sciences. Put simply; if you are into banking, it is our humble attempt at making a small dent in your universe. All we are trying to do with this service is to try and make your struggle to understand BAI files a little easier. We do this by converting boring BAI files to easily readable and understandable; Excel file.
You can sign up for this service for free and you can login using your newly created account. From that point on the service is simple; Stupid simple.
All you do is --- you download the BAI files from your banks website, you upload them one at a time and for every BAI file that you upload, the system gives you a crisp, well formatted, easy to understand, human readable excel file back.
The parser behind this free service that converts your BAI files to Excel is the same parser that powers our flag ship product CMO and processes thousands of transactions from seven top US banks on any given day.
If you are a treasurer who is looking for an intelligent, web driven treasury workstation you might want to try out CMO.
On the other hand, If you are someone who just wants to parse BAI files and analyze them on a file by file basis you should take a look at the free BAI to excel service and consider using it.
You can try out the service here.
We would like to hear from you. If there are any comments you have after trying out the service feel free to send them to me at firstname.lastname@example.org or leave us a comment on this blog.
If you like the service and you know others who might want to use it, spread the word.
If you don’t like it we would really like to hear from you on areas where we can improve.
Send us your suggestion.
Just in case you do not have a BAI file handy but you still want to try out the service, you can get a sample BAI file here.
If you would like you see a sample copy of the output generated using the service before you signup you can get a sample Excel file here.
More treasury goodness, fun tools, useful utilities and useful features to be announced soon.
Copyright © Rajiv Popat