Volume vs price
Revenue movement needed to be separated into quantity and pricing effects by product rather than reported as one headline value.
← Back to Case Studies
FEATURED CASE STUDY 04 · SALES & COMMERCIAL
Sales & Commercial Analytics.Operational Excel/VBA, later reimagined in Power BI.
An operational commercial reporting system originally built in Excel/VBA for cement sales, pricing, customer, credit and market analysis, with selected analytical views later rebuilt in Power BI as a modernization exercise.
CONTEXT & ORIGIN
This project started as an operating tool, not as a dashboard exercise.
The original solution was a multi-sheet Excel/VBA system built around day-to-day commercial reporting. Its workbook connects sales, credits, dispatch, products, customers, market areas and budget references into dedicated analytical views.
VBA-driven dropdowns and linked reporting sheets gave users a practical way to move between product and market analysis, credit and sales views, customer transaction cards, top-customer rankings and accumulated product analysis.
Design principleStart from the commercial decision, then build the reporting logic around it.
THE COMMERCIAL PROBLEM
Sales value alone was not enough. The business needed to understand what moved it.
Commercial performance sits at the intersection of quantity, price, product mix, market mix, customer activity, incentives, freight and credit. The reporting system had to keep those drivers connected.
Budget & history
Actual performance needed a clear reference against budget and prior-year positions to make variance meaningful.
Customer exposure
Sales contribution, credit, debit balance, incentives and transaction history needed to be visible at customer level.
Product & market mix
Management needed to see where volume was concentrated across products, markets and delivery areas.
SYSTEM EVOLUTION
The technology changed. The commercial logic stayed intact.
The Power BI version was not presented as the original operating system. It was a later reinterpretation of the same business domain using a modern analytical model.
ORIGINAL · EXCEL / VBA
Operational reporting system
- Product & market dashboard
- Credit & sales dashboard
- Customer transaction card
- Top-customer rankings
- Accumulated product analysis
- Sales & credit detail
LATER · POWER BI
Modern analytical interpretation
- Actual vs budget
- Actual vs prior year
- Product contribution
- Market distribution
- Customer analytics
- Volume & price variance
COMMERCIAL ANALYTICS ARCHITECTURE
Transaction records become structured commercial evidence for review and comparison.
Both generations of the system follow the same path: connect the underlying commercial records, calculate the business measures and expose the relationships management actually needs to see.
01 · SOURCE DATA
- Sales transactions
- Budget references
- Prior-year performance
- Customer records
- Product portfolio
- Market & area data
- Dispatch records
- Credit & incentives
02 · COMMERCIAL LOGIC
Measure & Reconcile
Connect quantity, pricing, customer, product, incentive, freight and credit measures around consistent reporting periods.
03 · COMPARISON
Budget · Prior Year · Mix
Separate the drivers of change, then compare products, customers and markets against the relevant commercial reference.
04 · REVIEW OUTPUT
Decision-ready visibility
Management summaries, customer-level detail, product performance, market distribution and exception visibility.
SYSTEM EVIDENCE
One commercial story across two generations of tools.
The original workbook now depends on a legacy desktop environment. The portfolio views below reproduce its analytical output from the workbook's own stored data and chart caches, with customer identities anonymized where applicable.
01 · ORIGINAL PRODUCT & MARKET VIEW
Commercial movement beyond total salesQuantity, product mix, market distribution and ex-factory price trends were part of the original operational reporting logic.Open full-size view ↗
02 · ORIGINAL CREDIT & SALES VIEW
Customer performance and exposure togetherTop-customer sales, credit exposure and incentive trends connect commercial activity with the financial position of the account.Open full-size view ↗03 · POWER BI · ACTUAL VS BUDGET
Volume and price variance separatedThe modern model compares quantity and price independently against budget, then brings contribution and overall performance into the same review.Open full-size view ↗
04 · POWER BI · PRODUCT DISTRIBUTION
Product and market concentration become visibleProduct contribution, geographic distribution and prior-year context make it easier to see where commercial performance is concentrated.Open full-size view ↗ANALYTICAL MODEL
The value came from connecting commercial drivers, not from drawing charts.
The workbook and the later Power BI model both organize the same underlying questions: what sold, at what price, to whom, in which market, against which reference and with what commercial deductions or exposure.
Volume & price
Separate quantity movement from price movement so commercial performance is not reduced to one sales-value number.
Actual vs budget
Compare both quantity and price against budget, then expose the resulting variance by product.
Actual vs prior year
Read current performance against historical movement to distinguish growth, mix changes and price effects.
Product & market mix
Show product contribution and geographic distribution so volume concentration becomes visible.
Customer exposure
Track customer sales, contribution, debit position and credit alongside transaction-level activity.
Net commercial value
Bring incentives, freight and other commercial deductions into the path from gross value to net sales.
HOW IT WORKS
Five steps from raw commercial records to management review.
The workflow keeps customer, product and transaction detail underneath the management view so the result can be investigated rather than merely observed.
- 01
Capture
Bring sales, customer, product, dispatch, credit and budget records into one reporting structure.
- 02
Structure
Organize the workbook around shared dates, products, customers and commercial dimensions.
- 03
Analyze
Calculate quantity, price, value, incentives, freight, contribution and customer-level measures.
- 04
Compare
Benchmark actual performance against budget and prior-year positions across products and markets.
- 05
Review
Surface management views while keeping detailed customer and transaction analysis available underneath.
WHAT THE SYSTEM ENABLES
A commercial view that explains the number instead of simply reporting it.
- Variance visibilitySeparate volume and price effects across actual, budget and prior-year performance.
- Customer intelligenceConnect transaction activity, contribution, incentives and credit position at account level.
- Market understandingSee product and geographic concentration instead of relying on aggregate sales totals.
- Commercial continuityCarry the same business logic from an operational Excel/VBA system into a later Power BI analytical model.
MY CONTRIBUTION
The business logic came first. The technology changed around it.
I designed and developed the original Excel/VBA reporting system around practical commercial requirements, including linked product, customer, sales, credit and pricing views. I later rebuilt selected analytics in Power BI to explore how the same commercial logic could be expressed through a modern data model and interactive reporting experience.
- Original delivery
- Excel · VBA · Linked operational reporting
- Commercial logic
- Sales · Pricing · Customers · Credit · Incentives
- Modernization
- Power BI data model & analytical views
- Role
- System Designer · Developer · Commercial Analysis
CASE STUDY 04
Sales & Commercial Analytics
Operational commercial logic carried from Excel/VBA into modern analytical reporting.