CBEY sensitivities webinar

By Haydn Palliser | February 19, 2020

CBEY webinar use Yale logo

Recorded Video

Missed it live – feel free to watch the recorded video of our webinar

Sensitvities and Scenarios Webinar 

Yale Webinar | Pivotal180


Good morning. Welcome to the webinar. I am Dan gross. I will be your host and we’re looking forward to, to having you join us for about the next 45 minutes to an hour. Before we get started, we have a couple of words from Viro Borg, Meyer from the Yale center for business and environment, Viro take it,

Daniel and Haydn thank you everyone for being here before Daniel and Haydn get started. We wanted to tell you a few things. Primarily. My name is Viro Borg Meyer. I’m the program director for clean energy and conservation finance at the Yale center for business and environment. Daniel Gross, as you may know, teaches the course,uthe Yale school of management and Yale school of forestry studies on renewable energy project finance, part of which was adapted into a certificate and financing and deploying clean energy. The admissions period is currently open and we wanted to make sure everyone here was aware of this opportunity and tell you just a few things about it. As you can tell from this screen, the application deadline is March 1st and you can learn a whole lot more about it on our website. But in broad strokes, the certificate curriculum is organized around the foundations courses, a year long program, primarily online.

The foundation’s core is called, was the fundamentals of energy and finance a full by a week on campus to meet the rest of the cohort. That would be an August this year, from August 3rd to August 7th. And then on August 17th, we start the first of four, four courses. The focus of the program is really, policy, technology and finance. That’s the, these are the three core areas. And we have a capstone on innovation and you can read more about it on the site. I just wanted you to be aware of this broadly. Here is a quick snapshot of who’s teaching in the program. And you can see Daniel Gross on the left over here. But we also have a roster of just phenomenal faculty from across yell and yell networks, including Richard Kaufman, who is the chair of the New York state energy research and development authority, including their esteem around for teachers in our technology. Transitions for can see recently featured in the New York times is one of the climate disruptors and innovators of the century. We have not one or two but three state level commissioners to teach you about policy. And we think it’s a really comprehensive program and good compliment to what Daniel and Haydn putting together in the field level and indication, which we’re really excited about as well.

So very quickly the workload that’s expected and as an average is it might be more or less depending on where you come from, is four to five hours per week to four hours of assignments up to one hour of video every week in a live every Thursday for the, for the whole year. So it’s a comprehensive program. Our goal and mission with this is to accelerate the deployment of clean energy. So if you are passionate about clean energy, too passionate about climate mitigation we hope that you you give the, the website or read we are holding an informational webinar on February 18th, so next week at noon and Haydn. And Daniel will, we’ll share, we’ll the details and the slides so you can you can click on that. And on this note, I hope you enjoy this hour 45 minutes with Daniel and Hayden who are both fantastic, fantastic teachers. You’re very lucky and I hope you have a blast and thank you very much.

Thank you so much. Viro it’s Dan gross again and so happy that you’re here to join us. As Viro mentioned, I am one of the instructors for the online certificate course that you know, I also teach an on campus course at Yale. Both of them focused on renewable energy projects. And that’s during today’s webinar what Hayden and I would like to do is walk you through a set of tools that are really best practices for running scenarios and scenario analysis and sensitivity analysis in Excel that can certainly be used for energy and renewable energy and project finance settings. But the application is far broader. And it’s one of the more exciting tools that that students find incredibly useful in my courses at Yale. And it’s something that Hayden and I teach independently at pivotal one 80, which offers in depth training largely in financial modeling.

We’re going to start by showing a video that really encapsulates the the purpose of scenarios and the setup of a scenario manager. And then following the video, Hayden is going to take us through a real world demo and Excel this webinar and the video that you watch will be available online as will the financial model demo that Hayden is going through. And to the extent you would like to practice the techniques that we demonstrate you’re welcome to download that model and we would encourage you to try to implement it so that you can use that new knowledge and develop the skill while it’s still fresh in your mind. With that let’s launch into a video on sensitivities and scenarios.

When we build a financial model, we’re not just interested in showing a single cashflow projection. One of the greatest advantages of Excel is how readily we can modify the inputs and see how change and certain variables would impact the project cashflow or the IRR or the of the project to repay debt. We want to quickly and efficiently evaluate what would happen if we select a different equipment or if we offer a different pricing structure under the offtake agreement. The challenge with running these kinds of scenarios is that inexperienced modelers tend to do one of two things. They either create a new model for each different scenario or else they manually update their base case model each time they want to run a new scenario with different assumptions.

I remember way back in the day when I was first learning the model, I would create a base case model easy enough and when it seemed to be in a good place, I would make a copy of the file, then rename it downside case. Then I would modify some of the assumptions and then I would make another copy of the file and rename it upside case and then do the same thing over again. All fine and good until I discovered that we needed to insert some kind of change into all three versions of the model. Say for example, the dead amatorization structure changed or the cap X cost went down where my manager found a mistake in the logic of my model and now instead of changing it in just one file, I had to make three separate changes. If there were more than three cases, I might have to make identical changes to eight or 10 different files.

And I mean no disrespect to Neil on this one, but trust me when he did it, there was always bound to be a mistake. It’s impossible to be perfectly consistent at anything.

Hey man, my hairstyle was flawlessly consistent. It never changes

True that mine too, but before you start feeling all too clever, why don’t you tell them how you originally tried to avoid those kinds of problems by just using one model for all of your scenarios and sensitivities? Mr smart guy.

Oh, it was so awful. I’m so embarrassed when I think about it. I built a great base case model. Then I would type in changes for one sensitivity case, write down the result, change it back to the original value and then type in the changes for another sensitivity case and manually change it back again. That worked fine for a while until my boss wanted me to start changing about 25 different assumptions for one of my sensitivity cases and I got completely lost trying to change it back to the original base case and then we realized that we’d been given incorrect information about the tenor of the loan and I had to rerun all those scenarios again. It was brutal and it happened to me like 20 times.

Neil just described two approaches to modeling scenarios that are highly problematic. They’re time consuming, they’re error prone, they’re inefficient and they’re very, very difficult to audit. The best practice that we would recommend is to create what’s called a scenario manager.

I love scenario managers almost as much as I love lacrosse.

Yeah, scenario managers are pretty awesome and there’s no risk twisting an ankle or blunt force trauma to the head. Here’s a really simple example of what a scenario manager can do. Let’s say that we want our model to be able to present the financial results of case one case two and case three a bass, an upside and a downside scenario. If you look at the inputs, you can see that for this company, the unit price and the volume sold are higher in the upside case and lower and the downside case. Now look here at the very top row in cell C one and I can see that we’ve created something labeled as the case selector. Here’s its function. If I type the number one into that magic cell in cell C1, the input values of the base case will populate in the blue in sheet links on column C and what’s driving the revenue calculations in our model?

Well it’s the numbers in the blue cells from column C that are currently live within the model and then if I change the number in that case, select or sell to case two, the price and the volume assumptions that are live in the model are now reflective of my upside case. And if I change that case selector cell to case three, the price and volume assumptions are replaced with the values from the downside case as specified in the input section of the model. So the big question would be how do I do that? What’s the function that you use in Excel? Let’s focus on the formula that we would put into cell C7, which is the cell that currently specifies the $750 price per unit reflective of our downside case. The function that we would use is called index. And the way we would use index is equals index.

Open parentheses and then we need to specify an array and what is an array? It’s simply a range of cells that we’re asking Excel to examine. And in this case for cell C seven what’s the relevant range of cells that we wish for Excel to examine? Well, that’s East seven through G seven the location of the input choices for our unit price assumption in each of the three cases, that one thousand two thousand or $750 per unit, and then you type a comma into the function and next we need to specify a row number. And here we really only have one choice. If our array had two rows or three rows or 15 rows. You can see how you might need to tell Excel which row we care about within the array. But in this case, our array only has one row. So our choice for the row number is one. The first row of the array is the only row.

All right?

Then we type a comma and next we’re asked to specify a column number. And this is probably the most complicated part of the function. The idea is to tell Excel from which column within the array we wished to pull the data. So Neil, how might you do that?

All right.

If I want case one to be active, I want to be pulling the number from the first column within the array, which is column E. If I want the second case to be active, I want to be pulling the number from column two within the array, which is column F and if I want case three and be active, I want to be pulling the number from the third column. So where would I find that one, two, three indicators specifying and column of interest within the array. I would find that in cell C1 it’s called the case selector, so it works perfectly because the value is one in case number one, two in case number two and three in case number three.

Exactly. Now look at the formula bar and see how we specified this column number as an absolute reference. We’ve used anchors, dollar signs, see dollar sign one. We’ve done this because we will eventually want to copy that formula downward from cell C7 down to cell C eight for example, and we will want the row of the array to move down the page with us, but regardless of the row, we will always want to use the case selector in cell [inaudible]. We don’t want that reference to be moving down the page as we copy down. That’s why we use the absolute reference. So with that, let’s switch into Excel and do a demo. What you’ll see here is a slightly more complicated version of what was just presented on the screen in our input selection. We have several different assumptions associated with each case and we want to make each of those assumptions live within our model based on how we toggle our case selector cell.

If you want to try solving this on your own, you can open up the demo file and press pause on the video at any time. Alternatively, if you want to walk through it together, just keep watching and I’ll coach you through it. Let’s start by focusing on cell C nine what’s the formula that we should place here? For our unit price? Well, that should simply be equals index open parentheses and then we’re asked to specify the location of the array that we want Excel to examine, which is right here from cell E nine to cell G nine this is the array and we type a comma and now Excel asks us to specify the row number within that array and you can see that the array only has one row, so it’s simply row one and finally Excel asks us to specify the column number within that array that’s of interest to us and you’ll recall that’s going to be our case select or cell up here in C2 because we want a one to indicate base case two upside, three downside.

Now within that formula we still need to anchor the reference to cell C two and we can either do that manually by typing dollar signs or by using the shortcut function key F for a and now if you’re using a Mac, that shortcut would be command plus T we closed parentheses, hit enter and now you can see if I cycle into case two the upside inputs become active and if I cycle to case three the downside input becomes active and of course it’s the values from right over here in column C which are flowing through the model to drive our revenues and expenses

Now I can just take this formula from cell C nine and copy it using control C and paste it down here using control V and you’ll see again as I cycle through the cases it’s updating appropriately so I can take the same formula, copy it with control C and paste it with control V and then again control V to paste it and control V one more time and now we have a working model. When I changed the case to all of the upside case, assumptions are flowing through and when I changed the case three, all of the downside case assumptions are flowing through.

Well, one of the great benefits of the way in which we’ve laid out our inputs is that a user or a decision maker can always see exactly what is gone in to each of those scenarios, and so there’s no confusion about how the downside case assumptions differ from the upside case assumptions. Every component of the sensitivity is clearly summarized in one place, and that’s incredibly convenient when you’re presenting your results in a, in a pitch deck or an investment committee memo. Trust me on this one.

Also, I want to call your attention to this assumption over here. Under time, we’ve assumed that the equipment life is different in each of our three cases, so we’ll have more years of revenue in the upside case and fewer years of revenue in the downside case. And how have we implemented that into our model? Well where the equipment life gets picked up within our model is right over here in row 22 where you’ll see that we’ve created a binary flag and the flag formula is simply evaluating whether we are within the useful life of the equipment and if so it gives us a value of one and if we are beyond the useful life of the equipment, it gives us a value of zero. The formula for revenue and the two formulas for costs are being multiplied by the binary flag. So if I toggle through the cases, you can see how the duration of the cash flow from our project now match the equipment life.

You’ll see looking down at the IRR, the downside case would be a very bad result for an equity investor. However, in the base case we might be quite pleased with it. Stepping out of Excel for a minute, note that the kind of sensitivity that we’ve been running in our demo is what’s called an overwrite sensitivity. Effectively. We’ve been replacing one value with another in the model and as we change from our base case to our upside case. Back to our downside case, we were simply substituting different values for the inputs. Overwrite sensitivities are an excellent approach but they are by no means the only approach available there for other types of sensitivities that are worth mentioning here. The first is what we would call a selection sensitivity. You’d use this approach when you wish to limit the number of choices that a user can have in determining what an input might be. So if for example, your model has the flexibility to be turned into a quarterly or a semiannual or an annual model, the user should really only be limited to those three choices and this can be achieved through data validation functions and to pull down menu which we will discuss later in the course.

Or you might create a switch sensitivity. This is for binary situations requiring either a yes or a no. For example, let’s say you want the ability to toggle between a levered forecast and an unlevered forecast. You can create a switch in your Snoop scenario manager to show what the project looks like with debt and what it looks like without debt.

Alternatively, you can run sensitivities using an addition flex or sometimes a subtraction flex. So if for example you wish to take the sales price and add an extra $50 per unit, you just indicate how much should be added on top of the base case price.

And finally you can run sensitivities using a multiplicative flex instead of increasing a number by a certain unit amount, you might want to increase or decrease it by a percentage amount. For example, if I want to see what happens when my capex cost is only 10% higher than the base case, I can simply multiply the base case cap X by one plus 10%

We hope you can see how the scenario manager tool can spare you all of the and inefficiency and heartache that Neil experienced before he learned the technique. I still have nightmares about back in the day when I was an associate and I had to rerun the same sensitivity like 15 times in a row because I was just manually updating the base case. We’ve all been there. And while it’s a huge improvement to be able to simply toggle through all of those cases by changing the case, select or sell and looking at the results, we actually have another best practice for you, which can take the analysis to a whole new level. Imagine if you could see the results of all of your scenarios simultaneously calculated all in one place without the need to toggle through each case. One by one. Excel actually has a tool for that. It’s called data tables and we’ll be covering it in the next lesson.

Super. so I think what I’ve just done is cued up my partner, Haydn to launch us into the live portion of this webinar where he will be walking us through a model and demonstrating precisely what I had just described, this idea of viewing all of the scenarios or the results of all of those scenarios at once in one place using data tables. And so with that I turn it over to Haydn. Palliser. Thank you Dan.

So I have on the screen here in Excel model and you will be able to download this after the session as well. So if you want to play with yourself or get familiar, by all means do so largely. This is exactly as you saw on screen. So up the top here we have this case selector that Dan referred to. Underneath that we have all of our current inputs for the model which pick up the relevant input depending on which case we’ve chosen off the top. So right now we’ve got the base case. If I change this to case two, we can see we’ve now got upside from case two. We’re using a price of 130 or so from case two and the same thing when I change to case three so we’ve set up that scenario manager itself. What I would like to point out is just exactly how these calculations flow through.

So directly below as per the video you just saw, we can see our operational period links up to this input itself. So these calculators beneath are always reflective of which case I’ve got selected. And for each of these different items we have an output. So this cell and a 25 for example is just the sum of all of my revenue costs, the total project life. So with the five year life, I have $60 million of revenue. If I change this to case two, that increases to 85.8 so you can see if I change this case elector, my outputs are not just my calculations themselves. As Dan mentioned, it’d be great if we could see all of the outputs, all of these totals, their revenue, the fixed cost, the variable cost costs for all three of these different cases at any one point in time. And we can with data tables. So what I’ll cover now is how to create a data table and how you can use this analysis yourself.

So I scroll down a little bit here. I’ve got some outputs that I have decided we want to see and the outputs I’ve decided I want to know what the name of my Karen cases. So it’s the base side, the upside or the downside case. I would like to know my equity IRR, the returned to the equity investors. I would like to know the total amount of distributions, the total fixed and variable costs and also just my equipment lives. There are things I’ve just started I want to see and they could be anything based on your model itself.

So I’m going to start linking some of these through the name of the case with put right up in D two here so I can click on that. And this is the current name for what case I’ve selected my equity IRA here is just worked out as my revenue, less my costs and I then come up with my cash available for distribution. So in year one my revenue of 12 million minus my cost of two and my fixed costs are to my rehab costs of 4.1 gives me the 5.9 and then I’ve got an equity investment of 20 million which is linked up to the inputs themselves and my eyebrows. 14.53% so I can link my equity IRA up to my output IRR. My lifetime distributions just equal the total distributions over the project life.


My fixed expenses I hear and it’s shown as negative, which is fine. And my variable expenses are the 20.5 they just, the total was across the life. And finally I have my equipment life itself, which I’ll click on the total. So these are the outputs that I want to see for all three of my different cases.

What we have here is a data table itself and if for high there’s area, this is sort of the area that’s going to become the table itself and what you have to recognize is these, this row here in row 37 is always the current outputs of our model. So if again, if I changed this to case two we see these value change with case two in the upside I have the upside name and I’ve got a higher IRR or distributions, et cetera. If I change it back to case one that changes, this is always my live outpost to my model. What I’d like to see is I want to see the results of case one two and three directly beneath the live results here so I can see results at any point in time. So I’m going to start just by doing and then I’ll explain how it’s done and I’m going to use integrated functionality called data tables in excel if I go up to data.

What if analysis, I have an option for data table. I’ll click on data table and ask me for some funny set of inputs. It asks me for column input cell and I’ll explain what this is shortly, but I’m going to click up on the case selector and again I’ll explain exactly what I’ve done in a short while.

As soon as I’ve done that. What we can see beneath here is I’ve got my base case showing in all three of these different cases and I’m seeing the same IRR in every single case. And the reason for this is the way we set up calculations and Excel is typically based on one of three options. We can either have our calculations being automatic being they are run all the time nonstop, including for things like this data table. My calculations are constantly updating or I can have automatic except for data tables. So right now my model is calculated. Any every time I change an input except it would not calculate a data table without me doing something, Oh I can have manual calculations and if you’ve got manual calculations set up, you have to place the F nine to make excel calculate. If I have automatic, except for data tables, my model calculates except for that a table.

So to make this run, I can now just press F-Nine. What’s happened is my model has now given me the results for base case, the upside of the downside cases given me the equity IRR, the lifetime distributions, the fixed costs of the equipment life. And again, I’ll explain this column input shortly. What does it really done? First of all I’ve got, I’ve across here this one, two, three and so what I’ve asked Excel to do is to take this number one, put it into this cell here. And when it does that, this is my live case, this is what’s coming out. And I’m asking Excel to take these values and copy and paste them down into this row here. And then through the data table functionality I say go and put the two up into here. My outputs change and I asked it to paste the valleys down against this row. And then the same thing with three some assay exits to do that with a Daredevil functionality. What’s important to also recognize is if I go and change any other number here in this spreadsheet. So for example, if I now go and change my input and capital costs instead of being 18,000 in the upside case, I make it 19,000


If I do that, I simply scroll back down to my data table and press F nine and it recalculates. I don’t have to do anything else. Once I’ve set up a data table, I can change any input I like to in the model and press F nine and I can automatically see every single output itself.

So one thing to bear in mind is deciding how big a data table is. And what I did early on was I highlighted this whole table. I highlighted the area that I want to create a date type of learn. So I’m going to create this one more time just to give you guys another experience of seeing how it’s built. I highlight, this whole section, I’ve said this top line here is my example calculations down here I’ve got column one, two and three. These are the. These represent the three cases. And I go onto data. What if analysis data table and I click column input cell, which I’ll explain next and I click it up here, I pressed the F nine and my data table, it automatically calculates, so all it’s really doing is taking the example outputs from all three of the cases and patient values down into a table. And hopefully you can see the power of this and that. I can see the outputs for all sorts of different sensitivities or things I’m thinking about and in my business. And I mentioned we’ll come back to this, this column input. And so we’ll do that. Now we’ll jump back into PowerPoint and I’ll explain that one.

Well how do we create a data table? The summary of what I just did is laid out here on screen so the slides would be like download and see these offer and it’s just got the step by step instructions over what I just did with a question we have is why was that input code a column input cell, a column input cell. What we do is we take the value that appears in the first column of the table. So what I’ve got here is this total area is my table and Excel, if you use a data table, just decides the far left column of that area you’ll select it is called column and it ignores the very top left cell, but it includes every other cell on the far left that’s called the column of the table. And when I put in the request for a day table and asked me where do I want to what is the column input cell? And what it’s asking me for is where do I want to take the column inputs, which is the first column on the table and where do I put them? And what I’m doing is I’m putting that column input into the case selector itself. So I’m cycling through

Each of these numbers one, two and three and putting them into the column inputs cell . And what it does is it uses this first row of the table highlighted in red as the example of the characters calculations I need. So it takes the one, it puts it into my case, selector copies the row, which is the red highlighted area down beside the one. It takes the two puts into the column input cell and then copies and paste these values back down against an opportunity. So does it automatically for us. So this is an automatic function in Excel and very useful and something that’s used widely across project finance. Any other financial modeling itself?

So we use a column input cell. Why then would we ever use a row input, cell and primary. The reason you would do that is if you want to switch the table around and you can see here we’ve got all of our outputs are on the left hand side. So we’ve got our case name, equity, our life and distributions there on the left hand side. And instead we’ve listed our cases horizontally across the row. So in this time we’re going to take a row input cell instead, the one, two, three and put that in the case selector, we can just turn our table around if we prefer to see that a different way.

Now there’s other things we can do with data tables as well. So that’s traditionally what is called a one dimensional data table. But there’s other types of sensitivities you may want to see. What we saw in that data table was a bunch of inputs, sorry, a bunch of outputs that we wanted to see based on what would impact our decision the most. We can also do something else such as what is the sensitivity of the IRR and NPV to different amounts of debt. So if I change the leverage, sorry, from 80,000 up to $100,000, what does it do to my IRR and my NPV? So we can create a data table that does that or what is the sensitivity of our EBITDA and IRR to different levels of costs or O and M expenses. And here we could simply set up a changing O and M expense increases and we just want to see what the EBITDA and IRR would do. And again, it would be using a column input cell here, but we would be overlaying this cost against a cost and put in the model. Just from a best practice standpoint. We typically will locate data tables on the page where our inputs are for that particular thing we’re sensitizing. So in our example we were sensitizing the duration of the project. For example, our data table was on the same sheet as the input for that. We can put data tables on other sheets through using something called name ranges, but it’s not recommended to do so. We, so we like to keep all our inputs and data tables together.

So with that is that broad overview of 10 examples and sensitivities. We can open it up for any questions you have. Okay. So just a reminder, you will be able to both download this webinar and you’ll be able to download the Excel spreadsheet and try this yourself. You’ll have a video that runs through this whole thing over again as well. So you can do it in your own time. Before we do wrap up, there’s just one further slide to go through. Which I’ll let Dan cover. Absolutely.

So we we hope that those of you who are interested in pursuing the online certificate course at Yale, we’ll follow up. And in this slide packet there is information and links to the website for more about the certificate program. For any of you who may be interested in going deeper into best practice financial modeling in a very intense way including building upon the scenario and data table exercise that we just did together. Hayden and I offer also public courses through an entity called pivotal one 80 and coming up in New York and the 25th of February. And again, on the 21st of April is of course on renewable energy project finance modeling. And we’ve got a subsequent course coming up on tax equity modeling and then in June and infrastructure version. So if you’re interested in building out your modeling techniques, we would love to help you along with that front. And there’s information about those courses. At pivotal one eighty.com.

And there is a question here from so-and-so. I’m just going to switch back to Excel to, to answer this question. Let me do that.


So the question here and I I’ll just double check. I’ve understood this correctly, but the question is, so we used index and now, so now I manage here and the index was relatively simple and we had this arrive with a height of one row and we picked the case selector here. So either work and a have to pick from the first column or second or third time.

And there’s sort of two other ways you would most commonly see this either with an offset function which does something a little bit different. Or what we often see is some people use something called a choose function where you highlight this whole row array and you say choose number one, two or three and I won’t do the calculation. Thank you. The person just responded to the question. So why would we use index and not offset? Well, the prime reason is offset is not very clear as a function. So what’s great about index here is I’ve got nice care array and if you look in the function, it kind of makes sense. I’ve got this case select drop here, we can see my array, I can directly see it on linking to, and I’ll just do a quick demonstration of offset instead the offset function

If I did this cell. And we’re just going to change this value, it’s the offset function works this way. I start with a particular reference, cell and I can start from here if I wanted any where I like, and then it gives you a result that’s offset by one, two or three or four columns. And the function is just the offset starting point. And then you offset it by a number of rows. And we’re not going to offset this by any rows now, but we could offset it by a number of columns. So if I made this value, for example, my case selector,

Here If I saw quota two I get one 33, I get one 15 so I’m getting the same result here as I am with the index function. Or you might see from this though is the offset function is just not as clear and transparent. And as a couple of things, one I can’t directly see where things are linking to. If I add a column and here for example my offset function would break because it would not know it was just offset from this point across one column. So I’ve had an offset and added a column here. It wouldn’t know it would be wrong. And really the other aspect to it is it’s just not clear. Like if I went and put something in the cell, I don’t even know it’s being used for something. So index is much clearer than offset hope. That’s answers the question.

The second question, which is also a great question is, is there a limit on the number of data tables I can use in a model and what’s optimal? You know, I guess technically there’s no limit as such. But let’s think about what the data table is doing. The data table here, it’s really doing my, here’s my base calculation and what the data table is doing is it’s doing the calculation three times. It’s during this calculation, it’s copying the values down here, changing the inputs, recalculating everything pasting those values down here, recalculating and doing it again. So it’s doing, you know, three times in a row calculation. So if this had say a hundred rows, you can imagine that could start to slow down your model. And in recent XR version, that’s got a lot quicker. And I would say, you know, there’s a broad rule of thumb.

The limit on the number of data tables is really a function of when a model just gets too slow for you. But I would say typically around three to four data tables nowadays in Excel, you don’t typically get too many problems. Once you get it beyond that, your model can become really, really slow. And what you might want to do is change your formulas up here to automatic, except for data tables and this model, it’s so small. It’s so quick. If I had this as automatic, my model is not going to be slow, but a full large model, your data tables could slow you down. And if they do get too slow, well then sometimes the solution unfortunately is a macro, which is not always the, the best solution.

The next question is around a two dimensional data table instead. So what we’ll do is we’ll post a video on that one. That’s a great tool to use to sort of sensitize one variable. So for example, what’s the impact of IRR versus when I change both my debt size and my costs and I can just see all my IRR for each of those pieces changing and see how debt size relates to cost themselves. And we provided video of that in due course. Our next question is, will this video be available to participants? Absolutely. You’ll get the video itself tidied up with any of our bad little studders that I’ll do occasionally. And you’ll also get the slides and you’ll see them and you will also have the Excel model. So you can, you’ll have a, a start and you can see in our. Model on screen is a unsolved version, which is where I’d been working. And there’s a solved version would the final answers so you can check your results.

Yeah. With that thank you everyone for participating today. Hope you’ve learned something and hope you will find great use for this tool. I think if you step back and think about it a little bit, you will discover that this has incredible applicability, not just in financial modeling, but in anytime you’re using Excel for simulation exercises. So hope it was a good use of your time. Thank you so much. And have a great day.

Share This Resource

Complexity simplified.

Advisory, financial modeling, and training courses within climate change, sustainable finance, renewable energy, and infrastructure.
We don’t just teach you how to build models. We teach you how to do deals.