Project: Dashboard for Low-Value Purchases
Objective:
To monitor and analyze low-value procurement activities (below 8 UITs) to identify irregularities or deviations in purchasing processes and support internal control efforts.
Context:
Electro Sur Este S.A.A. (ELSE) is an electricity distribution company operating in southern Peru, covering the regions of Cusco, Apurímac, and Madre de Dios. As part of its internal audit and control activities, the company reviews purchase records to ensure procurement practices are aligned with corporate policies. The focus of this analysis was on detecting potential issues such as duplicate purchases or vendors providing similar services, which are not allowed under procurement regulations.
Tools Used: Microsoft Excel
Process Overview:
Data Extraction:
Procurement data was exported from the SAP ERP system in Excel format.
Data Cleaning & Standardization:
Corrected inconsistencies in vendor names.
Aggregated purchase amounts by Purchase Order (PO), as individual orders were often split across cost centers, leading to data discrepancies.
Data Analysis:
Reviewed purchasing trends by area, department, and service type.
Compared current-year data against previous years.
Dashboard & Report Design:
Created a comprehensive Excel report with visuals, including a scatter plot highlighting anomalies.
Developed a Top 10 suppliers view based on purchase frequency to assist in control analysis.
Key Insights & Recommendations:
The scatter plot revealed vendors with a significantly higher number of purchase orders and associated amounts compared to the average.
Certain departments submitted purchase requests at a frequency well above average. These should be further reviewed for justification.
A noticeable trend showed low purchase activity in the first months of the year, with a spike toward year-end. Further investigation is needed to understand this seasonal pattern.
Additional data granularity is required to evaluate whether certain purchases are being duplicated.
Suggested improvements to procurement procedures for purchases under 8 UITs to prevent policy deviations.
The dashboard can be used as a monitoring tool to track vendors or departments with abnormal procurement patterns.
Visualizations Included:
Scatter plot showing relationship between number of purchase orders and total purchase amounts per vendor.
Bar charts by department, service type, and year.
Summary tables showing top vendors and top-requesting departments.
Dataset:
Due to confidentiality and corporate ownership, data and dashboards cannot be publicly shared. The project was conducted internally for Electro Sur Este S.A.A., and all rights are retained by the company.