Explore what a Stock Analysis dashboard looks like in Power BI
The next Power BI report in this series addresses a common issue that users have when trying to determine what stock they have, how they have received and used it and what the valuations are.
Sage has a very strong stock module which is crammed with features. This includes locations/Bins, Traceability, valuation methods, Unit of Measure etc.
Sage is great at producing list based reports that can be dropped into Excel for analysis, but this is the perfect time to use Power BI to unlock the data and present it in a way that users can understand and quickly navigate. The sample dashboard provides an answer to three of the main questions that are commonly asked:
- What’s the ageing of my stock and aged valuation?
- What are my stock trends of inputs and outputs?
- What’s my current stock position and do I have enough to meet my demand?
So, let’s talk through this dashboard and you can give it a go for yourself!
If you have not used Power BI before, or wish to refresh your memory, check out the quick tutorial we have put together which demonstrates how to navigate through Power BI.
Stock Ageing
We quite often are asked to report on how old stock is and what is the value of stock over X years. This is particularly useful for sites who may have slow moving or obsolete stock.
We also have a number of larger organisations where the finance team want to apply an impairment percentage to the values of stock dependant on how many years old the stock is.
The first Stock Ageing tab of the dashboard answers the above questions.
It shows the aged value of stock by both product group and stock item. This currently shows the value but could easily be changed to show the quantity or both.
The user also has a handy free text search box to allow them to look up words in the code/description.
Stock Movement
This dashboard provides the user with a historical view of the movement of stock through their system.
It will aid the identification of slow moving stocks or allow the user to further enquire into the activity that has occurred on any stock record.
Users can identify a stock record or product group and see the net movement by month and year. They can then start to filter by goods in or out if they want to identify receipts or usage.
They can then focus on the transaction types that interest them. For example, “Show me units despatched against Sales Orders in 2024. Show me stock written off in 2025. Show me Goods received on PO’s since 2020”.
Knowing where stock is moving can provide a powerful insight to help make future decisions such as setting stock regarding Order levels. This is also shown in the bottom right corner to give the users even more data.
Stock Levels
The final tab on the dashboard presents the user with the current position of stock. This shows all of the stock items and product groups with the ability to filter.
We have also pulled information from both Sales Orders and Purchase Order modules to preset the user with a calculation of current projected stock. This shows if any stock items are planning to go negative based on all firm planned orders and stock levels. It also highlights any stock items that are below the reorder level (perhaps not an alert) as well as below minimum (potentially an issue)
While this isn’t a replacement to the fully functional Sage MRP modules, it can provide a handy visual indicator if any of the stock items are at risk.
Please note, the dashboard doesn’t include Works Order processing, but it could be extended to include demand for components and replenishments from Works Orders.
Now that we’ve gone through the Stock Analysis dashboard step-by-step, why not try our free interactive dashboard to have a go in navigating around this report yourself?
If you are interested in finding out more about how Power BI can assist you, our experts are here to help. Complete the enquiry form on this page or contact us directly on 01332 959008.
