Creating Scenarios in an Excel Financial Model

Haydn  So welcome to this Pivotal 180 webinar on scenarios and data tables. Dan’s the star of the show today, but I’m Haydn Palliser. I’m a teacher and principal at Pivotal 180 and a financial modeling geek. As an administrative item, we’re going to take around 30 to 45 minutes and we’ll also have a bit of time afterwards for any questions and answers. If you have any questions, feel free to post them on the question board within Zoom. And also apologize for the noise you may hear outside at times we’re based in the iconic Chrysler building in Midtown Manhattan. So the noise of sirens or construction vehicles, uh, it is just a part of life here. So a little first about Pivotal 180, we provide training and mentoring to professionals, governments, students and nonprofits. Primarily in the renewable energy and infrastructure/project finance world, but also in private equity, general financial modeling or cashflow forecasting and also corporate finance. Our courses don’t just cover the financial modeling aspects. They also cover the commercial concepts and also overviews of important legal documents required to do deals who transact. We offer online courses, in person classes that are scheduled regularly throughout the world, primarily at the moment within the US Canada and Europe, which we will expand over time. And we also offer very highly tailored courses at your site based on your sector as well. So even down to things like how is tax structured in a renewable energy project finance deal in Sub-Saharan Africa. We can get very, very detailed in terms about our tailoring of our courses.  So with that it’s time to hand over to Dan who’s leading the webinar. So after you Dan.

Dan Thank you Haydn and thank you everyone for joining. Today’s webinar is on scenario manager and the use of data tables, and maybe before we start, let’s just step back and discuss for a minute why we’re talking about scenarios. When you build a financial model, you’re really not only interested in a single cash flow projection. There is not only one case or one view of what the future may hold. One of the most important functions of that financial model is to give you the flexibility to see what if a variety of things happen. What would be the return to the equity investor? Am I able to size debt? What if I used a different piece of equipment? And all of those things are different scenarios or alternatively sensitivities you might wish to put through a model and we would advise as a best practice that you do it in an orderly and tidy way.

Basically, the challenge is that inexperienced modelers tend to do one of two things with respect to scenarios. They either create a new model for each different scenario, so they create a really solid model that they’re happy with, that represents the base case. Then they copied the file, create a new version and call it upside case or a downside case, make a couple of changes to the inputs and there they have three different cases, and they can compare the results and that mostly works until there’s some kind of change that needs to be put through to all three versions of the model. Your debt amortization structure changes, your PPA price changes. There’s a mistake in the logic of your model and suddenly you’re not just changing at once, but you are changing it three times, and if you have more than three cases, you may be changing it five or six or eight or ten times. And trust me on this, when you do it, there will be a mistake, some kind of inconsistency.

The other way that less experienced modelers tend to approach things is they stick with a single model. They have their base case and then when they wish to run different sensitivity cases, they manually change the inputs right down the result or copy and paste some section showing the results and then they restore the numbers back to the original from the base case. That also works fine for awhile.  but if the cases that you’re running require you to make changes to a large number of inputs, almost with certainty, at some point in time you will forget to change something back to where it should be in it’s base case or forget to implement some element of it. And then it becomes brutally painful to rerun all of those scenarios every time you make some change to the logic of your underlying model. So those two approaches are not a good idea cause they’re time consuming, they’re error prone and they’re inefficient and difficult to audit.

The best practice that we recommend is what’s called the creation of a scenario manager. And that’s what we’re going to focus on this morning. So just note that when we teach at Pivotal 180, whether it’s in a public class or online, when introducing a new concept, we always like to start with something that’s very simply presented. Basically take out a lot of the moving parts so you can just focus on the concept that we’re teaching. And then together we will subsequently take that concept and implement it into a full blown complicated project model that may be quarterly and over 30 years. We just think that that’s an overwhelming and distracting way to introduce new concepts, whether it be something like this scenario manager or something like taxes. So that is our approach to teaching.

So let’s take a really simple example where what we wished to have flow through our model is a case one case two and case three, a base upside or downside. And what you’ll see is in the upside case, the price and volume are higher for this company and in the downside case, the price and volume of what they’re selling is lower and these are specified in an inputs area of our model. I’ve created at that very top row what I call the case selector cell and that’s in cell C1. And here’s its function. If I type the number 1 into that magic cell in C1, what populates in the, um, in the focused area of column C is the assumptions that are currently live and flowing through our model. Basically what should be driving price and volume and revenue in the model, it’s the numbers that are currently live in column C. and what this lets me do is if I then change the number in that case selector cell, to case number two, price and volume are now reflective of my upside case and that’s what will be populating my model. If I change that case selector to case number three, the price and volume have gone down to what’s reflective of the downside case and that is what is currently live and flowing through within my model. So the big question would be how do we do it? What’s the function that you use in Excel? And so for this, let’s focus on the formula that we would put into cell C7 which is the place that currently is specifying the $750 price per unit that is reflective of our downside case. So the function that we would use is a function called index. And the way we call index is =INDEX() and we’re asked to specify an array and what an array is, is it’s a range of cells that we’re asking Excel to examine. And in this case, for C7, what’s that relevant range of cells that we wish for Excel to examine? Well, it’s E7 through G7, the place where we have the various input choices for our price assumption that $1,000, $2,000 or $750. You type a comma in the function and next, Excel asks us to specify a row number and in this case we really only have one choice. If our array had two rows or three rows or fifteen rows, you can see how you might need to tell Excel which row within that array are we looking at. But in this case, our array only has one row, therefore our choice for the row number is 1. Look at the first row of the array; it’s the only row. And then we type a comma, and next we’re asked to specify a column number. And this is probably the most complicated part about the function, although it’s really not that complicated. The idea is to say which column within the array do we wish to be pulling data from. So if I want case 1 to be active, I want to be pulling that data from the first column within the array. If I want case 2 to be active or the upside case to be active, I want to be pulling the data from column 2 within the array from column F, and if I want the downside case, I want to be pulling from column 3. So where can I come up with that 1, 2, 3 that’s specifying which column within the array. So we come back to that magic case selector cell in cell C1, because that is 1 in case 1, 2 in case 2, 3 in case 3. But you’ll see within the formula bar, I’ve specified this as an absolute reference. I’ve used anchors: dollar sign C, dollar sign 1. And the reason that I’ve done this is because if I wish to copy that formula downward, from currently cell C7 down to cell C8, for example, I want the subsequent choice of row for the array to be moving down the page. But I always want to be pulling my case selector from cell C1. I don’t want that to be moving down the page as I copy down. 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. We have a model and in our input section we have a number of different inputs associated with each case, which we wish to make live within our model based on how we toggle our case selection cell. So let’s start by focusing on cell C9. What’s the formula that we should place into our price? And that should simply be =INDEX() And now we are asked to specify where is the location of the array that we want Excel to examine. And in this case, this is the array that we wish for Excel to examine. We type a comma and now Excel asks us what’s the row number within that array? And you can see the array only has one row, so it’s simply row 1. And finally, what’s the column number within that array that’s of interest to us? And you’ll recall that’s going to be our case selector cell because we want a one to indicate base case 2 upside, 3 downside. Within that formula I need to anchor my cell, my cell C2, and I can do that either by manually typing dollar signs or using the shortcut function key F4, close parentheses, hit enter, and now you can see if I cycle into case 2. The upside input becomes active. If I cycle to case three, the downside input becomes active and it is the values that are located in column C which are flowing through the model to drive our revenues and expenses. So now I can simply take this value, or take this formula in cell C9, copy it using Ctrl+C, and paste it over here and you’ll see again as I cycle through the cases, it’s updating appropriately. I can take the same formula, copy it, Ctrl+V, and paste it. I can again, Ctrl+V, paste it, and Ctrl+V paste it one more time, and now we have a working model. When I change to case 2, it is all of the upside case assumptions which are flowing when I changed to case 3, is all of the downside case assumptions that are flowing. One area of note is this assumption here, under time. We’ve assumed that you in our upside case, the equipment life is one year longer, it’s six years, not five. And then our downside case, the equipment life is shorter. And how have we implemented that in our model? Well, we’ll show you that just down below. So where that equipment life gets picked up within our model, is right here in row 22 where we’ve created what’s called a binary flag,and binary flags are something that we will get into in content that will subsequently be available on the website and that’s something that we cover within our classes. Absolutely a best practice in modeling, but simply what it is, is it’s a 1 or 0 designation associated with some kind of yes-no situation. So in this case, what’s going on in this section over here, is an evaluation of are we within the useful life of the equipment and if so, give me a value of 1 and are we beyond the useful life of the equipment and if so, give me a value of 0. So you can see in our base case we have an extra year of equipment life and in our downside case, we have two fewer years of equipment life. Now the convenience of that is what’s happening down here in our revenue and expense formulas, is the formula for revenue and expense is being multiplied by that binary flag by the one 0.As you can see right here from this area highlighted in purple, we are multiplying that formula by the value in that flag. Multiplying anything by 1 gives you itself, multiplying anything by 0 gives you a 0 and this is how we’re able to adjust the duration of the cashflows from our project or investment to match the equipment life. You’ll see looking down in 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 the financial performance of this company. Stepping out of Excel for a minute, we want to point out that the kind of sensitivity that we’ve been running in our demo is what’s called an overwrite sensitivity. Effectively what we’ve done is replacing one value with another in the model as we moved from our base case to our upside case to our downside case, we were simply substituting a different value for revenue or for cost.This is an excellent practice but it is by no means the only practice that one can use for sensitivities. And there are four other versions that are worth mentioning. The first is what we would call a selection sensitivity. And you would use this when you wish to limit the number of choices that a user can have in determining what that 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, there are really only three choices that the user should have. And therefore you can use the data validation functions and a pull down menu to create a sensitivity that looks that way. Alternatively, you might create a sensitivity which is a switch, and which is another one of those binary situations. It’s a yes or a no. So for example, perhaps you wish to run one sensitivity, which shows the performance of the project, if there is no debt, what are the financial projections look like versus financial projections, if the project has debt, that could be done with a yes, no or one zero switch within your scenario manager. You can modify numbers using an addition flex. So if for example, you wished to take a price and add an extra $5 a megawatt hour or $50 per unit to that price, you just indicate how much should I be adding to that price? And that’s the sensitivity you’re running. What’s the addition? Or alternatively, you can do something called a multiplicative flex, which is instead of increasing a number by a certain unit amount, it’s increasing it by a percentage amount. So if instead I wished to increase my price by 10% or my CAPEX cost by 10%, effectively I am multiplying my base case CAPEX price, by 1plus 10% and you can see how any of those could be specified within our scenario manager provided that you’ve built a formula to take that number and multiply and use it appropriately in forecasting your revenues and expenses.

Now something that will be extraordinarily handy about our scenario manager is that we don’t just need to look at a single scenario, whatever is currently active and live within the model and manually cycle through 2 and case 3. If we wish, we can look at the results or the key results of all three of those cases simultaneously, which gives a decision maker a great way to quickly assess risk within a project. And for this we’re going to use a data table. And here’s how data tables work: on this first row, in row 37, we’re going to tell Excel, where do I find the key outputs that I wish to be displaying within the model? So for example, where would I find the name of the currently active case? And in this case I can find the name up here in cell D2. And so I tell Excel to go there for the life. Where can I find the equity IRR? I can find that in cell C31, enter. Where can we find the total lifetime distributions to equity? Well, I’m going to look in the cash available for distribution here in the column that includes the totals over the project life. Where can we find the total lifetime fixed expenses that comes from our sum total of fixed costs up here, B=fixed. Where can we find our total variable cost? It comes from the sum total of variable costs in row 27 and finally, where can we find the equipment life? And you’ll recall it’s the same number, which we are using in our operational flag for how long is this project operational?

Now once we’ve told Excel, where is the location of all of these outputs that we wish to see within our table, we then want to tell Excel, how do I populate that table and the instructions that we’re giving it is to take the number 1, 2, 3, and cycle it through our magic case selector cell in cell C2. And give me the results of whatever appeared in these fields that we’re referencing on the first row of the table. So to do that, we are going to select the entire space of the table. The first row that we select is the row containing that data from the live case or the row that shows Excel, where are we going to pull the values of interest. And the first column of the table is the place where we’re going to locate the case numbers, the 1, 2, 3, which we wish to be placed into our case selector cell and to cell C2. From there, we go into data, what if analysis, data table, and Excel is prompting us to provide either a row input cell or a column input cell. In just a minute, I’ll describe the difference between the two, but trust me on this for now, we’re going to select cell C2 as our column input cell and the reason it’s because the 1, 2, 3, that are specifying our case, appear in the first column of our data table. We hit okay and because of the way Excel is configured on my setup here, it looks like all we’re seeing is the downside case, but fear not. If you simply press F9 (function key nine), the data table will update and now we can simultaneously see the results of all three of those cases. And it doesn’t matter which case is currently active. As I move to the base case, you will see it’s the value of the base case that’s currently flowing through the model and the value of the base case that’s indicated as being alive. But these numbers down here haven’t changed because they are always showing you the value of either base upside or downside, as indicated. Now we’ve created on this slide a quick reference for the steps that we went through just now live on the demo. I’m not going to walk through it on this webinar, but instead, if you are watching this on replay, we would encourage you to hit pause. You’ll see the complete list and have instructions if you wish to do this on your own time or within your own model as opposed to the demonstration model that we’ve been using here. Now the question becomes, why? Why was that called a column input cell? Why were we specifying that C2 was our column input cell and how can I remember whether to be using a column input cell or a row input cell. When I use data tables in Excel, hopefully this will help. What the column input cell means is, take the value that appears in the first column of the table. In this case, you’ll see that first column is written in green just as that green box is surrounding our 1, 2, 3. Then place that value into the cell in Excel that is specified as our column input cell and what’s our column and puts out? Well, it’s simply that magic case selector cell that was in cell C2 of our demo model. And then what do we want Excel to do? We want Excel to give me the output values that were specified on that first row of the table where we were showing where we pulled the IRR and lifetime distribution in equipment life for each of those respective cases. That’s it. So alternatively, when would we use a row input cell? If our data table was configured with the axes flipped, it might look something like this, and what would the row input cell mean? It would mean we are asking Excel to take the value that appears in the first row of the table. that 1, 2, 3, place that value into a cell that we’re going to identify as the row input cell and what’s our row input cell? Again, it’s that magic case selector cell that was in cell Cw2 of our demo. And what do we want to do? We want to take the output values that were indicated on that first column of the table. The place where we told Excel is the location of this data of interest and paste those values in each subsequent column associated with the one, two, or three that appeared in the first row. This scenario manager was set up to be cycling through cases that were specified as base upside downside, and you can build 20, 30, 100 cases, as many as you like. However, data tables are very powerful for showing the range of potential results and things even when you’re not looking at different cases. So for example, let’s say you wished to run a sensitivity of what is the NPV and IRR of this project for an equity investor, when the project borrows different amounts of debt, you could specify in your column input, $100,000 in debt and $90,000 in debt or $80,000 in debt. And effectively what your data table will do, is take that amount of debt, put it into the cell that Excel is using to demonstrate debt size, and provide for you the corresponding IRR and NPV, with each of those values. Or what is the sensitivity of EBITDA on IRR to different level of O&M expenses. You specify the specific O&M expense in the first column of that table and tell Excel I want to give me the result of the corresponding, EBITDA and IRR and it will do so. Everything that we’ve shown you here is what’s called a one dimensional data table, where basically we are only cycling through things on either the first column or the first row of that table. But you can imagine situations where you might like to see a sensitivity to two variables. For example, what’s the equity IRR within a range of debt amounts, so a percentage of leverage or a certain number of dollars of lending, and also a range of interest rates on that debt. And if you specified on one axis of the table how much debt and on the other axis, what is the interest rate, you could build a two dimensional data table and then it’s just a question of which one of those items, was my column input cell or my row input cell, an enormously valuable tool. As a best practice, we believe that it’s always a good idea to locate your data tables on the same tab that contains that row input cell or the column input cell. There is some opportunity for error and confusion, if you begin to mix and match. It is possible within Excel to move a data table onto any tab. We just recommend not doing so. And with that, it looks like we’ve got a couple of questions and so let’s dive right in. Right. Okay, thanks Dan. So a couple of questions. Maybe the first one. In the context of renewable energy project finance, what are the common types of sensitivities that are run? It’s really a function of the stage within the life of a project we’re talking about. So in the development stage, before all of your documents and deal terms are finalized, it would be quite common for developers to run scenarios based on the use of different equipment. I’ll run my wind project economics based upon the use of different turbines or, for example, we’re still in the process of negotiating a PPA, what does my project look like if I offer a PPA that has a flat tariff with no escalation, versus a PPA that starts at a low level and escalates every year in the operating phase of the project, you might wish to look at things like a base case, upside case and a downside case, where you’re really trying to figure out things like, do we default on our debt or can we actually pay debt as scheduled, and what would be our equity IRR? But also, what would be quite common is to use scenarios for the sizing of debt. When figuring out how much debt a lender will provide, lender is usually using a set of cash flows that are based upon assumptions that are more conservative than the developer. Developers are eternal optimists and lenders are largely trying to manage their downside risk. And so lenders will often use a more conservative case in terms of expenses or if we’re talking wind and solar of the P value. So if you’re familiar with terms like P-50 versus P-90 or P-99, it might be quite common for lenders to run a P-99 case, which is what is the 99% certainty level of wind or solar? Something where I just, I know every year is going to be better than that. Probabilistically speaking, can I still repay the debt? There may be nothing in it for the equity, but can I still repay the debt? That might be a very important case for lenders, which would be a scenario that you would run in determining the appropriate size of the debt for a project. That’s great, and there’s two more questions before I have one. The first one’s simple. Can participants get a copy of Excel file? Yes, absolutely. When this webinar is posted online, we will also post a link so that you can download the demo file if you wish to operate it or recreate what we’ve been doing. Well, we’ll give that to you, of course. Makes sense. And the next one is, and I think the question really relates to this with data tables, if you have lots of data tables in a model a really large data table, it can slow down the calculations in the model.

So any advice or guidance you can give around that? The big advice would be within Excel, you have an ability to control how your calculations work. So let’s just get back into Excel and show you where it’s located. If you go into formulas, calculation options, you can see checked here, within this version of Excel, is automatic calculations except for data tables. So you’ll recall when I created that data table, how at first it was populated with all the same value on every row. I think it was the downside case that was active and I said fear not. And we pressed F9 (function key nine), that was telling Excel to recalculate the data tables because Excel before that was calculating everything automatically except for data tables. Data tables can slow down your calcs if every time you’re creating a new formula, they are constantly being refreshed. So if you use a lot of them or very large ones, we would recommend you change your calculation options. Just remember before looking at the results of those data tables, you have to hit F9 to refresh. That’s great. And maybe, in addition from me, try and limit the number of data tables in your model. So let’s say three to four generally works pretty well in the latest versions of Excel, but I wouldn’t go beyond three or four. I think that’s a, that’s pretty good guidance. Um, there is another option as well. One of the challenges with data tables, if you have say four data tables, you might not need to update every single one of them every time you want to update the outputs by pressing F9, what you can do instead is, although we don’t encourage the use of macros too much, is you can replace each data table with a macro, such that you can just run one macro or one data table or a part of a data table with a click of a button. That’s another option for you as well. Maybe we can run a webinar on that as well Dan. And the final one’s one from me actually and since we’re in the Excel model, Dan if you can scroll up to the inputs. I go and look at price, and maybe look at case three downside. So let’s say case three downside was 120 instead of 115.

Quite often these models, you have much more than sort of three scenarios. You might have 10 or 15 and you’re trying to identify what’s different in each one of these cases, compared to case one. So for example, let’s say price was 120 in case three, could I make that 0 so that I can see only the things that are different to case one. In this case, my upside price is 130. Can I make that 0 instead just so I can really understand the model of a little easier? You absolutely can, and that’ll be a technique that we do teach in our public courses and online and it really is an excellent practice if you’re trying to help the user focus only on what’s changed from the base case in this particular scenario. And everything else is the same as the base case. The function that we would use in Excel is called ISBLANK. And effectively what it’s doing is evaluating is the value in that cell blank, and if so, use the 120 that’s in our base case, and if that value is not blank, use whatever is there. So if you’re a, if you’ll be taking the courses with us, we’ll walk you through it. If not, Google is blank and you should be able to figure that out for yourself. Just before we do a quick summary with Dan as well, there’s an onscreen you can see our upcoming courses through to the end of March. They’re all in the US or in Canada, but there are courses overseas post the end of March as well. You can find this complete schedule of courses on our website and we’ll send a link out with that after this webinar If you’d like to register for one, you can just do that directly online if you don’t want to have to speak to Dan or I. Otherwise, if you’d like to know more about the detail of each of these courses, you can give us a phone call or you can email us at sales@pivotal180.com. Anything else? If you like what you’ve heard on the webinar and what you’ve seen on the screen, we encourage you to sign up and visit our site often. We will be regularly posting new webinars, new how to’s, blogs, and video content that are samples from our course. And just a further brief plug about our courses. One of the things that distinguishes the way in which we teach, is the way in which we incorporate online curriculum and the use of videos. Effectively, you take a three day course with us and we are able to fit five or six days of content into that course by providing a year of access to online videos that can take you deeper into the modeling concepts and the financial concepts, but also provide the context of how these things fit within a transaction. And so we’re not just about teaching modeling, we’re about teaching how to do deals, which include financial structures, legal risk management, documentation. We cover some of that in class, and refer people to online videos in order to go deeper. I think it’s really important to recognize that modeling is just one piece of the deal. But what it is you’re modeling is ultimately rooted in the documents, and without understanding that document and transaction structure, your ability to function as an analyst and as a deal team member is quite limited, and so we really try to offer both. It is possible to sign up for online courses only, in which case you don’t have the benefit of working directly with me or Hayden and there’s no one holding your hand, but all of that content would be available to you online. Sure. And feel free to register on our website so you can receive a notification about upcoming webinars. And with that, thanks Dan for your time and thank you everyone for joining. Thank you so much. Take care.