Select SKU (row 7)
1.2 · SKU lookups (rows 8–9)
Description row 8 · VLOOKUP → Master Price List col 2
Origin row 9 · VLOOKUP → SKU Source CS&L Rates col 2
1.3 · UOM & prices (rows 10–16)
Multiplier — units per box row 14 · list price ÷ wholesale unit price
P&L UOM to bundle conversion row 10 · =$D$3 / multiplier
List price (ex GST) row 12 · Master Price List col E
Wholesale unit price row 13 · Master Price List col H
PB shelf price (excl. GST) row 15
PB shelf price per box row 16 · =row15 × multiplier
1.5 · Customer investments (rows 17–22)
Customer discount row 18 · list × D18
Order profile row 19 · (list − disc) × D19
Ullage row 20 · (list − disc − OP) × D20
SD rebate row 21 · (list − disc − OP − ullage) × D21
Total customer investments row 17 · SUM(rows 18:21)
Purchase price row 22 · list − customer investments
1.6 · Consumer investments (rows 23–25)
FFL rebate row 24 · D24 = 2%×55% · applied to list price
Promo funding row 25 · FFL rebate × D28
Total consumer investments row 23 · SUM(row 24)
ROI metrics
Free unit cost (1 in bundle of ) purchase price ÷ bundle size
Net revenue per bundle purchase price × X ÷ bundle
Total promo cost as % of list
Effective margin (net rev ÷ list)
All SKUs — promo cost % of list