Cashflow Modelling for the Energy Industry James Henderson April 2018 •The Economics of Energy Corporations (2) Introduction to the Course 1.Cashflow Modelling 2.Time Value of Money 3.Discounted Cashflow 4.Building a Model –Revenues –Costs –Taxes –Other Key Assumptions •Valuing the Output –The Discount Rate –Terminal Value –Internal Rates of Return •Testing the Model –Sensitivities –Breakeven Analysis –Risk Analysis The Question •Value an oil and gas asset given specific assumptions •Test the sensitivity of the model •Provide an investment conclusion for senior management title •Detailed breakdown of company operating and financial performance •Investment analysts are responsible for asking fundamental questions of senior management •There is pressure to perform across a broad range of metrics •A “Sell” recommendation can have big implications •Bad news! Share price determines market valuation •Share price multiplied by number of shares in issue = market value •Market value divided by profits gives “price to earnings ratio” •Potential value can be derived by using multiples and future profit forecasts Comparison with Peer Groups A typical spreadsheet summary of a cashflow model Time Value of Money •Money available at the present time is worth more than the same amount in the future due to its potential earning capacity. • •This core principle of finance holds that, provided money can earn interest, any amount of money is worth more the sooner it is received • •Equally, money available now can buy more than a similar amount of money available in the future because inflation erodes the value of money over time Time Value of Money Example •If you had $10,000 today, you could earn interest on it • •Its future value is $10,000 x (1 + interest rate)No. of years • •If interest rate is 5%, then $10,000 in 3 years is worth –$10,000 x (1+.05)3 = $11,576 – •As a result, $10,000 in 3 years is not worth $10,000 now –$10,000 / (1+.05)3 = $8,638 – Impact of inflation •I have $100 • •A bar of chocolate costs $1 • •Inflation is 5% • •In Year 1 I can buy 100 bars of chocolate • •In Year 2 the cost of a bar of chocolate has risen to $1.05 • • •Money • • •$100 • •$100 • •$100 • •$100 • •$100 • •$100 •Cost of chocolate • •1 • •1.05 • •1.102 • •1.158 • •1.216 • •1.276 • •No. of Bars (whole) • •100 • •95 • •91 • •86 • •82 • •78 •Year • • •1 • •2 • •3 • •4 • •5 • •6 Real and Nominal Figures •Nominal cashflows include the impact of inflation • •They are called Money of the Day (MoD) because they reflect the actual worth in a certain year • •If we were forecasting the cost of a project, for example, we would need to add inflation to each year as we moved across the time horizon • •This is relevant for multi-year developments when parts are being purchased over time Nominal Costs Example •Costs will rise over time because of inflation (in this example 5% per annum) Using “Real” figures makes life easier •When making assumptions in nominal, every figure needs to take an inflation assumption into account • •This can make things very complex • •To make life easier, we can just assume that our model is in “today’s money” – otherwise known as “in real terms” • •Generally, we would define all the figures as being in (e.g.) US$2018 • •All figures in the cashflow will be lower as a result, and so it is important to define how the model is considering inflation Discounted Cashflow •In Year 0 (today), I decide to invest $30mm over 3 years in a plant that will run for 7 years, generating $20mm per year •The plant will then be dumped •What is the value (worth) of this investment in today’s terms? •A Simple Cashflow The DCF Calculation as a foundation Image result for discounted cash flow analysis •Management thought process is encapsulated in the DCF model –Key assumptions include price, cost, tax, long-term outlook, short-term cashflow and the value of money •Management must ensure at all times that the combined value of their assets remains NPV positive, and should aim to maximise the return on their assets Discounted Cashflow Example •The further away that money is earned (or spent) the less worth (value) it has today •We discount future cashflow by a factor reflecting the other options we had for using the initial funds •If the total sum of negative and positive cashflow is positive then the investment is worth making A Good Explanation from Harvard •https://hbr.org/2014/11/a-refresher-on-net-present-value Functionality in Excel •The NPV function in Excel makes life very easy •=NPV(discount rate, range of net cashflow) Real vs Nominal Cashflow and NPV •To make our lives easier, all our modelling will be carried out in real terms •Our expectations of return should therefore be lower Starting to construct a cashflow model •Revenues –Production of oil and gas –Oil and gas price • •Cost of Development (Capex) –How much will it cost to put the necessary infrastructure in place? – •Cost of Operations (Opex) –How much will it cost to run the infrastructure and extract the oil and gas –How much will it cost to transport it to market? What will the government get out of it? •Operating taxes –Royalty –Export tax –Other social taxes • •Profit Tax –Depreciation is a key assumption • •Alternative forms of taxation –Production Sharing Agreement The Discount Rate •A firm is like a pool of cash that has been financed from two sources – debt from banks and equity capital from shareholders • •Both sources of financing demand a return for providing cash • •Companies therefore need to at least recuperate their Weighted Average Cost of Capital from each investment they make – Firm •Debt finance •Equity finance •Investment •Cash •Return on Investment Weighted Average Cost of Capital •WACC = [E/V * Re] + [D/V * Rd * (1-Tc)] • •E = firm’s equity, D = firm’s debt, V = total value of firm’s financing (V = E+D) • •Re = cost of equity, Rd = cost of debt • •Tc = corporate tax rate (firms can claim cost of interest against tax) Cost of Debt •How much does it cost to borrow money? • •Government borrowing rate (LIBOR) –US$ 1.75% –UK£ 0.70% • •Corporate borrowing rate (LIBOR + X%) –Depends on loan amount and credit worthiness of borrower –Ratings agencies provide assessments used by lenders • •Corporate bond rate (latest Eurobond offering) –Gazprom 2017 Eurobond – 4.25% –BP 2017 US$ bond – 2.24% • •Interest payments are allowable for tax –Cost of debt = Interest rate x (1-tax rate) • • Credit ratings impact the cost of debt, as well as investor preceptions Cost of Equity •What constitutes a return for a shareholder? –Dividends –Capital Growth –Total Shareholder Return – •Average cost of equity –The minimum acceptable return – the risk free rate –The premium for investing in the equity market (the return on the equity market compared to the risk free rate) –The specific premium for each company (the Beta) – how different is it to the market •Beta value is a measure of specific risk for a company – 1 is the market average •BP – 0.99; ExxonMobil - 0.84 •Sound Energy – 2.83; Chesapeake – 2.68 • •Risk free rate (LIBOR) + (Beta for a specific company * the equity market premium) » • Total return to shareholders •Almost no gain in share price terms over almost 20 years •Shareholders doubled their money when dividends and other incentives are included Total return on FTSE World Index •Average return over 10 years = 6.86% •Average return over 5 years = 9.85% •Average return over 1 year = 12.0% •Average return over 20 years = 10.53% • The DCF Calculation as a foundation – WACC concept Image result for weighted average cost of capital •Weighted average cost of capital is corporate “interest rate” •2. •WACC is the cost to a company of financing the capital for a project, including debt and equity • •Cost of debt = average interest rate for company • •Cost of equity is theoretical return to investors in the company •Cost of Equity = Risk free rate +Beta*(Market return – Risk free rate) • •Essentially, how much return would an investor expect relative to putting his money with US Treasury stock, or in the stock market WACC Calculation •BP •Debt/Equity – 30:70 •Equity Market return – 10.53% •Risk free rate – 1.75% •Cost of Equity •1.75 + (0.99 x (10.53 – 1.75)) = 1.75 + 8.69 = 10.44 • •Cost of Debt – 2.24% x (1-0.2) = 1.79% • •WACC calculation • (10.44*0.7)+(1.79*0.3) • =7.31% + 0.54% • =7.85% • WACC Calculation •Sound Energy •Debt/Equity – 50:50 •Equity Market return – 10.53% •Risk free rate – 1.75% •Cost of Equity •1.75 + (2.83 x (10.53 – 1.75)) = 1.75 + 25.85 = 26.60 • •Cost of Debt – 5.75% (LIBOR+4%) x (1-0.2) = 4.60% • •WACC calculation • (26.60*0.5)+(4.60*0.5) • =13.3% + 2.3% • =15.6% • WACC Questions •Calculate the WACC based on the following assumptions: • •General –Risk-free rate – 1.5% –Equity market return – 8% –Corporate tax rate – 25% – •Specific –Company 1: Beta – 0.85, Interest rate on Debt – 3.5%, Share of Equity – 40% –Company 2: Beta – 1.75, Interest rate on Debt – 5%, Share of Equity – 30% –Company 3: Beta – 3.0, Interest rate on Debt – 7.5%, Share of Equity – 70% – •Double the Beta of Company 1. What happens to the WACC? –Do the same for company 3. What happens? – •In general, what is the optimal financing strategy for reducing WACC? –Can you think why it may or may not be possible to achieve this? – Terminal Value Calculation (1) •Two methodologies –Perpetual Growth Method –Exit Multiple Method • •Perpetual Growth Method –TV = [FCFn x (1+g)] / (WACC-g) – –TV = terminal value –G = perpetual growth rate of FCF –WACC = Weighted average cost of capital – •Generally used in academia rather than business –Need to assume “G” Terminal Value Calculation (2) •Exit Multiple Method –Preferred by industry as it compares a value of a business or asset with an observation in the market –The multiple tends to be the average for the industry or a peer group –The EV/EBITDA multiple is the most common – •The Exit Multiple Formula –TV=Financial Metric (EBITDA) x Trading Multiple (EV/EBITDA) – •Assume Terminal Value in final year +1, then discount with rest of cashflow model Terminal Value Calculation (3) •Looking for multiples •Average: 4.9 (Oil only) •Average: 8.6 •Average: 5.9 Internal Rate of Return •To calculate a NPV, we have to use a discount rate • •This rate is set by calculating the cost of capital, based on the expected rate of return expected by debt and equity investors • •But how high could this expected rate go before the NPV equals zero? • •This figure tells us the Internal Rate of Return (IRR) of the project –When the NPV is zero, it means that all the capital is repaid plus a certain level of return –As long as the IRR is higher than our discount rate, then the project will have a positive NPV and as reasonable rate of return Establishing the IRR of a project cashflow •NPV = 0 •Discount Rate Payback •How long does it take to recover the initial investment • •Measured in years (usually) but can be months for very rapid projects • •Can be calculated in simple or discounted terms –In other words either taking into account the time value of money or not Calculating Payback •US$30mm invested over three years • •Simple payback – US$30mm recovered after 1.5 years • •Discounted payback - $26mm recovered after 2 years Analysis to Support the Decision to drill an exploration well •Geologists/Geophysicists: –Interpret Seismic data and assess reservoir size probability distribution. –Assess the probability of source, reservoir and trap. •Reservoir Engineer: –Assess the recoverable reserves and reservoir properties for the 90%,50% and 10% cases. –Assess the number of production wells required. –Develop annual production profile for the life of the field. •Facilities Engineer: –Creates conceptual design for min, mean and max cases with costing and cost phasing. •Petroleum Economist: –Models the cashflow of the three reserve cases including tax or Production sharing effects. Derives the Net Present Value of Cashflows, the Internal rate of return and other metrics. –Integrates the NPV’s over the reserve distribution range to derive the Expected Present value. –Performs decision tree analysis based on the probability of the exploration well being successful. –Presents the investment case to management. •Create a theoretical cashflow based on assumptions known to date At exploration stage add risk to calculate an Expected Present Value (integration over range of reserves uncertainty) •EPV = $1,870 mm @10% Discount rate •N.B. If the field is viable over the entire range then assume the NPV of the 50% case equals the EPV Decision Tree Analysis •Min NPV $800mm •Oil Case •Gas Case •(not evaluated) •Probability of oil case =100% •Probability of gas case = 0% •EPV $1,870mm •Cost of Exploration Well =$50mm •Probability of finding hydrocarbon •= 20% •Probability of not finding hydrocarbon •= 80% •0.2 *(1870 – 50) •= 364 •0.8 *(– 50) •= -40 •$324 mm •This is called the Expected Monetary Value (EMV) at the discount rate used. •Dry Well •Mean NPV $1,870mm •Max NPV $2,900mm •EPV $1,870mm Risked Rate of Return •Risked Rate of Return = 15% •EMV @ 10% Discount Rate = $ 324 mm Exploration Proposal •‘It is recommend that the company drill an exploration well on the prospect at a cost of $50mm. • •The probability of discovering oil is 20% (in in 5). The mean discovery case has a recoverable reserves level of 900 million barrels of oil and a NPV @ 10% discount rate of $1,900mm. • •Risked exploration economics indicate an Expected Monetary value of $324mm @ 10% discount rate and a Risked Rate of Return of 15%.’ The Development Decision •Congratulations – you discovered oil at a level just above the mean reserves case. • •The exploration well, in addition to confirming a discovery, has provided useful information on reservoir quality, well flow rate and oil quality. • •Your share price has soared but you now need to drill four appraisal wells to narrow the uncertainty on the reserves range, work out what it will cost to develop the discovery and what the economics of the project are before you go to the banks and your shareholders to raise more capital. • • Decisions on incremental investments •I have discovered something new about the field • •I need to make an investment to enhance production • •Should I go ahead? • •How to adapt model? Reacting to a momentous event •I have developed an oil field and spent many billions of US$ • •Production has started • •The oil price collapses by 50% 2 years into the project • •How do I decide whether to continue or not? Scenario Planning •text Inflation and interest rates •I have $500 • •Inflation is running at 4% per annum, and the interest rate is 5% • •I want to purchase printer ink, which costs $5 per cartridge • •How many fewer cartridges can I buy in 7 years time than now if I just keep my $500 in my wallet? • •If I put my $500 in an interest bearing account, how many cartridges could I buy in 4 years time? Real and Nominal Figures •Question 1 –The cost of a plant in 2023 is $100mm in real (2018) terms –Inflation throughout the period has been 2.5% per annum –What is the cost of the plant in nominal terms in Year 5 – •Question 2 –We are assuming that the oil price is $60 in real (2018) terms –Inflation is assumed to be 2% per annum –What is the real oil price in 2021? –What is the nominal price in 2021? –What is the real price in 2021 if we assume that the oil price will rise at 1% above inflation? Questions •Using the Shale Production profile, calculate the NPV and IRR using the following assumptions –Production starts in 2019 –75% oil exports (export oil price $60 real) –Capex of $10 per barrel, with 80% of capex in Year 1 (2018) and 20% in Years 2-5 –Opex of $7 per barrel –Depreciation on a unit of production basis –Export tax of 30%, Royalty 5%, Other taxes 1% (all oil) •WACC assumptions –Cost of Debt – 5% (tax rate 20%) –Risk-free rate – 1.5% –Equity market return – 11% –Company Beta – 1.2 –Debt:Equity split is 40:60 • • • • 1.Apply the same discount rate to the Conventional Model –What are the NPV, IRR and breakeven oil price now? » 2.What is the breakeven oil price for the Shale model? 1. 3.Create the spider graph to show the sensitivities of the Shale model (put in Word Document and briefly discuss) » 4.Test the shale model with an oil price scenario and discuss the results 1. 5.If you had to drill an exploration well to justify the shale model and were told that the risk was 33% and the well cost was $5mm would you proceed? –What would the risk have to be before you decided not to drill? » 6.Compare the NPVs, IRRs, breakeven oil prices and sensitivities of the two projects. Which do you prefer? • •Please send me both models so I can see your workings • •Please write answers in a Word or Pages document and use graphs where appropriate • •