I am a blogger on Power BI and your accounting data and the creator of Accounting Insights for Sage 50 which is the only Microsoft Power BI Content Pack available for Sage 50. Please feel free to try it out with your own Sage 50 system on the web or with Power BI Desktop.
I am a director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high-performance Fireball dinghy racing and food.
I thought that I would share a quick preview of the kind of thing that we are working on with Power BI to analyse and forecast cash flow. It is work in progress, but I think that it shows that if you are building this up from a strong dataset of fine-grained transaction lines you can achieve some very useful results. https://www.useloom.com/share/9d39f3a6e00e4af2b4d0ec499cc97d88
I can help you - depending on how you want to read the files. My service provides a connector that reads the company files and presents them to you as an OData feed. It pulls pretty much all of the Sage data and works from v23 and up. The use-case envisaged is to pull the data into Power BI for reporting. Feel free to contact me on [email protected]
Hi Phil, Something that I have been experimenting with is using Power BI to do just that. The beauty of this approach is that you can take into account all the transaction line detail over the last while. I typically take the last 800 days' data to give me a full two years so that I can try to take into account seasonality. The important thing with any forecasting model is to understand how it is working and therefore does it make sense to your own particular situation. It is difficult, for example, to produce a forecast related to a particular supplier if you have only been dealing with that supplier for a couple of months.
I have experimented with this approach for forecasting product demand, sales by customer, and cash flow by customer & supplier as well as larger groupings. The approach I took seems to work well if you are doing a lot of repeat business with the same partners (even if it is up and down a lot). It becomes possible not only to produce a forecast, but a range of expected values. I know, however, that this approach won't work well in other situations.
So....to answer your question "Is there a panecea?", I don't think so. Are there a different approaches that you could have on the shelf that you can apply in different situations? Yes.