Global Spend Analysis Challenge: Solution - Part1 : Find Hidden Tail in Procurement Spend

TL;DR

This video tutorial demonstrates how to solve various spend analysis challenges using Excel, such as identifying cost-saving opportunities, hidden tail transactions, suspicious transactions, and poor sourcing decisions.

Transcript

hello everybody my name is gaurav it has been a month since we announced our first global spend analysis challenged we receive entries from all over the world in this session we are going to solve the problem statement that we announced using just the excel so let's get right into it this was the procurement data that we shared with all the candida... Read More

Key Insights

• 📛 Analyzing data quality is crucial in spend analysis to ensure accurate results and identify potential issues with duplicate suppliers or product names.
• 🍸 Hidden tail transactions represent a significant challenge in spend analysis, as they indicate purchases made outside of negotiated contracts.
• 🇦🇪 Monitoring unit prices and identifying discrepancies between contracted and non-contracted rates can highlight the impact of hidden tail transactions on costs.
• 🌸 Recovering losses from suppliers involved in hidden tail transactions is a recommended course of action in spend analysis.
• 🎟️ Poor sourcing decisions can result in increased costs, missed savings opportunities, and complications in supplier management.
• ☠️ Regularly refreshing contractual rates and addressing contract compliance issues can help mitigate the risk of poor sourcing decisions.
• 💾 Spend analysis provides valuable insights into cost-saving opportunities and helps optimize procurement strategies.

Questions & Answers

Q: How can you analyze the quality of the procurement data using Excel?

To analyze data quality in Excel, you can create a pivot table to check for duplicate suppliers and product names. By using the distinct count feature, you can identify if there are any duplicate entries. Additionally, arranging the data in ascending or descending order can help identify similar-sounding suppliers.

Q: What is hidden tail in spend analysis?

Hidden tail refers to transactions that are part of pre-negotiated contracts but have been placed outside of them. It is a problem when purchases are made outside of the agreed-upon contracts, resulting in potential cost increases and missed savings opportunities.

Q: How can you identify hidden tail transactions using Excel?

In Excel, you can create a pivot table to analyze the split between contract and non-contract purchases. By filtering the data for transactions where the contract is 'yes' and the purchase order exists but the contract is 'no,' you can identify hidden tail transactions. This analysis allows you to quantify the extent of the problem and take appropriate corrective actions.

Q: What are the potential causes of poor sourcing decisions?

Poor sourcing decisions can occur due to various reasons, such as overlooking pre-negotiated contracts, buyers placing orders outside of contracts, lack of supplier compliance with contracts, or insufficient volume commitments. These decisions can lead to increased costs, missed savings opportunities, and complications in supplier management.

Key Insights:

• Analyzing data quality is crucial in spend analysis to ensure accurate results and identify potential issues with duplicate suppliers or product names.
• Hidden tail transactions represent a significant challenge in spend analysis, as they indicate purchases made outside of negotiated contracts.
• Monitoring unit prices and identifying discrepancies between contracted and non-contracted rates can highlight the impact of hidden tail transactions on costs.
• Recovering losses from suppliers involved in hidden tail transactions is a recommended course of action in spend analysis.
• Poor sourcing decisions can result in increased costs, missed savings opportunities, and complications in supplier management.
• Regularly refreshing contractual rates and addressing contract compliance issues can help mitigate the risk of poor sourcing decisions.
• Spend analysis provides valuable insights into cost-saving opportunities and helps optimize procurement strategies.
• Excel is a useful tool for conducting spend analysis, offering functionalities such as pivot tables, data organization, and visualization to facilitate data analysis and decision-making.

Summary & Key Takeaways

• The video tutorial focuses on solving four problem statements in spend analysis: identifying cost-saving opportunities and amounts, discovering hidden tail transactions, flagging suspicious transactions, and identifying cases of poor sourcing decisions.

• The presenter emphasizes the importance of analyzing the quality of the data and demonstrates how to check for duplicate suppliers and product names using pivot tables in Excel.

• The hidden tale problem, which refers to transactions placed outside of pre-negotiated contracts, is explored in detail, and specific examples are provided to illustrate the impact on procurement spend.