Commodities Trading Model
Capital Markets Financial Model (Free Excel Download)
Forecast traded volumes, forward curves, storage, transport, hedging, margins, and working capital to evaluate commodity-trading profitability.
professionals from Deloitte
Used by professionals from
About this model
This commodities trading model forecasts a physical trading house's revenue, working capital needs, and leverage for three commodity segments (energy, metals, agriculture) operating with thin margins per tonne. Revenue scales from trading volumes (tonnes) multiplied by spot prices and stable gross margin per tonne - not margin percentage - because commodity prices fluctuate widely while margin per tonne is the stable metric traders track. The model projects 50 million tonnes annually across three segments, with $14/tonne energy margin, $12/tonne metals margin, and $10/tonne agriculture margin, yielding ~2% gross margin percentage (stable across scenarios).
The model includes a demand-driven borrowing base facility where trade finance is drawn only as needed to fund working capital (receivables plus inventory minus payables). Opening balances and complete Day 0 balance sheet reconciliation are critical to prevent multi-billion-dollar imbalances. Capex and PP&E tracking includes existing assets ($1,200M gross with $400M accumulated depreciation) and new growth capex, with explicit opening gross PP&E and accumulated depreciation assumptions replacing magic-number scalars. The model separates RMI (ready-to-melt inventory) as a percentage of total inventory, using RMI only in eligible borrowing base and adjusted net debt calculations.
This model serves trading houses and their lenders (syndicated warehouse providers) managing borrowing base facilities and covenant compliance. Institutional investors evaluating acquisition or growth capital opportunities use it to model working capital dynamics, leverage profiles, and scenario sensitivity to commodity price volatility and credit cycles.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Commodities Trading Model
- Physical and derivative position tracking by commodity
- Margin-per-tonne revenue build (not margin percentage)
- Working capital schedule for receivables, inventory, and payables
- Borrowing base facility with eligible RMI inventory mechanics
- Capex and PP&E with explicit opening gross and accumulated depreciation
- Daily mark-to-market valuation with forward curves
- Basis spreads and hedge ratios by term
- VaR and Greeks calculation by portfolio segment
Commodities Trading Model: How the Template Works
This commodities trading model helps evaluate liquidity and trade finance capacity for a physical trading house. It simulates three segments (energy, metals, agriculture) and produces integrated financial statements.
The model is built around per-tonne margins, working capital cycles, and a demand-driven borrowing base. This overview explains its drivers, calculations, outputs, and practical use.
Operating Drivers: Volumes, Per-Tonne Margins, and Working Capital
The model's core driver is volume multiplied by gross margin per tonne for each commodity segment. This reflects how traders earn stable margins per tonne rather than volatile percentage margins.
- Revenue derives from volume times price, while gross profit uses volume times margin per tonne, so percentage margins shrink as prices rise. Working capital is driven by days sales outstanding, days inventory outstanding, and days payable outstanding, determining receivables, inventory, and payables.
- Inventory is split into readily marketable and non-RMI portions, which affects borrowing base eligibility and adjusted net debt.
Calculation Flow: From Revenue to Borrowing Base Drawdown
The calculation flow starts with segment volumes and margins, feeding into revenue, gross profit, and operating expenses. The bonus pool is based on pre-bonus operating profit, ensuring it reflects trading performance before financing costs.
- Working capital balances are computed from revenue and COGS using days metrics. The borrowing base eligible amount combines receivables and eligible inventory at advance rates.
- A pre-financing cash waterfall determines cash surplus or deficit, and trade finance drawdown fills only the deficit, capped by the eligible base. Interest on the facility uses the opening balance, avoiding circularity.
Outputs: Integrated Statements and Covenant Monitoring
The model produces a full set of integrated financial statements: income statement, balance sheet, and cash flow, all built from the drivers. The balance sheet includes a Day 0 opening column where share capital is derived to balance, preventing reconciliation errors.
- Outputs also include a ratios and covenants sheet that monitors current ratio, adjusted net debt to EBITDA, interest coverage, and borrowing base utilisation. A checks sheet validates that the balance sheet balances in every year and that all covenants pass, providing a clear pass/fail signal.
- These outputs help assess liquidity and compliance with lender requirements.
Practical Use: Evaluating Liquidity and Capital Needs
Practically, the model is used by lenders and traders to size borrowing base facilities and plan equity capital. It shows whether projected working capital needs can be funded within the available facility limit and whether covenant thresholds are expected to be met.
- The demand-driven drawdown ensures the facility is not over-drawn, revealing headroom and commitment fees on undrawn amounts. Users can adjust assumptions for volume growth, price escalation, margin per tonne, and working capital days to test different scenarios.
- This supports decisions about facility sizing, capital structure, and financial resilience.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a commodities trading model?+
It is a three-statement model for a physical commodities trading house with stable per-tonne margin, working capital schedules, and borrowing base facility mechanics.
Why margin per tonne instead of margin percentage?+
Commodity prices fluctuate widely while margin per tonne stays stable. Margin percentage is a misleading anchor when underlying prices move 20–40%.
How does the borrowing base facility work?+
Trade finance is drawn only as needed to fund working capital (receivables plus eligible inventory minus payables), with RMI inventory treated separately for eligibility.
Does it support stress scenarios?+
Yes. The model is built to flex commodity prices, working capital intensity, and covenant headroom across base, downside, and recovery scenarios.
Is this useful for lenders?+
Yes. Syndicated warehouse providers use this structure to size facility limits, monitor covenants, and stress credit through commodity cycles.
Have more financial modelling questions? Contact us
Related templates
Commodity Hedging Model
Structure and monitor hedges for commodity price exposure with effectiveness metrics.
FX Hedging and Portfolio Model
Currency exposure tracking and hedging strategy model for multinational companies with multi-currency revenue and cost bases.
Bond Trading Model
Fixed income trading desk P&L and risk analytics.
DCF Model
Discounted cash flow valuation model with explicit forecast, terminal value, and WACC.
