We recently had a request about being able to forecast how the cash will enter and leave the company accounts. So for each cash account (company has several bank accounts and also accept money in cash from customers) the company want’s to know (in a date in the future):
The current amount in cash
The amount to be received
The amount to be paid
The expected amount in the date ( current + received - paid)
All this information is quite valuabe to be able to move the cash amount between banks and avoid running out of cash on some bank accounts.
Another requirement is to be able to plan the movement between some bank accounts and see this amount updated on the forecast.
Our idea was to add a Many2One on move lines of receivable, payable and cash accounts to determine on which cash account the amount will be transfered to. This will allow to create a view that computes all the previous created amounts based on a query on the lines table. It should be also possible to manually create draft moves with to plan the movement between bank accounts.
We considered a cashflow module a couple of years ago and created prototype you can find in here.
Planned moves registration
We create a model (account.cashflow.move) to register the planned incoming and outgoing moves.
Those moves have the following fields:
Company
Issue Date
Planned Date (the one used to compute the cashflow report)
Description
Bank Account
Amount
Party
Account
Origin
Managed (boolean)
The idea of the Managed flag is that the user can create those moves manually (Managed=False) or we can create modules that automatically create cashflow moves (Managed=True).
For example, a confirmed sale could create a managed cashflow move that plans for the future payment of its invoice.
Also a managed cashflow line is created for each account move line with a maturity date.
Reporting
A model named account.cashflow.line.forecast is computed with a table query similar to account.general_ledger.line.
The cumulated balance field shown in those records, should include the value of the bank account as of today.
Also, a context model is used which allows the user to decide if the cumulated balance is computed grouping by bank account or not. So the company can have an idea of the total cashflow or in a per-bank basis. Useful for planning cash moves between bank accounts.
First, I think the name cash flow is not right or ambiguous. Depending of the country, it may mean cash flow statement which is very different from your proposal. I think a better name is treasury.
The first point is that we need a way to find the account that represent cash. They are on the balance statement and not fixed asset but we may need a new Boolean.
I have been thinking lately about this topic and it is not easy to come with a nice, smart and useful design.
The main problem for me, is that we want to compute the balance of cash accounts with the addition of the payable and receivable lines that are not yet reconciled.
The second problem is that we do not know which cash account will be used to reconcile a receivable account (a little more for payable).
I think it at first we should work with global sum. It is the main point of the treasury management, known if the company has enough cash no matter where it is (known for each account is an optimization).
I also think we should avoid creating lines if we have the data somewhere else like in the receivable/payable accounts or on draft document like sale or purchase. It will be better to work with UNION queries and so avoid synchronization and batch jobs. And if necessary we can create a Model to plan cash moves that have no draft document (but this could be for an extension module).
For the detail on which cash account a receivable/payable line should be put, we could have a field on the line to store it as information. It can be empty and/or field by other modules that knows. And the query for the draft documents should provide a value if it has one.
An important point is to use the balance of the cash account from the computed field to be sure to use the fiscal year deferral instead of summing all the old lines.
For the display, I think it should be shown as a timeline with the cummulated debit, credit (bar) and balance (line). In the context, the user may choose a specific cash account and to use draft or posted lines.
Maybe the solution for that comes from the ideas described in Customizable defaults on payment terms and Comercial conditions as long as we accept to introduce into purchases and sales a “desirable/pre-agreed…” payment method between stakeholders that, of course, could be overwritten later to another payment method if necessary.
I think it’s worth mentioning that some applications such as Microsoft Dynamics don’t use one account per bank account but register it somewhere else similar to what we do with account + party field. I’m not saying that we should have a account + bank field because I think we would have too many fields in the interface for account move lines. But I think it’s worth understanding that some companies can have lots of bank accounts.
I have to admit, though, I’ve thought in some cases that it would make sense to have an account + resource concept were resource would be a Reference field. I’m not happy with the interface that would bring us to, just mentioning in case somebody builds a better idea out of that.
That’s why we add the bank account to invoices and later to receivable/payables.
I can see how a query could bring information from receivalbe/payable accounts but not from other documents such as sales or purchases where the computation is not going to be simple at all because we need to have some rule to make a guess on when the invoice is going to be posted and later compute the payment terms (which currently is a complete “rule system” in itself).
Also, I don’t see it as a big issue having to compute it because most probably that’s something that a company is going to check once a day.
I’ve just think on this issue and I do not think using the bank account is the right aproach but we need a higher level that the user can create the named records. Some companies may not be interested on knowing the exact bank account but just the bank where the money resides or other information to know the liquidity of the money. So I think we should create a new model named account.treasury.source so the user can create the wanted structure
On the other hand, I think we need to add a Many2One to the Treasury Source to invoices/moves that allows the user to plan on which type the money will be added. I think the Many2One should be also added to account.payment.journal so each time a payment related to a move line, we now that such amount will be assigned to the Treasury source of the related journal.
For moves lines without a Tresaure source we may add a view that allows the user to search them and assign to a single one (using a wizard). This will also allow to replan lines to pay on the future if needed.