Contract Price Adjustment Clauses: Index-Linked Formulas, Base Dates, Caps & Floors, Thresholds and Multi-Year Escalation

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Apply the classic index formula P = P₀ × (a + b × M/M₀ + c × L/L₀) to six fictional supply contracts, round the new prices, read the change % and spot the contract whose shares do not add up to 1.
• Work out which index month a clause points to with EOMONTH, pull the base and review index values from a fictional monthly index table with XLOOKUP and calculate the index change for six contracts with different lags.
• Limit formula changes with a cap and a floor (a collar) using MIN and MAX, price six contracts and follow one contract over five reviews where a cumulative cap stops the price at 115% of the base.
• Apply two fictional clause types: a threshold that ignores small index moves, and a sharing band that passes on nothing, half or all of a move depending on its size – for rises and falls alike.
• Find the energy surcharge band of each month with an approximate XLOOKUP, calculate an alloy pass-through per kilogram and measure what both surcharges added to eight months of invoices.
• Compare two offers for a five-year contract – a fixed yearly escalation and an index-linked price on a fictional index scenario – year by year and in total, and decide which is cheaper.
• Recompute a fictional supplier's claimed price increase from the clause – shares, index values and the annual cap – flag every item that is overcharged and add up what the overcharges would cost per year.
• Turn ten reviewed contracts into a budget impact: spend × change per contract, totals by category with SUMIFS or SUMPRODUCT, the weighted impact % of the whole portfolio and a column chart for the budget meeting.