ERP Manufacturing Reporting & Analysis: Top Analytics to Track in Power BI & Tableau
Microsoft Power BI and Tableau are data visualization tools. They are both used by manufacturing businesses to track and report the performance of their procurement, inventory and production processes. Businesses often use ERP software such as Microsoft Dynamics, Sage and SAP to run these manufacturing processes.
ERP software is a primary source of data for a business that wants to analyze manufacturing performance with Power BI and Tableau. But ERPs are sophisticated pieces of software and the data they produce is often complex and not structured in a way that makes it easy to analyze.
The data needs to be restructured, or transformed, into a structure for analytics and reporting. Data management automation software, or ELT data warehouse automation software, like ZAP Data Hub, can be used to access data from ERP software and structure it for manufacturing analytics and reporting in Tableau and Power BI.
(You might also be curious to read "Do Tableau and Power BI replace the need for a Data Warehouse?")
Once ERP data is structured for reporting, a business needs to decide which manufacturing analytics, calculations and KPIs to track. This should reflect the priorities of the business and can be a challenge to start.
That’s why we’ve produced our ERP Manufacturing Reporting and Analysis Top Analytics to Track. It’s designed to provide a starting point for businesses looking to kick-start their manufacturing reporting.
(We also recommend checking out "Sales & Marketing Reporting: Top Analytics to Track in Power BI & Tableau")
Top Procurement Analytics to track in Tableau and Power BI
Spend analytics:
- Total spent: total value of purchase orders raised in the current year
- Quantity purchased: total quantity on invoices processed in the current year based on purchase unit
- Current backorder: amount and quantity on backorder
- Received not invoiced: value of purchase order lines received where no invoice has been processed
- Top-ten item spending: top-ten inventory items by invoice value for current year
- Top-five procurement category: top-five procurement categories by invoice value for the current year
- Open purchase value: value of purchase order lines by current line status for the current year
- Open purchase value: quantity on purchase order lines by current line status for the current year based on purchase unit of measure
Cost efficiency analytics:
- Purchase price variance percent (actual purchase order invoice value/invoice value using the standard cost price): average variance between unit price on invoice and standard cost in the ERP
- Procurement cost per currency unit spent (invoice value based on inventory value/actual purchase order invoice value): amount above one indicates the amount of mark-up costs on inventory items. The amount below one indicates rebates or write down on items
- Procurement cost per invoice (invoice value based on inventory value/actual purchase order invoice value): indication of the number of mark-ups or rebates processed against an invoice
Delivery performance analytics:
- On-time delivery: percentage of purchase orders delivered within the expected delivery date
- Full delivery: percentage of purchase orders delivered in full on first delivery
- Perfect order index (POI): delivery precision delivery fulfilment
Quality analytics:
- Returned quantity: current year returned quantity in purchase units
- Returned amount: current year returned amount
- Return percentage of total: value of returned items compared the total value of orders for current year
Supplier Pareto analytics — vendor with 80% of total spend:
- Lists the vendors and their associated spend value that make up 80% of the total procurement spend
Spending over time – current year vs. previous year analytics:
- Quantity in stocking units moved this month
- Quantity movement in stocking unit by direction over the last twelve months
Top 10 supply chain vendor statistics:
- Total spent for the current year
- Number of invoices processed for the current year
- Quantity purchased in purchase unit for current year
- Amount currently on backorder
- Quantity currently on backorder
- Calculated price variance for the vendor where a standard cost price exists for the items been procured
- Delivery performance is the perfect order index (POI) for the vendor the current year
- Return quantity for vendor for current year
- Return amount for vendor for current year
- Procurement cost per currency unit spent
- Procurement cost per invoice
Top Inventory Analytics to track in Tableau and Power BI
Current months’ inventory statistics analytics to track:
- Average inventory days of supply: inventory valuation/average daily cost of sales
- Inventory to sales ratio: inventory valuation/sales amount
- Days in inventory: average inventory value/average daily cost of sales amount
- Opening balance of inventory for the selected month
- Backorder rate for the selected month: amount on backorder/ total sales order amount entered for the month
- Order fill rate: the percentage of order lines fulfilled in one delivery
- Inventory turn: cost of goods sold/average inventory comparison selected month vs. prior month
- Inventory valuation: comparison selected month vs. prior month
Inventory quantity by issue and receipt status analytics:
- Number of items sent from warehouse by their current issue status
- Number of items received into the warehouse by their current receipt status
Inventory-to-sales ratio over time analytics:
- Average inventory value over selected twelve months
- Net valuation change from selected period to prior period
- Inventory valuation comparison over last twelve months
- Sales and inventory to sales ratio comparison over last twelve months
Item group valuation analytics:
- Top-five item groups by stocking unit quantity movement split by direction.
Quantity movement-by-origin analytics:
- Top-five transaction types by stocking unit quantity movement split by direction
- Internal transfer volume vs. external transfer volume
Inventory quantity movement trend analytics:
- Quantity in stocking units moved this month
- Quantity movement in stocking unit by direction over last twelve months
- Inventory aging analysis report: inventory movement over time
- Item analysis report: quantity on hand compared to last sale quantity and date, also last purchase quantity and date
- Slow moving inventory analysis: inventory not sold or consumed
Top Production Analytics to track in Tableau and Power BI
Order status analytics:
- Number of open production orders for current month (not ended or reported as finished)
- Number of completed production orders for the current month (marked as ended or reported as finished)
Order fulfilment analytics:
- On-time delivery: percentage of production orders completed within expected delivery date
- Quality: percentage good quantity compared to total number of produced items
- Order fulfilment: on-time percentage multiplied by the quality percentage
- Late production order analytics: number of late production orders by aging bucket
Efficiency analytics:
- Average planned lead time based on the planned time to complete operations divided by number of production orders
- Average actual lead time based on actual time to complete operations divided by number of production orders
- Efficiency percentage: ratio of actual to planned cycle times
Production cost analytics:
- Actual cost of all production orders for current month
- Planned cost or estimated cost of the production orders for the current month
- Actual percentage of planned cost (actual cost/planned cost)
Quantity produced vs. quantity remaining analytics:
- Top-ten items by production volume, produced quantity vs. remaining quantity
- Number of early production orders
- Number of production orders that have been completed before the expected date
Actual percentage of planned cost analytics:
- Actual percentage of planned (actual cost/planned cost) for the current month, prior month, year to date and prior year to date:
Actual cost by resource group analytics:
- Actual production order cost by resource group dissected by resource type
Production order current status over time analytics:
- Count of production orders over the current year by status
Order fulfilment over time analytics:
- Count of production orders over the current year. Data labels show the count of ended production orders
- Line graph the order fulfilment percentage, on-time percentage, and quality percentage
Actual vs. planned consumption by resource group analytics:
- Resource group id and description
- Planned cost amount for current month
- Actual cost amount for current month
- Variance (actual cost minus planned cost)
- Variance percentage (variance/actual cost)
Getting the Most from Your Manufacturing Data Analytics
If you are a manufacturing business using ERP software and would like to report in Power BI and Tableau, then ELT Data Warehouse automation software like ZAP Data Hub makes it easier to produce meaningful analytics from this data.
ZAP Data Hub automates the processes of accessing and structuring data for analytics and reporting in Tableau, Power BI, Qlik and other BI tools. It is specifically optimized for the Dynamics 365 suite providing:
ZAP is a Sage Rockstar ISV and ZAP Data Hub is optimized for key Sage products as:
ZAP Data Hub is also optimized for other major platforms:
About the Author:
|
Trevor Legg
Trevor is a 20+ year seasoned professional who is passionate about delivering solutions that are based on analytics, data management, Business Intelligence, strategic planning, and coaching for individuals or organizations looking to optimize their performance.
View my social profiles:
LinkedIn |
|
Any feedback or questions? Leave a comment below...