Financial models, scenarios, sensitivity — and bonuses, commissions, cashback and discounts
Build driver-based models with explicit assumptions, run scenarios and sensitivity tests, and calculate incentive and pricing mechanics exactly — including their profit impact.
This lesson builds on: Financial statements — P&L, margins, balance sheet and working capital.
- Structure a driver-based financial model with an assumptions sheet
- Run scenario and sensitivity analysis and present results
- Implement bonus and commission schemes with exact boundary logic
- Evaluate cashback and discounts by their effect on margin, not revenue
- Build a simple cost model and assess sales profitability
Driver-based financial models
A financial model projects outcomes from drivers (assumptions) through calculations to outputs. Professional structure:
- Inputs/assumptions sheet — every number someone might change, labelled with unit and source.
- Calculation sheets — formulas only, referencing inputs; no typed-in constants.
- Outputs — P&L, KPIs, charts.
- Checks — balance checks, totals that must reconcile, flags.
Example drivers for a store opening: footfall per day, conversion %, average basket, gross margin %, rent, staff cost, ramp-up curve (months 1–6 at 60%, 75%, 85%, 90%, 95%, 100% of mature sales).
Scenario analysis
A scenario changes several assumptions together to describe a coherent world:
| Driver | Downside | Base | Upside |
|---|---|---|---|
| Mature daily orders | 45 | 60 | 70 |
| Average basket | 80 | 90 | 95 |
| Gross margin | 36% | 40% | 41% |
| Monthly fixed cost | 26,000 | 24,000 | 24,000 |
Annual mature profit = orders × 365 × basket × margin − fixed × 12.
- Base: 60 × 365 × 90 × 0.40 − 288,000 = 788,400 − 288,000 = 500,400
- Downside: 45 × 365 × 80 × 0.36 − 312,000 = 473,040 − 312,000 = 161,040
- Upside: 70 × 365 × 95 × 0.41 − 288,000 = 995,172.5 − 288,000 = 707,172.5
Implement with a scenario selector cell and CHOOSE/INDEX or XLOOKUP on the assumptions table.
Sensitivity analysis
Sensitivity changes one driver at a time to see how much the output moves. Base case: each +1 percentage point of gross margin adds 60 × 365 × 90 × 0.01 = 19,710 of profit; each +1 daily order adds 365 × 90 × 0.40 = 13,140. A tornado chart ranks drivers by impact; Excel Data Tables (What-If Analysis) produce two-way grids.
Assumptions deserve as much scrutiny as formulas: document source, owner and confidence; test the ones the result is most sensitive to.
Bonus calculators
Translate the policy text into exact rules — especially at boundaries.
Example (the generic scheme used in Project 2): attainment a = actual ÷ plan.
- a < 0.80 → 0
- 0.80 ≤ a ≤ 1.00 → 20% + (a − 0.80) ÷ 0.20 × 80% of target bonus (linear from 20% to 100%)
- a > 1.00 → 100% + (a − 1.00) × 200% of target bonus, capped at 150%
Target bonus 1,000:
| Attainment | Payout % | Bonus |
|---|---|---|
| 79% | 0% | 0 |
| 80% | 20% | 200 |
| 90% | 20% + 0.5 × 80% = 60% | 600 |
| 100% | 100% | 1,000 |
| 110% | 100% + 0.10 × 200% = 120% | 1,200 |
| 130% | 160% → capped at 150% | 1,500 |
Excel: =IF(a<0.8,0,IF(a<=1,0.2+(a-0.8)/0.2*0.8,MIN(1.5,1+(a-1)*2)))*Target.
(The founder's "VO DEP-based" bonus rule is awaiting definition; it will be added as a separate scheme.)
Commissions
Common structures: flat % of sales; tiered (marginal rates per band, like tax); accelerators above plan; caps; clawbacks for cancelled deals; agent commissions for partners selling on your behalf.
Tiered (marginal) example: 2% on the first 20,000, 4% on 20,000–40,000, 6% above. Sales 50,000 → 400 + 800 + 600 = 1,800. A common bug applies 6% to the whole 50,000 (= 3,000) — "retroactive" tiers must be explicitly intended.
Cashback and discounts
Revenue grows with discounts; margin may not.
Product price 100, cost 60 → margin 40 per unit. A 20% discount: price 80, margin 20 per unit. To earn the same total margin, volume must double: required uplift = old margin ÷ new margin − 1 = 40 ÷ 20 − 1 = 100%.
Cashback (e.g. 5% back on next purchase) costs only when redeemed, may increase repeat purchases, and creates a liability for unredeemed balances. Evaluate with incremental margin vs a control group — selection bias is strong (Module 7).
Cost models and sales profitability
A cost model separates fixed costs (rent, base salaries), variable costs (COGS, payment fees, commissions per sale) and semi-variable costs. Contribution margin = revenue − variable costs. Break-even volume = fixed costs ÷ contribution per unit.
Example: fixed 24,000/month; contribution per order 90 × 0.40 − 2 (payment fee) = 34 → break-even ≈ 24,000 ÷ 34 ≈ 706 orders/month (≈ 23/day).
Sales profitability goes beyond revenue: margin after discounts, returns, commissions and cost-to-serve by customer, channel or product. A large customer with heavy discounts and frequent returns can be unprofitable.
Practice
Marginal tiers: 2% on the first 20,000, 4% on 20,000–40,000, 6% above 40,000. Commission on sales of 35,000?
Price 100, unit cost 70. After a 10% discount, by what % must unit volume rise to keep the same total gross margin?
Scheme: below 80% attainment → 0; 80–100% → linear from 20% to 100% of target bonus; above 100% → 100% + 2 × (attainment − 100%), capped at 150%. Target bonus 2,000; attainment 95%. Bonus?
What distinguishes sensitivity analysis from scenario analysis?
Summary
- Models: inputs → calculations → outputs → checks; no hard-coded numbers in calculations.
- Scenarios change coherent sets of assumptions; sensitivity changes one at a time.
- Incentive schemes must be exact at boundaries; tiered commissions are usually marginal.
- Judge discounts and cashback by incremental margin; know fixed vs variable costs and break-even.