commodity risk management - guide
Post on 05-Sep-2015
9 Views
Preview:
DESCRIPTION
TRANSCRIPT
-
global Risk Center
IN PRACTICE GUIDEsix stePs to assess commoditY risK exPosure
-
About this RepoRt
this report and the associated workbook provide guidance for companies to better
understand and quantify the impact of commodity risks on earnings in order to improve an
organizations commodity risk management.
the supporting excel workbook is available by clicking on the pushpin icon. this guide is a
companion piece to Volatility not Vulnerability published by the Association for Financial
professionals (AFp) and oliver Wyman in october 2011. the article is available by clicking on
the pushpin icon or can be found online at www.oliverwyman.com.
CoRpoRAte ChAllenges in mAnAging Commodity Risk
developing management strategies (pricing, procurement and hedging) using a holistic risk-return perspective instead of just heuristics to make decisions
evaluating portfolios of potential mitigation strategies which have impact on internal diversification and aggregation in adequate depth
Automating the aggregation and integration of a wide range of data and assumptions (e.g., purchasing contracts, commodity forecasts, ability to pass-through)
Applying sufficient tools to engage suppliers on contract terms using a risk-return perspective and to develop innovative structures that are mutually beneficial
engaging champions and senior sponsors who are passionate about implementing long-term risk mitigation strategies such as revised contract structures or pricing changes
treating commodity price risk management as a margin stabilization process rather than a cost.
-
Global Risk Center
VOLATILITY, NOT VULNERABILITYTO SURVIVE SWINGS IN COMMODITY PRICES, COMPANIES MUST FIRST BUILD A RISK-MANAGEMENT FRAMEWORK
-
Historically, most businesses could simply withstand changes in commodity prices, given that the swings were usually temporary, cyclicaland manageable. However, structural changes in the global economy are creating wilder swings in commodity prices that are not only affecting short-term profits, but also long-term planning and investment. In this environment, every company must develop a formal risk management approach to counter the growing volatility in commodity prices. For corporate leaders, this means building the infrastructure, governance programs, and analytical capabilities that can help them better manage their exposure to commodities.
-
1. COMMODITY PRICES AND VOLATILITY
After a brief respite in 2009, the volatility that has come to define many commodity markets
roared back in the latter half of 2010much to the dismay of businesses for whom oil,
industrial metals, and other raw materials comprise a significant share of their costs. Crude
oil cracked the psychological barrier of $100 per barrel, major food price indexes reached
record highs in the first quarter of 2011, and base and industrial metals such as copper and
aluminum also reached new highs creating significant pain for buyers (see Exhibit 1). Even
where prices pulled back, the cost of many commodities remained higher than beforethe
result of structural shifts in supply and demand on both a local and global scale (For a closer
look at the causes of volatility see Exhibit 2).
These commodity shocks are not only cutting into corporate profits but are testing the
abilities of even the best businesses to plan for and invest in the future. Already in 2011, the
CEOs of consumer-facing companies as diverse as PepsiCo, Kraft, Kimberly-Clark, and Levis
have said they expect commodity price inflation to pose a significant challenge to continued
earnings growth over the next several years. Those in the middle of the value chain have a
tougher challenge as they cannot easily raise prices to recover lost margins. Few expect this
storm to pass quickly: The 2011 World Economic Forum Global Risks Survey found corporate
executives in agreement that a key risk they face in the coming decade is extreme volatility in
energy and other commodity prices.1
1 World Economic Forum. Global Risks 2011, 6th edition, January 2011. Oliver Wyman was a contributor to that report.
EXHIBIT 1: COMMODITY PRICES ARE SOARING INDEX
8
12
10
6
4
2
0
14
MULTIPLE OF INITIAL PRICEREAL PRICE HISTORY
1987 1991 1995 2003 20111999 2007
Crude Oil
Copper
Wheat
source: Datastream, 2011
Copyright 2011 Oliver Wyman 3
-
ExhIBIT 2: WhY ThE VOLATILITY?
CAUSE DESCRIPTION
Erratic weather Catastrophic weather events have affected production (2009 drought in Australia affected global wheat prices)
Emerging markets Growth in emerging markets has increased demand for food, energy, and raw materials. Global food output will have to rise 70% by 2050 to meet demand* while global energy demand is predicted to increase by 36% between 2008-2035
Speculation Emergence of commodities as an investment class (development of commodities-based ETFs)
Infrastructure spending Deteriorating infrastructure in developed countries and a lack of infrastructure in emerging economies hampers the physical flow of commodities
Supply-country strategies Development of market-distorting trade policies (Russian wheat export ban followed crop shortfall in 2010)
Purchasing-country strategies
Countries developing more sophisticated buying capabilities (Korea opened a grain-trading office in Chicago in 2011)
* Food and Agriculture Organization of the United Nations, World Summit on Food Security, November 2009 International Energy Agency, World Energy Outlook 2010
This volatility could persist for years, particularly given that governments appear willing to disrupt or intervene in markets, including halting exports. These collective forces have created pressure for corporations to develop strategies to mitigate this growing risk. While companies in agri-processing, oil refining, and wholesale electricity generation have developed formal approaches to trading and risk management programs, the majority of companies need to do moremuch more.
Given the likelihood of further volatility in commodity prices, every company must adopt an analytic risk framework based on a clear understanding of its exposure. This paper offers guidance on developing a plan for managing commodity risks that draws on the best practices of companies from around the world. The approach is built around the three pillars of a best practices program: governance, infrastructure, and analytics. The paper provides an in-depth review of the role of analytics and outlines a new systematic approach to managing commodity risk, illustrated through case studies.
ONE COMPANYS MOVES TO TAME ITS VULNERABILITY TO VOLATILITY
A leading industrial company failed to deliver its projected earnings due to a decline in the profitability of non-core energy activities. To measure the companys total exposure to energy volatilityand quantify the potential effect on future profits, it needed to develop an integrated energy risk profile. In other words, the company needed the ability to aggregate the energy exposure of each business unit and evaluate the effectiveness of existing mitigation efforts before it could truly understand net exposure.
The company analyzed commodity volatilities and correlations to produce a probabilistic analysis and derive EPS-at-risk estimates, demonstrating the portion of earnings that were vulnerable to these price volatilities. It then adopted a risk assessment tool capable of performing scenario analysis linked to alternative market states and specific events, integrating this discipline by training both operational and financial staff in Treasury, Procurement, Planning, and Operations. This helped the company to gain insights into the aspects of its energy exposure that were most sensitive to price movements under different future states, and thus were targets for mitigation efforts.
Copyright 2011 Oliver Wyman 4
-
2. COMMODITY RISK MANAGEMENT FRAMEWORK
DEVELOPING A COMMODITY RISK GOVERNANCE PROGRAM
A crude oil producer/marketer in Eastern Europe was recently seeking to enhance margins by creating regional physical trading capability. First, the Board required stakeholder alignment on a multi-dimensional risk appetite statement to guide risk and scale considerations. High level loss limits were linked to the risk appetite, and from these desk-level limits were calculated. By codifying the risk limits in policies, the equity usage, growth targets, and working capital requirements were made explicit and served to support all later decisions on the business expansion plan.
A structured commodity risk management framework is built around three pillars:
Governance, Infrastructure, and Analytics. Together, these pillars support both short-term tactical decisions and long-term strategic initiatives (see Exhibit 3).
GOVERNANCE
The first pillar of this framework is Governance in which an organization identifies the main
objectives of its commodity risk management program. Companies first create a risk
appetite statement. In this critical step, an organization defines its risk tolerance and
aligns it with its broader performance objectives. The risk appetite statement thus
establishes the basis of the organizations risk limits.
ExhIBIT 3: COMMODITY RISK MANAGEMENT FRAMEWORK
SHORT-TERM TACTICAL DECISION MAKING
LONG-TERM STRATEGIC DECISION MAKING
GOVERNANCE
Risk appetite and corporate objectives Risk limits Risk policy Delegations of Authority Decision frameworks
INFRASTRUCTURE
Reporting Accounting IT/systems Organizational design Human capital
ANALYTICS
Price forecasting Commodity exposure modeling Stress and scenario testing Hedge optimization
Copyright 2011 Oliver Wyman 5
-
INFRASTRUCTURE
The second pillar, Infrastructure, comprises the organizational structure, systems and
human capital that companies need to measure and manage risks. Organizations need to
assess whether they have the systems to capture and monitor the necessary data flows. The
organizational structure is important because the volatility in commodity markets requires
discipline and a tight alignment between managing cost- and revenue-related risks across
the enterprise. historically, these functions worked independently, but today that results in
inefficiencies or even conflicting actions by different internal teams. human capital is also
key. In some cases, a corporation may not have the experience or capabilities to manage
commodity risks effectively. While a company can outsource risk management to financial
institutions or other market participants, it pays a significant premium for that service and
potentially forfeits any upside potential. In the process, it gives its partner proprietary
information they can use to trade for solely their own benefit.
INCREASING EFFECTIVENESS ThROUGh IMPROVED INFRASTRUCTURE
The effects of steady growth were slowly compromising the risk management effectiveness of a major North American crude and distillates marketer/trader. Reporting timeliness was eroding, error rates were climbing, and managements confidence in risk control was decreasing, prompting a full review of the organizational design and processes. This revealed that the risk control function (middle office) was still performing much of the trade reconciliation, that trade details were transmitted by email, and that most reporting was done through spreadsheets.
Several straightforward organizational shifts and systems enhancements eliminated a number of bottlenecks, and enabled risk reporting down to the cargo level, by desk and counterparty.
ANALYTICS
The third pillar of a commodity risk management program is Analytics. Organizations
cannot assess their financial exposure to commodity price swings without robust analytical
tools. These analytics (or modeling) platforms support decision making at every level
by helping managers model the future paths of commodity prices, conduct stress- and
scenario-testing, and evaluate and optimize risk-return profiles using a range of price-risk
management strategies. however, analytical capabilities vary dramatically from firm to firm.
Companies should take a sequential approach to developing a robust analytics framework
that will support commodity risk management.
Copyright 2011 Oliver Wyman 6
-
ThINK LIKE A TRADER
Trading is viewed as a necessary evil by some corporations, given the connotations of excessive speculation and risk-taking with derivatives (in this case, commodity futures contracts). A recent review of annual reports reveals that many companies disclosing commodity positions caution that these are strictly for hedging purposes and that the company does not hold financial derivative positions for the purposes of trading
Whether they realize it or not, many companies are in fact speculating on commodity priceson both the cost and revenue sides. Procurement officers are tasked with getting the best price on purchases for the company and its business units. So the procurement staff commonly enters into forward, fixed-price arrangements to guarantee both supply and pricing. Just like traders, they have thus made a bet on future prices and concluded price risk management with that view in mind.
Companies need to accept that the days of a set it and forget it approach to risk management are over. That approach was fine when volatility was low and prices increased gradually over time. Today, companies need to empower designated executives to think like traders: Is this a good or bad price? Should I buy more or run down inventory? Since most companies arent traders by nature, they need to create a trading playbook that is integrated with their overall game plans.
First, a company must understand its commodity risk profile. This helps the organization
assess its net exposure to commodity pricesand their inevitable volatilityacross
business and customer segments. Detailed analysis also allows the organization to then
develop long-term strategies to mitigate commodity-related risks.
A commodity risk profile provides a common understanding for senior management and
a fact-based foundation for evaluating the effectiveness of risk-mitigation actions. With
this knowledge, management can determine if the companys current commodity risk
exposure is within its risk tolerance and whether it has communicated these expectations
to stakeholders. This analysis also helps the management team evaluate different risk
management strategies. It promotes risk mitigation at the portfolio level, which helps
reveal any risks lurking in individual business units or departments. In short, this analysis
will allow the company to optimize risk-return positioning.
ThE NET ExPOSURE ChALLENGE
Determining true net exposure requires consideration of several factors: gross commodity exposure, existing risk mitigations, foreign exchange flows, demand sensitivity and the interactions among these. This can be challenging for an enterprise with wide-spread operations or multiple product lines. Typically, each factors net impact at the corporate level is first assessed and any offsets across factors can then be accounted for.
Copyright 2011 Oliver Wyman 7
-
To manage commodity price risks in ways that are consistent with broader corporate
objectives, companies need a robust set of analytic tools to calculate the current exposure.
Exhibit 4 offers a six-step analytical approach that companies can use to determine the
effect of commodity price risks and mitigation efforts on key financial metrics.
The first step in the analytical framework requires management to build a forecast
of commodity prices using simulations or other techniques. The company should
complement these forecasts with an analysis of alternative price outcomes based on stress
events to understand fully how prices may evolve.
In the second step, the management team estimates the volume of future commodity
purchases across the enterprise. In Step 3, this demand forecast is combined with
price projections to determine the firms gross commodity exposure. In Step 4, the risk
management strategies already in place are identified. Then, in Step5, these are applied
against the gross exposure to generate a net commodity exposure for the enterprise. The
process ends with Step 6, the creation of a holistic, company-wide risk profile with the
sensitivities in price projections identified in Step 1 used to explore the potential impact on
EBITDA, debt covenants, and other financial metrics.
Copyright 2011 Oliver Wyman 8
-
ExhIBIT 4: CREATING A hOLISTIC COMMODITY RISK PROFILE
Use analytic engines to simulate potential commodity price pathways (data driven)
Incorporate market context and paradigm shifts/scenario analysis ( judgment driven)
Integrates historical patterns, market intelligence, and fundamental analysis
Unifies disparate views of expected and high/low commodity price scenarios across organization
Incorporates interrelationships and correlations between commodities and currencies
STEP ANALYSIS BENEFITS
Centralizes commodity requirements across business units and geographies
Enables testing of alternative commodity purchase requirements using price elasticity analysis
Determine expected commodity purchase volume based on sales expectations
Gives context of expected commodity exposure compared to P&L and other risks
Accounts for natural hedges in the portfolio
Shows shifts due to changes in business mix, commodity price expectations
Calculate expected commodity exposure (e.g., price multiplied by volume)
Brings together and coordinates dierent functions of the organization (e.g., Strategy, Procurement, Treasury) and geographies
Sets understanding of risk management options for key commodity exposures
Define options for managing commodity price risk
Centralize risk management options undertaken across organization
Helps management assess the eectiveness of the risk management portfolio vs. desired exposure
Provides understanding of how commodity price risk flows through the organization
Calculate exposure after incorporating current risk management portfolio
Enables objective evaluation and comparison of a range of risk management strategies
Promotes risk mitigation at a portfolio level to minimize sub-optimal risk mitigation at individual business unit or department level
Provides view of the earnings impact from commodity prices at dierent levels of probability
Determine impact of commodity price projections and exposure on financial metrics (e.g., EBITDA, cash flow, debt covenants)
COMMODITY PRICEPROJECTIONS
SALES, PRICING,ANDPURCHASE VOLUMES
GROSS EXPOSURE
RISK MANAGEMENTPORTFOLIO
NET EXPOSURE
HOLISTIC COMMODITYRISK PROFILE
Source: Oliver Wyman
Copyright 2011 Oliver Wyman 9
-
IMPROVING COMPETIVENESS WITh A STRATEGIC RESPONSE TO COMMODITY VOLATILITY
A global food ingredient processor was losing sales to much smaller competitors solely because those firms were much more responsive to the highly volatile product price. By analyzing the impacts of its forward sales process and market price uncertainty, a new strategy was developed to migrate customers to shorter term contracts and to begin linking prices to market indices which immediately improved the firms competitive positioning.
STRATEGIES FOR MANAGING COMMODITY RISK
With a well-defined risk profile and an understanding of its ability to manage risk, a
company can build a plan that matches its net exposure to commodities with its tolerance
for risk. To manage commodity prices in the short term, companies generally have three
tools at their disposal:
Product pricing identifying customer segments where the company has the ability to raise prices or create pricing structures that mitigate risk
Procurement contract structuringdeveloping innovative risk-sharing contracts with suppliers
Financial hedgingusing financial instruments for hedging to reduce overall risk exposure
The effectiveness of these short-term strategies depends on the size of the organizations
exposure to a given commodity and the commodity itself. For example, financial hedging
works best in commodity markets that are liquid (e.g., energy products such as crude oil and
natural gas, or agricultural products such as wheat and corn). Meanwhile, passing higher
commodity costs to customers through price increases is often ineffective in competitive
markets such as consumer products. however, when commodity cost increases are
significant and widespread, pass-through pricing might be more viable. Some companies
have taken creative approaches to raising prices. For instance, a number of consumer
products companies have reduced the volume of productwhile keeping the same
package sizeto maintain margins in the face of higher commodity costs. Other firms have
substituted cheaper ingredients to lower their net product costs.
Over the long term, volatility in commodity prices affects the behaviors of consumers,
companies, and their suppliers. In response, some companiesand even countries--have
embedded commodity risk strategies into their long-term strategic plans.
Copyright 2011 Oliver Wyman 10
-
CONCLUSION
Large and sustained commodity price swings are reshaping whole industries. This means
that commodity risk management can no longer be considered the sole responsibility of
the procurement or finance staff. With rising commodity prices affecting both the short-
term earnings and long-term strategies, it is imperative that C-level executives develop a
deeper understanding of how to mitigate these risks. Developing a structured commodity
risk management program built around the components outlined here is crucial. Given
the new realities of higher prices and more volatility in commodities, organizations must
integrate commodity risk management into their day-to-day operations. They also must
build it into their long-term strategies to ensure the viability of the firm itself.
ABOUT THE AUTHORS
MICHAEL J. DENTON
Partner, Global Risk & Trading, Oliver Wyman
Michael J. Denton, PhD, is a New York based Partner in Oliver Wymans Global Risk & Trading practice, with specialized experience in
energy, agriculture, and the commodities risk and trading sector. As a practitioner and as a consultant, he has worked extensively in market
risk modeling, portfolio dynamics, and risk-based decision frameworks. Recent projects have focused on due diligence evaluations,
competitive contracting, and counterparty credit risk mitigation.
ALEX WITTENBERG
Partner, Global Risk & Trading, Oliver Wyman
Alex is the Managing Partner of Oliver Wymans Global Risk Center and has over 20years of cross-industry experience in risk management
advisory and risk transfer solutions. Alex specializes in integrating risk into strategic decision-making and financial performance, designing
risk governance for Boards and Management, and developing corporate risk monitoring, mitigation and transfer frameworks.
Copyright 2011 Oliver Wyman 11
-
Copyright 2011 Oliver Wyman. All rights reserved. This report may not be reproduced or redistributed, in whole or in part, without the written permission of OliverWyman and Oliver Wyman accepts no liability whatsoever for the actions of third parties in this respect.
The information and opinions in this report were prepared by Oliver Wyman.
This report is not a substitute for tailored professional advice on how a specific financial institution should execute its strategy. This report is not investment advice and should not be relied on for such advice or as a substitute for consultation with professional accountants, tax, legal or financial advisers. Oliver Wyman has made every effort to use reliable, up-to-date and comprehensive information and analysis, but all information is provided without warranty of any kind, express or implied. Oliver Wyman disclaims any responsibility to update the information or conclusions in this report. Oliver Wyman accepts no liability for any loss arising from any action taken or refrained from as a result of information contained in this report or any reports or sources of information referred to herein, or for any consequential, special or similar damages even if advised of the possibility of such damages.
This report may not be sold without the written consent of Oliver Wyman.
www.oliverwyman.com
Oliver Wyman is a leading global management consulting firm that combines deep industry knowledge with specialized expertise in strategy, operations, risk management, organizational transformation, and leadership development. Oliver Wymans Global Risk Center is dedicated to analyzing increasingly complex risks that are reshaping industries, governments, and societies. Its mission is to assist decision makers in addressing risks by combining Oliver Wymans rigorous analytical approach to risk management with leading thinking from professional associations, non-governmental organizations, and academic institutions. For further information, please visit www.oliverwyman.com/globalriskcenter.
The Association for Financial Professionals (AFP), headquartered outside Washington, D.C., serves a network of more than 16,000members with news, economic research and data, treasury certification programs, networking events, financial analytical tools, training, and public policy representation to legislators and regulators. AFP is the daily resource for the finance profession.
For more information, please contact:
MIChAEL J. DENTON
Partner, Global Risk & Trading, Oliver Wyman +1.646.364.8423 michael.denton@oliverwyman.com
ALEx WITTENBERG
Partner, Global Risk & Trading, Oliver Wyman +1.646.364.8440 alex.wittenberg@oliverwyman.com
BRIAN T. KALISh
Director, Finance Practice Lead +1.301.961.6564 bkalish@afponline.org
Oliver WymanFile AttachmentCommodity volatility.pdf
-
intRoduCtion
given the current and future trends in commodity prices and volatility, every company must
better understand its true commodity exposure. Companies can often be squeezed by
rising and volatile commodity input prices that cannot be passed along to customers in
their entirety. A commodity risk management program can help. not all organizations can,
or should, adopt the sophisticated mechanisms of a pure commodity business. however,
most organizations, particularly those in the middle of the value chain, can improve their
commodity risk analytics.
exhibit 1: CompAnies ARe ChAllenged by Rising Commodity volAtility
Output prices
Competitive pressures Limited ability to pass
through higher costs
COMPANY
Understand current forecasts (starting position and price curve)
Trace impact of price volatility to earnings
Align commodity risk management program strategy with corporate objectives
Commodity risk management program strategy, instruments, etc.
Input prices
Increasing volatility Increasing absolute price Breakdown in correlations
the starting point is to understand the companys holistic commodity risk profile using
analytics and modeling tools. A holistic commodity risk profile helps the organization assess
its individual and net exposure to commodity pricesand the inevitable volatilityacross
business and customer segments on a forward-looking basis. the profile provides a common
understanding for senior management and a fact-based foundation for evaluating the
effectiveness of current risk-mitigation actions and alternative risk management strategies.
With this knowledge, management teams can determine if current commodity risk exposure
is within the companys risk tolerance and communicate these expectations to stakeholders.
it also helps to promote risk mitigation at the portfolio level by identifying the most
important drivers of overall risk and ensuring the capture of any offsetting risks that may be
present. in short, this analysis will allow the company to optimize risk-return positioning.
this In Practice Guide provides an overview of a stepwise approach and analytic processes
necessary to develop an organizations net commodity exposure.
Copyright 2012 oliver Wyman 3
-
six steps to deteRmining Commodity exposuRe And impACts
exhibit 2 provides an overview of a six-step analytical approach to determine the impact of
commodity price risks on key financial metrics.
exhibit 2: six steps to CReAte A holistiC Commodity Risk pRoFile
6. HOLISTIC COMMODITY RISK PROFILE
5. NET EXPOSURE
4. RISK MANAGEMENT
PORTFOLIO
3. GROSS EXPOSURE
2. SALES, PRICING AND COMMODITY
PURCHASE VOLUMES
1. COMMODITY PRICE
PROJECTIONS
STEPS
ANALYSISUse analytic engines to simulate potential commodity price pathways (data driven)
Incorporate market context and paradigm shifts/ scenario analysis (judgement driven)
Calculate expected commodity exposure (i.e. price multiplied by volume)
Define options for managing commodity price risk
Centralize risk management options undertaken across organization
Calculate exposure after incorporating current risk management portfolio
Determine impact of commodity price projections and exposure on financial metrics (e.g. EBITDA, cash flow, debt covenants)
Determine expected commodity purchase volume based on sales expectations
Copyright 2012 oliver Wyman 4
-
step 1: Commodity pRiCe pRojeCtions
CoRe AnAlysis user-defined, baseline commodity prices and volatilities for a predefined set of commodity
inputs are incorporated into a standard simulation process to generate a distribution of
potential future price paths for each commodity
outputs A distribution of terminal price values for each commodity and time horizon
beneFits integrates historical patterns, market intelligence and fundamental analysis
unifies views of commodity price scenarios across the organization
incorporates interrelationships and correlations between commodities and other price risk factors (e.g., currencies)
the first step in the analytical framework requires
management to build assumptions for commodity
prices in the future using simulation or other techniques.
the management team should complement these
forecasts with an analysis of alternative price outcomes,
based on stress events, to understand fully how prices
may evolve.
A simple price simulation model is presented in the
attached workbook (accessed through the pushpin
icon). the inputs for these simulations are located on
the tab labeled interface under the heading price
and volatility. the user defines base case prices and
volatilities for each commodity and forward period
(i.e., 1st quarter, 2nd quarter and so on). the simulated
terminal value prices or outcomes for each commodity
and time horizon are shown on the tab labeled Cmdy
price proj. each of these values reflects possible future
outcomes for commodity prices based on three factors:
mean price, volatility and time.
the outputs of the simulations for each commodity are
summarized on the interface page in the Commodity
price projections section. in each case, 5th and 95th
percentile outcomes are shown along with the baseline
price scenario over the course of the time period to
provide a richer understanding of possible outcomes
versus a single, static forecast. not surprisingly, the
uncertainty of price forecasts grows with the increasing
time horizon.
Copyright 2012 oliver Wyman 5
ReadMe
Oliver Wyman: Six Steps to Assess Commodity Risk Exposure
Read Me - File Overview
Overview
The workbook has been prepared as a supplement to the In Practice Guide, "Six Steps to Assess Commodity Risk Exposure," prepared by Oliver Wyman for AFP members and available at www.afponline.org. The purpose of this workbook is to provide a simple tool for illustration purposes only to better understand and quantify commodity risk impacts on earmings. The example focuses on a corporation which utilizes key commodities including natural gas, diesel fuel, sugar, electricity and aluminum. The components of the workbook include a primary "Interface" worksheet as well as support worksheets which house the calculations used to generate outputs on the "Interface" sheet. Note that all user inputs and outputs are exclusively contained in the "Interface" sheet - all other sheets are for reference only. Cells in white in the "User Inputs" block represent input cells (prices, volatilities and hedge designation). Any changes in outputs are generated by using the "Calculate" button.
The "Interface" worksheet receives inputs and provides outputs for a probabilistic earnings forecast model and illustrates the impacts of commodity prices and volatilities on the earnings profile. In particular, each of the outputs reflects one of the steps in the 6 step process. Outputs include:
(1) Commodity price projections - User-defined, baseline commodity prices and volatilities for a pre-defined set of commodity inputs are incorporated into a standard simulation process to generate a distribution of potential future price paths for each commodity
(2) Estimated commodity volumes - A predefined set of commodity volumes reflecting commodity demand over time
(3) Gross commodity exposure - User-defined baseline prices are combined with simulated price paths and volumes to generate commodity exposure - measured as the difference between the 95th percentile worst case outcome and the baseline, expected outcome (4) Net commodity exposure - Gross commodity exposure is adjusted for any financial or physical hedges for each of the commodities. Hedges are assumed to "lock in" the commodity at the baseline, expected price. Price correlations between commodities are taken into account.
(5) Holistic commodity risk profile - Commodity exposures are combined with predefined estimates for revenues and other fixed costs/SGA to illustrate both positive and negative impacts on the earnings profile
Key assumptions
Key assumptions in the model include the following: - A GBM process is used to simulate all of the commodity price paths - Correlations are predefined and fixed resulting in a static set of shocks used in the simulation process- User defined volumes and revenues are static- User defined prices and volatiliities are used as baseline assumptions for each quarter
Interface
Oliver Wyman: Six Steps to Assess Commodity Risk Exposure
USER INPUTSRISK PROFILE OUTPUTS
Date1/1/125. Holistic commodity profile and impact on earnings (baseline, 95% confidence band, 5% confidence band)
Price and VolatilityBase5th %-ile95th %-ile
Q1Q2Q3Q42012Un hedgedHedgedImpactUn hedgedHedgedImpact
PriceVolatilityPriceVolatlityPriceVolatilityPriceVolatilityof hedgeof hedge
Sugar ($/lbs)$0.2070%$0.2170%$0.2270%$0.2370%Sales2,0692,0692,0690.02,0692,0690.0
Natural gas ($/MMBTU)$5.7348%$5.7348%$5.7348%$5.7348%Cost of Goods Sold
Electricity ($/MWh)$62.2769%$62.2769%$62.2769%$62.2769%Fixed Costs4804804800.04804800.0
Diesel ($/gal)$2.2437%$2.2437%$2.2437%$2.2437%Sugar11958580.03113110.0
Aluminum ($/KTon)$18.7832%$18.7832%$18.7832%$18.7832%Natural gas2371121120.03513510.0
Electricity24987870.03813810.0
Exposure risk level95%Diesel1551011010.01661660.0
Aluminum13191910.02482480.0
Commodity volumes (in millions)Variable Costs8924504500.01,4561,4560.0
Q1Q2Q3Q4Total COGS1,3729309300.01,9361,9360.0
Sugar (lbs)128.0134.0141.0150.0Gen. & Admin. Exp.3603603600.03603600.0
Natural gas (MMBTU)9.610.110.611.1EBITDA3377797790.0(228)(228)0.0
Electricity (MWh)1.01.01.01.0
Diesel (gal)16.016.817.618.5
Aluminum (KTon)1.61.71.81.94. Net commodity exposure 2012 by commodity (in millions $)
Other financial statement assumptions
Q1Q2Q3Q4
Sales volume (in millions)3200336035283704
Sales price ($)0.150.150.150.15
Fixed costs ($ millions)120120120120
G&A Exp ($ millions)90909090
Hedge Selection
Futures
SugarNo
Natural gasNo
ElectricityNo
DieselNo
AluminumNo
3. Gross commodity exposure 2012 (in millions $)
a) By quarterb) By commodity
All exposures reported as the difference between the 95th percentile highest exposure and the expected, baseline gross commodity exposure
2. Estimated commodity volumes for 2012 by quarters (variable units, in millions $)
1. Commodity price projections (baseline, 95% confidence band, 5% confidence band)
Interface
1
#REF!
Cmdy Price Proj
2012 Gross Exposure2012 Gross Exposure124.418764137136.7693617635264.858061148378.621793223380.3244438443
560.5736599794124.418764137SugarSugarSugarSugarSugar
423.8042982159136.7693617635Natural gasNatural gasNatural gasNatural gasNatural gas
158.9462370676264.8580611483ElectricityElectricityElectricityElectricityElectricity
80.324443844378.6217932233DieselDieselDieselDieselDiesel
080.3244438443AluminumAluminumAluminumAluminumAluminum
Invisible
Grey
Sugar
Natural gas
Electricity
Diesel
Aluminum
Correlations
0.11070873170.34527157280.2
0.08545521290.40508272140.21
0.06956460390.46167296350.22
0.0610346050.53404686860.23
5%
95%
Mean:
Sugar
Sales, Pricing, Volumes
18.594761323620.970001661238.128995505611.349690950810.5940968217
26.141084666227.820295760263.644092627215.977750784116.4064511715
34.075887859437.468427825876.165867101223.889316659523.0844682326
45.607030287850.510636516286.919105914527.40503482930.2394276185
Sugar
Natural gas
Electricity
Diesel
Aluminum
Gross Exposure
1
#REF!
EPS-at-risk
Risk Mgmt Portfolio
1
#REF!
EBITDA
Simulation Net Exposure
4.06886109037.91546800285.7310928297
3.27420301868.48557755845.7310928297
2.88870253039.26585017185.7310928297
2.667994756410.2816006245.7310928297
Diesel
Electricity
Natural gas
Crude oil
Price change
EPS impact
Invisible
Grey
Sugar
Natural gas
Electricity
Diesel
Aluminum
2012 Gross Exposure
2012 Gross Exposure
2012 Gross Exposure
2012 Gross Exposure
2012 Gross Exposure
2012 Gross Exposure
2012 Gross Exposure
Sugar
Sugar
Sugar
Sugar
Sugar
Sugar
Sugar
Natural gas
Natural gas
Natural gas
Natural gas
Natural gas
Natural gas
Natural gas
Electricity
Electricity
Electricity
Electricity
Electricity
Electricity
Electricity
Diesel
Diesel
Diesel
Diesel
Diesel
Diesel
Diesel
Aluminum
Aluminum
Aluminum
Aluminum
Aluminum
Aluminum
Aluminum
191.2572265773
113.8117311849
131.9658025171
11.406289411
116.2397994146
373.4236225276
191.2572265773
259.6118913427
113.8117311849
127.6460888256
131.9658025171
116.2397994146
11.406289411
0
116.2397994146
5%
95%
Mean:
Natural Gas
Net Exposure
34.4141498094100.399538895762.2705433902
26.4877650618125.914636017362.2705433902
21.9723962528138.435867101262.27
15.528763279149.189649304662.2705433902
5%
95%
Mean:
Electricity
Fin Statement
1.65081892052.95381393172.2444582473
1.46726970383.19551484162.2444582473
1.30747670023.60180578482.2444582473
1.19538910333.72581148132.2444582473
5%
95%
Mean:
Diesel
Price shock contuity
13.476902836925.401310513618.78
11.972165240128.427959942518.7771063122
10.634717634931.601810885918.7771063122
9.583960912434.692594532518.7771063122
5%
95%
Mean:
Aluminum
Graph support
1289.61161.6
13410.1116.81.7
14110.6117.61.8
15011.1118.51.9
Sugar
Natural gas
Electricity
Diesel
Aluminum
-16.1353539101132.656980337761.1516334878
-42.1367106558179.814703640376.0774397443
-64.2177203637214.439926492891.8591594906
-105.0874848209252.1161989213108.0153466317
95%ile
5%ile
Earnings statement
Price Simulations
Commodity prices are simulated through geometric brownian motion and utilize correlated random shocks based on an input correlation matrix
SugarNatural gasElectricityDieselAluminum
Period:Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4
Mean:0.200.210.220.235.735.735.735.7362.2762.2762.2762.272.242.242.242.2418.7818.7818.7818.78
Volatility:0.700.700.700.700.480.480.480.480.690.690.690.690.370.370.370.370.320.320.320.32
Time:0.250.500.751.000.250.500.751.000.250.500.751.000.250.500.751.000.250.500.751.00
Simulated
5%0.110.090.070.064.073.272.892.6734.4126.4921.9715.531.651.471.311.2013.4811.9710.639.58
95%0.350.410.460.537.928.499.2710.28100.40125.91138.44149.192.953.203.603.7325.4028.4331.6034.69
Std Dev:0.070.100.120.161.351.862.382.7021.0030.7437.1345.210.440.620.770.913.394.935.967.07
10.130.120.110.094.974.154.143.7955.6654.6049.5640.772.382.492.432.2720.1420.5419.9420.24
20.210.180.180.185.385.155.004.8952.3150.9045.9646.232.232.202.312.0316.1314.5014.5114.09
30.100.080.070.064.464.133.623.1848.1041.0437.0530.502.172.272.182.1215.2014.5612.6812.13
40.120.100.090.084.994.664.403.9946.7942.8941.7536.302.272.222.022.0715.6414.0713.2813.21
50.140.120.140.104.794.934.163.6150.3142.0938.3936.832.252.222.282.3614.9413.7412.5912.05
60.250.280.280.357.747.868.499.6088.4789.37105.90131.502.632.852.803.1321.4823.3223.7125.13
70.210.240.230.257.047.828.138.2795.26103.96111.49121.512.772.963.123.5017.8517.4117.4217.29
80.150.150.120.115.344.844.584.1156.9153.4852.9654.691.861.791.541.6121.0121.3422.5721.46
90.170.170.160.154.343.523.353.0936.8726.5922.0815.571.671.471.261.2015.0713.7712.4811.73
100.290.330.370.404.173.893.372.9648.6844.0838.0934.011.811.671.431.4515.3813.9412.7911.58
110.110.080.060.054.494.083.543.3447.5839.3535.2432.361.951.911.761.5318.7117.7717.9517.51
120.200.200.220.236.656.687.126.3792.15113.63138.42143.272.222.152.151.8418.7318.1818.0918.39
130.210.210.200.237.838.428.429.0788.1195.6386.8692.202.462.342.462.5421.2421.9723.0723.36
140.160.140.120.116.035.905.706.4963.0160.1058.1957.712.212.102.202.1321.3922.8122.2624.47
150.250.290.320.346.807.147.127.5581.6884.2490.9485.822.692.903.092.9919.9819.9619.6619.27
160.230.260.230.277.358.058.718.3673.1783.1985.9992.602.562.672.653.1321.5322.5522.6822.33
170.160.150.140.137.067.928.718.2894.5794.9695.57117.332.342.462.322.5417.9917.1016.4915.44
180.210.240.210.216.437.056.996.7975.4587.0678.8879.602.342.362.322.3417.9516.8216.8816.43
190.170.160.160.157.917.958.8110.53104.20128.31148.42148.802.762.943.023.0126.4530.6131.5835.17
200.120.090.080.085.455.255.324.8349.6449.7844.1837.342.522.462.402.4217.7317.9317.9215.50
210.260.280.310.339.8011.7315.3116.23129.34170.45205.68255.873.564.325.045.5521.6822.0122.2524.30
220.220.240.220.245.394.895.134.7453.2348.3940.7839.742.071.952.041.8715.2513.7412.4111.60
230.160.150.140.115.295.064.954.1457.6161.0353.6546.952.082.031.992.0120.9020.9520.8921.03
240.250.280.270.338.8910.3912.1613.04116.17153.34167.48192.962.852.883.213.1825.5928.4232.0034.67
250.220.220.230.246.176.146.416.6173.8177.4177.3473.552.011.831.811.6719.5219.9620.1719.82
260.300.400.360.476.386.466.116.4476.3183.8175.9283.002.001.861.761.7318.8118.4717.4618.05
270.150.140.120.135.054.654.224.3361.1159.4053.3848.221.701.521.311.3415.4013.7313.6812.91
280.160.130.120.124.664.374.253.6848.3743.3937.8630.722.082.032.011.7217.9018.1117.3916.86
290.110.080.060.064.223.482.902.7646.9539.9232.1327.351.741.651.421.3116.8916.4516.5815.18
300.170.160.150.145.485.134.855.0460.6660.5053.8253.142.212.272.182.0416.4014.9514.8513.75
310.160.140.130.135.515.245.044.2057.2249.4844.4240.391.961.751.621.5921.0021.3321.6323.68
320.290.300.340.384.984.433.953.4941.4732.9829.7425.121.941.791.691.5115.1713.6013.1212.09
330.230.240.270.296.786.957.247.3278.6882.3084.4384.492.552.752.652.9427.4531.4234.9238.08
340.350.400.490.547.828.439.259.7977.5578.2181.6688.652.883.073.443.3419.9720.5920.9522.39
350.120.100.090.085.515.325.654.8869.8573.6368.7261.152.122.192.012.0419.5620.3318.6618.58
360.360.510.600.747.488.219.029.0884.3988.8395.35112.482.402.482.312.4528.2031.9137.6642.38
370.180.160.150.154.313.893.383.0341.0734.3431.5725.201.871.711.511.4213.4812.0010.679.59
380.250.280.300.295.816.115.255.4758.3049.6246.8143.742.532.612.692.7022.0723.0723.3125.11
390.220.240.240.276.826.747.036.9062.8967.0761.8857.352.332.492.442.5020.9320.9122.3523.35
400.250.300.350.337.688.238.349.69101.34125.86138.76156.682.923.113.713.9223.5227.1427.5128.83
410.250.300.270.317.097.228.138.5880.6681.2890.7582.882.712.903.053.1917.2415.8216.0215.58
420.120.100.080.062.962.161.691.5123.1014.359.867.711.501.211.090.9012.5310.549.407.89
430.180.170.160.147.087.407.768.8889.39106.41100.54106.322.732.892.953.1619.3719.3219.9419.38
440.160.140.140.144.173.583.182.8843.7334.2028.9725.431.791.611.451.2818.3718.4918.1117.83
450.240.260.260.264.684.233.933.7048.2038.7834.3628.252.082.031.881.6917.2416.7217.2116.91
460.190.200.200.195.766.376.106.1065.3365.9866.6667.862.042.071.811.8119.4619.8820.7819.28
470.080.050.040.043.082.471.921.7726.3718.9814.089.721.331.050.860.7511.269.258.157.39
480.310.360.380.436.817.747.878.6057.8260.0860.5456.912.592.622.652.7218.4218.3418.0516.57
490.150.150.130.145.885.815.905.8276.1292.2983.7785.732.021.901.821.7818.3718.3318.1517.62
500.160.140.120.104.093.462.962.7334.4827.2824.3316.801.651.581.391.3413.3611.519.899.56
510.220.210.240.226.316.546.286.5665.1262.3868.6357.332.352.372.282.4521.0621.8821.7423.10
520.140.130.110.115.445.444.915.0560.0752.4348.2349.221.881.771.661.5518.5118.9917.7118.06
530.170.160.160.147.969.1910.2910.80100.35126.87125.87169.052.903.193.683.5324.3228.0429.1932.92
540.360.450.510.537.518.059.549.1170.5076.4375.1073.162.682.832.903.1721.5122.1223.6324.17
550.120.090.080.063.532.822.282.0832.6824.4619.8914.811.571.291.121.1111.559.688.496.85
560.160.140.130.144.654.093.713.3740.0430.1826.2723.062.132.122.141.9517.2316.4415.3915.73
570.350.440.570.768.689.4110.8511.27108.66146.32174.31200.892.632.922.893.1827.2333.3036.1838.36
580.250.290.280.326.117.026.647.2057.7362.8853.4657.872.242.152.012.0816.9516.0415.0114.66
590.150.150.130.116.416.946.826.7269.3270.9974.7269.622.532.832.942.8219.5519.8819.1919.87
600.130.120.100.093.702.982.752.4130.2322.3819.4714.411.421.171.000.8515.2413.7313.3511.03
610.220.240.220.257.157.767.848.4972.8180.6187.2185.622.662.762.832.8121.0421.8721.3021.21
620.130.110.110.095.695.305.074.9353.4348.9045.3441.222.322.252.352.4414.3212.8311.419.91
630.130.110.100.084.634.243.543.4638.3832.6925.4022.231.921.781.651.5919.1519.0819.5218.90
640.140.130.110.106.656.856.747.1172.5475.7782.4273.892.762.843.123.4319.0218.7717.8418.81
650.260.290.350.367.918.448.9810.0897.27109.40127.87134.833.013.353.513.7124.5828.5730.2231.26
660.160.150.150.134.564.013.763.4140.1830.1126.7321.471.671.501.311.3317.3516.3115.1413.58
670.230.230.250.287.018.298.488.1684.9590.0197.5499.792.953.153.443.7218.8119.7519.6819.60
680.140.110.100.083.622.802.372.1833.1522.2817.5713.791.721.571.391.2515.0313.5113.0910.81
690.140.140.130.124.694.564.243.9640.9532.3527.1624.692.322.182.352.1518.4617.8317.6917.59
700.190.200.190.186.406.746.706.7267.8563.0464.1961.862.502.472.472.4120.6221.7524.2124.30
710.200.210.230.226.676.777.077.5281.4683.2588.7983.412.372.412.362.4220.9322.7522.3222.74
720.190.160.170.166.456.376.816.0864.3262.6763.6455.362.763.153.093.3816.9517.3016.8917.02
730.220.240.260.267.387.888.318.6082.0685.6494.1987.043.213.623.774.4020.8223.0522.8122.63
740.120.100.090.075.064.704.364.3152.4347.5846.8739.991.941.781.621.4817.2916.0815.7416.32
750.280.300.340.397.057.268.357.9564.0867.5960.9457.392.702.863.013.2619.3719.5418.9619.22
760.230.240.210.216.827.107.217.8878.8878.5889.0083.403.003.383.603.9221.7022.1222.9524.79
770.370.430.460.537.288.468.798.4586.0383.7697.76103.682.372.492.632.6119.9421.6321.8522.64
780.250.250.240.316.757.077.647.5186.0999.4096.32111.282.602.662.672.7520.3721.1620.3320.58
790.280.300.370.385.635.385.385.3960.6964.2857.3253.822.071.981.781.6919.9720.5121.4621.50
800.210.200.240.236.246.496.346.4956.6456.2848.6248.652.492.762.772.7919.9119.7820.4220.79
810.360.490.580.756.827.638.218.2186.88100.0296.62115.952.562.632.672.7225.3928.2732.2436.03
820.250.280.270.314.594.043.603.4142.5136.5330.9027.162.182.092.132.1721.1321.3922.2021.71
830.320.400.450.585.655.345.725.1954.9250.6344.5741.352.001.891.891.7613.9512.4411.6410.68
840.200.180.180.186.246.586.936.2067.9270.8764.8668.222.292.482.402.3518.7218.5718.0017.82
850.190.190.190.185.685.054.914.7960.7756.5654.3557.572.031.831.741.6215.0313.1012.3411.58
860.250.280.290.318.229.029.0510.2796.57117.30122.87139.473.714.434.945.6721.4622.1924.3224.37
870.170.170.170.174.594.423.703.5943.5836.1029.9525.072.111.981.921.8418.5018.3418.6018.86
880.150.150.120.125.214.724.284.1056.9456.5345.9742.342.372.512.402.3717.2516.2615.7214.14
890.170.150.160.164.393.903.563.1942.0232.7829.3827.411.651.401.351.1513.0210.879.498.34
900.290.350.420.446.386.637.588.2283.6094.2797.5890.922.722.943.083.1722.9423.9226.1226.39
910.110.100.080.075.104.964.674.3256.6750.2541.5040.242.212.042.001.9320.3721.3522.3522.28
920.190.190.170.204.684.524.123.8646.8137.8434.7630.302.021.891.811.7315.5013.2512.7212.04
930.090.070.050.044.874.143.883.9254.7044.1439.9034.222.162.082.041.9719.3619.1820.1320.08
940.120.110.090.085.255.064.774.5554.6151.9741.8235.412.072.141.831.9317.4916.6416.1315.59
950.140.120.110.106.536.816.237.2087.4593.44106.28113.022.923.163.293.4723.6627.5028.0630.56
960.160.160.140.145.345.395.044.6860.8756.3760.3451.872.342.412.392.4218.2117.7717.4916.62
970.110.090.070.064.163.292.902.6836.1128.9826.2919.451.861.791.581.4815.6013.9713.3511.68
980.260.310.320.387.037.648.018.5374.6579.4079.7164.892.642.802.833.0316.7914.9614.1113.93
990.210.210.220.215.465.104.794.8958.5152.7657.8748.322.322.222.182.3717.4517.0616.6417.48
1000.160.140.120.115.715.625.945.2362.3861.4963.7563.922.612.712.852.9020.8421.4823.0222.67
Simulation of commodity prices provides a richer understanding of possible price outcomes vs. a single, static forecast. Simulations also support analysis of possible physical and/or financial hedging strategies
Disclaimer: The calculation methodology is intended to support an illustrative, high-level example of risk exposure determination. More robust and accurate methods should be used to determine forward price evolution and subsequent risk measurement and mitigation.
Correlations
Correlated random shocks generated from correlation matrix
CorrelationsSugarNatural gasElectricityDieselAluminum
Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4
SugarQ11.000.990.990.990.650.650.650.650.550.550.550.550.470.470.470.470.430.430.430.43
Q20.991.000.990.990.650.650.650.650.550.550.550.550.470.470.470.470.430.430.430.43
Q30.990.991.000.990.650.650.650.650.550.550.550.550.470.470.470.470.430.430.430.43
Q40.990.990.991.000.650.650.650.650.550.550.550.550.470.470.470.470.430.430.430.43
Nat GasQ10.650.650.650.651.000.990.990.990.940.940.940.940.860.860.860.860.690.690.690.69
Q20.650.650.650.650.991.000.990.990.940.940.940.940.860.860.860.860.690.690.690.69
Q30.650.650.650.650.990.991.000.990.940.940.940.940.860.860.860.860.690.690.690.69
Q40.650.650.650.650.990.990.991.000.940.940.940.940.860.860.860.860.690.690.690.69
ElectricityQ10.550.550.550.550.940.940.940.941.000.990.990.990.820.820.820.820.710.710.710.71
Q20.550.550.550.550.940.940.940.940.991.000.990.990.820.820.820.820.710.710.710.71
Q30.550.550.550.550.940.940.940.940.990.991.000.990.820.820.820.820.710.710.710.71
Q40.550.550.550.550.940.940.940.940.990.990.991.000.820.820.820.820.710.710.710.71
DieselQ10.470.470.470.470.860.860.860.860.820.820.820.821.000.990.990.990.660.660.660.66
Q20.470.470.470.470.860.860.860.860.820.820.820.820.991.000.990.990.660.660.660.66
Q30.470.470.470.470.860.860.860.860.820.820.820.820.990.991.000.990.660.660.660.66
Q40.470.470.470.470.860.860.860.860.820.820.820.820.990.990.991.000.660.660.660.66
AluminumQ10.430.430.430.430.690.690.690.690.710.710.710.710.660.660.660.661.000.990.990.99
Q20.430.430.430.430.690.690.690.690.710.710.710.710.660.660.660.660.991.000.990.99
Q30.430.430.430.430.690.690.690.690.710.710.710.710.660.660.660.660.990.991.000.99
Q40.430.430.430.430.690.690.690.690.710.710.710.710.660.660.660.660.990.990.991.00
ColumnCCCCGGGG0.0KKK0.0OOOSSSS
Indirect:C_Prices!0.00.0
CorrelatedSugarNatural gasElectricityDieselAluminum
N(0,1)Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4
1(1.08)(0.92)(0.82)(1.07)(0.47)(0.78)(0.57)(0.62)(0.15)(0.03)(0.08)(0.27)0.410.520.410.220.520.510.360.39
20.31(0.07)(0.06)(0.03)(0.15)(0.14)(0.12)(0.09)(0.33)(0.17)(0.21)(0.09)0.050.050.26(0.09)(0.87)(1.03)(0.79)(0.74)
3(1.76)(1.67)(1.59)(1.55)(0.92)(0.79)(0.90)(0.98)(0.58)(0.61)(0.57)(0.69)(0.09)0.180.070.02(1.24)(1.01)(1.28)(1.21)
4(1.34)(1.19)(1.12)(1.22)(0.46)(0.44)(0.43)(0.52)(0.66)(0.52)(0.37)(0.44)0.160.09(0.16)(0.03)(1.06)(1.16)(1.11)(0.94)
5(0.75)(0.80)(0.49)(0.77)(0.63)(0.28)(0.57)(0.72)(0.45)(0.56)(0.51)(0.42)0.110.100.210.32(1.35)(1.27)(1.30)(1.23)
60.820.820.720.951.371.101.151.321.190.981.191.430.951.040.851.080.921.070.981.07
70.350.500.380.440.981.081.051.001.401.291.271.311.231.191.191.39(0.24)(0.22)(0.13)(0.10)
8(0.56)(0.44)(0.76)(0.66)(0.17)(0.33)(0.33)(0.45)(0.09)(0.07)0.030.16(0.91)(0.74)(1.02)(0.71)0.780.680.800.58
9(0.25)(0.15)(0.23)(0.27)(1.04)(1.27)(1.09)(1.05)(1.35)(1.50)(1.44)(1.66)(1.49)(1.48)(1.65)(1.51)(1.30)(1.26)(1.34)(1.31)
101.261.161.151.12(1.20)(0.97)(1.07)(1.14)(0.54)(0.46)(0.52)(0.53)(1.07)(0.99)(1.25)(0.99)(1.17)(1.20)(1.25)(1.35)
11(1.66)(1.82)(1.73)(1.74)(0.90)(0.83)(0.95)(0.89)(0.61)(0.70)(0.65)(0.60)(0.66)(0.48)(0.61)(0.84)0.06(0.13)(0.02)(0.06)
120.140.140.340.340.740.620.730.461.311.481.641.550.03(0.03)0.02(0.35)0.06(0.03)0.000.10
130.340.280.110.351.421.301.131.201.181.120.860.910.600.300.440.520.850.810.880.84
14(0.48)(0.59)(0.63)(0.68)0.330.260.200.500.210.170.190.230.01(0.12)0.090.050.890.970.750.99
150.790.910.900.910.830.820.730.810.960.860.930.811.071.121.150.960.470.380.300.24
160.580.710.340.591.161.171.221.030.640.840.840.920.810.800.681.080.930.920.820.70
17(0.46)(0.41)(0.43)(0.48)0.991.121.211.011.381.111.021.260.310.490.270.52(0.19)(0.30)(0.33)(0.45)
180.300.520.250.240.600.780.680.590.730.930.690.700.310.330.260.30(0.20)(0.37)(0.24)(0.26)
19(0.34)(0.31)(0.25)(0.26)1.461.141.241.511.661.731.751.611.211.161.080.972.222.272.012.12
20(1.19)(1.38)(1.36)(1.14)(0.09)(0.09)0.03(0.12)(0.48)(0.21)(0.28)(0.40)0.730.480.370.39(0.28)(0.09)(0.03)(0.44)
210.920.810.850.852.362.282.572.412.292.312.302.392.592.642.692.630.980.820.750.97
220.470.520.310.42(0.14)(0.30)(0.06)(0.16)(0.28)(0.27)(0.41)(0.31)(0.35)(0.40)(0.14)(0.31)(1.22)(1.27)(1.35)(1.34)
23(0.43)(0.47)(0.46)(0.69)(0.21)(0.19)(0.14)(0.44)(0.05)0.200.05(0.06)(0.32)(0.25)(0.21)(0.12)0.750.600.520.51
240.820.830.640.891.951.922.021.951.982.091.951.981.381.091.281.132.011.942.062.08
250.390.300.350.390.420.370.480.540.670.690.660.59(0.50)(0.65)(0.51)(0.61)0.320.380.400.33
261.301.571.131.380.570.520.360.480.760.850.630.76(0.53)(0.58)(0.61)(0.52)0.090.04(0.12)0.04
27(0.63)(0.63)(0.67)(0.46)(0.41)(0.45)(0.53)(0.35)0.120.150.04(0.03)(1.40)(1.36)(1.52)(1.20)(1.16)(1.27)(1.01)(1.01)
28(0.51)(0.67)(0.68)(0.58)(0.75)(0.63)(0.51)(0.68)(0.56)(0.50)(0.53)(0.68)(0.31)(0.25)(0.19)(0.53)(0.22)(0.05)(0.14)(0.18)
29(1.65)(1.63)(1.76)(1.55)(1.15)(1.30)(1.43)(1.28)(0.65)(0.67)(0.81)(0.85)(1.28)(1.05)(1.26)(1.27)(0.58)(0.47)(0.31)(0.50)
30(0.29)(0.31)(0.29)(0.32)(0.07)(0.16)(0.19)(0.03)0.100.180.050.120.010.170.07(0.07)(0.77)(0.89)(0.71)(0.81)
31(0.40)(0.55)(0.58)(0.43)(0.04)(0.09)(0.10)(0.41)(0.07)(0.23)(0.27)(0.28)(0.65)(0.82)(0.86)(0.75)0.780.680.650.89
321.230.981.021.05(0.47)(0.59)(0.69)(0.79)(1.01)(1.06)(0.94)(0.97)(0.69)(0.74)(0.72)(0.89)(1.25)(1.31)(1.15)(1.22)
330.570.520.660.700.820.740.770.750.850.820.810.790.790.900.680.912.452.392.382.37
341.741.561.621.561.421.311.361.350.810.710.750.861.431.331.501.260.460.520.530.71
35(1.21)(1.27)(1.11)(1.22)(0.04)(0.05)0.18(0.09)0.510.590.460.32(0.21)0.03(0.18)(0.08)0.340.460.120.13
361.872.051.952.031.231.231.301.201.050.971.011.200.460.510.250.422.622.462.652.70
37(0.19)(0.31)(0.29)(0.29)(1.06)(0.97)(1.06)(1.08)(1.03)(0.98)(0.84)(0.97)(0.90)(0.92)(1.07)(1.06)(1.99)(1.87)(1.90)(1.94)
380.800.800.810.690.180.36(0.01)0.14(0.02)(0.22)(0.18)(0.17)0.740.710.730.691.091.020.921.07
390.400.530.440.580.850.650.700.630.200.400.290.230.290.530.420.470.760.590.770.84
400.851.001.080.891.341.231.111.331.581.691.641.681.511.381.731.691.491.741.521.50
410.820.970.610.761.010.851.051.080.920.790.930.761.111.111.121.13(0.45)(0.64)(0.44)(0.42)
42(1.23)(1.34)(1.35)(1.47)(2.63)(2.71)(2.74)(2.54)(2.70)(2.76)(2.79)(2.68)(2.10)(2.23)(2.08)(2.27)(2.45)(2.44)(2.36)(2.55)
43(0.16)(0.18)(0.18)(0.34)1.000.920.941.151.221.341.101.121.151.091.021.110.270.240.350.26
44(0.48)(0.52)(0.42)(0.35)(1.20)(1.22)(1.21)(1.19)(0.85)(0.98)(0.98)(0.95)(1.12)(1.15)(1.20)(1.32)(0.06)0.050.01(0.00)
450.690.670.590.54(0.73)(0.73)(0.70)(0.67)(0.57)(0.73)(0.70)(0.80)(0.33)(0.26)(0.39)(0.57)(0.45)(0.40)(0.18)(0.17)
460.050.110.130.050.140.480.360.370.310.360.410.47(0.43)(0.18)(0.52)(0.39)0.300.370.500.24
47(2.35)(2.56)(2.32)(2.22)(2.47)(2.31)(2.42)(2.21)(2.32)(2.19)(2.19)(2.35)(2.72)(2.77)(2.82)(2.78)(3.12)(3.01)(2.87)(2.75)
481.431.311.191.260.841.060.971.08(0.04)0.170.250.210.870.720.670.71(0.04)0.01(0.00)(0.23)
49(0.63)(0.45)(0.54)(0.35)0.230.210.280.270.751.050.800.81(0.48)(0.52)(0.49)(0.45)(0.06)0.010.02(0.04)
50(0.55)(0.60)(0.66)(0.79)(1.29)(1.32)(1.38)(1.30)(1.54)(1.45)(1.27)(1.55)(1.57)(1.21)(1.33)(1.21)(2.05)(2.05)(2.17)(1.95)
510.390.280.440.310.520.560.430.520.300.250.460.230.350.340.220.420.790.790.670.81
52(0.87)(0.75)(0.88)(0.67)(0.09)0.02(0.16)(0.02)0.07(0.11)(0.13)0.00(0.87)(0.77)(0.79)(0.81)(0.01)0.16(0.07)0.04
53(0.32)(0.24)(0.25)(0.35)1.491.561.611.561.561.701.481.791.481.471.701.411.701.881.731.91
541.861.791.681.551.241.171.431.210.530.660.610.581.061.010.951.120.930.840.970.95
55(1.35)(1.44)(1.37)(1.50)(1.90)(1.92)(2.01)(1.88)(1.70)(1.67)(1.61)(1.74)(1.85)(1.99)(2.01)(1.71)(2.96)(2.81)(2.73)(2.99)
56(0.52)(0.51)(0.58)(0.40)(0.75)(0.83)(0.84)(0.87)(1.11)(1.24)(1.15)(1.09)(0.19)(0.09)0.02(0.19)(0.46)(0.47)(0.58)(0.39)
571.731.741.862.061.851.631.741.651.791.992.022.040.951.130.951.132.402.642.512.39
580.760.870.720.840.390.770.560.72(0.05)0.260.040.240.08(0.04)(0.18)(0.02)(0.56)(0.58)(0.67)(0.61)
59(0.72)(0.37)(0.56)(0.72)0.590.730.630.570.480.510.600.510.741.021.000.800.330.370.220.34
60(1.09)(0.93)(1.04)(1.06)(1.70)(1.75)(1.56)(1.56)(1.92)(1.85)(1.65)(1.78)(2.38)(2.34)(2.37)(2.43)(1.22)(1.27)(1.09)(1.50)
610.440.480.320.471.041.060.961.060.630.770.860.811.020.920.890.800.790.790.590.54
62(1.05)(1.01)(0.86)(0.94)0.09(0.06)(0.09)(0.07)(0.27)(0.25)(0.23)(0.25)0.260.150.300.41(1.61)(1.57)(1.66)(1.84)
63(0.95)(1.03)(1.05)(1.22)(0.77)(0.72)(0.95)(0.81)(1.23)(1.08)(1.20)(1.15)(0.77)(0.75)(0.81)(0.75)0.200.180.280.18
64(0.79)(0.70)(0.83)(0.86)0.740.700.600.690.620.650.770.591.211.031.191.330.160.11(0.05)0.17
650.960.931.060.971.461.311.291.421.471.401.501.461.691.661.561.541.761.971.861.75
66(0.49)(0.46)(0.37)(0.42)(0.84)(0.88)(0.81)(0.84)(1.10)(1.25)(1.12)(1.20)(1.49)(1.40)(1.51)(1.24)(0.42)(0.51)(0.64)(0.85)
670.610.450.520.640.961.261.150.981.071.001.051.031.571.421.501.550.090.340.310.29
68(0.85)(1.14)(1.00)(1.17)(1.80)(1.94)(1.92)(1.78)(1.65)(1.86)(1.82)(1.84)(1.35)(1.23)(1.34)(1.40)(1.31)(1.34)(1.16)(1.57)
69(0.84)(0.62)(0.57)(0.60)(0.71)(0.50)(0.52)(0.53)(1.04)(1.10)(1.09)(1.00)0.280.020.310.08(0.03)(0.12)(0.08)(0.04)
70(0.02)0.110.070.020.580.650.580.570.420.270.350.340.690.500.450.370.660.761.060.97
710.230.240.350.270.750.660.710.810.950.840.890.770.390.400.320.390.760.960.760.76
72(0.05)(0.26)(0.08)(0.13)0.610.480.620.360.270.260.340.171.211.431.161.29(0.56)(0.25)(0.24)(0.15)
730.480.500.560.531.181.111.101.090.970.900.990.832.031.961.782.000.721.020.840.74
74(1.19)(1.22)(1.10)(1.37)(0.40)(0.41)(0.45)(0.36)(0.33)(0.31)(0.18)(0.30)(0.71)(0.76)(0.86)(0.93)(0.44)(0.57)(0.50)(0.28)
751.140.951.031.100.980.871.110.920.260.410.260.231.091.061.081.200.270.290.170.23
760.570.480.220.220.850.800.760.900.860.720.900.771.671.691.631.690.980.840.861.03
771.901.711.521.551.121.321.241.051.110.851.051.080.400.530.650.590.460.740.690.75
780.770.590.480.770.800.790.900.801.111.201.031.190.890.770.700.730.590.640.420.45
791.181.001.161.070.05(0.02)0.060.110.100.310.160.13(0.33)(0.34)(0.56)(0.59)0.460.500.620.58
800.330.170.450.320.470.530.450.50(0.10)0.04(0.12)(0.01)0.660.930.820.770.450.340.440.48
811.821.971.902.050.851.011.070.991.141.221.031.250.810.730.710.711.971.922.092.20
820.760.810.620.78(0.80)(0.86)(0.91)(0.84)(0.93)(0.85)(0.87)(0.86)(0.06)(0.14)(0.00)0.090.820.690.740.61
831.561.531.471.660.06(0.04)0.200.03(0.19)(0.18)(0.26)(0.25)(0.52)(0.52)(0.37)(0.48)(1.78)(1.71)(1.59)(1.60)
840.17(0.10)(0.00)0.030.480.580.660.400.420.510.370.480.200.520.370.300.060.06(0.01)(0.00)
850.060.070.06(0.00)0.09(0.20)(0.17)(0.13)0.100.050.070.23(0.45)(0.64)(0.63)(0.69)(1.31)(1.48)(1.38)(1.35)
860.780.800.760.771.621.501.311.451.441.541.441.512.812.732.622.690.910.851.070.97
87(0.34)(0.19)(0.10)(0.11)(0.80)(0.60)(0.85)(0.74)(0.86)(0.87)(0.93)(0.97)(0.24)(0.34)(0.33)(0.36)(0.01)0.010.100.17
88(0.70)(0.46)(0.64)(0.58)(0.28)(0.40)(0.49)(0.46)(0.09)0.05(0.21)(0.21)0.380.560.360.33(0.45)(0.52)(0.50)(0.73)
89(0.31)(0.37)(0.25)(0.13)(0.99)(0.97)(0.94)(0.98)(0.97)(1.07)(0.96)(0.84)(1.58)(1.67)(1.44)(1.63)(2.21)(2.30)(2.32)(2.38)
901.231.301.361.280.570.600.880.991.031.091.050.891.121.161.151.121.331.181.331.22
91(1.46)(1.26)(1.33)(1.32)(0.37)(0.26)(0.29)(0.35)(0.10)(0.20)(0.38)(0.29)0.00(0.24)(0.20)(0.22)0.590.680.770.69
92(0.01)0.07(0.09)0.18(0.73)(0.53)(0.58)(0.58)(0.65)(0.78)(0.68)(0.70)(0.48)(0.52)(0.51)(0.52)(1.12)(1.43)(1.27)(1.23)
93(2.26)(2.02)(2.25)(2.16)(0.56)(0.79)(0.73)(0.55)(0.20)(0.46)(0.45)(0.52)(0.11)(0.17)(0.14)(0.16)0.270.210.390.37
94(1.31)(1.13)(1.12)(1.15)(0.25)(0.19)(0.23)(0.24)(0.21)(0.13)(0.37)(0.47)(0.34)(0.05)(0.48)(0.23)(0.37)(0.42)(0.41)(0.42)
95(0.87)(0.84)(0.91)(0.90)0.660.680.410.721.161.081.191.211.521.441.361.361.521.801.591.68
96(0.42)(0.35)(0.40)(0.38)(0.18)(0.01)(0.10)(0.18)0.110.040.250.080.310.400.350.39(0.11)(0.13)(0.12)(0.22)
97(1.51)(1.57)(1.67)(1.62)(1.22)(1.47)(1.43)(1.34)(1.41)(1.32)(1.14)(1.34)(0.93)(0.74)(0.93)(0.93)(1.08)(1.19)(1.09)(1.32)
980.961.010.911.090.971.021.011.070.700.740.710.400.970.980.890.99(0.62)(0.89)(0.89)(0.77)
990.330.280.290.21(0.08)(0.17)(0.22)(0.09)(0.01)(0.10)0.18(0.02)0.270.080.070.33(0.38)(0.31)(0.30)(0.06)
100(0.55)(0.51)(0.66)(0.67)0.110.110.290.050.180.220.340.380.910.850.910.880.730.710.870.75
Correlations among commodity prices dictate the way in which commodity prices move together and are used to develop commodity price projections via simulation
Disclaimer: The calculation methodology is intended to support an illustrative, high-level example of risk exposure determination. More robust and accurate methods should be used to determine forward price evolution and subsequent risk measurement and mitigation.
Sales, Pricing and Volumes
all figures in millions (except prices)Q1Q2Q3Q42012
Sales480.00504.00529.20555.602,068.80
Volume3,200.003,360.003,528.003,704.0013,792.00
Price ($)0.150.150.150.150.16
0.00
Cost of Commodity Goods Sold328.85337.92347.34357.581,371.70
Fixed Costs120.00120.00120.00120.00480.00
Sugar ($)25.6028.1431.0234.50119.26
Volume (lb)128.00134.00141.00150.00
($/lb)0.200.210.220.23
0.00
Natural gas ($)55.0257.8860.7563.62237.27
Volume (MMBTU)9.6010.1010.6011.10
Price ($/MMBTU)5.735.735.735.73
0.00
Electricity ($)62.2762.2762.2762.27249.08
Volume1.001.001.001.00
Price62.2762.2762.2762.27
0.00
Diesel ($l)35.9137.7139.5041.52154.64
Volume16.0016.8017.6018.50
Price2.242.242.242.24
0.00
Aluminum ($)30.0531.9233.8035.68131.44
Volume1.601.701.801.90
Price18.7818.7818.7818.78
Variable Costs208.85217.92227.34237.58891.70
Commodity volumes may be determined by reviewing sales and demand planning forecasts. Volumes may be aggregated when sourced from different business divisions or regions to arrive at a corporate-wide volume estimate.
Gross exposure calculations
Gross Exposure
all figures in millions (except prices)Q1Q2Q3Q4Q1Q2Q3Q4Total
Simulated commodity costs
Sugar44.254.365.180.118.626.134.145.6124.4
Volume (lbs)128.0134.0141.0150.0
Price0.350.410.460.53
0.00.00.00.00.0
Natural gas76.085.798.2114.121.027.837.550.5136.8
Volume (MMBTU)9.610.110.611.1
Price7.928.499.2710.28
0.00.00.00.00.0
Electricity100.4125.9138.4149.238.163.676.286.9264.9
Volume (MWh)1.01.01.01.0
Price100.4125.9138.4149.2
0.00.00.00.00.0
Diesel47.353.763.468.911.316.023.927.478.6
Volume (gal)16.016.817.618.5
Price2.953.203.603.73
0.00.00.00.00.0
Aluminum40.648.356.965.910.616.423.130.280.3
Volume (tons)1.61.71.81.9
Price25.4028.4331.6034.69
Total308.5367.9422.0478.399.6150.0194.7240.7685.0
Risk management portfolio
FuturesDescription
SugarFutures or OTC contracts may be used to "lock in" or fix commodity prices for defined periods of time
Natural gasFutures or OTC contracts may be used to "lock in" or fix commodity prices for defined periods of time
ElectricityFutures or OTC contracts may be used to "lock in" or fix commodity prices for defined periods of time
DieselFutures or OTC contracts may be used to "lock in" or fix commodity prices for defined periods of time
AluminumFutures or OTC contracts may be used to "lock in" or fix commodity prices for defined periods of time
Correlated Price Simulations
Commodity prices are simulated through geometric brownian motion and utilize correlated random shocks based on an input correlation matrix
SugarNatural gasElectricityDieselAluminumTotal Net Exposure
Period:Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Full-Year
Mean:0.200.210.220.235.735.735.735.7362.2762.2762.2762.272.242.242.242.2418.7818.7818.7818.78
Volatility:0.700.700.700.700.480.480.480.480.690.690.690.690.370.370.370.370.320.320.320.32
Time:0.250.500.751.000.250.500.751.000.250.500.751.000.250.500.751.000.250.500.751.00
Volume:128.00134.00141.00150.009.6010.1010.6011.101.001.001.001.0016.0016.8017.6018.501.601.701.801.90
Simulated
5%0.140.110.100.083.622.802.372.1833.1522.2817.5713.791.721.571.391.2515.0313.5113.0910.81487.15
95%0.360.510.600.747.488.219.029.0884.3988.8395.35112.482.402.482.312.4528.2031.9137.6642.381457.22
Std Dev:0.070.100.120.161.351.862.382.7021.0030.7437.1345.210.440.620.770.913.394.935.967.07
Mean:
Net ExposureNet Portfolio Exposure
323.80470.080.050.040.043.082.471.921.7726.3718.9814.089.721.331.050.860.7511.269.258.157.39323.80
339.48420.120.100.080.062.962.161.691.5123.1014.359.867.711.501.211.090.9012.5310.549.407.89339.48
399.48550.120.090.080.063.532.822.282.0832.6824.4619.8914.811.571.291.121.1111.559.688.496.85399.48
435.42600.130.120.100.093.702.982.752.4130.2322.3819.4714.411.421.171.000.8515.2413.7313.3511.03435.42
449.77680.140.110.100.083.622.802.372.1833.1522.2817.5713.791.721.571.391.2515.0313.5113.0910.81449.77
489.11500.160.140.120.104.093.462.962.7334.4827.2824.3316.801.651.581.391.3413.3611.519.899.56489.11
498.24970.110.090.070.064.163.292.902.6836.1128.9826.2919.451.861.791.581.4815.6013.9713.3511.68498.24
526.1490.170.170.160.154.343.523.353.0936.8726.5922.0815.571.671.471.261.2015.0713.7712.4811.73526.14
542.47890.170.150.160.164.393.903.563.1942.0232.7829.3827.411.651.401.351.1513.0210.879.498.34542.47
544.67290.110.080.060.064.223.482.902.7646.9539.9232.1327.351.741.651.421.3116.8916.4516.5815.18544.67
560.81370.180.160.150.154.313.893.383.0341.0734.3431.5725.201.871.711.511.4213.4812.0010.679.59560.81
569.50660.160.150.150.134.564.013.763.4140.1830.1126.7321.471.671.501.311.3317.3516.3115.1413.58569.50
587.40440.160.140.140.144.173.583.182.8843.7334.2028.9725.431.791.611.451.2818.3718.4918.1117.83587.40
592.33630.130.110.100.084.634.243.543.4638.3832.6925.4022.231.921.781.651.5919.1519.0819.5218.90592.33
602.39110.110.080.060.054.494.083.543.3447.5839.3535.2432.361.951.911.761.5318.7117.7717.9517.51602.39
603.1630.100.080.070.064.464.133.623.1848.1041.0437.0530.502.172.272.182.1215.2014.5612.6812.13603.16
616.64560.160.140.130.144.654.093.713.3740.0430.1826.2723.062.132.122.141.9517.2316.4415.3915.73616.64
652.6940.120.100.090.084.994.664.403.9946.7942.8941.7536.302.272.222.022.0715.6414.0713.2813.21652.69
652.73920.190.190.170.204.684.524.123.8646.8137.8434.7630.302.021.891.811.7315.5013.2512.7212.04652.73
657.64690.140.140.130.124.694.564.243.9640.9532.3527.1624.692.322.182.352.1518.4617.8317.6917.59657.64
658.76930.090.070.050.044.874.143.883.9254.7044.1439.9034.222.162.082.041.9719.3619.1820.1320.08658.76
660.78870.170.170.170.174.594.423.703.5943.5836.1029.9525.072.111.981.921.8418.5018.3418.6018.86660.78
661.16740.120.100.090.075.064.704.364.3152.4347.5846.8739.991.941.781.621.4817.2916.0815.7416.32661.16
665.62280.160.130.120.124.664.374.253.6848.3743.3937.8630.722.082.032.011.7217.9018.1117.3916.86665.62
667.8150.140.120.140.104.794.934.163.6150.3142.0938.3936.832.252.222.282.3614.9413.7412.5912.05667.81
682.44270.150.140.120.135.054.654.224.3361.1159.4053.3848.221.701.521.311.3415.4013.7313.6812.91682.44
692.90940.120.110.090.085.255.064.774.5554.6151.9741.8235.412.072.141.831.9317.4916.6416.1315.59692.90
697.01320.290.300.340.384.984.433.953.4941.4732.9829.7425.121.941.791.691.5115.1713.6013.1212.09697.01
707.96100.290.330.370.404.173.893.372.9648.6844.0838.0934.011.811.671.431.4515.3813.9412.7911.58707.96
712.22620.130.110.110.095.695.305.074.9353.4348.9045.3441.222.322.252.352.4414.3212.8311.419.91712.22
712.61450.240.260.260.264.684.233.933.7048.2038.7834.3628.252.082.031.881.6917.2416.7217.2116.91712.61
727.02910.110.100.080.075.104.964.674.3256.6750.2541.5040.242.212.042.001.9320.3721.3522.3522.28727.02
737.34200.120.090.080.085.455.255.324.8349.6449.7844.1837.342.522.462.402.4217.7317.9317.9215.50737.34
738.01520.140.130.110.115.445.444.915.0560.0752.4348.2349.221.881.771.661.5518.5118.9917.7118.06738.01
741.08880.150.150.120.125.214.724.284.1056.9456.5345.9742.342.372.512.402.3717.2516.2615.7214.14741.08
743.1710.130.120.110.094.974.154.143.7955.6654.6049.5640.772.382.492.432.2720.1420.5419.9420.24743.17
746.60220.220.240.220.245.394.895.134.7453.2348.3940.7839.742.071.952.041.8715.2513.7412.4111.60746.60
748.08310.160.140.130.135.515.245.044.2057.2249.4844.4240.391.961.751.621.5921.0021.3321.6323.68748.08
749.98820.250.280.270.314.594.043.603.4142.5136.5330.9027.162.182.092.132.1721.1321.3922.2021.71749.98
753.5080.150.150.120.115.344.844.584.1156.9153.4852.9654.691.861.791.541.6121.0121.3422.5721.46753.50
758.50850.190.190.190.185.685.054.914.7960.7756.5654.3557.572.031.831.741.6215.0313.1012.3411.58758.50
762.7020.210.180.180.185.385.155.004.8952.3150.9045.9646.232.232.202.312.0316.1314.5014.5114.09762.70
780.37300.170.160.150.145.485.134.855.0460.6660.5053.8253.142.212.272.182.0416.4014.9514.8513.75780.37
782.26230.160.150.140.115.295.064.954.1457.6161.0353.6546.952.082.031.992.0120.9020.9520.8921.03782.26
810.22960.160.160.140.145.345.395.044.6860.8756.3760.3451.872.342.412.392.4218.2117.7717.4916.62810.22
820.68990.210.210.220.215.465.104.794.8958.5152.7657.8748.322.322.222.182.3717.4517.0616.6417.48820.68
826.43350.120.100.090.085.515.325.654.8869.8573.6368.7261.152.122.192.012.0419.5620.3318.6618.58826.43
870.60140.160.140.120.116.035.905.706.4963.0160.1058.1957.712.212.102.202.1321.3922.8122.2624.47870.60
876.14830.320.400.450.585.655.345.725.1954.9250.6344.5741.352.001.891.891.7613.9512.4411.6410.68876.14
896.14460.190.200.200.195.766.376.106.1065.3365.9866.6667.862.042.071.811.8119.4619.8820.7819.28896.14
902.971000.160.140.120.115.715.625.945.2362.3861.4963.7563.922.612.712.852.9020.8421.4823.0222.67902.97
914.92490.150.150.130.145.885.815.905.8276.1292.2983.7785.732.021.901.821.7818.3718.3318.1517.62914.92
922.85790.280.300.370.385.635.385.385.3960.6964.2857.3253.822.071.981.781.6919.9720.5121.4621.50922.85
925.17800.210.200.240.236.246.496.346.4956.6456.2848.6248.652.492.762.772.7919.9119.7820.4220.79925.17
925.20580.250.290.280.326.117.026.647.2057.7362.8853.4657.872.242.152.012.0816.9516.0415.0114.66925.20
932.97380.250.280.300.295.816.115.255.4758.3049.6246.8143.742.532.612.692.7022.0723.0723.3125.11932.97
934.92840.200.180.180.186.246.586.936.2067.9270.8764.8668.222.292.482.402.3518.7218.5718.0017.82934.92
940.10720.190.160.170.166.456.376.816.0864.3262.6763.6455.362.763.153.093.3816.9517.3016.8917.02940.10
953.55250.220.220.230.246.176.146.416.6173.8177.4177.3473.552.011.831.811.6719.5219.9620.1719.82953.55
959.81510.220.210.240.226.316.546.286.5665.1262.3868.6357.332.352.372.282.4521.0621.8821.7423.10959.81
965.90700.190.200.190.186.406.746.706.7267.8563.0464.1961.862.502.472.472.4120.6221.7524.2124.30965.90
966.59590.150.150.130.116.416.946.826.7269.3270.9974.7269.622.532.832.942.8219.5519.8819.1919.87966.59
989.84390.220.240.240.276.826.747.036.9062.8967.0761.8857.352.332.492.442.5020.9320.9122.3523.35989.84
994.73640.140.130.110.106.656.856.747.1172.5475.7782.4273.892.762.843.123.4319.0218.7717.8418.81994.73
1,004.38180.210.240.210.216.437.056.996.7975.4587.0678.8879.602.342.362.322.3417.9516.8216.8816.431,004.38
1,049.25260.300.400.360.476.386.466.116.4476.3183.8175.9283.002.001.861.761.7318.8118.4717.4618.051,049.25
1,066.83710.200.210.230.226.676.777.077.5281.4683.2588.7983.412.372.412.362.4220.9322.7522.3222.741,066.83
1,070.46480.310.360.380.436.817.747.878.6057.8260.0860.5456.912.592.622.652.7218.4218.3418.0516.571,070.46
1,089.29750.280.300.340.397.057.268.357.9564.0867.5960.9457.392.702.863.013.2619.3719.5418.9619.221,089.29
1,097.89170.160.150.140.137.067.928.718.2894.5794.9695.57117.332.342.462.322.5417.9917.1016.4915.441,097.89
1,099.40980.260.310.320.387.037.648.018.5374.6579.4079.7164.892.642.802.833.0316.7914.9614.1113.931,099.40
1,119.63610.220.240.220.257.157.767.848.4972.8180.6187.2185.622.662.762.832.8121.0421.8721.3021.211,119.63
1,131.40410.250.300.270.317.097.228.138.5880.6681.2890.7582.882.712.903.053.1917.2415.8216.0215.581,131.40
1,144.75150.250.290.320.346.807.147.127.5581.6884.2490.9485.822.692.903.092.9919.9819.9619.6619.271,144.75
1,154.20760.230.240.210.216.827.107.217.8878.8878.5889.0083.403.003.383.603.9221.7022.1222.9524.791,154.20
1,154.70120.200.200.220.236.656.687.126.3792.15113.63138.42143.272.222.152.151.8418.7318.1818.0918.391,154.70
1,155.43430.180.170.160.147.087.407.768.8889.39106.41100.54106.322.732.892.953.1619.3719.3219.9419.381,155.43
1,155.99160.230.260.230.277.358.058.718.3673.1783.1985.9992.602.562.672.653.1321.5322.5522.6822.331,155.99
1,156.28950.140.120.110.106.536.816.237.2087.4593.44106.28113.022.923.163.293.4723.6627.5028.0630.561,156.28
1,156.95130.210.210.200.237.838.428.429.0788.1195.6386.8692.202.462.342.462.5421.2421.9723.0723.361,156.95
1,167.47780.250.250.240.316.757.077.647.5186.0999.4096.32111.282.602.662.672.7520.3721.1620.3320.581,167.47
1,187.94330.230.240.270.296.786.957.247.3278.6882.3084.4384.492.552.752.652.9427.4531.4234.9238.081,187.94
1,207.98670.230.230.250.287.018.298.488.1684.9590.0197.5499.792.953.153.443.7218.8119.7519.6819.601,207.98
1,221.3470.210.240.230.257.047.828.138.2795.26103.96111.49121.512.772.963.123.5017.8517.4117.4217.291,221.34
1,235.16730.220.240.260.267.387.888.318.6082.0685.6494.1987.043.213.623.774.4020.8223.0522.8122.631,235.16
1,256.06900.290.350.420.446.386.637.588.2283.6094.2797.5890.922.722.943.083.1722.9423.9226.1226.391,256.06
1,269.28540.360.450.510.537.518.059.549.1170.5076.4375.1073.162.682.832.903.1721.5122.1223.6324.171,269.28
1,288.46770.370.430.460.537.288.468.798.4586.0383.7697.76103.682.372.492.632.6119.9421.6321.8522.641,288.46
1,288.7860.250.280.280.357.747.868.499.6088.4789.37105.90131.502.632.852.803.1321.4823.3223.7125.131,288.78
1,308.07340.350.400.490.547.828.439.259.7977.5578.2181.6688.652.883.073.443.3419.9720.5920.9522.391,308.07
1,403.98190.170.160.160.157.917.958.8110.53104.20128.31148.42148.802.762.943.023.0126.4530.6131.5835.171,403.98
1,424.14810.360.490.580.756.827.638.218.2186.88100.0296.62115.952.562.632.672.7225.3928.2732.2436.031,424.14
1,439.17530.170.160.160.147.969.1910.2910.80100.35126.87125.87169.052.903.193.683.5324.3228.0429.1932.921,439.17
1,450.05650.260.290.350.367.918.448.9810.0897.27109.40127.87134.833.013.353.513.7124.5828.5730.2231.261,450.05
1,456.38360.360.510.600.747.488.219.029.0884.3988.8395.35112.482.402.482.312.4528.2031.9137.6642.381,456.38
1,473.30400.250.300.350.337.688.238.349.69101.34125.86138.76156.682.923.113.713.9223.5227.1427.5128.831,473.30
1,499.55860.250.280.290.318.229.029.0510.2796.57117.30122.87139.473.714.434.945.6721.4622.1924.3224.371,499.55
1,673.85240.250.280.270.338.8910.3912.1613.04116.17153.34167.48192.962.852.883.213.1825.5928.4232.0034.671,673.85
1,784.69570.350.440.570.768.689.4110.8511.27108.66146.32174.31200.892.632.922.893.1827.2333.3036.1838.361,784.69
1,958.30210.260.280.310.339.8011.7315.3116.23129.34170.45205.68255.873.564.325.045.5521.6822.0122.2524.301,958.30
Simulation of commodity prices provides a richer understanding of possible price outcomes vs. a single, static forecast. Simulations also support analysis of possible physical and/or financial hedging strategies
Disclaimer: The calculation methodology is intended to support an illustrative, high-level example of risk exposure determination. More robust and accurate methods should be used to determine forward price evolution and subsequent risk measurement and mitigation.
Impact of hedging
all figures in millions (except prices)
FuturesUsed
SugarNo
Natural gasNo
ElectricityNo
DieselNo
AluminumNo
Net exposure (95%)
Q1Q2Q3Q4Q1Q2Q3Q4Total
Simulated commodity costs
Sugar46.368.684.0111.620.740.453.077.1191.3
Volume (lbs)128.0134.0141.0150.0
Price0.360.510.600.74
0.00.00.00.00.0
Natural gas71.882.995.6100.816.825.034.837.2113.8
Volume9.610.110.611.1
Price7.58.29.09.1
0.00.00.00.00.0
Electricity84.488.895.3112.522.126.633.150.2132.0
Volume1.01.01.01.0
Price84.488.895.3112.5
0.00.00.00.00.0
Diesel38.541.640.745.32.63.91.23.811.4
Volume16.016.817.618.5
Price2.42.52.32.4
0.00.00.00.00.0
Aluminum45.154.367.880.515.122.334.044.8116.2
Volume1.61.71.81.9
Price28.231.937.742.4
Total286.1336.1383.4450.777.3118.2156.1213.1564.7
Earnings statement
all figures in millions (except prices)Q1Q2Q3Q42012
Sales480.00504.00529.20555.602,068.80
Volume3,200.003,360.003,528.003,704.0013,792.00
Price ($)0.150.150.150.15
0.00
Cost of Goods Sold328.85337.92347.34357.581,371.70
Fixed Costs120.00120.00120.00120.00480.00
Sugar ($/lbs)25.6028.1431.0234.50119.26
Volume (lbs)128.00134.00141.00150.00
Price0.200.210.220.23
0.00
Natural gas ($/Kcf) - Henry Hub55.0257.8860.7563.62237.27
Volume9.6010.1010.6011.10
Price5.735.735.735.73
0.00
Electricity ($/MWh) - NY ISO62.2762.2762.2762.27249.08
Volume1.001.001.001.00
Price62.2762.2762.2762.27
0.00
Diesel ($/gal) - NY Harbour35.9137.7139.5041.52154.64
Volume16.0016.8017.6018.50
Price2.242.242.242.24
0.00
Aluminum30.0531.9233.8035.68131.44
Volume1.601.701.801.90
Price18.7818.7818.7818.78
Variable Costs208.85217.92227.34237.58891.70
0.00
General & Admin. Exp.90.0090.0090.0090.00360.00
0.00
EBITDA61.1576.0891.86108.02337.10
TitleTo quickly check to make sure prices are still the same, filter the prices on the tab proj 3 to go in order 1 to 100 and this should match
[Optional comments]
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
yesyesyesyesyesyesyesyesyesyesyesyesyesyesyesyesyesyesyesyesnoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesyesyes
nononononononononononononononononononononoyesyesyesyesyesyesyesye
top related