OIL AND GAS WELL INVESTMENT ANALYSIS USING THE LOTUS 1-2-3 DECISION SUPPORT SYSTEM Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 245 OIL AND GAS WELL INVESTMENT ANALYSIS USING THE LOTUS 1-2-3 DECISION SUPPORT SYSTEM John Wingender, Oklahoma State University Jack Wurster, Steve Silver Trading Company ABSTRACT This paper describes a decision support system using Lotus 1-2-3 designed to introduce the student to widely used measures of investment worth. The software has been applied successfully in the classroom and also on a professional level in evaluating the value of oil and gas investment proposals. The basic procedure involves forecasting revenues and expenses and the resulting cash flows. These cash flows are then evaluated using several different techniques. The student can use sensitivity analysis to gain an appreciation for the critical variables by changing the values of the input variables and observing the corresponding changes in the output variables. INTRODUCTION Presentation of capital budgeting and investment analysis techniques is a critical part of any business oriented curriculum. While the described simulation system is rather specialized, it is an excellent vehicle for presenting these concepts and provides hands on interaction with the individual student. The objectives of capital budgeting are to identify and to rank projects that will maximize the present value of a firm or an individual. A common method of ranking project proposals currently being used throughout the petroleum industry is the discounted cash flow technique (see Thompson and Wright [3]). The annual cash flows associated with an oil or gas property are seldom constant and cannot be treated like an annuity. Non-constant cash flows over time result in a need to evaluate the investment on a year by year basis and can involve huge amounts of “number crunching” for proposals with expected lives of over 20 years. An effective computerized decision support system can greatly reduce the user time required to evaluate a proposal by handling the quantitative aspects of the investment analysis (see McLeod and Mazursky [2]). This will allow the user more time to concentrate on the qualitative aspects of the analysis that require judgment or expert knowledge. The purpose of this paper is to describe a decision support system (DSS) designed to assist the user in evaluating oil and gas investment opportunities. The input and output variables are described in chronological order in the body of the report with reference to actual output found in Figures A and B. The general description of the system is followed by a critique of its flexibility and limitations. Basic instructions required to operate the system are provided in Appendix I. Appendix II outlines the procedure for calculating the production of oil and gas over a given period. General Overview The sequential nature of Lotus 1-2-3 operations resulted in a “dual” spreadsheet (see Bolocan [1]). The input format is demonstrated in Figure A. Most output variables shown in Figure B were calculated in cells outside of the printed range. This approach was required due to the fact that the value of certain cells in the matrix were dependent on values in cells that came sequentially later. In order to avoid having to calculate the entire spreadsheet twice, the required calculations were located outside of the printed range and sequentially higher on a row basis. Calculation is done on a row by row basis and this allowed the correct values to be available for placement in the printed range. A more detailed description of this technique will be provided in the output variable section of this paper. System operation is fairly straightforward and the output consists of several generally accepted measures of investment worth. This information can be used to aid in capital allocation decisions. Input Variables The input variables will be discussed in chronological order as they appear in Figure A. 1. General Information: The data located at the top of page one identifies the name of the well being evaluated, the name of the owner, and the date the evaluation was run. A space is provided that can be used to distinguish between multiple runs on the same well. The example provided shows that this is the “worst” case analysis of Gusher No. 1. 2. Initial Potential: The initial potential is the expected starting flow rate for both oil and gas in barrels of oil per day and thousand cubic feet of gas per day units respectively. This information is normally provided by an engineering professional who bases his estimate on offset wellbores (if available). 3. Annual Decline Rate: Predicting the annual exponential decline rate requires subjective engineering judgment and can be based on analogous wellbores or local trends. The meaning of the annual decline will be described in detail in the output section of the paper. It is important to note that all input variables displayed on a percentage basis must be input as fractions; e.g., 1.0. 4. Working Interest: This variable describes the percentage of all costs that the evaluated interest must pay. The “evaluated interest” is simply a reference to the individual or group for whom the evaluation is being run. 5. Net Interest: This variable is the percentage of all revenues to which the evaluated interest is entitled. Individual working and net interests will be clearly defined in a document known as the Joint Operating Agreement. 6. Product Prices: Current market prices for oil and gas on a per barrel (BBL) and per thousand cubic feet (MCF) basis are used. 7. Escalate: The escalate variable allows prices to change over time depending on perceived future economic Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 246 factors and conditions. A negative value here will cause product prices to fall over time. 8. Maximum Price: This variable allows the user to set a ceiling product price following a systematic escalation described above. 9. Severance Tax Rate: This variable is a tax on gross production revenue that varies from state to state. Oklahoma’s current rate is 7.085% for both oil and gas. 10. Annual Expenses: The annual expenses amount is the summation of annual fixed and variable expenses associated with the operation of the new well proposal. This variable is also generally referred to as operating and maintenance expenses. 11. Escalate: The escalation option for annual expenses is independent of the escalate variable for prices (#7). 12. Discount Rate: The desired discount rate is applied to annual cash flows that will be displayed in the output. There are several criteria used to select the discount rate such as the evaluated interest’s average investment opportunity rate, the cost of capital, or perhaps some management established minimum acceptable rate of return. 13. Project Life: The maximum life of the project to be considered is independent of the overall economics. Historical data sometimes shows that the physical life of the wellbore in question may be less than the economic life. The system is set up to end the evaluation based on the input project life or the economic limit, whichever comes first. The economic limit is defined as that point in time where the annual cash flow becomes negative. 14. Starting Year: This variable provides a starting reference point for the evaluation. 15. Investment Required: The estimated time zero investment variable is the dollar amount required to generate the forecasted cash flows. It is the cost of the proposed well. 16. Discounting Method: Two types of fiscal year discounting methods are available in the analysis. Inputting 1 assumes that all annual cash flows following the initial investment occur at mid-year--June 30th. Inputting 2 assumes that cash flows occur at the end of the year. 17. General Information: Brief input instructions and descriptions for some of the input variables are provided at the bottom of Figure A. Output Variables Output variables will be described in chronological order as they appear in Figure B. 1. Gross Production: One of the most common and generally accepted methods of forecasting future oil and gas production is the exponential (constant percentage) decline technique. The basic relationships and process are as follows: The starting flow rate and annual decline variables provided as input data can be used to calculate the rate at any time t in the future. To calculate the total oil or gas produced during a period (year) both the starting and ending rate must be known. The starting and ending rates for oil and gas are calculated outside the printed range and above where this data is required to generate printed output. Integration of the rate equation above will result in a relationship that can be used to calculate fluid produced during any given time period. Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 247 This is the basic relationship used to calculate the gross annual production for oil and gas. An example calculation is provided in Appendix III. 2. Net Production: Net production is simply the product of gross production and net revenue interest. 3. Revenue: Net revenue is the sum of net oil production times oil price and net gas production times gas price. 4. Production + WP Taxes: The production tax as mentioned earlier is simply a severance tax placed on gross revenue from oil and gas production. WP stands for windfall profit tax. This charge has currently fallen out of project analysis due to the fact that depressed product prices are at the present time below the base price used to determine windfall profit and the corresponding taxes. 5. Future Investment: The future investment column was provided to allow the user to account for future expenses not included in normal operating expenses. For example, the $40,000 incurred in year four in Figure B is for a replacement gear box and motor for a pumping unit. This forecasted expense would not be included in the annual expense column. If future one time investments of this nature are forecasted, the user must place these values in the appropriate cells prior to calculation. 6. Operating Income: Operating Income equals Revenues minus (Production and WP Taxes), minus Operating Expenses and minus Future Investments. Operating income also has to be calculated outside of the printed range and above the printed output cell so that the economic limit can be identified and the evaluation terminated at that point. Including negative cash flows in the evaluation would not be realistic. 7. Discount Factor: The discount factor is used in the compound interest formula to calculate the present value of future cash flows discounted at a given rate. Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 248 n = DM/2 + years from tune zero to cash flow where DM is the discount method provided as input data. 8. P.V. @ 12%: The present value of future operating income discounted at the given rate as outlined above. 9. Cum Gross Prod: Cumulative gross oil and gas production. 10. Cum Net Prod: Cumulative net oil and gas production. 11. Cum Nondisc Op Income: Cumulative non-discounted operating income. 12. Cum Disc Op Income: Cumulative discounted Operating income. 13. Internal Rate of Return: This variable is the discount rate that equates the future cash flows to the initial investment, or results in a net present value of 0. This is a built-in function that generates a solution through an iterative process. 14. Nondiscounted x’s Investment Earned: Nondiscounted cumulative operating income/initial investment. 15. Discounted x's Investment Earned: Discounted cumulative operating income/initial investment. 16. Present Value Profile: The present value of future operating income discounted at the indicated rate minus the initial investment. This is a built-in function that assumes end of period cash flows. Flexibility and Limitations The primary advantage of this system is that several different scenarios can be run with a minimum time investment. Students have run a sensitivity analysis on the hypothetical well--Gusher No. 1. In this case it is assumed that some of the input variables, such as the annual decline rate and project life, were known while others like the initial potential, product prices, expenses, and the initial investment were subject to variability. The best, base and worst cases give the decision maker a range of possible values for measures for investment worth and an indirect indication of the risk associated with the investment. This DSS, while obviously not as sophisticated as some mainframe software packages, does give a fairly good indication of oil and gas investment worth. When the DSS results were compared to various mainframe programs, they were very similar. The DSS is limited to the evaluation of oil and gas properties with production declining at an exponential rate. There are several other decline models arid the exponential decline method does not always provide the best curve fit. Other declining production relationships must be specially calculated and inserted into the DSS. The sequential computation associated with the spreadsheet causes additional considerations, but most special needs can be handled by customizing the basic spreadsheet for specific applications. This aspect of the system does require, however, that the individual user be familiar with spreadsheet operations. While spreadsheet based decision support systems have certain built-in unavoidable limitations, creative thinking and a thorough understanding of operational procedures can go a long way in reducing inherent disadvantages. Conclusion The described software can be used by educators to develop the fundamental techniques of investment analysis. Scenarios developed by the instructor and executed by the student can be an effective demonstration of the importance of certain input variables such as the discount rate and the timing of cash flows. The software has proven to be a very effective learning tool in presenting time value of money concepts and the basics of investment analysis. REFERENCES [1] Bolocan, David, Lotus 1-2-3 Simplified, 2nd Edition (Tab Books, Inc., 1986). [2] McLeod, Raymond, Jr. and Alan D. Mazursky, Decision Support Software for the IBM Personal Computer (Science Research Associates, Inc., 1986). [3] Thompson, Robert S. and John D. Wright, Oil Property Evaluation (Thompson-Wright Associates, 1985). APPENDIX I INSTRUCTIONS FOR USER A basic understanding of PC operations will be assumed. All references are to Figures A and B. Additional information concerning input and output variables can be found in the body of the report. A. Place the Lotus system disk in drive A and the data disk in drive B. Turn the machine on. B. Work through the menus to the Lotus spreadsheet. C. Use the FILE command to call up the ECON spreadsheet. D. Move the cursor to the appropriate cells and input the well name, owner, date and case description. E. Move the cursor to column F and input the required information for oil production. All percent values must be input as fractions. Move the cursor to column H and input the gas information. F. Move the cursor back to column F and input the rest of the required information. G. Move to column L and input any forecasted future investments as in the output variable section. H. Press F9 (Function Key #9) to calculate the new spreadsheet. I. Save the new spreadsheet to a file name of your choice J. Obtain a printout of the results if required. K. Develop a new case or quit. APPENDIX II EXAMPLE PRODUCTION CALCULATION Find: Oil production for year 1 given the information in Figure A. Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 249 Table of Contents Volume 14, 1987 Simulation Gaming as An Experimental Context for the Study of Multicriteria Decision Making How to Multiply Your Management Game Grading the Personnel In-Basket Exercise Simulation: The Players™ Perspective The Acute Susceptibility of Nominal Grouping to Negativity The Relative Value of the National ABSEL Meetings: An Analysis of Perceptions by Faculty and Deans A Survey of Behavioral Labs Used by American Business Schools A Structured Framework for Applying Conceptual Models to Problem Analysis: Hardening Up An Expert System Approach for Teaching Marketing Case Analysis From Theory to Practice: A Model for Teaching Beginning Advertising The Use of a Simple Forecasting Technique During an Interactive Computerized Business Game An Investigation of the Relationship Between Formal Planning and Simulation Team Performance and Satisfaction The Exponential Logarithm as an Algorithm for Business Simulations Learning macroeconomic Theory and Policy Analysis Via Microcomputer Simulation Laptop: A Principles of Marketing Simulation Teaching About Sampling in a Marketing Research Class Integrating Decision Support Systems and Business Games Demand Generation in a Service Industry Simulation: An Algorithmic Paradox ABSEL - At a Crossroads? Research on Predicting Performance in the Simulation An Expert System for Financial Planners "Developing Various Student Learning Abilities Via Writing, the Stock market Game, and Modified Marketplace Game in Beginning Macroeconomics" Professional Activity Reports: Getting back to Basics Œ A Preliminary Investigation "A Comparison of Performance, Attitudes, and behaviors of MBA and BBA Students in a Simulation Environment: A Preliminary Investigation" Decision Styles and Student Simulation Performance: A Replication Personal Power Strategies: An Experiential Exercise LTL I: A Trucking Simulation Game Total Enterprise Business Games: An Evaluation Can the Skill of Management be Taught? Developing Critical Thinking and Effective Communication Meaningful Notebooks and Enhanced Learning Using a Totally Menu-Driven Data base and Decision Support (DSS) Program Using a Computerized Menu-Driven Data Entry and fiWhat-Iffl Program as a Pedagogical Enhancement for Management Simulation Using a Joint Project Involving MBA Marketing Management and Undergraduate Marketing Research Students to Teach Marketing Research Testing the PAGE Technique: Results and Further Developments A Comparison of Student Perceptions with Accepted Expectations for Business Simulations "Conflict Management in Action: Verbal Strategies, Nonverbal behaviors and Conflict Styles" Dramatic Monologues as Surrogates for Experiential Learning Influencing One™s Superior: An Assessment and Exercise Personality Traits and Organizational Cultures: Two One-Hour Experiential Learning Exercises Computype: A Strategic Marketing Game Open System Simulations and Simulation Based Research "Teaching Forecasting to the Masses: Little Decision Support Training, But a lot of Answers" Goal Setting and Performance Evaluation with Different Starting Positions Œ The Modeling Dilemma The Gamesmanship of Pricing: Building Pricing Strategy Skills Through Spreadsheet Modeling Teaching MPR Experientially Through the Use of Lotus 1-2-3 Business Simulation Through Activities of a Manufacturing Company Simulated Stock Broker Teaching Employee Counseling Skills to Management Students: A Simulation Airline: A Strategic Management Simulation Developing Simulations and Experiential Exercises on the Personal Computer: Some Critical Issues A Powerful Tool Œ For What? An Integrative Systems Approach to Simulating Free Enterprise in a Small Business Course in a Business School Profits: The False Prophet The Use of Expert Systems to Develop Strategic Scenarios: An Experiment Using a Simulated Market Environment An Interdisciplinary Workshop: Spreadsheet Modeling and Case Analysis Techniques for Beginning Executive MBA Students An Innovative Approach to an O. D. Class Œ The Creative Interaction The Application of Spreadsheet Software Technology to Complete Taxpayer Elections Experiential Learning as the Cornerstone of an Industry Management Course "Identifying, Exercising and Enhancing Right Brain Skills in the Business Classroom" Utilizing Envisionary Technology in the Business Policy Course: An Experiential Pedagogy Business Simulation in the Policy Course: A Survey of American Assembly of Collegiate Schools of Business The Moving Van: Vehicle to Success of Disaster for the Two-Gender Work Force Oil and Gas Well Investment Analysis Using the Lotus 1-2-3 Decision Support System Team Cohesion Effects on Business Game Performance