Set up an Excel Spreadsheet to do the computations as required below. Fill input areas in yellow. Note that a new reserve report is available December 31 each year so you will add production for the year to get the best estimate of reserves at the beginning of the year. X1 is the first year, X2 is the second year, etc. Compute DD&A expense for Boomer and Sooner for the four years (X1 through X4) on an annual basis. Also, compute the balance in the payout account at the end of each year.

On 1/1/X1, Boomer Oil Company paid Ms. Ellis $100,000 and acquired the working interest and Ms. Ellis retained a 1/8 royalty interest. On March 1, X1, Boomer Oil Company farmed out the WI in the Ellis property to Sooner Oil Company. The farmout agreement provided that Boomer would retain a 10% of 7/8 overriding royalty interest convertible to a 50% working interest after payout. Payout is defined as the first day of the month after which the net cash flow to the farmee equals the expenses paid by the farmee for IDC and L&WE. Sooner Oil Company incurred expenses of $400,000 for IDC and $100,000 for lease and well equipment for drilling the Ellis #1 well. The present value of the cost to plug and abandon the well at the end of its productive life is estimated to be $100,000. The cost will be borne equally by Boomer and Sooner. Production began on October 1, X1.

Gross oil production, reserves, sales price, and expenses for X1 were as follows:

Total production during X1 (Constant for Oct through Dec

at 240 barrels per month) 720 bbls

Sales price for year $90/bbl

Severance tax rate 5%

Lease operating expenses $10/bbl

Proved reserves 12/31/X1 30,000 bbls

Proved developed reserves 12/31/X1 16,000 bbls

Required:

1. (50 points) Compute DD&A for Boomer Oil Company and Sooner Oil Company for X1. What is the remaining balance in the payout account as of 12/31/X1 (or if payout occurred, in which month)?

Gross oil production, reserves, sales price, and expenses for X2 were as follows:

Total production during X2 (Constant per month) 2,760 bbls

Sales price for year $100/bbl

Severance tax rate 5%

Lease operating expenses $12/bbl

Proved reserves 12/31/X2 25,000 bbls

Proved developed reserves 12/31/X2 12,000 bbls

Required:

2. (50 points) Compute DD&A for Boomer Oil Company and Sooner Oil Company for X2. What is the remaining balance in the payout account as of 12/31/X2 (or if payout occurred, in which month)?

Gross oil production, reserves, sales price, and expenses for X3 were as follows:

Total production during X3 (Constant per month) 2,640 bbls

Sales price for year $80/bbl

Severance tax rate 5%

Lease operating expenses $12/bbl

Proved reserves 12/31/X3 22,000 bbls

Proved developed reserves 12/31/X3 12,000 bbls

Required:

3. (50 points) Compute DD&A for Boomer Oil Company and Sooner Oil Company for X3. What is the remaining balance in the payout account as of 12/31/X3 (or if payout occurred, in which month)?

Gross oil production, reserves, sales price, and expenses for X4 were as follows:

Total production during X4 (Constant per month) 2,520 bbls

Sales price for year $110/bbl

Severance tax rate 5%

Lease operating expenses $16/bbl

Proved reserves 12/31/X4 19,000/bbl

Proved developed reserves 12/31/X4 9,000/bbl

Required:

4. (50 points) Compute DD&A for Boomer Oil Company and Sooner Oil Company for X4. What is the remaining balance in the payout account as of 12/31/X4 (or if payout occurred, in which month)?

HINT: The most difficult part of this assignment is the computation of DD&A expense. In year 1 we estimate each partys share of the reserves by computing payout using the sales price and operating expenses for year 1. However, the sales price changes in year 2 so that we have to re-estimate when payout will occur using the new data for year 2. At the end of year 2, look at the balance in the payout account then determine when payout will occur using the new sales and expense numbers for year 2. Then use that information to allocate the new reserve number at the end of year 2. Follow this pattern for years 3 and 4.