Commodity Hedging Model

The template begins with a single producing asset: 500,000 tonnes per annum, growing 3% annually, sold at an active LME spot price in US dollars per tonne. A scenario toggle on the assumptions sheet selects one of three price paths.

• All three start from the same Year 1 spot, then diverge through annual growth rates. That shared starting point matters because the hedge book is struck against Year 1 conditions, so notional divergence begins only from Year 2.
• Costs are captured as a per-tonne stack with separate lines for C1 cash cost, treatment and refining charges, and site G&A, each inflated annually at a common rate. This separation lets you see each component rather than a single collapsed cost figure.

Corporate admin is a fixed dollar amount that does not scale with volume, which creates operating leverage when prices move.

The hedge overlay has two instruments. A forward covers 50% of annual production at a fixed price equal to the Year 1 base spot, with no contango premium added.

• Settlement is the hedged volume multiplied by the difference between that fixed price and the active spot. The sign is two-sided: a gain when spot falls below the forward, an opportunity cost when spot rises above it.
• A bought put covers 20% of production at a strike below spot, with the premium expensed in full in the first year rather than amortised. The put payoff is the maximum of zero and the difference between strike and spot, multiplied by hedged put volume.

Total hedge settlement equals forward settlement plus put payoff minus the first-year premium. Because only a bought put is used, upside above the strike remains unhedged, which is deliberate: selling a call to finance the put would cap the high-price scenario and muddy the effectiveness comparison.

Total hedge settlement = forward settlement + put payoff − the first-year premiumCopy

Revenue on the hedged statement equals unhedged revenue plus hedge settlement. That effective revenue figure then drives several downstream items: capital expenditure is a percentage of effective revenue, receivables are calculated on effective revenue, and inventory and payables are calculated on the C1 cost stack.

• From effective revenue the model deducts the three cost lines and corporate admin to reach EBITDA, then depreciation, interest and commitment fees to reach pre-tax profit. Tax applies only when pre-tax profit is positive, with no refund modelled.
• Net income feeds dividends at a fixed payout when positive, and retained earnings roll forward. Cash flow is built indirectly, with working capital changes based on opening balances rather than the full first-year balance.

A term loan amortises straight-line, while a revolver with a cash sweep repays drawn balances when operating cash exceeds a minimum floor, or draws when it falls short. The balance sheet carries no derivative asset or liability line because settlements flow through profit and loss rather than sitting as open mark-to-market positions.

Revenue on the hedged statement = unhedged revenue + hedge settlementCopy

The primary analytical output is a hedge effectiveness sheet that reads all three price scenarios simultaneously through independent helper rows, so it does not depend on the active toggle. For each year it computes the standard deviation of unhedged and hedged revenue, EBITDA and net income across the scenarios, then expresses variance reduction as one minus the ratio of hedged to unhedged standard deviation.

• A positive figure means hedging dampens volatility; averaging across years gives a single headline number per metric. Validation checks confirm the balance sheet balances each year, cash stays non-negative, the revolver remains within its limit, forward and put hedge ratios hold at their target percentages, the effective revenue reconciliation ties, and the put payoff is never negative.
• Practically, this helps a producer test whether locking in margins via forwards and buying price-floor protection actually smooths earnings across bull and bear cases. Note that the public download is a values-only preview; the live model captures these relationships.