Cash Flow Forecast Report for a group of 3 companies

Hello,

I use Xero on a group of 3 legal entities. We have multiple BUs which use all three legal entities. All three legal entities use the same chart of account.

I want to generate a report to forecast the next 90 days of planned cash flows using the data of unpaid invoices/bills (amount + due date).

I want to use Datadear to get the balance of the accounts I want to monitor (for ex: Sales for BU1 and Costs for BU1), then using the invoices/bills which are awaiting payments and get the sum of all planned cash movements for each of the following 90 days.

And, I want to consolidate these information per legal entity and also per BU.

What is the recommended method to arrange such an excel sheet using Datadear?
Do I need to use one sheet per data table request per Xero company?
Is there a way to make the update of such a document “one click”?
Can I set a date in an excel sheet and use this date in the Datadear request (for ex: get all transactions after date in cell A1?)

Thanks in advance

Hey @catkfr - Welcome to the community!

Sounds like a superb solution you’re looking to build. All very achievable, I’d recommend;

  • 1 sheet per company data feed (invoice/credit note lines data table)
  • 1 sheet to pull down the groups Chart of Accounts data table (accounts)
  • Use additional sheets for other feeds as required (Report data etc.)

You’ll be able to use the Refresh all Sheets button to automatically refresh each data table ( 2 clicks, sorry!)

Yes, you can dynamically link your data requests to cells within Excel, please see;
How can I use dynamic values from a cell or formula in the parameters window?

And finally, should you need a hand achieving your desired build, please let me know or check out our DataDear Experts that could definitely deliver this solution;

Thanks!
Carl :bar_chart::chart_with_upwards_trend:

1 Like

Thanks for the tips Carl.

One thing that is not yet clear to me: what happens when an excel file that was generated with the datadear plugin goes to someone who doesn’t have the plugin (say he has a mac). He stills edits the excel file. When you go back to the first computer, will it still work?

I did not find a way to download the balance of an account. I can get the chart of account using the accounts table, but not the balance.
How could I do this?

Hey @catkfr,

Check out the Trial Balance, Profit & Loss or Balance Sheet reports to get to the balances you need :slight_smile:

It will depend on what is edited within the file, as long as the data feed remains untouched and only additional calculations are added to the right of the table, when you get this back into your windows environment this should still be connected and ready to refresh.

Watch this space as our Excel Online add-in is due for release very very soon!

Thanks,
Carl

Thanks again.

Will try again in those reports. Did not find what I needed the first time I checked, but I’ll double check.

Good to hear for Excel online. Any plans to also support google sheet by any chance? :slight_smile:

Hi @catkfr,

It’s certainly on our radar, but not in the short term. Of course you can transfer your data to your preferred spreadsheet tool of choice, once we’ve automated it into Excel :slight_smile:

Thanks,
Carl

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.