Oil & gas isn’t that complicated.

The energy industry impacts everyone in one way or another, from commuters to bottled water consumers. Oil and natural gas are the world’s leading energy supply, with gas stations in every neighborhood fueling cars and trucks that travel millions of miles a day. Supporting the entire oil & gas sector is the entire oil & gas services ecosystem.

We begin with an oil & gas services industry primer by introducing the oil & gas field development cycle and the corresponding supporting oil services, from feasibility studies to contract drilling from onshore to offshore, marine-based oil rigs. Drill into the drilling related services & equipment and understand casing and completion to infrastructure & installation and production & maintenance.

Then we focus on analyzing a leading global oil & gas services provider (Halliburton). We will build and constructing the detailed segment build-up portion of the financial model that feeds the Income Statement of your oil & gas services financial model. Understand various industry conventions for rig counts (SWACO and Baker Hughes).

Oil & Gas Services Industry Primer

• What exactly is oil & gas? How did oil start? Who started it?
• Products that use petroleum; different types of oil (light vs. heavy, sweet vs. sour)
• Worldwide oil benchmarks and why they matter
• Onshore well drilling and pumping; primary and secondary recovery
• Offshore drilling from start to finish, including various drilling platform types
• Mining oil sands; what is special about bitumen?

• Oil & Gas Services: Learn all the services with which oil firms need assistance
• Who do they call on when they need help, tools and parts?
• Deep dive into major oil services: seismology, contract drilling, drill bits, casing & completion, infrastructure and production & maintenance
• Understand key drivers of growth: How many oil rigs are out there today? How are they counted?
• Brief overview of oil & gas taxation and concession vs. production based contracts

Oil & Gas Services Financial Modeling

• Quantify the readily identifiable drivers of growth for oil & gas services provider
• Understand rig counts and various footnotes to roll up to revenue figures
• Gain insight into channel checks required to properly ascertain future growth
• Roll-up segment and geographic operating data in footnotes into Income Statement
• Project from Revenue down to Net Income and Earnings per Share
• Analyze various one-time adjustments required and how to properly calculate normalize profitability
• Construct trading statistics analysis, summarize current market valuation of company
• Build fully integrated financial model (optional)

Prerequisites

• Accounting & Financial Statements Integration
• Basic Financial Modeling
• Advanced Financial Modeling - Core Model (optional)

The energy industry impacts everyone in one way or another, from commuters to bottled water consumers. Oil and natural gas are the world’s leading energy supply, with gas stations in every neighborhood fueling cars and trucks that travel millions of miles a day. However, oil’s ubiquity should not be mistaken for simplicity; sound investment decisions require the exploration of the many intricacies within this space.

In this abridged version of our full blown oil & gas integrated analysis, we explain the various sectors and subsectors of an E&P company with our short introductory primer to familiarize the concepts and terminology. We then introduce and build a basic, simplified version of our oil & gas integrated model to communicate how one should approach modeling out the key drivers of growth and basic NAV analysis.

Oil & Gas Integrated Industry Primer

• What exactly is oil & gas? How was oil formed? What are the different worldwide benchmarks?
• Products that use petroleum; different types of oil (light vs. heavy, sweet vs. sour)
• Upstream: Onshore & offshore well drilling and pumping; primary, secondary & tertiary recovery
• Downstream: converting unusable crude oil into refined, useable various end products
• Summary review of the process of refining, distillation and cracking; transportation and petrochemicals
• Understanding Nelson complexity index for refineries and economics of crack spreads
• Brief overview of oil & gas taxation and concession vs. production based contracts
• Financial statements analysis: oil reserves (P1, P2, P3) & expense treatment (successful efforts vs. full cost)
• Operating metrics: reserves to production ratio, reserve replacement ratio, cost ratios, production curves

Oil & Gas Integrated - Segment Build-up

• Build simplified oil & gas model, highlighting the important modeling concepts for oil & gas integrateds
• Upstream segment build-up analysis: project future exploration & production revenue and EBITDA based on oil-equivalent production volume, realized prices, % realized vs. benchmark and other key drivers of growth
• Downstream segment: project refining capacity, throughput, capacity utilization, gross vs. net refining margins, crack spreads, realized vs. crack spreads to calculate refining & marketing revenue and EBITDA
• Petrochemicals segment: project volume, petrochemicals product price differentials to derive future revenue
• Roll-up upstream, downstream and petrochemicals segments to start Income Statement projection model

5-Year Income Statement Projection Model

• Input historical financial results and recast as necessary for one-time adjustments
• Incorporate segment build-up as the start of future projection years into Income Statement
• Calculate historical growth rates and margins which serve as the basis for your projection assumptions
• Calculate projected profitability from revenue down to EPS

NAV and PV-10 Valuation Model

• Analyze reported proven reserves footnote which serve as the starting point NAV and PV-10 analysis
• Incorporate production curve projections from larger financial model to project future total production
• Estimate realized prices and production costs per barrel from your larger model and past projection period
• Calculate revenue and costs to derive pre- and post-tax cash flows and discount for NAV and PV-10 analysis
• Include other segments (downstream and petrochemicals) to arrive at estimated Total Enterprise Value

Prerequisites

• Accounting & Financial Statements Integration
• Basic Financial Modeling
• Advanced Financial Modeling - Core Model (optional)

The energy industry impacts everyone in one way or another, from commuters to bottled water consumers. Oil and natural gas are the world’s leading energy supply, with gas stations in every neighborhood fueling cars and trucks that travel millions of miles a day. However, oil’s ubiquity should not be mistaken for simplicity; sound investment decisions require the exploration of the many intricacies within this space.

In this full blown version of our oil & gas integrated financial modeling, we explain the various sectors and subsectors of an E&P company with our short introductory primer to familiarize the concepts and terminology. We then build a full integrated model including detailed build-ups of various segments of our oil & gas integrated model to fully map out drivers of growth for both financial modeling and valuation.

Oil & Gas Integrated Industry Primer

• What exactly is oil & gas? How was oil formed? What are the different worldwide benchmarks?
• Products that use petroleum; different types of oil (light vs. heavy, sweet vs. sour)
• Upstream: Onshore & offshore well drilling and pumping; primary, secondary & tertiary recovery
• Downstream: converting unusable crude oil into refined, useable various end products
• Summary review of the process of refining, distillation and cracking; transportation and petrochemicals
• Understanding Nelson complexity index for refineries and economics of crack spreads
• Brief overview of oil & gas taxation and concession vs. production based contracts
• Financial statements analysis: oil reserves (P1, P2, P3) & expense treatment (successful efforts vs. full cost)
• Operating metrics: reserves to production ratio, reserve replacement ratio, cost ratios, production curves

Oil & Gas Integrated - Segment Build-up

• Build detailed oil & gas integrated financial statement model, quantifying all key drivers of growth
• Upstream segment build-up analysis: project future production volume by type of oil & gas production: crude oil & NGL, bitumen, synthetic oil and natural gas growth rates
• Discuss and select relevant benchmarks for each product type and calculate average prices, input and analyze realized prices, and calculate product price differentials based on % realized
• Project future E&P revenue, reconcile to reported unconsolidated revenue and incorporate intercompany eliminations to arrive at GAAP reported revenue that properly foots to footnotes and segment reporting
• Reconcile and sort through confusing EBITDA build-up based on reported segment data

• Downstream segment: project refining capacity, throughput, capacity utilization for both US and non-US
• Discuss and understand difference between gross vs. net refining margins and when to use which margin
• Understand and incorporate key drivers: crack spreads, realized vs. crack spreads
• Calculate refining & marketing revenue and EBITDA including very confusing EBITDA reconciliation
• Petrochemicals segment: project volume, petrochemicals product price differentials to derive future revenue

• Construct robust sensitivity and scenario analysis for toggling various future spot prices assumptions that ultimately drive the profitability of oil & gas companies
• Analyze the implications of price impacts on upstream and downstream segments and the offsetting nature
• Construct massive detailed Consolidating Income Statement that fully reconciles historical and future projected segment build-up of revenue and expenses that ties to Income Statement line items and incorporating Corporate division and Minority Interest (this Herculean task cannot be understated enough!)

Income Statement Projection Model

• Input historical financial results and recast as necessary for one-time adjustments
• Incorporate segment build-up as the start of future projection years into Income Statement
• Calculate historical growth rates and margins which serve as the basis for your projection assumptions
• Calculate projected profitability from revenue down to EPS

Balance Sheet & Cash Flow Statement Projection Model

• Input historical Balance Sheet and understand the drivers of growth for various line items
• Review of days outstanding and different working capital projection methods
• Project "first pass" of the Balance Sheet, the first 75% of line items that don't require Cash Flow Statement
• Construct Cash Flow Statement based on the completed Income Statement and "first pass" Balance Sheet
• Calculate all of CFO, estimate Capital Expenditures for CFI and project capital structure items for CFF
• Incorporate calculation and payment of dividends into your integrated financial model
• Emulate announced share repurchase program by estimating implied price and shares repurchased

Debt Sweep and Integration & Balancing of Financial Model

• Balance the model using debt schedule and revolver logic - extremely important for balancing the model
• Incorporate automatic debt payments and use cash generated to either pay down debt or build cash
• How does the revolver facility actually balance the model? Avoid messy nested "if" statements!!
• How does the balance sheet and financial statements balance by itself without the use of "plugs"?
• How are the financial statements integrated using the Interest schedule?
• What are circular references, why should they be avoided and how to get around circular references

Enhancements to the Core Model and Supporting Schedules

• Build a stand-alone depreciation schedule by incorporating details such as remaining useful life estimates
• Depreciate existing Net PPE and new Capital Expenditures based on weighted average life
• Construct detailed financial accounting ratios to quantify profitability & operating efficiency metrics
• Analyze liquidity ratios, profitability ratios and asset management efficiency ratios
• Credit and leverage statistics ratio analysis with automated comparisons vs. S&P rating statistics
• Build an analysis of trading statistics that can be used to compare companies across an industry
• Provides current snapshot of the current public market valuation

Traditional Discounted Cash Flow Analysis

• How is a discounted cash flow analysis actually constructed?
• Estimate unlevered free cash flow (free cash flow to firm)
• Terminal Value estimation: what are the differences between the EBITDA multiple and perpetuity growth approaches and what are the implications on value?
• Learn subtle nuances including the proper figure for "cash flow" in perpetuity growth models
• Calculate from enterprise value down to equity value and ultimately down to stock price per share

NAV and PV-10 Valuation Model

• Analyze reported proven reserves footnote which serve as the starting point NAV and PV-10 analysis
• Incorporate production curve projections by product type to project future total production
• Estimate realized prices and production costs per barrel by product type, during and past projection period
• Calculate revenue and costs to derive pre- and post-tax cash flows and discount for NAV and PV-10 analysis
• Include other segments (downstream and petrochemicals) to arrive at estimated Total Enterprise Value

Prerequisites

• Accounting & Financial Statements Integration
• Basic Financial Modeling
• Advanced Financial Modeling - Core Model