Is the analysis done by me correct. If not can you please provide some feedback. some input on the report will also be appreciated.
Thanks
>Assumptions
Sheet
Information ,000,000
.45
.00%
.00%
.00%
Information Notes 0 1 2 3 4 5 10 11 10 ^7 ,231
6,061
5
1 – 0 1
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 100 100 100 100 100 100 80 60 40 20 -20 -20 -20 -20 Information Notes 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 20 ^5 Well operating costs ($s per GJ) 1 ^7, ^9 ^7, ^11 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 -10 -10 -10 -10 Notes – Allowed Extraction Costs). Allowable Extraction Costs include operating expenses and depreciation but exlcudes the well capping and rehibilitation expense.
Notes ^1 Contract specifies that this is a fixed price of gas at the wellhead over the life of project. ^3 State Royalties are tax deductible. ^4 Tax is paid in the year of income. Note that it is assumed that any tax loses in any year will offset tax gains within the Primitive Energy tax consolidated group. 100 ^5 Capital expenditure on wells is recorded at the start of year in which the well is drilled. 20 ^6 Wells must operate for stated life to ensure well pressure is sufficiently low for well capping. 10 ^7 All revenues and operating expenses are assumed to occur at the end of given year. ^8 Straight Line depreciation method (depreciating down to zero over the life of the well). ^9 Wages and other operating expenses are all captured by the operating cost per GJ. Value
0 ^10 Given the nature of the project, supply and servicing contracts, project has no working capital requirements. 0 ^11 Wells drilled in years as indicated. Capital expenditure recorded at the beginning of the year. For example, the beginning of Year 1 corresponds to the 0 column. 3,000,000 – 30,000,000 – 30,000,000 – 30,000,000 – 30,000,000 – 30,000,000 DA
– 18,000,000 – 24,000,000 – 30,000,000 – 30,000,000 – 30,000,000 – 30,000,000 – 30,000,000 – 30,000,000 0 0 9,291,031 6,000,000 12,000,000 18,000,000 24,000,000 30,000,000 30,000,000 30,000,000 30,000,000 30,000,000 – 60,000,000 – 60,000,000 – 60,000,000 – 60,000,000 – 0 – 0 – 0 – 0 – 0 – 0 0 0 – 60,000,000 7,655,302 39,254,094 12,129,679 – 2,369,904 – 9,328,076 – 12,334,089 – 13,505,533 – 60,000,000 Vertical Well Drilling Information Information Notes 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 17 ^6 Wells must operate for stated life to ensure well pressure is sufficiently low for well capping. ^8 Straight Line depreciation method (depreciating down to zero over the life of the well). 0 ^9 Wages and other operating expenses are all captured by the operating cost per GJ. 250,000,000 – 6,000,000 – 12,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 15,000,000 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 – 14,705,882 0 – 50,000,000 – 50,000,000 – 50,000,000 – 50,000,000 0 0 0 0 0 0 0 0 0 0 0 0 0 Salvage 0 71,864,486 42,119,492 23,775,757 12,545,487 5,610,875 1,265,789 – 1,502,459 – 3,296,270 – 4,477,979 – 5,268,769 – 5,805,834 – 6,175,663 – 6,433,643 Horizontal Well Drilling Information Information Notes 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 EFB210: FINANCE 1, Semester 1 2020
Capital Budgeting Report and Analysis
___________________________________________________________________________________
General Information
Marks: 25
Weight: 25%
Format: Report and Analysis
Due: 17 May 2020 (end of day, 11:59:59pm)
Submission: Submit report and Excel analysis files by uploading your files to the Finance 1
Assignment portal in Blackboard. Note that when saving and submitting the report
and analysis files, please ensure that:
– You upload the correct files and that the files are not corrupted in any way. As
markers, we can only mark what is provided to us at the time of submission.
– Each filename must include your name and student number, and the file
extensions must be either x (report) or .xlsx (analysis). o For example, if Johnny Depp was studying Finance 1, he would save his
report and analysis as:
Johnny Depp 1234567 Report x
Johnny Depp 1234567 Analysis.xlsx
o Please note that file types that cannot be opened in Word or Excel,
e.g. .numbers files, are not accepted.
o Part of the assignment’s CRA relates to correct filenames.
__________________________________________________________________________________ Outline
Primitive Energy owns several coal seam gas reserves in south-west Queensland. As a relatively
minor player in the Queensland Liquefied Natural Gas (LNG) market, Primitive does not have the
capacity to transfer and process the gas for sale to domestic or international buyers. Instead,
Primitive simply extracts the gas and then sells it immediately (at the well-head, which is at the
surface) to one of the major gas companies operating in the area. Recently, Primitive entered into a
contract to sell gas from one of its reserves for the fixed price of $4.45 per gigajoule (GJ) for the life
of the reserve.
With this contract in place, Primitive’s management are currently trying to determine whether they
should extract the gas through conventional ‘Vertical’ drilling or the recently developed ‘Horizontal’
drilling. As indicated in Figure 1, the Vertical drilling approach drills to the coal seam while the
Horizontal approach drills down to and then across the coal seam.
Figure 1: Vertical and Horizontal Drilling
Source: http://geology.com/articles/horizontal-drilling/ (modified)
Additional points of note are summarised in Table 1.
Table 1: Summary Points for Vertical and Horizontal Drilling
Vertical: Smaller capital outlay, however, more wells are generally required due to Horizontal: Higher capital outlay given length of drilling, technology and difficulty. Specific detail on each of the well types including: capital outlay, production, maintenance,
depreciation, state royalties, well drilling and capping schedule and other related information
required for analysis is presented in the ‘Well Drilling Information’ spreadsheet.
http://geology.com/articles/horizontal-drilling/ Task
Provide a detailed financial analysis of each well type and an accompanying report that explains and
justifies methodology, recommends a well type and highlights limitations with the analysis and
recommendations. To complete this task, the manager has requested the following:
– The financial analysis is to be completed in Excel. The file is to be easily adjustable for different
scenarios and all inputs must be in the one sheet called ‘Assumptions’ with the analysis of each
well conducted on a separate sheet.
– The report is to be short (600 words + 20% tolerance) and written in a manner that can be
understood by a person with a basic understanding of financial analytical tools. It should have
the following sections,
o Summary: Brief outline of task, methodology and recommendation. o Methodology: Explains and justifies the selected evaluation metric. o Recommendations: Recommends a preferred drilling approach and provides insight into o Limitations: Highlights the key limitations with the analysis and recommends
2
Gas Drilling
Information
General Information
Notes
Total Reserve Size (gigajoules – GJs)
2
5
0
Price per gigajoule of production
4
^
1
State Royalties
10
^2, ^
3
Tax
30
^4
Required Rate of Return
11
discount rate
Vertical Well Drilling Information
6
7
8
9
12
13
14
Capital expenditure ($s per well)
3,000,000
^5
Well life (years)
^6
Well salvage ($s per well)
– 0
Well capping and rehabilitation expense ($s per well)
300,000
^7
times the total number of wells
Well production profile (GJs per well)
5
17
744,2
50
486,064
288,770
15
76,722
34,311
13,958
5,
16
1,739
total production per well
100
Well depreciation method
straightline
^7, ^8
2,324,271
232,427,071.69
Well operating costs ($s per GJ)
^7, ^9
Working Capital
^10
Wells drilled in given years and their operating life
^7, ^11
Wells drilled at the beginning of
Year
20
Wells drilled at the beginning of Year 2
Wells drilled at the beginning of Year 3
Wells drilled at the beginning of Year 4
Wells drilled at the beginning of Year 5
Wells in operation
40
60
80
Wells capped
-20
Horizontal Well Drilling Information
18
19
21
Capital expenditure ($s per well)
5,000,000
Well life (years) 17 ^6
Well salvage ($s per well) – 0 times the total number of wells
Well capping and rehabilitation expense ($s per well) 300,000 ^7 total production per well 50
Well production profile (GJs per well) ^7
680,922
1,136,692
1,068,150
731,671
463,089
288,688
181,135
115,289
74,630
49,154
32,923
22,406
15,477
10,840
7,691
5,523
4,010
4,888,290
244,414,502.05
Well depreciation method straightline ^7, ^8
Working Capital – 0 ^10
Wells drilled in given years and wells in operation profile
Wells drilled at the beginning of Year 1
Wells drilled at the beginning of Year 2 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 3 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 4 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 5 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells in operation 10 20 30 40 50 50 50 50 50 50 50 50 50 50 50 50 50 40 30 20 10
Wells capped
-10
^1 Contract specifies that this is a fixed price of gas at the wellhead over the life of project.
^2 in a given year, State Royalties ($) = State Royalties (%) x (
Revenue
^3 State Royalties are tax deductible.
^4 Tax is paid in the year of income. Note that it is assumed that any tax loses in any year will offset tax gains within the Primitive Energy tax consolidated group.
^5 Capital expenditure on wells is recorded at the start of year in which the well is drilled.
^6 Wells must operate for stated life to ensure well pressure is sufficiently low for well capping.
^7 All revenues and operating expenses are assumed to occur at the end of given year.
^8 Straight Line depreciation method (depreciating down to zero over the life of the well).
^9 Wages and other operating expenses are all captured by the operating cost per GJ.
^10 Given the nature of the project, supply and servicing contracts, project has no working capital requirements.
^11 Wells drilled in years as indicated. Capital expenditure recorded at the beginning of the year. For example, the beginning of Year 1 corresponds to the 0 column.
Vertical DCF
General Information Information
Total Reserve Size (gigajoules – GJs)
250,000,000
Price per gigajoule of production
4.45
State Royalties
10.00%
^2 in a given year, State Royalties ($) = State Royalties (%) x (Revenue – Allowed Extraction Costs). Allowable Extraction Costs include operating expenses and depreciation but exlcudes the well capping and rehibilitation expense.
Tax
30.00%
Required Rate of Return
11.00%
No. of wells
Per year
Well Life (years0
Depr’ method
Straight line
Dep’ rate
10%
Salvage
working Capital
Capital Expenditure per well
Capital Expenditure for 100 wells
300,000,000
Year 0 1 2 3 4 5 6 7 8 9 10 11
Revenue
46,033,602
132,476,417
129,779,162
102,802,150
69,447,092
34,141,240
15,268,201
6,211,262
2,298,556
773,773
Operating Costs
–
517,231
–
744,250
– 486,064
– 288,770
–
156,061
– 76,722
– 34,311
– 13,958
–
5,165
– 1,739
Well Capping and Rehabilition exp
–
6,000,000
–
12,000,000
–
18,000,000
–
24,000,000
–
30,000,000
EBIT
39,516,371
119,732,168
111,293,097
78,513,380
3
9,291,031
4,064,518
– 14,766,110
– 23,802,696
– 27,706,609
– 29,227,965
Depreciation
– 6,000,000
– 12,000,000
Gain/Loss on Sale
EBIT
33,516,371
107,732,168
93,293,097
54,513,380
– 25,935,482
– 44,766,110
– 53,802,696
– 57,706,609
– 59,227,965
State Royalties
3,951,637
11,973,217
11,129,310
7,851,338
3,929,103
406,452
– 1,476,611
– 2,380,270
– 2,770,661
– 2,922,797
Tax
– 11,240,402
– 35,911,615
– 31,326,722
– 18,709,415
– 3,966,040
7,658,709
13,872,816
16,854,890
18,143,181
18,645,229
NOPAT
26,227,606
83,793,769
73,095,685
43,655,302
9,254,094
– 17,870,321
– 32,369,904
– 39,328,076
– 42,334,089
– 43,505,533
Add back Dep’. And Gain/Loss
30000000
CF from Ops.
32,227,606
95,793,769
91,095,685
6
7,655,302
39,254,094
12,129,679
– 2,369,904
– 9,328,076
– 12,334,089
– 13,505,533
Cap.Ex
– 60,000,000
– 300,000,000
Salvage 0
Change in Working Capital
CFt
– 27,772,394
35,793,769
31,095,685
Discount Factor
1.0000
0.9009
0.8116
0.7312
0.6587
0.5935
0.5346
0.4817
0.4339
0.3909
0.3522
PV of CFt
– 25,020,175
29,051,026
22,736,897
5,042,785
23,295,394
6,485,022
– 1,141,484
– 4,047,699
– 4,821,701
– 4,756,439
NPV
– 13,176,376
Capital expenditure ($s per well) 3,000,000 ^5
Well life (years) 10 ^6
Well salvage ($s per well) – 0
Well capping and rehabilitation expense ($s per well) 300,000 ^7 times the total number of wells
Well production profile (GJs per well) ^7 517,231 744,250 486,064 288,770 156,061 76,722 34,311 13,958 5,165 1,739 total production per well 100
Well depreciation method straightline ^7, ^8 2,324,271 232,427,071.69
Well operating costs ($s per GJ) 1 ^7, ^9
Working Capital – 0 ^10
Wells drilled in given years and their operating life ^7, ^11
Wells drilled at the beginning of Year 1 20 20 20 20 20 20 20 20 20 20 20
Wells drilled at the beginning of Year 2 20 20 20 20 20 20 20 20 20 20 20
Wells drilled at the beginning of Year 3 20 20 20 20 20 20 20 20 20 20 20
Wells drilled at the beginning of Year 4 20 20 20 20 20 20 20 20 20 20 20
Wells drilled at the beginning of Year 5 20 20 20 20 20 20 20 20 20 20 20
Wells in operation 20 40 60 80 100 100 100 100 100 100 80 60 40 20
Wells capped -20 -20 -20 -20 -20Horizontal DCF
General Information Information
Total Reserve Size (gigajoules – GJs) 250,000,000 Notes
Price per gigajoule of production 4.45 ^1 Contract specifies that this is a fixed price of gas at the wellhead over the life of project.
State Royalties 10.00% ^2 in a given year, State Royalties ($) = State Royalties (%) x (Revenue – Allowed Extraction Costs). Allowable Extraction Costs include operating expenses and depreciation but exlcudes the well capping and rehibilitation expense.
Tax 30.00% ^3 State Royalties are tax deductible.
Required Rate of Return 11.00% ^4 Tax is paid in the year of income. Note that it is assumed that any tax loses in any year will offset tax gains within the Primitive Energy tax consolidated group.
No. of wells 50 ^5 Capital expenditure on wells is recorded at the start of year in which the well is drilled.
Well Life (years)
Depr’ method Straight line ^7 All revenues and operating expenses are assumed to occur at the end of given year.
Dep’ rate
6%
Salvage Value
working Capital 0 ^10 Given the nature of the project, supply and servicing contracts, project has no working capital requirements.
Capital Expenditure per well 5,000,000 ^11 Wells drilled in years as indicated. Capital expenditure recorded at the beginning of the year. For example, the beginning of Year 1 corresponds to the 0 column.
Capital Expenditure for 50 wells
Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Revenue
30,301,025
101,165,589
142,598,073
130,237,387
103,037,288
64,233,024
40,302,438
25,651,826
16,605,178
10,936,734
7,325,375
4,985,233
3,443,617
2,411,981
1,711,345
1,228,879
892,327
Operating Costs
– 680,922
– 1,136,692
– 1,068,150
– 731,671
– 463,089
– 288,688
– 181,135
– 115,289
– 74,630
– 49,154
– 32,923
– 22,406
– 15,477
– 10,840
– 7,691
– 5,523
– 4,010
Well Capping and Rehabilition exp
– 3,000,000
– 9,000,000
– 15,000,000
EBITDA
26,620,104
94,028,897
132,529,922
117,505,717
87,574,199
48,944,336
25,121,304
10,536,537
1,530,548
– 4,112,420
– 7,707,548
– 10,037,172
– 11,571,860
– 12,598,859
– 13,296,347
– 13,776,644
– 14,111,683
Depreciation
–
2,941,176
–
5,882,353
–
8,823,529
–
11,764,706
–
14,705,882
Gain/Loss on Sale – 0
EBIT
23,678,927
88,146,544
123,706,393
105,741,011
72,868,316
34,238,454
10,415,421
– 4,169,345
– 13,175,335
– 18,818,303
– 22,413,431
– 24,743,054
– 26,277,742
– 27,304,741
– 28,002,229
– 28,482,526
– 28,817,565
State Royalties
2,667,893
9,414,654
13,270,639
11,774,101
8,786,832
4,923,845
2,541,542
1,083,065
182,467
– 381,830
– 741,343
– 974,305
– 1,127,774
– 1,230,474
– 1,300,223
– 1,348,253
– 1,381,757
Tax
– 7,904,046
– 29,268,360
– 41,093,110
– 35,254,534
– 24,496,544
– 11,748,690
– 3,887,089
925,884
3,897,860
5,760,040
6,946,432
7,715,208
8,221,655
8,560,565
8,790,736
8,949,234
9,059,797
NOPAT
18,442,774
68,292,839
95,883,923
82,260,578
57,158,604
27,413,609
9,069,874
– 2,160,396
– 9,095,008
– 13,440,093
– 16,208,342
– 18,002,152
– 19,183,861
– 19,974,651
– 20,511,716
– 20,881,545
– 21,139,525
Add back Dep’. And Gain/Loss 2,941,176 5,882,353 8,823,529 11,764,706 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882 14,705,882
CF from Ops.
21,383,950
74,175,192
104,707,452
94,025,284
71,864,486
42,119,492
23,775,757
12,545,487
5,610,875
1,265,789
– 1,502,459
– 3,296,270
– 4,477,979
– 5,268,769
– 5,805,834
– 6,175,663
– 6,433,643
Cap.Ex
– 50,000,000
Change in Working Capital 0 0 CFt – 50,000,000
– 28,616,050
24,175,192
54,707,452
44,025,284
Discount Factor 1.0000 0.9009 0.8116 0.7312 0.6587 0.5935 0.5346 0.4817 0.4339 0.3909 0.3522
0.3173
0.2858
0.2575
0.2320
0.2090
0.1883
0.1696
PV of CFt – 50,000,000
– 25,780,225
19,621,128
40,001,617
29,000,818
42,648,075
22,518,800
11,451,793
5,443,819
2,193,430
445,791
– 476,705
– 942,208
– 1,153,143
– 1,222,327
– 1,213,445
– 1,162,829
– 1,091,356
NPV
90,283,034
Capital expenditure ($s per well) 5,000,000 ^5
Well life (years) 17 ^6
Well salvage ($s per well) – 0 times the total number of wells
Well capping and rehabilitation expense ($s per well) 300,000 ^7 total production per well 50
Well production profile (GJs per well) ^7 680,922 1,136,692 1,068,150 731,671 463,089 288,688 181,135 115,289 74,630 49,154 32,923 22,406 15,477 10,840 7,691 5,523 4,010 4,888,290 244,414,502.05
Well depreciation method straightline ^7, ^8
Well operating costs ($s per GJ) 1 ^7, ^9
Working Capital – 0 ^10
Wells drilled in given years and wells in operation profile ^7, ^11
Wells drilled at the beginning of Year 1 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 2 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 3 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 4 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells drilled at the beginning of Year 5 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Wells in operation 10 20 30 40 50 50 50 50 50 50 50 50 50 50 50 50 50 40 30 20 10
Wells capped -10 -10 -10 -10 -10
smaller drainage area (the portion of the reserve that a well can access).
Preliminary designs indicate project requires 100 Vertical wells.
Greater access to gas reserves and larger spacing between wells, which
means fewer wells required. Preliminary designs indicate that project
requires 50 Horizontal wells.
why one drilling approach creates more value than another.
additional analysis to alleviate these limitations where appropriate.