Project Finance: Flexing scenarios through a scenario manager & data table #Finance

hello everyone Kyle here this is an excerpt from the project Finance modeling package here the underlying model is a windfarm model with most of the Cash Flow

elements built uh except for tax in this specific CashNews.co nevertheless we go through running a few scenarios through the scenario manager and then recording the results on the data table let’s jump into it all right so here we have a fully built model and we’re on the scenario

manager that’s got two components the scenario manager itself and then the data table for viewing outputs here we have the model which is built the calculations tabs and here the inputs tabs so for case one we have the base case now for the price selection let’s put this as Central this

is the price estimate that just says well it’s going to be pretty much the middle case then generation selction that’s the volume and we’re going to select p50 this just means that the 50th percentile probability of Revenue that we will receive so I.E kind of like

the middle case as well what does that actually do well here on the inputs page we’ve got the Revenue section and generation I volume and we’ve got three different types of inputs those three different categories we got p50 p75 P90 and don’t worry about that for

now all that’s important is that this number here is going through to the applied cell and this number is going through to the model so the whole point of this is that if we pick the p50 here on the scenario manager which is coming through from here if I’m in case one then what’s

happening is that this is active throughout the model and the same with the central the central is now active the 80 is active if I change it to high for example and let me demon that now then the 85 is now active and that’s going through the model so that changes all the model metrics of

course because you’ve got a higher price okay so that’s the basics of it so case one base case that’s usually the base case usually the Debt case so you might have that as a P90 and as a let’s say a central okay so we need to put our data table in I’m

going to select this and I’ll explain more what I’m doing later in the scenario manager column input cell is the case number hit okay and there’s no changes why well because all these cases are currently the same they’re all pulling what’s the inputs in case one are

for case 2 3 4 5 but we’re saying okay for the base case which let’s say it’s our de sizing case it’s uh P it’s Central and P90 the npv is $4.6 million and the IR is 14.6% so that’s actually pretty good I mean partly the reason it’s good is that we’re

not actually paying tax on this model because we’re not calculating tax but anyway that’s what we’re producing our dscr 1.24 so maybe that’s a little bit low well actually uh it should be okay one it might be 1.1 for a Debt size in case on a P90 that might

be what we’re sizing to so that could be okay let’s move on to case two so I’m going to hit two here price high so what should we do for that let’s say the price selection is no longer Central it’s high and let’s say we’re going to bump it up by no actually

let’s just leave it to one variable so it’s calculated automatically so I didn’t need to push F9 cuz my calculation options here were it to automatic so usually go for this one and I’ll explain that later as well okay so price High you get a bump on npv and the iri goes up

to 16.85% your minimum dscr is 1.32 so that’s the cads divided by your Debt service at the minimum point and that’s directionally seems right you’ve increased your price okay so let’s go on to case three let’s say Opex goes up by let’s say 20% so

it’s rather large so what’s going to happen here well we’re expecting what’s going to happen is the npv is going to drop and same with the IR if this model’s directionally correct at least then an increase in opix should mean you have less Cash Flow

available for Equity and therefore less npv and IR so hit enter F9 and you can see that drop slightly so now it’s 14.5% with a 14.64% IR Cash Flow of edable Equity slightly lower not actually that bigger impact you look at the dscr why is

that well actually in a windar model the Opex might be between 10 and 20% of your Revenue so it doesn’t have a huge impact what you can do is say okay how much does this have to increase by in order to get a dscr of one so that might be analysis that the the bank would be

interested in doing and so on so forth you can actually automate that so now I’ve hit case three so here is going to be coming through it’s hard to see but if I do alt a w and G for goalseek setting the cell to value One by changing the cell okay so this has to go up by 1000% before the

dscr hits one now we can just check that and look at the cash waterfall and this is what you all need to do so if these go up by 1000% then yeah if you look at the cashow available for Equity it’s really really low okay so it’s quite low and what we’re saying is

we have a cads only just able to cover your Debt service and that’s about right and we’re saying in the minimum position it should be one you can also say where is the minimum date well 31st of March 22 31st of March 22 that’s over here cads is 972 and The

Debt Service is 972 so that’s your one so you can run a scenario like that okay so in case two and case three we’ve been running scenarios that uh kind of one variable I want to show is the power of the scenario manager to run multiple variables let’s say

we’re running a sort of disaster case and this is where we’re just throwing all the darts in the dart board and we’re saying hm okay what about if we get low price and maybe it’s even lower than low it’s – 5% and the generation is P90 which is low and maybe

Interest Rates go up by 2% and um the Inflation rate goes up by 1% well actually not sure that will have a negative impact on overall R just yet what does that do okay so hit F9 your npv is negative your IR is still positive so 4.21% so not a complete disaster of a

project but you’ve breached your Covenant there so you’ve gone below one hopefully you can see that when we Pile in a whole bunch of negative factors how the overall project responds it’s also very interesting to run them like we have here where we’ve isolated them so that

you can see okay what impact does the individual variable changing an individual variable have on the key metrics that you’re looking for and the key metrics that someone else will look for whether it’s investors or Banks and here they are here obviously the npv and irr the

Equity side and then from the Debt side the dscr and the minimum date and here’s some overall kind of C metrics so very interesting for all parties to see that one thing you can do as well is to say okay for a 1% response in these key variables if the price

goes up by 1% or down by 1% or Opex goes up or down by 1% or the interest rate goes up or down by 1% what does that change do to a key variable like the irr and then you can plot that on a diagram so you can see how that comes out and give everyone a very visual idea of how the project responds to

key variables so that’s all I really wanted to cover in the end game tutorial hopefully that makes sense to you and just bear in mind that this is the result of many hours worth of effort which you’re going to go through in the course to get there but it’s really really important

to understand what are we actually using this model for and this is it it’s for the Valuation it’s for the scenario analysis what we’re doing here and just for the understanding of how the asset how the project that we’re modeling how that actually responds

to the key variables so this is kind of the top of the triangle there in summary you learned how this case study seeks to model a wind farm and the end game how Deon Equity might look at a finished model