Add Concession Agreement Initial with airSlate SignNow

Byline concession agreement

hi everyone welcome to yet another financial modeling tutorial by alfonso pulido this time we are going to talk about concession agreements accounting better known as e3 public concessions are a wide spread subject of project finance mobiling a concession occurs when a public authority assigns the use of a public domain asset or service in return of a long-term investment to develop it on the rift road the right to use a public domain asset or service acquires the form of either a financial asset or an intangible asset concurrently revenues have to be recognized alongside the usage of the asset following es18 in this video we will build a financial asset and will recognize revenues accordingly to many accounting standards well it's easier than it seems let's get into it okay so let's get started uh we'll need a couple of parameters the margin on opex and the marginal capex i'll explain in a while what these two mean then just a period a little bit of formatting here that's it okay we are gonna just have two construction periods minus two minus one and then followed by nine operation speeds um just start building our piano account revenues as defined by v312 followed by implicit interest you may be wondering if this implicit interest should be added up to the gross margin on this or answer is yes because it's not interest as we understand them in traditional way interest coming from lending accounts but interest on the financial asset and they shall be adapted to the revenue phase so okay uh opex um our operations cost is going to be 800 from year one let's put a total here on topics capex is gonna be say two thousand each year let's add a total of periods copying across that's it so we've got our opex and capex and the next row is going to be the cross margin which is obviously the addition of both revenues [Music] and pay attention both opex and catricks that's one of the main differences uh you you wouldn't see opex uh sorry you don't see capex as being part of the gross margin but here if you are counting under if 12 that that's that's the way your you shall do it then your gross margin is the best interest rate is just the gross margin divided by revenues and implicit interest and what else yeah we need payments we need payments from client so the client is gonna start uh paying us from first operations period and it's going to pay us 1500 a period so no payments throughout construction so that that would be a typical concession agreement okay so let's calculate our revenues so it's as simple as taking both opex and capex and crossing it up with the margin on objects and marking on capex which by the way i haven't defined but then just let's let's write the formula and then put a right there so marginal cup x plus the gross apple margin on opex excel okay so our first period we would have a cell of 240 which as you can see would yield a gross margin of 40 or in percentage rates 2 percent a little bit of formatting here the same goes for payments okay so if we copy this across here it is as you can take in row 15 our gross margin is two percent through our construction of eight percent throughout operations because this is the margin and opex and capital so we define upstairs so a little they have here a couple of turtles so as you can see payments are 15 uh 13.500 on revenues are just under 12 000 which is causing an imbalance with solving in a while while we're not in the implicit interest so let's build our financial assets asset it's going to be a balance sheet titan on a set is going to have a beginning of pdf balance some additions also packs and capex some other additions coming from the implicit interest the very same one will have to add to the revenues up in the table on some reductions which are gonna be the payments and then the end of period balance so okay um well beginning of period this is gonna be by definition the closing balance from the preceding period open opex and capex is going to be as easy as taking the revenues with calculated up in row 8. to the balance and the implicit interest is just is gonna be a rate on opening balance and additions we don't know the rate yet but just we'll assume say three percent have to recalculate that to get the closing balance at the end of the turn to zero and the reductions are going to be the payments from our client so end of period balance is going to be the total develop spread this across and that's it you just got your financial asset built and as i say as i said the implicit interest rate is going to be the one you've calculated in your financial asset [Music] so is that the irr of the project three percent or whatever it will be the uh the rate that they will close the balance to zero no is not this three percent of whatever the implicit rangers rate will be will be represents the premium that your client is paying for the project to be financed through a project finance deal but the project cash flow before tax irr is nine percent in this case which is just as you just saw is quite easy to calculate because we just got the payments here let's gonna put sixteen hundred it's twelve seventeen hundred this will be really high for project before taxes so we just need to to run a quick goal seek to get the closing balance to to zero on by doing so we'll match the revenues plus in implicit interest to the payment amount the total payment amount we've got from our client so gold sick closing balance last period to zero by changing the implicit interest rate as you can see we are charging our client 8.5 percent to fund the whole project which is quite high so let's reduce that to 16 100 a period again go seek make the last balance of the project zero now we are charging 6.5 to fund the whole project let's come back to the original 1500 so the iar is getting closer to what we would normally expect with finance so yeah four point three percent that would be the deal as if the our client would want to finance that through commercial lending and we've got a project irr before taxes of 9.1 so let's gonna build on the balance sheet how would that look like well we're going to have not that many items in our balance sheet because it's quite a brief one so we are going to have our financial asset the cash would represent total assets a little bit of formatting here and copy this across that's it and in the equity and liabilities side you are going to have the profit of the year which will be the gross margin because we on our piano account just end there return earnings which is just the cumulative of the above and that's it this is equity and liabilities so a little bit of formatting here as well totals some formatting that's it and then just control balancing control [Music] okay so our financial asset balance is going to be what you will just got a single balance in every period again a little bit formatting here we copied that across that's it some more formatting cache is going to be quite quite easy our cash is gonna be the project cash flow is the single one we've got in cumulative terms this is gonna be our cash which obviously it obviously starts as negative because it's a castle float to fund the project to fund the capex profits of the year is the gross margin and return errands is the cumulative of the above if we copy that across boom here we are we've got our balance sheet properly populated all the squared and that's it so if you have noticed the gross margin is not the either of these that the one we are recognizing in our piano account is either of the implicit interest rate we set the implicit interest rate to zero you'll note something well let's have a look to the profile so this is the in in writes in this is the marking recognition throughout the project it doesn't look flat so if we remove the financial asset we'll come back to two percent throughout construction and eight percent on our backs throughout the operations but by adding up the yield of the financial asset we'll have the our margin recognition as the profile we just saw so oh pretty peculiar isn't it well um again if you want to have this very small the model uh for you to to check how formulas actually work and learn a little bit about just reach me on linkedin and well give me a thumbs up did you like it uh thanks for watching and i really hope you learned something today