Cash Flow Forecast

[jjscarafia]

Anyone working or planning a cash flow forecast using mis_builder?

If I'm no wrong it would be great to allow account.move.line analysis via de date_maturity field. It could be solved by mis_bilder queries by I think it would be better (and easier) to allow using date_maturity directly with some parameter on kpis.

@sbidoul any thoughts to share?

BR!

[sbidoul]

Interesting idea. Could you elaborate a little bit on the kind of report you have in mind?

[gonzaloruzafa]

Hi @sbidoul! The main goal would be to give visibility to forecasted funds flowing in or out of the company. Using the date_maturity of the account move lines (usually Payable or Receivable Accounts), you could project when that account is expected to become liquid cash (going in or out). With that info (you can use any account), you can project your future financial situation and take decisions.

Example: - Today: 07/05/2018

Current Bank Balance: 500k

• Payables move lines
  101200 Account Payable - 100k - Date maturity 15/05/2018
  101200 Account Payable - 50k - Date maturity 21/05/2018

• Receivable move lines
  101200 Account Receivable - 20k - Date maturity 15/05/2018
  101200 Account Receivable - 800k - Date maturity 30/05/2018

• Draft account.move.line for hipotetical in/out flow
  xxxxxx Other expected in - 10k - Date maturity 15/05/2018
  yyyyyy Other expected Out - 5k - Date maturity 30/05/2018

[jjscarafia]

@sbidoul exactly, the idea is more less the one explained by @gonzaloruzafa
Just to add a new boolean "use_date_maturity" kpis so that move lines are grouped by that field instead of date field.

Perhaps another great addition would be to think on some model (similar to budget) to add some forecast in/out flows. But this could also be achieved using a draft account.move with many account.move.lines representing that forecast in/out flows

[sbidoul]

Thanks for the example.

At first glance, adding "use date maturity" on kpis does not fit very well with the current model and algorithms.

What you can do today, without changing mis_builder is create a view model such as

SELECT
  date_maturity AS date
  company_id,
  account_id,
  debit, 
  credit
FROM account_move_line
WHERE ... account type in payable, receivable ...

And then use the Actuals (alternative) data source when creating the forecast columns.

There is an example in mis_builder_demo.

[jjscarafia]

Thanks @sbidoul. That's a great possibility.
But if I'm not wrong making this method aware of date vs date_maturity would be enaught. Doen't it?

If so, what do you suggest or think is better:

• PR introducing use date_maturity on mis-builder (the one I most like)
• PR with new module that inherits mis-builder and allows to use date_maturity
• No PR
• PR with new module for forecast where we create an sql view as you suggest

BR!
Juan

[sbidoul]

Having some forecasting feature, in an extension module or in mis_builder core, would be definitely great. And PR are always welcome, that's for sure.

I think it will not be enough to change the method you suggest, though. If you follow that path you will see that you will need to select date or date_maturity at the column level, not kpi as you initially proposed. And I'm not sure how that would work for your use case. Selecting date/date_maturity at the kpi level is a much more profound change. And that too would not necessarily solve your use case.

So my intuition so far, is that it's better that you experiment with a new SQL view. Depending on the conclusions of that experiment we can then see what's the best approach.

At first glance, that view should be a union of

• all move lines (by their original date) for liquidity accounts
• all move lines (by date_maturity) for payable/receivable accounts (probably keeping only unreconciled move lines)

Then the report template would have kpis such as (pseudocode)

• cash = bale[account_id.type = liquidity]
• in = bale[account_id.type = receivable]
• out = bale[account_id.type = payable]

Then in the report:

• the first column with source = account move, end date = today.
• then a column for each future period with source = the new view and end dates in the future

But don't take my word for it, I might very well miss something :)

[JordiBForgeFlow]

In my opinion it may make more sense to use the capabilities of mis builder budget.

When you construct a cash flow forecast you need to consider both projected revenue AND projected expenses. Both projections could be based on actual data, or just on forecasted data provided by the sales/purchasing team.

In the scenario that the forecasted data is purely invented, MIS Builder Budget is completely capable to allow a user to enter the projected revenue/expenses in the Budget Items:

Then you can run a report that compares the actual cash flow vs. projected (budgeted).

Assuming that you can achieve that, perhaps it would make sense then to define a process that imports/updates the budget items from the actual projected revenue, considering the due date. I am thinking of extending the Budget Item with some field 'Extract from actual data'. Then a cron job evaluates the expression of the KPI (e.g. balp[220000]), then considers the date_maturity in addition.

And that would only apply to some of the items. Those that are to be extracted from actual data, which are likely going to be just a small subset of the overall cash flow projection.

[sbidoul]

If it's easier to forecast on the KPI level, then the budget approach is good. If it's easier to forecast at the account level, creating a new datasource is easier. Depends on the use case.

[elicoidal]

I am reviving an old thread but it is quite common in company reporting to have 3 measures (usually for the current month or week):

• actual data
• forecast data
• budget data
• any KPI comparing these series

Given that, IMO it would make sense to think about a module mis_builder_forecast introducing the forecast data series similar to budget, to ease data entry and add to corporate reports.
This entry could easily be overriden by custom method for the companies that have clear calculation on their forecast (eg.: expenses forecast is their draft or confirmed - but not validated - vendor bills and forecast income is their proforma invoices).
cc @sbidoul @jbeficent

[sbidoul]

@elicoidal in my understanding, a manual forecast is no different than a budget. You can create several budgets for the same period and chose the one you use in each column of the report.

When there is a clear manner to obtain forecast from other erp data, then creating a SQL view and using it in a report column with data source "actuals (alternative)" is the way to go.

Other than that, so far I don't have a reasonable understanding of what a generic solution would be, as the possibilities for forecasting seem endless.

[elicoidal]

@sbidoul Agree with you: I didnot see the possibility to create a budget series called "Forecast" which is IMO good enough too.
Thanks for the reply!

[jjscarafia]

Hi, we've just upload this module for the cash flow https://github.com/ingadhoc/enterprise-extensions/tree/11.0/mis_builder_cash_flow. We are currently testing it and looking to propose it to this repository if you think it is interesting.

[pedrobaeza]

Is it still depending on enterprise?

[sbidoul]

@jjscarafia this is a great module, I remember the demo at OCA Days 2018!

This is very interesting to have in the community, if you can make it use the OCA grid widget.

I'll create a new repo mis-builder-contrib for this because it is a module that will follow a distinct maintenance cycle. Indeed we are maintaing mis-builder as a small repo with all modules migrated at once and it would not be sustainable to do it for all modules that depend on mis-builder.

[fclementic2c]

Hi, we've just upload this module for the cash flow https://github.com/ingadhoc/enterprise-extensions/tree/11.0/mis_builder_cash_flow. We are currently testing it and looking to propose it to this repository if you think it is interesting.

@jjscarafia I would like to test it please but I miss the dependency account_ux

[jjscarafia]

Hi @fclementic2c . I've made a new PR without custom dependencies and some improvements here

I close the issue for now, we can continue on the PR.
Thanks!