Commercial & Sales Finance
Four connected analyses that answer the core commercial question: where is revenue actually coming from, what is it costing, and where should resources shift next period?
Revenue lives in the ERP, campaign spend with budget owners, COGS in Finance, trade deal terms in procurement — each team uses different definitions of the same metric. Below is how I map field ownership upfront, resolve definition conflicts before collection starts, and keep intake lightweight enough that owners can contribute without a meeting.
Before collection starts, every field maps to a single owner. This prevents the most common failure: multiple teams partially filling the same column with different definitions.
Here's what typical intake looks like — and how each issue is handled before it reaches the model.
| What Comes In | Why It's a Problem | How I Handle It |
|---|---|---|
| Month as "January", "jan", "1", "01/2024" | 4 different formats = lookup failures, broken filters | Template enforces dropdown selection (Jan–Dec). Parser normalises on intake. |
| Revenue in local currency (EUR, SGD, GBP) with no FX note | Totals are meaningless without a common currency base | Template header requires: currency + FX rate used. Finance team provides a shared rate card per period. |
| COGS missing for certain channels — "finance will add later" | GP% and contribution are blank = model can't compute Channel P&L | Validation flags missing COGS rows before submission. Default: use prior-period COGS% as placeholder, flagged in output. |
| Promo cost split across 3 budget lines by 2 different owners | Double-counting or under-counting campaign cost = wrong ROI | Single "promo_cost" field. Template notes: include all fully-loaded campaign costs. One owner per campaign signs off the total. |
| Discount pool agreed verbally, no deal memo | Procurement and Accounting have different numbers — accrual disputes at quarter-end | Procurement required to submit signed deal summary before data collection opens. Pool amount locked at that point. |
| ERP export has 12 tabs (one per month) instead of flat rows | Model needs flat format — one row per channel/month. Tabs = manual reformatting risk. | Pre-built Power Query / Python script flattens the export into the unified schema before upload. Template also accepts flat paste-in. |
- ✓All 20 required columns across four analyses: channel P&L, promo ROI, price/volume/mix, and trade accrual — pre-labelled with field name and expected format
- ✓Column naming signals owner: channel and margin columns belong to Finance/Revenue Ops; promo columns to Marketing/Budget Owners; discount pool and deal timing columns to Procurement
- ✓Two pre-filled example rows (Direct and Online channels for January) so contributors see exactly what a complete, correctly formatted row looks like
- ✓All monetary values should be in a single agreed currency before submission — the model applies no FX conversion, so alignment on currency must happen before the template goes out
- ✓Upload validation checks for all required columns and returns the specific missing column names — no silent failures
- 1.Template + deadline sent to each owner via async message (Slack/email) with clear field instructions — no meeting required to kick off
- 2.Shared tracker (e.g. Airtable) shows submission status per owner and region — no chasing over email threads
- 3.Validation script runs on each submission — flags gaps and sends back a specific query, not a generic "please fix"
- 4.Regional leads each have a single point of contact who consolidates their region's inputs — reduces noise for Finance
The hardest part of a model like this isn't the analysis — it's getting distributed owners across time zones to agree on what the numbers mean. Here's how I approach it:
1. Promotion ROI Analysis
Use this to drive the next-cycle decision: if the promo is profitable and margin holds, scale it. If net profit is positive but GP% is compressing, trim the discount depth before repeating. If net profit is negative, restructure the deal or pause it — volume uplift alone is not a success if margin is destroyed in the process.
Before / After Comparison
2. Channel-level P&L
Compare channels by contribution margin to identify where to reallocate budget. Channels with high GP% and positive contribution are where investment should concentrate. High-revenue channels with negative contribution are consuming budget without returning profit — they require pricing, cost, or exit decisions before the next planning cycle.
12-Month Channel Performance
3. Price / Volume / Mix Bridge
Plain English: Revenue changes are rarely one-dimensional. PVM breaks total variance into its three structural causes — so leadership knows whether growth is driven by real pricing power, genuine volume gains, or a temporary portfolio shift. The answer determines whether to hold the strategy or act.
Revenue Variance Decomposition
4. Trade Discount & Accrual Schedule
Post monthly journal entries as the schedule runs. At deal settlement, reconcile the actual invoice against the cumulative accrual — any gap is an adjustment that should be documented in close commentary. A clean, straight-line accrual schedule reduces audit exposure and keeps period P&L free of quarter-end settlement spikes.