Project Description

View PDF Version

Overview

Our client is a leading welding supplier and hire specialist in Western Australia.

The objective of the project was to develop an Excel-based solution that centralises and standardises supplier pricing. This initiative enabled automated updates to a Master Price List, leveraging supplier pricing and predetermined markup rules. The goal was to streamline the process of updating costs, markup codes, and sell rates in the client’s accounting system, ensuring the product pricing remains competitive and margin positive. Additionally, we aimed to develop a standardised markup code system tailored to product and supplier types, facilitating the accurate calculation of sell rates across different markup categories.

Business Challenge

Our client faced significant challenges in managing multiple price lists from diverse global suppliers, each presented in varying formats. The manual process of extracting, transforming, and integrating data into the Master Price List and ultimately to their accounting system was prone to errors and inefficiencies, lacking any form of automation.

With over 20,000 products listed, tracking and correcting errors in Excel became increasingly cumbersome. Furthermore, the critical knowledge of supplier price list management, including the application of specific rules and data transformation requirements, was concentrated in a single individual. Coupled with time constraints, this situation often led to delays in updating the price list, impacting operational efficiency and pricing accuracy.

How Access Analytic Helped

Access Analytic addressed these challenges through a combination of strategic solutions, innovative approaches, and expert services, demonstrating our value and expertise in the project. Utilising Excel’s Power Query engine, we automated the importation and transformation of individual supplier price lists into a unified standard template. This process was meticulously documented to ensure clarity and ease of use for both current and future stakeholders.

By standardising and consolidating the price lists, we facilitated a seamless comparison with the current pricing in the client’s accounting system. Our solution also introduced the capability to adjust exchange rates for international products and incorporate freight costs into the product’s final cost.

A flexible markup system was developed, allowing the client to easily modify the markup rate for each product based on supplier type and product category. This innovation has significantly expedited the process of setting sell rates, promoting standardisation and efficiency.

Furthermore, we implemented a system of checks and balances, incorporating data validation and audit functionalities. This adds valuable insights into price variances, importation discrepancies, and instances of missing pricing, enhancing the overall integrity and reliability of the pricing strategy.

Business Benefits

The solution delivered by Access Analytic yields substantial benefits, including:

  • Automation: Significantly reduces manual data handling, minimising the risk of errors.
  • Centralisation: Establishes a consolidated source of truth for all pricing data, enhancing accessibility and management.
  • Standardisation: Guarantees uniformity and precision in supplier pricing data across the board.
  • Efficiency: Optimises the pricing update workflow, conserving valuable time and resources, and ensuring operational agility.

Through these improvements, our client is now better positioned to maintain competitive pricing and achieve positive margins, while also enhancing operational efficiency and data integrity.

Client Testimonial

“We had a positive experience with Access Analytic. Their professionalism and ease of communication made the process straightforward, and we’re satisfied with the results.”