[Solution] Least Squares Regression Curve
Create a Gas In Place (OGIP) distribution, determining the P10, P50 & P90 values using the following hints. Document your results in a 2 page report with key screen shots. Also upload your @Risk excel sheet.
a. Use the HW3_OGIP.XLS spreadsheet which has two tabs:
i. Welldata – this is the raw data from 25 wells in the same field.
ii. VolumetricModel – this is where the calculations reside for that field.
b. In the VolumetricModel tab, replace the Input Values for Depth, Thickness, GC, Density, PhiM, and Sw with distribution functions from @Risk. To obtain the distributions functions, go first to the WellData TAB.
c. In the WellData tab, choose a distribution function — for each of these inputs — that you think adequately matches the data. Be careful of using functions that result in nonsensical values (i.e. negative thickness; use RiskTruncate).
d. In the VolumetricModel tab, replace the OutputValue for GIPTotal with a RiskOutput function.
e. Run the Monte Carlo simulation utilizing the distributions of input variables and the single output variable GIPTotal to generate the distribution for GIPTotal and the P10, P50 and P90 estimates. Use at least 1000 iterations.
Convert the probabilistic OGIP estimation of HW3 to an EUR estimation, using the recovery factors for the gas given as a probability input function in HW4A_RF.xls. Use the function for shale gas RF as inputs for the EUR computations. Here are the steps
a. Your output function needs to be an RIskOutput function.
b. Run the MC simulation.
c. Determine the P10, P50 & P90 EUR values (assuming well is economic)
d. Give a table with P10, P50 & P90 EUR, as well as proved, probable and possible reserves volumes.
Using the data in ‘HW4B_Correlation Data.XLS’, do the following:
- Determine the correlation between depth and Gas Content, and simulate (with @RISK) 100 pairs of randomly generated depth and Gas Content data;
- Give a cross-plot on the same graph with (1) the raw input data, and (2) the data generated from the distributions [under task (a)].
- Fit a least squares regression curve through each of the data sets using xcel.
- Show the equations and correlation coefficients of each of the equations in the plot.
If you have done this properly, the input and simulated dataset will have similar clustering and similar least squares fits.