Tiered & Banded Calculations in Excel: All-Units vs Incremental Tiers, Block Tariffs, Volume Rebates, Stepped Costs & Band Lookups
Master the calculation behind every price list, tariff and commission plan with steps. You will look up the right band, tell all-units tiers from incremental (marginal) tiers, bill block tariffs, pay retroactive volume rebates, model stepped fixed costs and build one tier engine that works for any table, with every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
This course works through tiered and banded calculations for a fictional wholesaler, with every price list, tariff and plan clearly fictional. You look up bands with approximate-match XLOOKUP and INDEX/MATCH and check band tables for gaps and overlaps, price all-units lists with their cliffs and break-even quantities, split quantities into incremental tiers with MAX/MIN and one SUMPRODUCT, bill a block tariff with a fixed charge, compare retroactive and prospective volume rebates and accrue them from a forecast, pay a commission plan with an accelerator, staff stepped fixed costs with ROUNDUP and build one tier engine that compares two price lists in a chart.
Lessons
- Band Lookups: Approximate Match on Lower Bounds, Open Top Bands, Gaps & Overlaps Pro
- All-Units Tiers: One Rate for the Whole Quantity, Cliffs & Buying Up to the Next Band Pro
- Incremental Tiers: Units per Band, One SUMPRODUCT & the Effective Rate Pro
- Block Tariffs: Fixed Charge, Rising Blocks, Bills for Many Sites & the Marginal Price Pro
- Volume Rebates: Retroactive vs Prospective Scales & Accruing from a Year-End Forecast Pro
- Tiered Commissions & Fee Tables: Accelerators, Fixed + Percent Fees & the Effective % Pro
- Stepped Fixed Costs: Staff Added Every N Units, Cost per Unit & the Best Operating Points Pro
- A Tier Engine: One Band Table, One Formula for Every Customer & Two Price Lists Compared Pro