Case Study: Net Revenue Management in Excel
Introduction
The Fast Moving Consumer Goods (FMCG) industry is a dynamic sector that significantly impacts our daily lives. This case study focuses on understanding how prominent brands like Coca-Cola, Nivea, and Lay's are managed. The study involves analyzing a large dataset to perform market analysis, calculate market share, and create visuals that reveal actionable insights. The principles of Net Revenue Management (NRM) are applied to identify new growth opportunities for companies.
Data Sources and Resources
This analysis is based on a comprehensive datasets provided by DataCamp. To support transparency and reproducibility, the following resources are available:
Excel Analysis Workbook: The complete Excel workbook containing all raw data, calculations, and visualizations used in this case study can be accessed here.
Dataset Metadata: For a detailed understanding of the dataset structure and column definitions, refer to the metadata sheet here. This document provides comprehensive information about all variables used in the analysis.
Dataset: All 4 Datasets can be downloaded from here
Objectives
This case study aims to:
- Analyze market share and brand performance in the FMCG sector
- Identify growth opportunities using NRM principles
- Evaluate promotional effectiveness and profitability
- Provide strategic recommendations based on data-driven insights
Summary of Findings & Process:
Methodology:
The analysis was conducted using Excel by exploring various functions for data extraction, segmentation, and visualization. Data was loaded into different sheets and synthesized to generate meaningful insights on market dynamics, sales forecasting, and profitability.
1. Data Collection and Segmentation:
Data from various sources (e.g., Brands per Supplier, HealthMax Growth, Internal Sales Data) was consolidated to assess brand performance, supplier contributions, and sales trends. Segmentation of data was done across key metrics such as market share, sales performance, and category growth.
2. Brand & Supplier Insights:
Analysis of supplier and brand data revealed that some key brands contributed disproportionately to overall market growth, while others lagged, leading to shifts in market share. The "Brands per Supplier" sheet helped identify market leaders and underperformers, offering a comprehensive view of brand influence across suppliers.
3. Market Growth Trends:
Using the "HealthMax Growth" and "MAT Value Total Category" sheets, significant growth opportunities were identified in health-related categories, particularly organic and specialty products. MAT value trends showcased high-performing categories with strong market demand, indicating key areas for strategic investment.
4. Sales Forecasting (2024):
Historical sales data and forecasting techniques were applied to project sales for 2024, showing a steady increase driven by new product categories and market expansion efforts. The "Sales until 2022" and "Forecast 2024" sheets suggest potential success for upcoming product launches (e.g., 50ml Shampoo), targeting emerging market segments.
5. Promotion and Profitability Effectiveness:
Promotional strategies were analyzed using the "Promotion Analysis" and "Promotion Graph" sheets, identifying which promotions offered the highest return on investment. The "Profitability Matrix" highlighted areas where profit margins could be improved through strategic pricing adjustments and more effective promotional campaigns.
6. New Category Opportunities:
Emerging opportunities in categories like organic and health-centric products were identified through the "New Category Opportunity" sheet. These insights align with shifting consumer preferences, positioning these categories as potential growth drivers.
7. Strategic Recommendations:
Based on findings from the profitability matrix, it is recommended to optimize pricing and promotional efforts to enhance overall profit margins. Strategic investments in high-growth areas and new product categories (such as organic shampoo) are advised to capitalize on market trends.
Conclusion:
To improve ROI, companies should focus on optimizing their pricing and promotional strategies, focusing on high-growth and high-margin categories, and improving operational efficiency. Digital channels, customer retention strategies, and innovation will also play key roles in maximizing profitability while capitalizing on emerging market trends.