End-to-end Excel VBA data model for evaluating price elasticity, promotion lift, and sales performance. Automates data preparation and integration, calendar mapping, KPI computation, and scenario simulations for FMCG analysis.
Upload in progress
The key business questions are defined in order to clarify the commercial objectives of the analysis and ensure all subsequent work remains aligned with decision-making needs and value delivery. The FMCG company requires a deep understanding of how pricing, promotions, and competitive dynamics influence sales and margin performance, therefore pricing decisions, promotional planning, and resource allocation can be made more effectively.
1. Price sensitivity: How do changes in price affect sales volume, revenue, and margin across different products and store segments?
2. Promotion effectiveness: To what extent do promotions generate incremental demand versus merely shifting existing sales?
3. Profitability of promotions: Which promotional campaigns drive positive margin contribution, and which result in margin erosion?
4. Promotion ROI: What is the return on investment (ROI) of promotional activities when comparing incremental sales to promotional costs?
5. Competitive price positioning: How do our price levels compare to competitor offerings, and how does this positioning influence customer purchasing behavior?
6. Media and marketing influence: What role does media or marketing support play in amplifying or moderating the effects of pricing and promotional activities?
In this step, the analytical techniques and evaluation logic are defined to ensure that the analysis is consistent, repeatable, and aligned with commercial decision-making needs. The approach specifies how sales performance will be decomposed into baseline and incremental components, how price responsiveness will be measured, and how promotional and competitive effects will be quantified.
The key performance indicators are categorized to reflect different analytical perspectives:
- Sales metrics: sales volume, revenue, margin, and average selling price.
- Baseline and incremental Metrics: baseline demand, incremental uplift during promotions, incremental revenue, and incremental margin.
- Price sensitivity metrics: price elasticity and price–volume relationships.
- Promotion effectiveness metrics: promotional uplift, cost of promotion, and ROI.
- Competitive positioning metrics: price index and relative price gap to competitors.
- Media and marketing influence metrics: media support levels and their interaction with sales and promotion outcomes.
Note: All data used in this portfolio project is entirely fictional and does not represent any real companies, products, brands, stores, or market activities. The dataset is a scaled-down demo version created for portfolio presentation purposes.
The analysis uses eight integrated tables simulating a fictional FMCG company's promotional and sales activities across five major Hungarian retailers, covering the full 2024 calendar year.
The dataset contains the following tables:
Sales: YearWeek, WeekStart, StoreID, SKU, Units, NetPrice_LCU, NetRevenue_LCU, PromoFlag, FeatureDisplayFlag, OnInvoiceDiscount_Pct, OffInvoiceRebate_Pct, Returns_Units
Products: SKU, Brand, Category, Segment, PackSize_ml, UnitsPerCase, LaunchDate, Status, StdUnitCost_LCU
Stores: StoreID, Retailer, Channel, Region, Format
Calendar: YearWeek, Date, WeekStart, WeekEnd, Month, Quarter, FiscalPeriod, HolidayFlag, Season, ISOWeek, ISOYear
Promos: PromoID, SKU, StoreID, WeekStart, WeekEnd, Mechanic, Depth_Pct, FeatureDisplayFlag, CoopFunding_LCU, Comments, OverlapCount
Pricelist: YearWeek, SKU, StoreID, ListPrice_LCU, AvgNetPrice_LCU, AvgUnitCost_LCU
Competitor: YearWeek, CompetitorBrand, SKU_Comp, AvgPrice_LCU, PromoFlag
Media: YearWeek, Channel, Spend_LCU, Impressions, GRPs
This step establishes a consistent data foundation that enables reliable analysis and automated reporting. The objective is to organize the raw data into a structured model where each dataset (Sales, Products, Stores, Calendar, Promos, Pricelist, Competitor, Media) is connected through common keys and standardized formats.
The data is integrated using conformed dimensions such as Product, Store, and Date, ensuring that performance metrics can be analyzed consistently across categories, channels, and time periods. During this step, data types are aligned, lookup relationships are validated, and the datasets are prepared for efficient processing in VBA.
To support the analytical model, the workbook is organized as:
- Fact table: Sales transactions and promotional activity records.
- Dimension tables: Products (product attributes) Stores (store attributes), and Calendar (calendar variables).
- Supporting tables: Pricelist (list of prices), Competitor (competitor price data), and Media (media activity inputs).
Manual checks (COUNTIF/COUNTIFS, SUMPRODUCT) verify keys and relationships before any modeling or automation.
Join blueprint: It shows which tables join on which keys, and the exact lookup formulas (INDEX/MATCH, COUNTIFS keys, helper keys).
This is the phase where the quality assurance process is automated, ensuring that all input datasets are structurally valid and internally consistent. The VBA validation module standardizes data quality by detecting errors and inconsistencies across the eight integrated data sheet, highlighting invalid cells directly within source tables, logging all validation issues in a structured DataValidation report sheet, and ultimately reducing manual data cleaning time through automated checks.
The core subroutine RunDataValidation() orchestrates the process by iterating over all sheets and calling specific validator modules.
Each validator applies a structured set of checks:
- Structural validation
- Data type and range validation
- Referential integrity checks
- Logical consistency checks
The DataValidation sheet has five columns where the detected issues are reported.
Note for myself: Insert example screenshot here
The invalid or inconsistent cells are highlighted in the sheets as well (light red color=errors, missing values; light yellow color=warnings, suspicious values) The DataValidation report combined with the on-sheet highlighting provides a complete traceability for data cleansing.