skip to content
Logo FLORIAN ZEBA
Cover Image

Retrospective Price Analysis of an Automotive Parts Manufacturer

/ 4 min read

Updated:
data analysis, automotive, financial analysis, price strategy

Retrospective Price Analysis of an Automotive Parts Manufacturer

1. Title Page

Title: Price Increase Analysis for an Automotive Parts Manufacturer
Company: Confidential Automotive Parts Manufacturer
Prepared by: Ing. Florian Zeba LL.B.
Date: 01.05.2023

2. Executive Summary

This case study outlines the analysis of price increases for an automotive parts manufacturer. The project involved calculating cumulative price adjustments agreed upon with customers, including adjustments based on the consumer price index (CPI). The analysis aimed to provide the buyer of a subsidiary with a clear understanding of the realized price increases. Despite challenges in data extraction and identification of retroactive price adjustments, the project delivered a comprehensive calculation of revenue i…

3. Introduction

Background: The client is an automotive parts manufacturer supplying major automobile brands. The company agreed to price increases with customers, some of which were applied retroactively. A precise analysis of these increases was required before the sale of a subsidiary.
Industry Context: The automotive supply industry faces intense competition, cost pressures, and frequent contract renegotiations.
Objectives:

  • To calculate the total amount of retroactively applied price increases.
  • To identify discrepancies between agreed and invoiced price adjustments.
  • To provide a reliable financial figure for the upcoming sale of the subsidiary.

4. Problem Statement

The client lacked clarity on how much of the agreed price increases had been invoiced to customers. The ERP system did not tag transactions with retroactive adjustments, making it difficult to isolate and quantify these amounts. Additionally, the analysis needed to account for the impact of the consumer price index (CPI) on price adjustments.

5. Analysis of the Situation

SWOT Analysis:

  • Strengths:

    • Established ERP system (SAP) with historical transaction records
    • Access to detailed sales, accounting, and contract data
    • Experienced accounting and sales teams for consultation

  • Weaknesses:

    • ERP system lacked markers for retroactive adjustments
    • Disorganized data across different modules (sales, accounting, and contracts)
    • Limited documentation of previous manual adjustments

  • Opportunities:

    • Improve financial forecasting through accurate price adjustment tracking
    • Increase transparency for future contract negotiations
    • Strengthen the company’s valuation for the sale of the subsidiary

  • Threats:

    • Risk of financial disputes with the buyer if inaccuracies are discovered
    • Data loss or corruption during extraction from the ERP system
    • Changes in CPI causing unexpected variations in margin calculations

Industry Analysis (PESTEL):

  • Political: Trade tariffs and regulatory changes affecting automotive supply chains
  • Economic: Inflationary pressures and rising raw material costs
  • Social: Increasing demand for cost-efficient and sustainable automotive components
  • Technological: Advancements in ERP and data analytics for financial tracking
  • Environmental: Pressures to reduce waste and optimize production efficiency
  • Legal: Compliance with international accounting standards and contractual obligations

6. Proposed Solutions and Alternatives

Solution 1 (Selected): Data-Driven Price Increase Analysis with ERP Integration

  • Pros: Accurate results, traceability of adjustments, and integration into financial reports
  • Cons: High technical complexity and time investment

Solution 2: Manual Review of Sales and Invoice Records

  • Pros: Lower technical complexity
  • Cons: Extremely time-consuming and prone to human error

Solution 3: External Audit by a Third-Party Consultancy

  • Pros: Independent verification and expert insights
  • Cons: High cost and dependency on external timelines

Justification for Selected Solution: Solution 1 was chosen for its potential to deliver accurate and reliable results directly from the ERP system while retaining full control over the data analysis process.

7. Implementation Plan

  • Phase 1 (Data Extraction and Preparation):

    • Connect Alteryx to SAP ERP for automated data extraction.
    • Compile transaction data from sales, accounting, and contracts modules.
    • Cross-reference extracted data with known price adjustment records.

  • Phase 2 (Data Transformation and Analysis):

    • Perform data normalization to unify formats and resolve discrepancies.
    • Develop algorithms to identify retroactive adjustments using metadata.
    • Integrate CPI data into calculations for adjustments impacted by inflation.

  • Phase 3 (Validation and Adjustment):

    • Consult with sales, accounting, and controlling teams to validate results.
    • Conduct a sample-based audit to ensure data accuracy.

  • Phase 4 (Reporting and Presentation):

    • Generate a detailed financial report with charts and analysis.
    • Provide a summary dashboard for management and potential buyers.

  • Phase 5 (Documentation and Handover):

    • Deliver technical documentation on the methodology used.
    • Provide recommendations for future price adjustment tracking.

8. Results and Outcomes

  • Financial Clarity: A comprehensive calculation of realized and unrealized price increases was provided.
  • CPI Integration: The impact of consumer price index adjustments was quantified.
  • Data Traceability: The methodology allowed future audits to verify calculations.
  • Increased Valuation Confidence: The buyer received reliable financial data for the transaction.

9. Lessons Learned

  • Automated Data Extraction Reduces Errors: Integrating Alteryx with SAP simplified a complex process.
  • Cross-Department Collaboration is Crucial: Inputs from sales, accounting, and controlling ensured accuracy.
  • CPI Impact Requires Early Consideration: Integrating CPI data early prevented recalculations.

10. Conclusion

The project successfully delivered a detailed analysis of retroactive price increases, incorporating CPI adjustments. The findings provided the buyer with a reliable basis for financial evaluation, reducing the risk of post-sale disputes. The project also established a replicable framework for future price adjustment analyses.

11. References and Appendices

  • References: ERP system logs, sales contracts, CPI datasets, and Alteryx workflows.
  • Appendices: Sample Alteryx workflows, analysis dashboard screenshots, and audit trail examples.

Any Questions?

Contact me on any of my communication channels:

Contact Links: