The role of data in procurement has become increasingly important in recent years, as companies seek to optimize their supply chain and reduce costs. This white paper/ article explores the process of cleansing purchase spend data to consolidate it into a business intelligence report using Power BI and Azure. The goal is to identify opportunities for savings by comparing costs to cheaper products on national frameworks and NHS supply chain products. We will also examine the benefits of consolidating spend and reducing long tail spend to optimize procurement processes.
Procurement has evolved significantly in recent years, with data-driven decision-making becoming a cornerstone of effective supply chain management. As organizations strive to reduce costs, increase efficiency, and maintain competitiveness, data analysis has emerged as an essential tool for identifying savings opportunities and improving procurement processes.
This article aims to provide a comprehensive guide to cleansing purchase spend data for consolidation into a business intelligence report using Power BI and Azure. The primary objectives are to identify cost-saving opportunities by comparing spend data to national frameworks and NHS supply chain products, and to discuss the benefits of consolidating spend and reducing long tail spend.
Data Collection and Extraction
To begin, it is crucial to collect and extract purchase spend data from various sources such as ERP systems, invoices, purchase orders, and other relevant documents. This raw data must be standardized and organized for further analysis.
Data collection and extraction are crucial initial steps in the process of cleansing purchase spend data. This involves gathering relevant data from various sources within an organization and converting it into a structured format for further analysis.
The following sub-sections detail the different aspects of data collection and extraction:
- Identifying Data Sources
Before collecting data, it is important to identify the relevant sources of purchase spend information within the organization. Common data sources include:
- Enterprise Resource Planning (ERP) systems
- e-Procurement platforms
- Accounts payable systems
- Purchase orders and invoices
- Supplier contracts
- Delivery notes and receipts
- Credit notes and returns.
- Defining Data Requirements
Next, define the data elements required for analysis. This may include:
- Supplier information (name, address, contact details, etc.)
- Product or service details (description, category, SKU, etc.)
- Purchase order information (order number, date, delivery date, etc.)
- Invoice details (invoice number, date, payment terms, etc.)
- Pricing and cost information (unit price, total cost, discounts, taxes, etc.)
- Contract terms (start and end dates, renewal options, etc.)
- Data Extraction Techniques
Once data sources and requirements are identified, various extraction techniques can be employed to retrieve the necessary data. Some common techniques include:
- Manual data extraction: Involves manually collecting data from source documents or systems. This method can be time-consuming and prone to errors but may be necessary for small-scale projects or when dealing with unstructured data.
- Automated data extraction: Uses software tools, such as web scraping or APIs, to automatically collect data from various sources. This method is faster and more accurate than manual extraction but may require technical expertise to implement and maintain.
- Direct integration: Involves connecting directly to the source system (e.g., ERP or e-Procurement platform) through APIs or other integration methods. This approach provides real-time access to data but may require collaboration with IT departments and system administrators.
- Data Formatting
Once the data is extracted, it must be converted into a structured format suitable for analysis. This may involve:
- Converting data from unstructured formats (e.g., PDFs, images, or emails) to structured formats (e.g., CSV, Excel, or XML)
- Standardizing date and time formats, currency symbols, and units of measure
- Organizing data into a consistent and logical structure, such as a database or spreadsheet
By carefully collecting and extracting purchase spend data, organizations can ensure that they have a solid foundation on which to build their data cleansing and analysis efforts.
Data Validation and Transformation
Once the data is collected, it must be validated to ensure accuracy and completeness. This process involves removing duplicates, correcting data entry errors, and filling in missing data. Data transformation may also be required to convert data into a suitable format for analysis.
Data validation and transformation are essential processes to ensure that the collected and extracted data is accurate, complete, and suitable for analysis. These processes help identify and correct any errors, inconsistencies, or missing data that may have occurred during the data collection and extraction phase.
- Data Validation
Data validation involves checking the quality and accuracy of the extracted data. Common validation steps include:
- Removing duplicates: Identifying and eliminating duplicate records, which can lead to inflated spend figures or inaccurate insights.
- Identifying data entry errors: Detecting and correcting data entry errors, such as typos, transposed numbers, or incorrect units of measure.
- Checking for missing data: Identifying and filling in any gaps in the data, either by manually locating the missing information or by using estimation techniques when the exact values cannot be found.
- Verifying data accuracy: Ensuring that the extracted data accurately reflects the original source documents and systems by cross-referencing and spot-checking.
- Data Transformation
Data transformation involves converting the validated data into a format suitable for analysis and integration with other data sources. Common data transformation steps include:
- Standardizing data: Converting data elements into a consistent format, such as ensuring that all dates follow the same format (e.g., YYYY-MM-DD) or that all currency values are represented in the same unit (e.g., USD).
- Categorizing data: Grouping data elements into meaningful categories, such as classifying products or services into specific categories or assigning suppliers to industry sectors.
- Normalizing data: Scaling numerical data to a standard range, which can help with data comparison and aggregation. For example, normalizing price data to a per-unit basis to enable comparisons across different products or suppliers.
- Calculating derived values: Creating new data elements based on existing data, such as calculating the total spend for each supplier or the average price per product category.
- Data Enrichment
In some cases, it may be necessary to enrich the data with additional information to support more detailed analysis or to fill in gaps in the original data set. Data enrichment can involve:
- Adding external data: Incorporating data from external sources, such as market benchmarks, industry reports, or third-party databases, to provide additional context or insights.
- Geocoding supplier data: Adding geographical information (e.g., latitude and longitude) to supplier records, enabling geographical analysis and visualization.
- Generating supplier scores: Calculating supplier performance scores based on criteria such as price competitiveness, delivery performance, and quality metrics, to support supplier evaluation and selection processes.
By thoroughly validating and transforming the purchase spend data, organizations can ensure that their data is accurate, complete, and ready for further analysis and integration with other data sources. This lays the groundwork for more reliable and actionable insights that can drive procurement optimization and cost savings.
Data Integration and Standardization
After validation, data from different sources must be integrated and standardized to enable meaningful comparisons and aggregations. This involves mapping data to a common set of categories, units of measure, and currency conversions.
Data Modelling and Visualization
With cleansed data in hand, Power BI and Azure can be used to model the data and create visualizations to better understand procurement trends, patterns, and anomalies.
Data modelling and visualization are crucial steps in transforming raw, cleansed purchase spend data into meaningful and actionable insights. By utilizing Power BI and Azure, organizations can create interactive dashboards and reports that enable data-driven decision-making in procurement processes.
- Data Modelling
Data modelling involves structuring and organizing data in a way that facilitates analysis and visualization. In Power BI, this can be achieved through the following steps:
- Importing data: Load the cleansed and transformed purchase spend data into Power BI from various sources, such as CSV files, Excel spreadsheets, or Azure databases.
- Creating relationships: Establish relationships between different data tables or sources to enable cross-referencing and data aggregation. For example, linking product and supplier data based on a common identifier, such as the supplier ID.
- Defining measures and calculations: Use Power BI’s Data Analysis Expressions (DAX) language to define custom calculations, aggregations, and measures that are relevant to the procurement analysis. Examples include total spend per supplier, average order value, or spend by product category.
- Designing a data model: Organize the data tables, relationships, and measures into a logical and intuitive data model that supports efficient data exploration and analysis.
- Data Visualization
Once the data model is created, organizations can use Power BI’s extensive visualization capabilities to create interactive charts, graphs, and reports that effectively communicate the insights derived from the purchase spend data. Key steps in the data visualization process include:
- Selecting the right visualizations: Choose the most appropriate visualization types based on the data and the intended audience. Common visualization options include bar charts, pie charts, line charts, treemaps, and geographical maps.
- Customizing visualizations: Customize the appearance of the visualizations by adjusting colours, fonts, labels, and other formatting options to ensure a consistent and professional look and feel.
- Adding interactivity: Incorporate interactive elements, such as filters, slicers, or drill-down functionality, that allow users to explore the data in more depth and focus on specific areas of interest.
- Designing dashboards and reports: Arrange the visualizations on a canvas to create cohesive and informative dashboards or reports that effectively communicate the key insights and findings from the purchase spend analysis.
- Best Practices for Data Visualization
When designing data visualizations, it is important to follow best practices to ensure that the insights are clear, accurate, and easily understood by the intended audience. Some key best practices include:
- Keep it simple: Avoid overly complex or cluttered visualizations that can be difficult to interpret. Focus on simplicity and clarity to ensure that the insights are easily understood.
- Use a consistent colour scheme: Use a consistent colour scheme throughout the dashboard or report to create a unified and visually appealing design.
- Focus on the most important insights: Highlight the key insights or findings that are most relevant to the procurement analysis and ensure that they are prominently displayed in the dashboard or report.
- Provide context: Include relevant context, such as historical trends or industry benchmarks, to help users understand the significance of the insights and how they relate to the organization’s procurement objectives.
By effectively modelling and visualizing the purchase spend data using Power BI and Azure, organizations can create powerful business intelligence reports that enable data-driven decision-making and support procurement optimization and cost savings.
Dashboard and Report Creation
Power BI allows for the creation of interactive dashboards and reports, providing real-time insights into procurement performance. These reports can be customized and shared with relevant stakeholders for decision-making and analysis.
Real-time Data Access and Collaboration
By leveraging Azure cloud services, organizations can benefit from real-time data access and collaboration, ensuring that procurement decisions are made based on the most up-to-date information. This also facilitates communication and cooperation among various teams and departments involved in procurement processes.
Identifying Cost Savings Opportunities
Benchmarking Against National Frameworks
By comparing purchase spend data to national frameworks, organizations can identify opportunities for savings by switching to cheaper products or suppliers. National frameworks often offer competitive prices due to their scale and collective bargaining power.
Leveraging the NHS Supply Chain
The NHS Supply Chain provides access to a wide range of products and services at negotiated prices. By utilizing this supply chain, organizations can achieve cost savings and gain access to a reliable source of products and services.
Supplier Performance and Risk Assessment
Analysing purchase spend data can reveal insights into supplier performance and potential risks, enabling organizations to make informed decisions about supplier selection and management. This can lead to improved supplier relationships, better product quality, and reduced risk exposure.
There are already existing free tools to NHS Organisations to view their spend data.
The largest and complete data set in the NHS is the Spend Comparison Service tool.
The NHS Spend Comparison Service (SCS) was commissioned by NHS England and is provided in collaboration with NHS Digital on behalf of providers. It provides users with price benchmarking and spend analysis of procurement data for all NHS trusts and foundation trusts within NHS England.
All NHS trusts (acute, specialists, mental health, community and ambulance) are required to upload their purchase order and accounts payable data to NHS Digital Strategic Data Collections Service (SDCS). Purchase Order data is collected on a weekly basis and Account Payable data is collected monthly.
The raw trust data is then integrated and aggregated by NHS Digital and NHS England, and this database then forms the foundation of the different visualisations and analysis found within the SCS analytics dashboards.
The product enables users to view the underlying data within a number of different formats. This allows for different methods of analysis, including:
benchmarking prices paid for goods and services
identifying alternative products per spend
clinical product category
This analysis may assist with achieving better value, as well as identifying inflation, possible sources of alternative stock, supplier spend and financial sustainability insight, spend by category and commonality, and insight into and trends within supply markets.
Also included within the service is the GIRFT clinical classification that allows providers to analyse their procurement data by medical and surgical specialty, procedure, and product classification.
If trusts require a more bespoke spend analytics solution there are still free services available such as those provided by NHS Procurement Hubs like NOECPC who can create bespoke analytics or help in your data transformation for the SCS requirement.
Benefits of Spend Consolidation and Long Tail Spend Reduction
Streamlined Procurement Processes
Consolidating spend and reducing long tail spend can help organizations streamline procurement processes, leading to increased efficiency and reduced administrative costs. By centralizing procurement activities, organizations can better manage supplier relationships and negotiate better terms.
Enhanced Negotiation Power
With consolidated spend, organizations can leverage their increased buying power to negotiate better pricing and terms with suppliers. This can lead to significant cost savings and improved supplier relationships.
Improved Compliance and Risk Management
Spend consolidation and long tail spend reduction can also help organizations improve compliance with procurement policies and regulations, as well as manage risks associated with supply chain disruptions and supplier performance issues.
Leveraging Advanced Analytics and Emerging Technologies
Predictive Analytics and Forecasting
Incorporating predictive analytics into procurement processes can help organizations forecast demand, identify potential supply chain disruptions, and optimize inventory management. This can lead to cost savings and a more efficient supply chain.
Artificial Intelligence and Machine Learning
AI and machine learning can enhance procurement processes by automating routine tasks, identifying patterns in data, and providing recommendations for cost savings and supplier selection. This can result in reduced manual workload, improved decision-making, and increased cost-saving opportunities.
Robotic Process Automation (RPA)
RPA can be employed to automate repetitive procurement tasks, such as data entry, invoice processing, and order management. This can lead to increased efficiency, reduced errors, and cost savings.
Internet of Things (IoT) and Blockchain
IoT devices can provide real-time data on inventory levels, product usage, and supplier performance, enabling organizations to make more informed procurement decisions. Blockchain technology can improve transparency and traceability in the supply chain, enhancing trust and reducing the risk of fraud.
Blockchain is a distributed ledger technology that enables secure, transparent, and tamper-proof transactions between multiple parties. In procurement, blockchain can be leveraged in the following ways:
- Supply chain transparency and traceability: Blockchain can be used to create a secure and immutable record of each transaction and data exchange within the supply chain, providing end-to-end visibility and traceability. This can help organizations verify the authenticity of products, ensure compliance with regulations, and identify potential risks or issues within the supply chain.
- Smart contracts: Blockchain-based smart contracts can automate procurement processes by automatically executing contract terms when predefined conditions are met. This can help reduce manual workload, improve efficiency, and minimize the risk of errors or disputes.
- Secure data sharing: Blockchain can enable secure and tamper-proof data sharing between organizations and their suppliers, partners, or customers. This can improve collaboration, streamline communication, and reduce the risk of data breaches or fraud.
- Supplier financing and payment: Blockchain-based payment systems can facilitate faster, more secure, and transparent payments between organizations and their suppliers, reducing transaction costs and improving cash flow management.
By embracing IoT and blockchain technologies, organizations can enhance their procurement capabilities and drive greater cost savings, efficiency, and transparency across the supply chain. These technologies, when combined with data-driven procurement practices, can enable organizations to unlock the full potential of their purchase spend data and deliver significant competitive advantages.
Building a Data-Driven Procurement Culture
Employee Training and Skill Development
Developing a data-driven procurement culture within an organization requires not only the implementation of appropriate tools and technologies but also a focus on employee training and skill development. Ensuring that procurement professionals are equipped with the necessary skills and knowledge to analyse, interpret, and act upon the data insights is crucial for the successful adoption of data-driven procurement practices.
- Identifying Skill Gaps
The first step in employee training and skill development is identifying the skill gaps within the procurement team. This involves assessing the current capabilities of team members and determining any areas where additional training or development is needed. Key skills to consider include:
- Data analysis and interpretation: The ability to analyse and interpret purchase spend data to identify trends, patterns, and insights.
- Technical proficiency: Familiarity with the tools and technologies used in data-driven procurement, such as Power BI, Azure, or other business intelligence platforms.
- Critical thinking and problem-solving: The capacity to apply data insights to real-world procurement challenges and develop effective solutions.
- Communication and collaboration: The ability to effectively communicate data insights and collaborate with stakeholders across the organization.
- Training Programs and Resources
Once the skill gaps have been identified, organizations can develop and implement targeted training programs and resources to address these gaps. Some options for training and skill development include:
- In-house training sessions: Conduct regular workshops, seminars, or training sessions led by internal experts or external trainers to help employees develop the required skills and knowledge.
- Online courses and certifications: Encourage employees to enrol in relevant online courses or certification programs offered by universities, professional organizations, or software vendors.
- Conferences and industry events: Support employee attendance at conferences, workshops, or other industry events where they can learn about the latest trends, best practices, and tools in data-driven procurement.
- Peer-to-peer learning: Facilitate knowledge sharing and learning among team members through regular meetings, presentations, or mentoring programs.
- Monitoring Progress and Measuring Success
It is essential to monitor the progress of employee training and skill development initiatives and measure their success in driving improvements in procurement performance. Some potential performance indicators to track include:
- Increase in data-driven decision-making: Monitor the extent to which procurement decisions are being informed by data insights and analysis.
- Cost savings and efficiency improvements: Measure the impact of data-driven procurement practices on cost savings, process efficiency, and other key performance indicators.
- Employee engagement and satisfaction: Assess the impact of training and skill development initiatives on employee engagement, job satisfaction, and retention rates.
- Adoption of new tools and technologies: Track the uptake and usage of new data-driven procurement tools and technologies within the organization.
By investing in employee training and skill development, organizations can ensure that their procurement teams are well-equipped to leverage data-driven insights and drive improvements in procurement performance. This, in turn, can contribute to cost savings, efficiency gains, and a more strategic and value-driven approach to procurement.
Collaboration and Cross-Functional Teams
Promoting collaboration among procurement teams, data analysts, and other departments can help organizations fully leverage the potential of data-driven procurement. Cross-functional teams can work together to identify cost-saving opportunities, optimize processes, and drive innovation.
Continuous Improvement and Adaptation
A successful data-driven procurement culture requires continuous improvement and adaptation. Organizations must be willing to learn from their data, adjust processes as needed, and adapt to new technologies and best practices.
Key Takeaways
In conclusion, data-driven procurement practices hold significant potential for driving cost savings, efficiency improvements, and strategic decision-making within organizations. By leveraging data cleansing, business intelligence tools like Power BI and Azure, and emerging technologies such as IoT and blockchain, organizations can transform their procurement processes and unlock the full potential of their purchase spend data. Some key takeaways from this white paper include:
- Importance of data cleansing: Ensuring the accuracy and completeness of purchase spend data is critical for effective analysis and decision-making. Data collection, extraction, validation, and transformation are essential steps in the data cleansing process.
- Power BI and Azure for business intelligence: Power BI and Azure provide powerful tools for modelling and visualizing purchase spend data, enabling organizations to create interactive dashboards and reports that drive data-driven decision-making in procurement.
- Leveraging IoT and blockchain: Emerging technologies like IoT and blockchain can enhance procurement processes by improving real-time visibility, traceability, and transparency across the supply chain. These technologies can complement and augment data-driven procurement practices.
- Consolidating spend and reducing long-tail spend: Data-driven procurement practices can help organizations identify opportunities to consolidate spend, negotiate better pricing, and reduce long-tail spend, leading to cost savings and improved procurement efficiency.
- Building a data-driven procurement culture: Ensuring the successful adoption of data-driven procurement practices requires a focus on employee training and skill development. Organizations must invest in building the necessary skills and knowledge within their procurement teams to analyse, interpret, and act upon data insights.
- Continuous improvement and performance monitoring: To fully realize the benefits of data-driven procurement, organizations should monitor and measure the impact of their initiatives on procurement performance, and continually refine and optimize their processes based on data insights.
By embracing these key takeaways and implementing data-driven procurement practices, organizations can unlock the full potential of their purchase spend data and achieve significant competitive advantages in today’s increasingly complex and competitive business environment.
– Author Chris Whitlock & GPT-4
