CONTRACT MANAGEMENT - 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.

The_Contract_Priced_Blank.xlsx  (Appendix A; Chapters 1 to 17)

   Assumptions!C7                    62000
   Assumptions!C8                    104
   Assumptions!C9                    109.5
   Assumptions!C10                   36
   Assumptions!C13                   58400
   Assumptions!C14                   51400
   Assumptions!C15                   49900
   Assumptions!C18                   0.084
   Assumptions!C19                   0.051
   Assumptions!C20                   12
   Assumptions!C21                   3
   Assumptions!C22                   24
   Assumptions!C23                   0.03
   Assumptions!C26                   55000
   Assumptions!C27                   0.045
   Assumptions!C28                   65000
   Assumptions!C29                   0.015
   Assumptions!C30                   66000
   Assumptions!C33                   45
   Assumptions!C34                   60
   Assumptions!C35                   0.064
   Assumptions!C36                   0.031
   Assumptions!C37                   0.017
   Assumptions!C38                   43
   Assumptions!C39                   14600
   Assumptions!C40                   0.22
   Assumptions!C41                   0.12
   Assumptions!C44                   18400
   Assumptions!C45                   11.5
   Assumptions!C46                   7
   Assumptions!C47                   16.25
   Assumptions!C48                   3
   Assumptions!C49                   5200
   Assumptions!C50                   0.05
   Assumptions!C51                   0.1
   Assumptions!C52                   12
   Assumptions!C53                   24
   Assumptions!C54                   36
   Assumptions!C55                   5.2
   Assumptions!C56                   0.0042
   Assumptions!C57                   890
   Assumptions!C58                   0.38
   Assumptions!C59                   0.71
   Assumptions!C60                   1850
   Assumptions!C61                   1240
   Assumptions!C62                   2
   Assumptions!C65                   7
   Assumptions!C66                   4
   Assumptions!C67                   6800
   Assumptions!C68                   138
   Assumptions!C69                   340000
   Assumptions!C70                   36
   Assumptions!C71                   415000
   Assumptions!C72                   12
   Assumptions!C73                   6
   Assumptions!C74                   96.2
   Assumptions!C75                   4100
   Forecast Risk!C6                  50200
   Forecast Risk!C8                  68500

   Result: clause package 1,565,906.48; net -1,478,602.44; equivalence price 94.1947; three clauses reach +21,103.79.

The_Clause_Calculators_Blank.xlsx  (Chapters 3 to 14, one sheet each)

   Payment Terms!C7                  17501681.88
   Payment Terms!C8                  45
   Payment Terms!C9                  60
   Payment Terms!C10                 0.064
   Payment Terms!C26                 0.0075
   Payment Terms!C27                 15
   Indexation!C7                     104
   Indexation!C8                     109.5
   Indexation!C9                     0.084
   Indexation!C10                    0.051
   Indexation!C11                    0.03
   Indexation!C12                    58400
   Indexation!C13                    51400
   Indexation!C14                    49900
   Volume Band!C7                    55000
   Volume Band!C8                    0.045
   Volume Band!C9                    58400
   Volume Band!C10                   51400
   Volume Band!C11                   49900
   Volume Band!C12                   104
   Volume Band!C13                   109.4133
   Volume Band!C14                   116.3174
   Rebate!C7                         65000
   Rebate!C8                         0.015
   Rebate!C9                         109.4133
   Rebate!C10                        50200
   Rebate!C11                        62000
   Rebate!C12                        68500
   Liquidated Damages!C7             18400
   Liquidated Damages!C8             11.5
   Liquidated Damages!C9             7
   Liquidated Damages!C10            16.25
   Liquidated Damages!C11            3
   Liquidated Damages!C12            5200
   Liquidated Damages!C13            104
   Liquidated Damages!C14            0.05
   Liquidated Damages!C15            0.1
   Liquidated Damages!C16            0.13
   Warranty!C7                       159700
   Warranty!C8                       0.0042
   Warranty!C9                       890
   Warranty!C10                      12
   Warranty!C11                      24
   Warranty!C12                      36
   Warranty!C13                      5.2
   Warranty!C14                      0.38
   Warranty!C15                      0.71
   Warranty!C16                      104
   Warranty!C32                      0.89
   Change Control!C7                 43
   Change Control!C8                 14600
   Change Control!C9                 0.22
   Change Control!C10                0.12
   Change Control!C11                3
   Change Control!C12                0.1
   Currency!C7                       5623843.62
   Currency!C8                       5804238.26
   Currency!C9                       0.031
   Currency!C10                      0.017
   Currency!C11                      0.025
   Currency!C12                      104
   Currency!C13                      159700
   Exit Cost!C7                      49900
   Exit Cost!C8                      12
   Exit Cost!C9                      6
   Exit Cost!C10                     116.3174
   Exit Cost!C11                     96.2
   Exit Cost!C12                     4100
   Exit Cost!C13                     340000
   Exit Cost!C14                     36
   Exit Cost!C15                     24
   Exit Cost!C16                     415000

   Result: 46,031.82; month 19 and 892,881.88; 514,263.68; 37,474.06; 81,120.00; 196,996.34; 188,340.00; 455,844.71; 2,009,092.93.

Price_Your_Own_Contract_Blank.xlsx  (Chapter 19)

   Your Contract!C7                  104
   Your Contract!C8                  109.5
   Your Contract!C9                  36
   Your Contract!C12                 58400
   Your Contract!C13                 51400
   Your Contract!C14                 49900
   Your Contract!C16                 50200
   Your Contract!C17                 55000
   Your Contract!C20                 0.084
   Your Contract!C21                 0.051
   Your Contract!C22                 0.03
   Your Contract!C53                 0.064
   Your Contract!C54                 18400
   Your Contract!C55                 0.0042
   Your Contract!C56                 890
   Your Contract!C57                 43
   Your Contract!C58                 14600
   Your Contract!C60                 0.031
   Your Contract!C61                 0.017
   Your Contract!C65                 45
   Your Contract!D65                 60
   Your Contract!C66                 0.045
   Your Contract!D66                 0
   Your Contract!C67                 0.05
   Your Contract!D67                 0.1
   Your Contract!C68                 12
   Your Contract!D68                 24
   Your Contract!C69                 0.22
   Your Contract!D69                 0.12
   Your Contract!C70                 1
   Your Contract!D70                 0
   Your Contract!C71                 7
   Your Contract!D71                 4
   Your Contract!C72                 12
   Your Contract!D72                 6
   Your Contract!C75                 0.38
   Your Contract!C76                 0.71
   Your Contract!C77                 5200
   Your Contract!C78                 3
   Your Contract!C79                 34.75
   Your Contract!C80                 6800
   Your Contract!C81                 138
   Your Contract!C82                 1850
   Your Contract!C83                 1240
   Your Contract!C84                 12
   Your Contract!C85                 340000
   Your Contract!C86                 36
   Your Contract!C87                 415000
   Your Contract!C88                 4100
   Your Contract!C89                 96.2
   Your Contract!C90                 7
   Your Contract!C91                 24
   Ledger!C7                         1000000

   Result: 9.8053 a unit, equivalence price 94.1947, low-case surcharge 247,164.64.

The_Negotiation_Ledger_Blank.xlsx  (Chapter 18)

   The Brief!C7                      104
   The Brief!C10                     9.8053
   The Brief!C33                     109.5
   Ledger!C7                         1478602.44
   Ledger!C9                         159700
   Ledger!C12                        538309.17
   Ledger!C13                        514263.68
   Ledger!C14                        455844.71
   Ledger!C15                        196996.34
   Ledger!C16                        188340
   Ledger!C17                        83309.93
   Ledger!C18                        81120
   Ledger!C19                        46031.82
   Ledger!B12                        Indexation capped at 3 per cent
   Ledger!B13                        Volume band removed, or the threshold set below the low case
   Ledger!B14                        Price denominated in the buyer's currency
   Ledger!B15                        Warranty of 24 months from delivery
   Ledger!B16                        Change control at cost plus 12 per cent
   Ledger!B17                        Force majeure with a four-week alternative sourcing trigger
   Ledger!B18                        Liquidated damages capped at 10 per cent of the affected order
   Ledger!B19                        Payment terms of 60 days
   The Brief!E15                     Whatever the index does, it does to both price paths. A cap costs the supplier nothing in a flat year.
   The Brief!E16                     A threshold above our own low case is a surcharge we have already forecast paying.
   The Brief!E17                     We carry the currency or you do. One of us is hedged already.
   The Brief!E22                     A supplier with an uncertain defect rate pays real money for a shorter window.
   The Brief!E23                     Change margin is the most profitable revenue in the agreement.
   The Brief!E24                     An exclusivity right through a disruption protects their whole order book.
   The Brief!E25                     A low cap protects the margin on every order they deliver late.
   The Brief!E26                     A supplier with thin working capital values days far above what they cost us.
   The Brief!B37                     Larchmere spent its negotiating credit in almost exactly the reverse of this order.
   Trade Planner!F8                  Already on their own list of asks.
   Trade Planner!F9                  Already on their own list of asks.
   Trade Planner!F10                 Held back: an exclusivity right is worth more than its price.
   Trade Planner!F11                 Already on their own list of asks.
   Trade Planner!F12                 Already on their own list of asks.
   Trade Planner!F17                 Whatever the index does, it does to bo
   Trade Planner!F18                 A threshold above our own low case is 
   Trade Planner!F19                 We carry the currency or you do. One o
   Trade Planner!C8                  1
   Trade Planner!C9                  1
   Trade Planner!C10                 0
   Trade Planner!C11                 1
   Trade Planner!C12                 1
   Trade Planner!C17                 1
   Trade Planner!C18                 1
   Trade Planner!C19                 1

   Result: must-haves worth 9.4454 a unit against a gap of 9.2586: the asks close it.

