Intelligent Data Mining for Financial Insights

To be sure, all ERP systems and certainly Microsoft Dynamics 365 Finance has several reports which comes out of the box. These reports are for the most part sufficient to show the usual KPI reports by module. To make sense of the data and drill into the root cause though, would take several reports from Sales, AR, Supply chain, cash flow and stitch them together to get the whole scenario: which takes the valuable resource; time. And it would be difficult to do it in real-time

Meron Taffese

Meron Taffese

How to Handle Cash Flow & Accounts Receivable in Microsoft Dynamics 365

Automate, Automate, automate…GREAT, all routine transactions which used to be manual are automated. Workflow; approval process all in sync; AR statements, Credit limit alerts, supply chain process… flowing. Microsoft ERP and Dynamics 365 Finance has done wonders; especially during the COVID-19 crisis.  

Dashboard shows all KPI info; Revenue, AR, AP, Top ten customers, Daily Cash etc. …and yet the cashflow is off. Now why is that? The approval que shows another credit limit increase request, as well as a large P.O request. Digging into the various reports shows a spike in orders from one of the Top 10 clients…great! They have been paying a bit later than usual although the overall aging is in order. P.O approval for top value item needed – another spike. Yet Sales is growing…  Inventory volume largely steady.

So Why Is Cash Flow Off?

The above AR and cashflow scenario is an example where a CFO can have challenge in connecting the dots and finding the root cause of the problem.

To be sure, all ERP systems and certainly Microsoft Dynamics 365 Finance and 365 Supply Chain Management have several reports which comes out of the box. These reports are for the most part sufficient to show the usual KPI reports by module. To make sense of the above challenge and drill into the root cause though, would take several reports from Sales, AR, Supply chain, cash flow & stitch them together to get the whole scenario: which takes the valuable resource; time.  And it would be difficult to do it in real-time.

Here is where Intelligent data mining for Microsoft ERP and Dynamics 365 Finance comes in.  Designing an intelligent report with Power BI can do wonders to solve challenges such as the above example and lead to action fast.  

What Is Microsoft Power BI?

Power BI is a tool where you can design meaningful reports using the obvious and not so obvious data and logic scheme in the system for different KPIs.  Using Power BI you can access data from different parts of the business such as Procurement, Item margins; Customer Receivables, arrange your data in a concise meaningful way to visualize the state of the business in real-time.

The reports also go a long way to show where a loophole within a business process may exist; insights to changes in a business trajectory to allow for impactful & swift decision. 

The Art of Making Better Cashflow Decisions Using Power BI Reports

Power BI works with model driven apps in Common Data Services such as Dynamics 365 Finance, 365 Sales and or Dynamics 365 Customer service. Integration with these systems is easy and provides powerful way for personnel to visualize and work with the data. 

Data can be personalized using charts and graphs and reflected in real-time. Most importantly the designed reports can be on the personnel’s individual dashboard and be enabled for mobile devices as well.  

An example of a Power BI Dashboard

Power BI can be designed based on individual KPI’s, the role of the employee, as well as the department and/or the overall business objectives, to get the desired information.  It’s relatively easy to design using tiles and graphs to tell a story on one screen in real-time.

Because of Power BI’s adaptability, you can design the reports using different set of data from all modules and business areas to create what the user is looking for in a report.

 

So, what about the Cash Flow mystery and Microsoft Dynamics 365 Finance data? Here is how Power BI helped:

Design of the report using Power BI: The CFO in the above example designed a Power BI report using a set of metrics to help keep track of the daily cashflow among other reports:

  • Margin by Items for Top Ten Items
  • Sales Orders
  • Purchase Requests for Inventory by Item
  • Receivable for Top Ten Customers by item
  • Daily Invoiced Items.
  • Daily Inventory Receipts and Inventory Amounts
  • Payments

Using these data points, a report was designed which includes calculations such as real-time Cash Flow; Cash Flow Projection as well as other relevant reports with all the key information in one spot. These reports once designed were saved on the CFO’s dashboard and are regularly refreshed. 

It turns out…

  • The sudden spike in order is related to one product only: the high demand high value item.
  • The customer was slower than usual in paying the invoices of sales of that item, while everything else is paid on time – Net 30 days.
  • Large amount cash is tied up in inventory that made up that item since it is high value item and in high demand and as result of delay in the customer payment. Because of the high demand, the company is forced to a large outlay of cash to Purchase the raw material Item. Hence the large P.O approval.
  • The order size for the product has gradually grown within the last two years.

Based on this insight, the CFO was able to discuss the situation with the Sales Team and Purchasing Department personnel to make the most prudent decisions.

  • The Sales Department was able to negotiate new Terms on Payment for the sale of the item only by offering discount instead of Increasing their Credit Limit. The Terms on the other product sales would remain the same, optimizing the overall DSO

This outcome and solution was amicable to both parties: the invoice was paid in a shorter time frame freeing up cash for the company and the client benefited by receiving a discount for early payment, creating a Win-Win solution.

In conclusion, Power BI enables the user to design effective reports coming from different data sources in order to gain insight to ALL areas of the organization; making it easier for effective decisions in a relatively short period of time. Microsoft Dynamics 365 and Power BI are user friendly and easy to navigate, enabling the user to design intelligent reports based on the objectives & metrics of the company.

Interested in Learning More?

Discover how D365 and Power BI automation can change how your business operates

Explore More Content:

Share Post

let's have a chat?

Book a free meeting and let us have a look at your opportunities with Dynamics 365

Team working on presentation

Request a free Dynamics 365 demo

Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.