Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

spreadsheet model that assumes the price per barrel of oil starts to decrease by 5% with respect to the previous year starting in 2021. Fracking

image text in transcribedimage text in transcribed

spreadsheet model that assumes the price per barrel of oil starts to decrease by 5% with respect to the previous year starting in 2021.

Fracking Oil Investment Decision S Constants Income Tax Rate Cash Needed to Start Year Decline Rate Initial Production Rate (IPR) Interest on Junk Bonds 2016 NA NA NA NA NA 2017 20% 1,000,000 0.70 500 500,000 2018 20% $ 1,000,000 0.20 NA $ 500,000 2019 20% $1,000,000 0.25 NA $ 500,000 2020 20% $ 1,000,000 0.25 NA $ 500,000 2021 20% $ 1,000,000 0.20 NA $ 500,000 2022 20% $ 1,000,000 0.20 NA $ 500,000 2023 20% $ 1,000,000 0.20 NA $ 500,000 2024 20% $ 1,000,000 0.15 NA $ 500,000 S 2017 NA $ Inputs 1 Price Per Barrel e Interest Rate on New Debt Requested Investment 2016 70 NA 10,000,000 2018 NA 0.04 NA 2019 NA | 0.04 NA 2020 0.04 NA 2021 NA 0.04 NA 2022 NA 0.04 NA 2023 NA 0.04 NA 2024 0.04 0.04 $ NA NA 5 Summary of Key Results 5 Net Income After Taxes -End-of-the-Year Cash On Hand 3 End-of-the-Year Debt Owed Net Present Value of Investment 2016 NA NA NA S $ $ 2017 3,691,786 3,691,786 - 2018 2019 $ 1,120,562 $ 650,993 $ 4,812,348 $ 5,463,342 $ - $ - 2020 2021 2022 2023 2024 S 348,245 $ 140,646 $ 517S (139,483) S (239,131) $ 5,811,587 $ 5,952,233 $ 5,952,750 $ 5,813,267 $ 5,574,136 $ - $ - $ - $ - $ NA NA ($4,674,570) 1 Calculations e Initial Production Rate/Day (IPR) Average Production/Day in the Year Barrels of Oil Produced in the Year 2016 NA NA NA 2017 500.0 325.0 118,625 2018 150.0 127.5 46,538 2019 105.0 91.9 33,534 2020 78.8 68.9 25,151 2021 59.1 53.2 19,402 2022 47.3 42.5 15,522 2023 37.8 34.0 12,417 2024 30.2 28.0 10,210 Income and Cash Flow Statement - Beginning of-the-Year Cash On Hand 2016 NA 2017 1,000,000 2018 $3,691,786 2019 $ 4,812,348 2020 2021 $ 5,463,342 $ 5,811,587 2022 2023 S 5,952,233 $ 5,952,750 2024 $ 5,813,267 $ $ 8,303,750 $ 3,257,625 $ 2,347,406 $ 1,760,555 $ 1,358,142 $ 1,086,514 $ 869,211 $ 714,685 Revenue Out of Pocket Costs and Expenses Oil Transport ($12/barrel) Well Operations ($3/barrel) General and Administrative Royalties to Land Owners Severance Taxes Interest on Junk Bonds Interest on New Debt Total Out of Pocket Costs and Expenses Income Before Taxes Income Tax Expense NA NA NA NA NA NA NA NA NA NA NA NA $ 1,423,500.00 $ 558,450.00 $ 402,412.50 $ 301,809.38 $ 232,824.38 $ 186,259.50 $ 149,007.60 $ 122,517.36 $ 355,875.00 $ 139,612.50 $ 100,603.13 $ 75,452.34 $ 58,206.09 $ 46,564.88 $ 37,251.90 $ 30,629.34 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 825,630 S 323,901 S 233,399 $ 175,049 $ 135,038 $ 108,031 $ 86,424 $ 71,060 $ 344,013 S 134,959 $ 97,250 $ 72,937 $ 56,266 $ 45,013 $ 36,010 $ 29,608 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 40,000 $ - S $ 3,689,018 S 1,856,922 S 1,533,665 $ 1,325,248 S 1,182,334 S 1,085,868 S 1,008,694 S 953,815 $ 4,614,733 $ 1,400,703 $ 813,742 $ 435,306 $ 175,808 $ 646 $ (139,483) $ (239,131) $ 922,947 $ 280,141 $ 162,748 $ 87,061 S 35,162 S 129 S . - S - S - S Sheet1 6 Income and Cash Flow Statement 7 Beginning of-the-Year Cash On Hand 2016 NA C 2017 1,000,000 D 2018 $ 3,691,786 E 2019 $ 4,812,348 F 2020 $ 5,463,342 G 2021 $ 5,811,587 2022 $ 5,952,233 2023 $ 5,952,750 2024 S 5,813,267 $ $ 8,303,750 S 3,257,625 $ 2,347,406 $ 1,760,555 $ 1,358,142 $ 1,086,514 $ 869,211 $ 714,685 9 Revenue 0 Out of Pocket Costs and Expenses -1 Oil Transport ($12/barrel) 2 Well Operations ($3/barrel) 3 General and Administrative 4 Royalties to Land Owners 5 Severance Taxes 6 Interest on Junk Bonds -7 Interest on New Debt 8 Total Out of Pocket Costs and Expenses 9 Income Before Taxes 0 Income Tax Expense -1 Net Income After Income Tax Expense NA NA NA NA NA NA NA NA NA NA $ 1,423,500.00 $ 558,450.00 $ 402,412.50 $ 301,809.38 $ 232,824.38 $ 355,875.00 $ 139,612.50 $ 100,603.13 $ 75,452.34 $ 58,206.09 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 825,630 $ 323,901 $ 233,399 $ 175,049 $ 135,038 $ 344,013 $ 134,959 $ 97,250 $ 72,937 $ 56,266 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 40,000 $ . $ 3,689,018 S 1,856,922 S 1,533,665 $ 1,325,248 S 1,182,334 $ 4,614,733 $ 1,400,703 $ 813,742 $ 435,306 $ 175,808 $ 922,947 $ 280,141 S 162,748 $ 87,061 $ 35,162 $ 3,691,786 $ 1,120,562 S 650,993 S 348,245 $ 140,646 $ 186,259.50 $ 149,007.60 $ 122,517.36 $ 46,564.88 $ 37,251.90 $ 30,629.34 $ 200,000 $ 200,000 $ 200,000 $ 108,031 $ 86,424 $ 71,060 $ 45,013 $ 36,010 $ 29,608 $ 500,000 $ 500,000 $ 500,000 $ 1,008,694 $ $ (139,483) $ 953,815 (239,131) S 1,085,868 $ 646 S S 517 NA S (139,483) S (239,131) $ Net Cash Position (NCP) Before 3 Borrowing and Repayment of Debt 4 Add: Increase in Borrowing -5 Less: Repayment of Debt -6 End-of-the-Year Cash On Hand $ 5,463,342 $ 5,811,587 $ 5,952,233 $ 5,952,750 S S - S S - $ 5,813,267 $ 5,574,136 $ - S NA NA NA 1,000,000 4,691,786 - 1,000,000 3,691,786 $ 4,812,348 S - $ $ 4,812,348 $ $ $ $ 5,463,342 $ 5,811,587 $ 5,952,233 $ 5,952,750 S 5,813,267 $ 5,574,136 2018 2019 2020 2021 2022 2023 2024 2016 NA 2017 1,000,000 $ $ 8 Debt Owed -9 Beginning-of-the-Year Debt Owed 0 Add: Increase in Borrowing 1 Less: Repayment of Debt 2 End-of-the-Year Debt Owed NA NA $ 1,000,000 $ $ 1,000,000 4 Net Present Value Data $ (10,000,000) $ 3,691,786 $ 1,120,562 $ 650,993 $ 348,245 $ 140,646 $ 517 $ (139,483) $ (239,131) 6 Net Present Value @ 15% ($4,674,570.40) NA NA NA NA NA NA NA Fracking Oil Investment Decision S Constants Income Tax Rate Cash Needed to Start Year Decline Rate Initial Production Rate (IPR) Interest on Junk Bonds 2016 NA NA NA NA NA 2017 20% 1,000,000 0.70 500 500,000 2018 20% $ 1,000,000 0.20 NA $ 500,000 2019 20% $1,000,000 0.25 NA $ 500,000 2020 20% $ 1,000,000 0.25 NA $ 500,000 2021 20% $ 1,000,000 0.20 NA $ 500,000 2022 20% $ 1,000,000 0.20 NA $ 500,000 2023 20% $ 1,000,000 0.20 NA $ 500,000 2024 20% $ 1,000,000 0.15 NA $ 500,000 S 2017 NA $ Inputs 1 Price Per Barrel e Interest Rate on New Debt Requested Investment 2016 70 NA 10,000,000 2018 NA 0.04 NA 2019 NA | 0.04 NA 2020 0.04 NA 2021 NA 0.04 NA 2022 NA 0.04 NA 2023 NA 0.04 NA 2024 0.04 0.04 $ NA NA 5 Summary of Key Results 5 Net Income After Taxes -End-of-the-Year Cash On Hand 3 End-of-the-Year Debt Owed Net Present Value of Investment 2016 NA NA NA S $ $ 2017 3,691,786 3,691,786 - 2018 2019 $ 1,120,562 $ 650,993 $ 4,812,348 $ 5,463,342 $ - $ - 2020 2021 2022 2023 2024 S 348,245 $ 140,646 $ 517S (139,483) S (239,131) $ 5,811,587 $ 5,952,233 $ 5,952,750 $ 5,813,267 $ 5,574,136 $ - $ - $ - $ - $ NA NA ($4,674,570) 1 Calculations e Initial Production Rate/Day (IPR) Average Production/Day in the Year Barrels of Oil Produced in the Year 2016 NA NA NA 2017 500.0 325.0 118,625 2018 150.0 127.5 46,538 2019 105.0 91.9 33,534 2020 78.8 68.9 25,151 2021 59.1 53.2 19,402 2022 47.3 42.5 15,522 2023 37.8 34.0 12,417 2024 30.2 28.0 10,210 Income and Cash Flow Statement - Beginning of-the-Year Cash On Hand 2016 NA 2017 1,000,000 2018 $3,691,786 2019 $ 4,812,348 2020 2021 $ 5,463,342 $ 5,811,587 2022 2023 S 5,952,233 $ 5,952,750 2024 $ 5,813,267 $ $ 8,303,750 $ 3,257,625 $ 2,347,406 $ 1,760,555 $ 1,358,142 $ 1,086,514 $ 869,211 $ 714,685 Revenue Out of Pocket Costs and Expenses Oil Transport ($12/barrel) Well Operations ($3/barrel) General and Administrative Royalties to Land Owners Severance Taxes Interest on Junk Bonds Interest on New Debt Total Out of Pocket Costs and Expenses Income Before Taxes Income Tax Expense NA NA NA NA NA NA NA NA NA NA NA NA $ 1,423,500.00 $ 558,450.00 $ 402,412.50 $ 301,809.38 $ 232,824.38 $ 186,259.50 $ 149,007.60 $ 122,517.36 $ 355,875.00 $ 139,612.50 $ 100,603.13 $ 75,452.34 $ 58,206.09 $ 46,564.88 $ 37,251.90 $ 30,629.34 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 825,630 S 323,901 S 233,399 $ 175,049 $ 135,038 $ 108,031 $ 86,424 $ 71,060 $ 344,013 S 134,959 $ 97,250 $ 72,937 $ 56,266 $ 45,013 $ 36,010 $ 29,608 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 40,000 $ - S $ 3,689,018 S 1,856,922 S 1,533,665 $ 1,325,248 S 1,182,334 S 1,085,868 S 1,008,694 S 953,815 $ 4,614,733 $ 1,400,703 $ 813,742 $ 435,306 $ 175,808 $ 646 $ (139,483) $ (239,131) $ 922,947 $ 280,141 $ 162,748 $ 87,061 S 35,162 S 129 S . - S - S - S Sheet1 6 Income and Cash Flow Statement 7 Beginning of-the-Year Cash On Hand 2016 NA C 2017 1,000,000 D 2018 $ 3,691,786 E 2019 $ 4,812,348 F 2020 $ 5,463,342 G 2021 $ 5,811,587 2022 $ 5,952,233 2023 $ 5,952,750 2024 S 5,813,267 $ $ 8,303,750 S 3,257,625 $ 2,347,406 $ 1,760,555 $ 1,358,142 $ 1,086,514 $ 869,211 $ 714,685 9 Revenue 0 Out of Pocket Costs and Expenses -1 Oil Transport ($12/barrel) 2 Well Operations ($3/barrel) 3 General and Administrative 4 Royalties to Land Owners 5 Severance Taxes 6 Interest on Junk Bonds -7 Interest on New Debt 8 Total Out of Pocket Costs and Expenses 9 Income Before Taxes 0 Income Tax Expense -1 Net Income After Income Tax Expense NA NA NA NA NA NA NA NA NA NA $ 1,423,500.00 $ 558,450.00 $ 402,412.50 $ 301,809.38 $ 232,824.38 $ 355,875.00 $ 139,612.50 $ 100,603.13 $ 75,452.34 $ 58,206.09 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 825,630 $ 323,901 $ 233,399 $ 175,049 $ 135,038 $ 344,013 $ 134,959 $ 97,250 $ 72,937 $ 56,266 $ 500,000 $ 500,000 $ 500,000 $ 500,000 $ 500,000 40,000 $ . $ 3,689,018 S 1,856,922 S 1,533,665 $ 1,325,248 S 1,182,334 $ 4,614,733 $ 1,400,703 $ 813,742 $ 435,306 $ 175,808 $ 922,947 $ 280,141 S 162,748 $ 87,061 $ 35,162 $ 3,691,786 $ 1,120,562 S 650,993 S 348,245 $ 140,646 $ 186,259.50 $ 149,007.60 $ 122,517.36 $ 46,564.88 $ 37,251.90 $ 30,629.34 $ 200,000 $ 200,000 $ 200,000 $ 108,031 $ 86,424 $ 71,060 $ 45,013 $ 36,010 $ 29,608 $ 500,000 $ 500,000 $ 500,000 $ 1,008,694 $ $ (139,483) $ 953,815 (239,131) S 1,085,868 $ 646 S S 517 NA S (139,483) S (239,131) $ Net Cash Position (NCP) Before 3 Borrowing and Repayment of Debt 4 Add: Increase in Borrowing -5 Less: Repayment of Debt -6 End-of-the-Year Cash On Hand $ 5,463,342 $ 5,811,587 $ 5,952,233 $ 5,952,750 S S - S S - $ 5,813,267 $ 5,574,136 $ - S NA NA NA 1,000,000 4,691,786 - 1,000,000 3,691,786 $ 4,812,348 S - $ $ 4,812,348 $ $ $ $ 5,463,342 $ 5,811,587 $ 5,952,233 $ 5,952,750 S 5,813,267 $ 5,574,136 2018 2019 2020 2021 2022 2023 2024 2016 NA 2017 1,000,000 $ $ 8 Debt Owed -9 Beginning-of-the-Year Debt Owed 0 Add: Increase in Borrowing 1 Less: Repayment of Debt 2 End-of-the-Year Debt Owed NA NA $ 1,000,000 $ $ 1,000,000 4 Net Present Value Data $ (10,000,000) $ 3,691,786 $ 1,120,562 $ 650,993 $ 348,245 $ 140,646 $ 517 $ (139,483) $ (239,131) 6 Net Present Value @ 15% ($4,674,570.40) NA NA NA NA NA NA NA

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Accounting Reporting And Analysis

Authors: Earl K. Stice, James D. Stice

7th Edition

0324227329, 978-0324227321

More Books

Students also viewed these Accounting questions

Question

Did you offer hard data that is verifiable? [D]

Answered: 1 week ago