Context

The customer is a FMCG company selling around the UK. They had historical data in Sage 50 and then migrated to Sage 200 which is what they currently use. One key challenge was to combine the historic data with the data from the new version of Sage.

They needed automated financial dashboards to monitor the sales, costs, profit margins, credits and other financial metrics. The client needed to break down these metrics were by product, customer, supplier and product category. 

Approach

The historic data from Sage 50 was extracted into an Excel spreadsheet and brought into the model as a static file. As for Sage 200, there is an underlying SQL database for every Sage 200 instance. Vidi Corp connected Power BI to this underlying Sage 200 database and automated the data refresh this way. The data from the 2 systems was brought to a common format and combined inside of Power BI.

The numbers inside of the Power BI dashboard were matched to Sage 200 to ensure data accuracy. This ensured minimum discrepancies and elevated the customer trust towards the Power BI dashboard. 

Dashboard

The key metrics in the dashboard are number of orders, sales, average order value and gross profit. Those metrics were compared to the previous period to give them more context.

Sales by product supplier were also analysed to help manage supplier relationships.

Top 10 and bottom 10 products were also analysed to drive decisions about which products should be discontinued or marketed more. 

The geographical split of customers was analysed to measure local market penetration. 

The filters below allowed the user to select the exact metrics to display in the bottom tables. The users also could select whether to break down the metrics by supplier, product group or product. 

Do you also have a project where you need to analyse financial data or data from Sage? If so, contact us now to discuss the nuances of this kind of projects!