please complete the table below and show all formulas below thanks
1 Depletion Worksheet Template 2 Note: Not all rows will be needed for all problems 3 Note: Will need column augmentation if N>10 4 Note: Column A is for reference and can be hidden to make printable region cover a 10-year project life 5 Problem: 11-64. Acquisition cost: $2,500,000; N=15 years; year-1= 90,000 bbl; production decreases 6000 bbl/year;selling price = $10/bbl; expenses = $4/bbl. 6 7 Depletion Allowance Calculation 5 7 = 0 1 2 3 4 6 8 9 10 11 12 13 14 15 8 0 Cost Depletion Recoverable Units Purchased Annual Production Mineral Acquisition Cost Cost Depletion 90,000 84000 78000 72000 66000 60000 54000 48000 42000 36000 30000 24000 18000 12000 6000 31250 29166.7 27083.3 25000 22916.7 20833.33 18750 16666.67 14583.33 12500 10416.67 8333.333 6250 4166.667 2083.333 0.8928571 0.79719 0.71178 0.63552 0.56743 0.506631 0.452349 0.403883 0.36061 0.321973 0.287476 0.256675 0.229174 0.20462 0.182696 27901.786 23251.5 19277.4 15888 13003.5 10554.82 8481.548 6731.387 5258.896 4024.665 2994.543 2138.959 1432.339 852.5826 380.6172 0.15 9 10 (10) 11 (11) 12 (12) 13 (13) = (12)*(11)/(10) 14 15 16 (16) 17 (17) 18 (18) 19 (19) = (17)(18) 20 |(20) = ($C$16)(19) 21 (21) = (cost/unit)* (18) 22 (22) = (19) - (21) 23 (23) = 0.5(22) 24 (24) = min(20,23) 25 26 (26) = max(13,24) 27 Percentage Depletion Depletion Percentage Rate (%) Price/unit Units sold Mineral Sales Revenue Mineral Sales x Depletion % Expenses (except Depletion) = $4/bbl Minerals Income 50% Income Cap Maximum Allowable % Depletion = Depletion Deduction 28 PV of Depletion Deductions (12% MARR) 1421725 29 30 11-64 The Red River oil field will become less productive each year. Rojas Brothers is a small company that owns Red River, which is eligible for percentage depletion. Red River costs $2.5M to acquire, and it will be produced over 15 years. Initial production costs are $4 per barrel, and the wellhead value is $10 per barrel. The first year's production is 90,000 barrels, which will decrease by 6000 barrels per year. (a)Compute the annual depletion (each year may be cost-based or percentage-based). (b) What is the PW at i = 12% of the depletion schedule? 1 Depletion Worksheet Template 2 Note: Not all rows will be needed for all problems 3 Note: Will need column augmentation if N>10 4 Note: Column A is for reference and can be hidden to make printable region cover a 10-year project life 5 Problem: 11-64. Acquisition cost: $2,500,000; N=15 years; year-1= 90,000 bbl; production decreases 6000 bbl/year;selling price = $10/bbl; expenses = $4/bbl. 6 7 Depletion Allowance Calculation 5 7 = 0 1 2 3 4 6 8 9 10 11 12 13 14 15 8 0 Cost Depletion Recoverable Units Purchased Annual Production Mineral Acquisition Cost Cost Depletion 90,000 84000 78000 72000 66000 60000 54000 48000 42000 36000 30000 24000 18000 12000 6000 31250 29166.7 27083.3 25000 22916.7 20833.33 18750 16666.67 14583.33 12500 10416.67 8333.333 6250 4166.667 2083.333 0.8928571 0.79719 0.71178 0.63552 0.56743 0.506631 0.452349 0.403883 0.36061 0.321973 0.287476 0.256675 0.229174 0.20462 0.182696 27901.786 23251.5 19277.4 15888 13003.5 10554.82 8481.548 6731.387 5258.896 4024.665 2994.543 2138.959 1432.339 852.5826 380.6172 0.15 9 10 (10) 11 (11) 12 (12) 13 (13) = (12)*(11)/(10) 14 15 16 (16) 17 (17) 18 (18) 19 (19) = (17)(18) 20 |(20) = ($C$16)(19) 21 (21) = (cost/unit)* (18) 22 (22) = (19) - (21) 23 (23) = 0.5(22) 24 (24) = min(20,23) 25 26 (26) = max(13,24) 27 Percentage Depletion Depletion Percentage Rate (%) Price/unit Units sold Mineral Sales Revenue Mineral Sales x Depletion % Expenses (except Depletion) = $4/bbl Minerals Income 50% Income Cap Maximum Allowable % Depletion = Depletion Deduction 28 PV of Depletion Deductions (12% MARR) 1421725 29 30 11-64 The Red River oil field will become less productive each year. Rojas Brothers is a small company that owns Red River, which is eligible for percentage depletion. Red River costs $2.5M to acquire, and it will be produced over 15 years. Initial production costs are $4 per barrel, and the wellhead value is $10 per barrel. The first year's production is 90,000 barrels, which will decrease by 6000 barrels per year. (a)Compute the annual depletion (each year may be cost-based or percentage-based). (b) What is the PW at i = 12% of the depletion schedule
