Understanding the risk and company evaluation Part 2

Ok so now you should have a general idea of what a DCF does and how important it is to have

Setting up your DCF spread sheet
I will go through this in bullet point form and if I lose anyone let me know and I will cover that area again with you by email
For this DCF model the first port of call is establishing production rates and on an annual base. I am using the already announced 135bopd and only sticking with what we do know for sure.
Step1 for this sum the excel calculations would look this
· Under current year in the wells column 6B is a manual input
· Under current year in the Production/day column the sum would look like =B6*135 the 135 is the amount of barrels that are forecast as the production rate pre well
· Under current year in Annual production the sum would look like =B7*365
· ERH have a 40% participation rate so in B9 or the column ERH share the sum will look like this =B8*0.4
Ok we have now known the estimated production rate for the current year and ERH’s share of that production. The sums that we just inputted into the spread sheet do not change in year 1 and the following years, so the sums can simply be dragged over from column to column.
See picture below for how the spread sheet should look

By the time you’re finished you should have something that looks like the spread sheet below and your DCF should run for the life of the project that you are evaluating.

Step 2 is to establish a product price and in this example we are going to use an average oil price of $60 a barrel for oil.
To set this up on your spread sheet follow these steps.
· In column B21 under Average this is a manual input
· In column C21 under 10% royalty the sum will look like this =B21/10
· In column D21 under 34%tax the sum will look like this =B21*0.34
· In the column E21 under 1% landowners the sum will look like this =B21*0.01
· In column F21 under Transport costs this is a manual input based off best estimates.
· In column G21 under Opex (operating expense) this is a manual input based of best estimates.
· In column H21 under Profit your sum will look like this =B21-C21-D21-E21-F21-G21

The above oil profit estimate is based off Brazil’s tax rate and should be adjusted when you take up investments in other countries to correspond with that counties tax rates. The Opex and transport costs are based on world averages.

Step 3, in this step we actual build the DCF component into our spread sheet. We will also adjust our resource reserve size in each corresponding year as the resource declines. Also in this section we can add things in like, if you think the oil price will rise 10% for the next 5 years or if the company has debt you can build the annual repayments into your evaluation and so on. This is why the DCF system is so good, as it allows you to build in different requirements where most other valuation systems don’t give you this flexibility.

To set this up on your spread sheet follow these steps.
· In column 11B Well reserve start the 16,000,000 is a manual input. (this will allow you to decrease or increase the reserve size as estimates are released)
· In column 12B Well reserve end your sum will look like this =+B11-B9 (this calculation is simply misusing your annual production away from your reserve estimate). This sum remains the same for all columns in line 12. So C12, D12 and so on would look like this C12 sum

=+C11-C9, D12 sum =+D11-D9. You should keep repeating this process until the resource reserve runs out
· In column C11 your sum will look like this =+B12, this is simply making the current year’s reserve last year’s reserve end. This sum is repeated so D11 sum would look like =+C12 and E11 sum would look like =+D12 and so on.
You should keep repeating this process until the resource reserve runs out
Next we tackle annual cash flow estimates
· This sum by itself is an easy one, it is simply the annual production rate times the resource price profit or in cell B13 the sum would look like this =+B9*H21 and cell C13 sum would look like this =+C9*H21 and so on for all the columns in line 13

This next section is a bit technical and only those with a good understanding of both Excel and DCF models should try and implement these functions and sums into their DCF model. Also once you have mastered the basics you can come back and add these bits in later.

It is in the cash flow estimates that we can build in other scenarios like the estimation of the oil price increasing 10% for the next 5 years. For that you would have a sum that looked like this; In the current year your sum would remain the same =+B9*H21, this is due to the fact that you have already established an oil price estimate of $60 for that year. But in the following year cell C13 would look like this =+C9*H21+(C9*H21*0.1). Or for layman’s terms that sum represent annual production rate times the resource price profit plus the in brackets, annual production rate times the resource price profit times 10%. In cell D13 you would use almost the same sum =+D9*H21+(D9*H21*0.2) the difference being that you have increased your percentage rate to 20% which I will explain why in a moment. But in cell E13 and the rest corresponding cells in line 13 you would use this sum =+E9*H21+(D9*0.1). The reason for this is due to the production plan that the company has set out which is estimating it will take 3 years to roll out to reach full production. For this reason you need to take your estimated oil price growth rate of 10% in cell C13 from the actual profits of that year. In the following year you need to make it a 20% growth rate because you are using the same figures as you used for the previous year and if you said 10% again this would keep your figures flat so by using 20% you are still only getting a 10% raise from the previous year.
You can also build debt into this model much the same way as we just used a 10% increase in the average oil price per annum. I will explain this in detail in later blog updates.

Ok guys and girls we are almost there, we now need to build in our discount cash rate which as I said before it will be 16% on this model. Once you have setup your model up you can change the discount cash rate to whatever percent you feel comfortable with, with the risk you feel you are taken. The lower the risk the lower the percentage should be and the higher the risk the higher the percentage should be.
· In cell B14 your sum should look like this =+B13*0.84. So basically we are misusing away 16% of the annual income with this sum on the next cell C14 you would uses the same sum =+C13*0.84 and again in cell D14 =+D13*0.84.
· But in cell E14 you would use this sum =+D14*0.84 and in F14 you would use the =+E14*0.84 and so on. You will notice that in the case of B13, C13 and D13 we used that year’s annual profits for the discount rate but from cell E14 onwards we base the discount cash rate from the previous year’s discount cash rate. The reason for this is because if we used the sums that were used in B13, C13 and D13 the cash rate wouldn’t actually discount and would remain the same. The reason we don’t use the sums we used in E14 onwards in B13, C13 and D13 is because of the increase of the production rates of the oil.

Step 4 the final step (thank god); the accumulated and the fare value share price

Firstly we will go over the accumulation bit.
· The accumulation is simply adding up all the cash amounted in the Discount cash rate line. For this the sum in cell B15 would look like this =SUM(B14:L14).
Value per diluted share
· This is also another very easy one it is simply your accumulated total of the discount cash rate divided by the fully diluted market cap which in ERH’s case is about 365,000,000 shares. So your sum should look like this =+B15/365000000.
Using the 16% discount rate we end up with a fair valued share price of $0.08c based on 330 alone at a production rate of 2000 bopd at a $60 average oil price.
How is this so you ask, after all you have been calling a share price of a lot more than that over the past months? Well the answer is simply; in this model I have increased the discount cash rate from 10% to 16% due to the Silver Marlin mishap and the constant retraction in world and credit markets. Also those of you that read my first post on this blog would know that I gave the company a cash backing of $0.15 on a fully diluted market cap based on the proceeds from that dilution so that would take the valuation to $0.23.
Ok you should have now completed your DCF model and it should look something like the picture below

It’s also worth noting that by changing your discount cash rate from 10% to 16% the impact this can have on your over all fair value forecast of your share price. A 6% movement in our discounted cash rate in this case has had about a 32% impact on the share price. So be carefully when using this system and ensure you calculate the risks correctly. You will find that most financial institutions will use this system to obtain fair value and the major difference between different valuations will be how they calculate the risk or the discount cash rate.

Once you have mastered Discount Cash Flow you will find it to be a very useful and powerful tool in your investments and I strongly urge everyone to take the time and learn this stuff.
In future blog updates i will also cover how to build the capex function into your model but I think we have covered enough ground for now.

Finally I would like to thank Spozzie for the use of he’s DCF spreadsheet for this blog. You are an absolute champion mate, if only your kind could play cricket you would be a god amongst man LOL. And also Sutts for he’s input. Thanks heaps guys, it is very much appreciated

If anyone gets stuck on it don’t hesitate to contact me, my email is ciggys@y7mail.com.
Good luck with it guys