Can you make this in excel using this:

Can you make this in an Excel using this:

1. Create a table with the headings: Year, Free Cash Flow (FCF), Discount Rate, Present Value (PV), Capital Expenditure, Depreciation, State Royalties, Operating Costs, and Tax. 2. Fill the Year column with the values 0 to 14. 3. In the Capital Expenditure column, fill the cells corresponding to years 0 to 4 with the value 80,000,000 (4,000,000 times 20 wells). Leave the remaining cells in this column blank. 4. In the Depreciation column, fill the cells from year 0 to year 9 with the value 23,244,712 (2,324,271 times 10 years times 20 wells). Leave the remaining cells in this column blank. 5. In the State Royalties column, fill the cells for each year using the formula: =-10.45*100*IF(B5>0,(B5-G5)*0.1,0), where B5 is the Free Cash Flow for that year, and G5 is the Well Capping and Rehabilitation Expense. 6. In the Operating Costs column, fill the cells for each year using the formula: =-E5*D5, where E5 is the Well Production Profile (GJs per well) for that year, and D5 is the Well Operating Costs ($s per GJ). 7. In the Tax column, fill the cells for each year using the formula: =-0.3*IF(B5>0,B5-G5+C5+H5,0), where B5 is the Free Cash Flow for that year, G5 is the Well Capping and Rehabilitation Expense, C5 is the State Royalties for that year, and H5 is the Depreciation for that year. 8. In the FCF column, fill the cells for each year using the formula: =B5+G5+F5+H5+C5, where B5 is the Revenue for that year (calculated as the Annual Production per Well times the Price per Gigajoule), G5 is the Well Capping and Rehabilitation Expense, F5 is the State Royalties for that year, H5 is the Depreciation for that year, and C5 is the Operating Costs for that year. 9. In the Discount Rate column, fill the cells for each year with the required rate of return, which is 12%. 10. In the Present Value column, fill the cells for each year using the formula: =PV(E5,B5,0,-F5), where E5 is the Discount Rate for that year, B5 is the Free Cash Flow for that year, F5 is the Initial Capital Expenditure (which is only included in year 0). 11. Calculate the Total Present Value (PV) by adding up the present values from year 0 to year 14. 12. Calculate the Net Present Value (NPV) by subtracting the Initial Capital Expenditure (which is included in year 0) from the Total Present Value. The resulting vertical DCF table in Excel should look like this: | Year | Free Cash Flow | Discount Rate | Present Value | Capital Expenditure | Depreciation | State Royalties | Operating Costs | Tax | |------|------------------|---------------|---------------|---------------------|----------------|-----------------|----------------|--------------| | 0 | -80,000,000.00 | 12.00% | -80,000,000.00 | -80,000,000.00 | 23,244,712.00 | 0.00 | 517,231.00 | -4,373,357.60| | 1 | 18,702,310.00 | 12.00% | 16,645,022.73 | | 23,244,712.00 | 1,007,793.30 | -514,250.00 | -3,873,364.60| | 2 | 32,492,108.70 | 12.00% | 26,728,392.57 | | 23,244,712.00 | 2,067,870.72 |-891,300.00 |-6,677,778.68| | 3 | 39,781,172.57 | 12.00% | 29,093,548.74 | | 23,244,712.00 | 2,420,012.94 |-678,192.00 |-7,400,082.04| | 4 | 43,501,459.40 | 12.00% | 28,590,630.92 | | 23,244,712.00 | 2,522,841.90 |-618,266.00 |-7,760,255.70| | 5 | 45,121,158.61 | 12.00% | 25,752,728.01 | | | 2,628,626.80 | | | | 6 | 45,499,315.69 | 12.00% | 22,105,096.00 | | | 2,729,631.57 | | | | 7 | 44,787,189.28 | 12.00% | 18,154,069.79 | | | 2,826,317.86 | | | | 8 | 42,928,289.34 | 12.00% | 14,330,596.33 | | | 2,919,063.22 | | | | 9 | 39,461,820.45 | 12.00% | 10,924,969.32 | | | 3,008,217.47 | | | | 10 | 33,834,180.45 | 12.00% | 8,413,171.46 | | | | | | | 11 | 25,952,036.75 | 12.00% | 5,954,775.45 | | | | | | | 12 | 16,520,032.22 | 12.00% | 3,395,563.05 | | | | | | | 13 | 6,293,129.13 | 12.00% | 1,702,331.40 | | | | | | | 14 | -1,046,646.56 | 12.00% | -351,667.65 | | | | | | The Total Present Value is 144,311,707.38, and the Net Present Value is 64,311,707.38, which indicates that the project is a financially viable investment.