Answered step by step
Verified Expert Solution
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
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started