Pricing & Profitability
Customer Sales Engine & Period-over-Period Profitability Workbook
Sales analytics workbook generator that converts customer and manufacturer sales data into margin metrics, period-over-period movement, top movers, risk flags, and executive-ready Excel summaries.
Sales profitability command view
Customer Sales Engine
Header aliases, sales metrics, margin logic, period comparison, movers, risk flags, and workbook delivery
Ingest
Sales file
Calculate
Margin
Compare
PoP
Rank
Movers
Deliver
Workbook
Workbook controls
Profitability preview
Excel output| View | Revenue | Margin | Move | Status |
|---|---|---|---|---|
| Customer Summary | $128K | 34.8% | +12% | Growth |
| MFG Summary | $91K | 29.4% | -5% | Review |
| Top Movers | $42K | 41.2% | +18% | Opportunity |
| Risk Flags | $27K | 18.6% | -14% | Risk |
Workbook outputs
Business problem
Customer sales and profitability reporting needed repeatable summary logic and trend analysis. Raw sales files could show revenue, but they did not easily explain margin quality, customer movement, manufacturer performance, or period-over-period risk.
The process needed a workbook engine that could normalize messy headers, calculate profitability measures, compare current and previous periods, and surface the accounts or manufacturers that needed attention.
System built
Built a Python, Pandas, and OpenPyXL workbook generator with header alias matching, sales, quantity, cost, Dealer Net, gross profit, weighted margin, and profit-after-expense calculations.
The system formats workbook tabs, applies KPI bands and conditional formatting, preserves hidden raw data, and compares current versus previous periods to identify top movers and risk signals.
Profitability signals
Signals reviewed
The engine reviews sales, quantity, cost, DNet, margin, period movement, and risk signals before producing the workbook output.
Workbook flow
How it works
Ingest
Load sales files and normalize headers through alias matching before workbook logic begins.
The engine starts by handling inconsistent column names so the same workbook logic can run across different source exports.
Calculate
Compute sales, quantity, cost, DNet, gross profit, margin, and profit-after-expense metrics.
The calculation layer turns raw transaction fields into profitability measures that can be reviewed by customer and manufacturer.
Compare
Compare current and previous periods to identify movement, risk, and performance changes.
Period-over-period logic helps the workbook tell whether accounts and manufacturers are improving, declining, or becoming risk areas.
Rank
Surface top movers, margin changes, customer trends, manufacturer summaries, and risk flags.
Ranking and risk logic help users focus on the customers and product groups that deserve attention first.
Deliver
Generate a formatted Excel workbook with KPI views, summaries, hidden raw data, and conditional formatting.
The final output turns the analytics into an executive-friendly workbook that can support sales, margin, and profitability review.
Analytics layers
What the workbook coordinates
Header alias layer
Maps inconsistent source columns into the expected fields so the workbook can run against messy exports.
Metric engine
Calculates sales, quantity, cost, DNet, gross profit, margin, and profit-after-expense measures.
Period comparison
Compares current and previous periods to identify movement, top changes, and profitability shifts.
Workbook delivery
Builds formatted Excel tabs, KPI bands, totals, conditional formatting, and review-ready summaries.
Impact signals
What the engine improved
Customer and manufacturer summaries for sales review
Weighted margin metrics for profitability visibility
Current-vs-previous period comparison
Top mover and risk reporting
Formatted workbook outputs with conditional formatting
Operational value
Sales data turned into profitability intelligence
Cleaner sales review
Turns raw sales exports into organized customer and manufacturer summaries that are easier to read and compare.
Better margin visibility
Adds weighted margin and profitability metrics so sales activity can be reviewed beyond revenue alone.
Stronger trend awareness
Period-over-period comparisons help highlight which customers, manufacturers, or segments are moving in the wrong direction.
Actionable workbook output
Formats the results into a professional Excel workbook that supports review, discussion, and follow-up decisions.
Why this project matters
Sales reporting becomes stronger when revenue, margin, and trend movement are reviewed together.
This project shows how customer sales reporting can move beyond static revenue summaries. By combining customer and manufacturer views with weighted margin, period comparison, top movers, and risk flags, the workbook creates a clearer story around profitability.
The value is not just creating an Excel workbook. The value is turning sales activity into a repeatable profitability review system that helps users understand where growth, margin pressure, and risk are coming from.
Confidentiality note
Visuals and descriptions are sanitized conceptual representations. They do not expose private company data, customer records, credentials, raw exports, internal pricing, sales files, operational screenshots, or proprietary source files.