PRICING STRATEGY - BLANK MODEL SET: THE BOOK'S OWN INPUTS

Type these back into the blank files and the book's results come back. Cell references are sheet!cell.

Price_Your_Own_Book_Blank.xlsx  (Chapters 9, 15 and 16)

   Your Inputs!C8              148
   Your Inputs!D8              78
   Your Inputs!C9              62
   Your Inputs!D9              36
   Your Inputs!C10             96
   Your Inputs!D10             58
   Your Inputs!C15             0.04517988494562106
   Your Inputs!C16             0.09257734273039496
   Your Inputs!C17             0.02662333683985516
   Your Inputs!C18             0.006739521056598207
   Your Inputs!C19             0.0165298007238097
   Your Inputs!C20             0.006584917487581751
   Your Inputs!C21             0.002924828052877205
   Your Inputs!C22             0.006614896646512262
   Your Inputs!C23             0.002511333904129151
   Your Inputs!C31             0.02
   Your Inputs!C32             3900000
   Your Inputs!C33             1400000
   Your Inputs!C34             2300000
   Your Inputs!C35             1800000
   Your Inputs!C40             145
   Your Inputs!C41             6500
   Sensitivity!C51             3700000
   Your Lines B6:G463 and Your Accounts B6:D185: copy them from Price_Your_Own_Book.xlsx in the main archive,
   where the 458 Ravensworth lines and 180 accounts arrive filled in (account, segment, product, units,
   invoiced revenue, pocket revenue; account, segment, orders a year).

   Result: pocket margin 26.97 per cent, one point of price worth 3.63 points of volume, floor at the 25th percentile 460,444.62.

Discount_Decisions_Blank.xlsx  (Chapters 2-3, 7, 8, 10, 11, 14 and 17)

   1. Your book!C5         69880000              Revenue at list (every unit at its published price)
   1. Your book!C6         60253524.93           Invoiced revenue
   1. Your book!C7         56124433.13           Pocket revenue (after every off-invoice deduction)
   1. Your book!C8         39780000              Variable cost of goods
   1. Your book!C9         0.02                  Sales commission, share of invoiced revenue
   1. Your book!C10        9400000               Fixed costs
   1. Your book!C12        1205070.54            Commission paid in the year (the ledger figure)
   2. The request!C5       148                   List price of the family
   2. The request!C6       78                    Variable cost per unit
   2. The request!C7       0                     On-invoice deductions the account already carries, share of list
   2. The request!C8       0.06                  Off-invoice leakage on this account, share of invoice
   2. The request!C9       0.05                  The extra discount asked for, points of list
   2. The request!C10      1000                  The account's annual units in this family
   2. The request!C11      0.875                 The segment floor, pocket price as a share of list (Chapter 15)
   2. The request!C13      0.03                  Band 1: a representative may sign up to
   2. The request!C14      0.06                  Band 2: a regional manager up to
   2. The request!C15      0.1                   Band 3: the pricing committee up to
   3. Rebate cliff!C5      9360                  Annual units this year
   3. Rebate cliff!C6      780270.35             Invoiced purchases this year
   3. Rebate cliff!C7      477148                Variable cost of those units
   3. Rebate cliff!C8      10000                 The tier above it, units
   3. Rebate cliff!C9      0.02                  Rebate rate today, share of invoice
   3. Rebate cliff!C10     0.035                 Rebate rate at the tier, RETROACTIVE on every unit
   3. Rebate cliff!C11     0.021                 Other off-invoice deductions on this account (freight, returns, cash, 
   4. Payment terms!C5     0.02                  Early-payment discount, share of invoice
   4. Payment terms!C6     10                    Paid within, days
   4. Payment terms!C7     30                    Otherwise net, days
   4. Payment terms!C8     0.07                  Your cost of money, a year
   4. Payment terms!C9     20303992.74           Invoiced revenue of the accounts that take the discount
   4. Payment terms!C10    406079.9              Cash discount they actually took in the year (the ledger figure)
   4. Payment terms!C11    6521105.3             Of the invoiced revenue: accounts that take it and pay late anyway
   4. Payment terms!C12    130422.13             Cash discount those late payers took
   4. Payment terms!C13    25                    The day those accounts actually pay
   4. Payment terms!C14    30                    Extra days of credit asked for (a longer term)
   4. Payment terms!C15    22240101.84           Invoiced revenue the longer term would apply to
   5. Match or hold!C5     20421807.27           Family pocket revenue
   5. Match or hold!C6     4861661.76            Family contribution
   5. Match or hold!C7     440145.51             Family commission
   5. Match or hold!C8     0.08                  The competitor's cut
   5. Match or hold!C9     -1.2                  Elasticity: DECLARED, not measured
   5. Match or hold!C11    13188420.78           Contestable part only: pocket revenue
   5. Match or hold!C12    3191207.65            Contestable part only: contribution
   5. Match or hold!C13    287833.13             Contestable part only: commission
   6. Price increase!C5    0.05                  Announced list increase
   6. Price increase!C6    0.041                 Variable cost inflation the same year
   6. Price increase!C7    39780000              Variable cost of goods, the year before
   6. Price increase!C10   20567884.64           OEM key accounts / Pocket revenue
   6. Price increase!D10   22240101.84           OEM key accounts / Invoiced revenue
   6. Price increase!E10   0                     OEM key accounts / Pocket revenue exempted
   6. Price increase!F10   0                     OEM key accounts / Invoiced revenue exempted
   6. Price increase!G10   0.025                 OEM key accounts / Rate obtained
   6. Price increase!H10   6                     OEM key accounts / Months at it
   6. Price increase!C11   14361659.44           Distributors / Pocket revenue
   6. Price increase!D11   16171230.89           Distributors / Invoiced revenue
   6. Price increase!E11   0                     Distributors / Pocket revenue exempted
   6. Price increase!F11   0                     Distributors / Invoiced revenue exempted
   6. Price increase!G11   0.025                 Distributors / Rate obtained
   6. Price increase!H11   10                    Distributors / Months at it
   6. Price increase!C12   14554871.87           Direct industrial / Pocket revenue
   6. Price increase!D12   15105365.52           Direct industrial / Invoiced revenue
   6. Price increase!E12   3545946.54            Direct industrial / Pocket revenue exempted
   6. Price increase!F12   3666834.59            Direct industrial / Invoiced revenue exempted
   6. Price increase!G12   0.04                  Direct industrial / Rate obtained
   6. Price increase!H12   11                    Direct industrial / Months at it
   6. Price increase!C13   6640017.18            Small direct / Pocket revenue
   6. Price increase!D13   6736826.68            Small direct / Invoiced revenue
   6. Price increase!E13   0                     Small direct / Pocket revenue exempted
   6. Price increase!F13   0                     Small direct / Invoiced revenue exempted
   6. Price increase!G13   0.05                  Small direct / Rate obtained
   6. Price increase!H13   12                    Small direct / Months at it

   Result: 3.63 points of volume per point of price; 58.16 to 51.35 of contribution per pail on the Chapter 14 deal (2.34 to one);
   DIR-075: 4,967.53 on the last 640 units and a below-cost window of 458.04 units (corrected); 2/10 net 30 at 37.2 per cent, overpayment 329,759.14;
   match or hold turns at -4.11; the increase realized 1,291,961.25 with 27,423.12 of commission and -366,441.87 on contribution (corrected).

