Transcript Extract:
Alright let’s talk about Scenario Tools in Excel! Scenarios are probably kind of the whole point of financial modelling really. We often find that the whole point of creating a financial model is so that you can perform scenario tools and scenarios can be really, really complicated, or they can be quite simple. I am going to take you through a couple of different tools and methods. In our financial modelling training courses we go through a number of different types of scenario analysis. I am going go over them at a fairly high level today because we don’t have a huge amount of time. I just want to give you a bit of an overview of the kind of tools that are available in Excel and you can certainly look into them a little bit further. And, also I’ve got a couple of Excel files that I’ll go through with you as well so I will make those available soon after the webinar.
What we are going to talk about today are the different technical methods of scenario analysis and I will just do a short practical application of each of those methods. Looking at scenarios, sensitivities, what-if analysis; all of that is fairly similar but a little bit different. Each of those terms has slightly different meanings. Uncertainty is not necessarily risk and a forecast is different to a scenario usually we just do a forecast and it is base case is what we think is going to happen. If you’re then able to put scenario analysis in there; if you’re able to identity what those drivers are, you can then create scenario analysis by tweaking those drivers just a little bit. Typically we do a best case, a base case and a worst case but of course as the model gets more detailed we can do a lot more analysis to that.
Here’s an overview of the different types of Excel scenario tools. I’m going to demonstrate each of these very, very briefly. I’m going to be taking you through some manual scenario selection, some data tables, scenario manager (not that great, but I might just show you that briefly), and we will do a very quick goal seek as well. Quite a lot to get through today.
What I would like to go over with you is just a quick case study. This is a model that if you guys have got a copy of my book, Using Excel for Business Analysis, you do get a copy of that book included if you attend any of the online training or any of the workshops. This is an example that we walk through in the book and we also cover it in the Advanced Financial Modelling training course. I would like to go through that with you. I am going to flick over to Excel, just so I can demonstrate.
What we’ve got here is; the first one I am going to talk about is the data validation drop downs. What we’ve got here is a best case, base case, worst case. So the way that we’ve built this is just using, you can see here that I’ve followed best practice. I’ve got all of my assumptions. I’ve used an input font here and I’ve used some calculations here; total expenses, net profit and profit margin there. So nothing overly complicated about the calculations, but the way that I’ve built this model is that we have everything linking back to my drivers. These are my drivers across here in Row 8. Then what we do is make those drivers change automatically depending on the drop down box. I’ve just used a very, very simple data validation drop down box here; so best case and then I can just change that to make it base case or I can then change it to make it worse case. And, you can see there that the output of the numbers just automatically change. That’s very, very simple. The way that we’ve built this is very simple but it can be applied to really, really complicated financial models. The way that this works, of course, is these formulas automatically link through to my drivers and then as my drop down changes, the formula automatically changes as well. You could use a number of different formulas for that. In this case, I’ve used a SUMIF formula. You could have used an index match; you could have used a VLOOKUP or a number of different formulas that could have been used.
Let’s go on now. That was a data validation drop down box. You guys probably know how to do that but that’s just done using a data validation here. I’ve just done that creating a data validation drop down. Very, very simple; nothing overly complicated about that.
Let’s go forward now to the very next tab. We’ve got here a best case, base case, worst case exactly like what we did before except I have a different kind of a drop down box and that’s using a combo drop down. It works in a very, very similar way except that it’s actually an object that sits over the top of the page. You can see there that if I move that away, you can see the number changing; base case, you can see that changes; worst case, and then if I hold down my Ctrl key I can move that back over the top to there, like that. Again you can see my formulas. In this case I’ve used a Choose function, but you could use an Index or a number of different functions for that one there. That’s done using a data validation drop down. That was built using the Developer tab. You’ve probably noticed, I’m using Excel 2016 which is fantastic. I’m a big fan of that. You can see it might just look a little bit different to your Excel but in any case you will need to have the Developer tab installed. If you haven’t got that installed you will just need to go into your options and you can see here, I’ve gone Insert and gone to a Combo box to create that kind of drop down box. The way that it works is exactly the same as I did with the data validation drop down except that in this case, if I click away from it, you can see that the drop down disappears. With this one, you can see it straight away. Either of those are very, very similar in the way that it’s built and the way that the user can use it.
Okay, Andrea wants to know the benefits of a Combo box over using the data validation? Not a massive difference Andrea. It really depends on; it’s really from the user perspective. For you as a developer, it’s much more work to create the Combo box but it just makes it a little bit easier for the user, for the person looking at it. If I open up this model and I look at it and go, hey that’s a drop down box I can see it straight away; it’s kind of in your face. We’re quite used to seeing drop down boxes and we know what to do when we see them, whereas if I go back to my data validation drop down box; I look at it and say hey, what am I supposed to do with this, until I click on it and go, right it’s a drop down box. So that’s the advantage of using a Combo drop down.
I will go now onto the data table. Going back to the PowerPoint that I was talking about earlier. What we’ve talked about so far, is a manual scenario selection. What I’d like to talk about now are data tables. They’re a really, really different type of scenario analysis. In fact, it’s not really, technically speaking it’s not really a scenario analysis; it’s more of a sensitivity analysis tool but they’re fantastic. It looks a little bit different, this example here. You can see here that my options are actually designed quite differently. We’ve got the building costs per square metre down that way and the sales price going up across the top. The way that it’s designed is quite different, but the fantastic thing about a data table is that you can see all of the outputs side by side. You can see every single possibility in a single table whereas, if you remember, with a drop down, we can only look at one at a time. I’m just going to go back to the other one for a second. We can only see our worst case and then if I change the drop down, we can only see our base case; it’s only one at a time. Whereas, if I go to my data table now, you can see all of the simultaneously which is much better to be able to see all of the different iterations as we go through. I will just show you quickly, how to create one. You can see it’s got little curly brackets in the table there. I need to highlight the whole lot and delete it, and you need to have a link here. The other limitation is that you’ve got to have all of the inputs and outputs on the same page, but it does take you through step by step how to do this in the book. If you’ve got a copy of the book, it’s on page 362. I think I’ve got some blog articles which explain how to do it as well. You just highlight the whole lot like that. Go to Data and go to What-If analysis here under Data table. You just need to give it your row input cells. Your row input cell is your sale price. Your column input cell is going to be your building cost and we say okay. There we go and that gives you your data table. Quite simple when I show it to you like that but it does take a little while to get your head around how a data table can work. Just a very, very quick overview of how a data table can help you. But if you’ve got a little bit of time, you can go through the examples I’ll give you and see if you can get it working and have a think about how you can apply that to your own models.
The other thing you can do with a data table; I’m just going to go over to the next tab across, is do some probability weighting across that.Cole’s just said to point out that this is a two-way data table. That’s a good point, Cole, thanks for that. You can either do a two-way data table or a one-way data table. I could have just done it with one way which is just all down one column or I can do it all along the row like that. The way I’ve done it, is actually a two-way data table.
What I will do next is have a look at some probability weighting because the problem; if I go back actually to my slides for a minute and just talk about probability for a second. I will go forward to here. Thinking about the likelihood of probability in scenarios is that there can only be one outcome. When you’re creating a scenario, it’s either going to be right or it’s going to be wrong. It isn’t necessarily, it could be this or it could be that, but there is only going to be one outcome and you can guarantee the only thing for sure is that it will definitely be wrong. If I were to day “according to our financial model, the annual profit will be, this much, 4.5 million, blah, blah, blah that means that our company is worth blah, blah, blah”, we know that I’m actually talking rubbish because there is no way that I can possibly know that. It really is just an estimate. One thing we can do is put in some probability and that can give us a little bit of weighting and a little bit more surety around our financial model.
What I’ll do is go back to my Excel. What I’ve done here is put in some probability weighting. I’ve said that under the building costs, I’ve said that I think it’s most likely to be the base case. There is a fifteen percent chance that it’s going to be the worst case and a thirty-five percent chance that it is going to be the best case. I mean that’s my best guess, based on historicals or it could be a gut feel or whatever it is. Then, similarly I’ve done the same thing with my sale price. Importantly you have to have your probability add up to 100 percent; it won’t work otherwise. Then what you do, is you just multiply it out. Now we all know how important using mixed cell referencing is. I’ve used some mixed cell referencing in this case. We just copy that across and down and that gives us our probability weighting and we can see there that adds up to 100 percent. I’ve done another data table here but instead of using the net profit margin I did before, I’m actually using my net profit. I do that and then over here I’m just multiplying it out. So based on the outcome multiplied by the probability and that, if I add all of those up, then it’s going to give me my probability weighted, predicted outcome. We know that it’s not going to be that, but based on the probability, the best case, base case, worst case; it gives me a little bit of comfort that this is the most likely outcome of my property development. That’s it for data tables and probability.
The other thing I wanted to go through with you is the Scenario Manager. I’m not a big fan of Scenario Manager and I’m going to take a minute to go over this; I don’t really like it very much. Basically, with this one, if we were to go in here, we’ve just got a very, very simple income, expenditure and savings. If I go in now too, I don’t use this very much; if I go into Scenario Manager. It’s together with the Goal Seek and it’s together with the Data Table, all those really useful things. Scenario Manager’s just not really great company. If I were to say, add and use Scenario and if I were to say, Scenario one and I were to change that one, and then that one. You know it will change that there, so I will say okay and that’s Scenario one. Then I add a new Scenario and I will say Scenario two and let’s say we were to have 18,000 and 2,500 or something like that. And, then we add another one and we call that Scenario three. There we go, so you have to manually go in there. Are you guys impressed? It’s very manual, not my favourite process to have to go through that. We’ll say okay and then we go, show. Did you see that? Blink and you’ll miss it. If I say Scenario one, show; scenario two, show; scenario three, show. There we go. That was it. The thing I suppose I don’t really like about this is, if I say close, and then I go into it.
Cole says, can you link the inputs to a cell? Yes, you can do formulas in there but these inputs really need to be hardcoded numbers and you need to go into; and now I go oh, scenario manager, there it is. There’s all my scenarios. I would much rather see it in a drop down box or a data table; something that’s really, really obvious and you can see what the modeller’s done. I don’t really want to have to go in here and say show like that. You can go in and show a summary I suppose. That’s kind of cool, is being able to look at them all side by side, using a summary like that. Personally, I’d much rather use a data table.
That’s your summary there. In my opinion, I would much rather go with a data table, this one here, or using a drop down. I would probably go with a data validation drop down. That’s in my opinion, the simplest and the easiest way of creating a scenario manager.
I will just go back to my PowerPoint just for a little bit of an overview. We’ve talked about each of those. The manual scenario analysis; the advantages of the manual is that it is easy build, it can handle a large number of inputs and outputs, so you can see all of the outputs at once. But the disadvantage is that you can only look at one at a time. So you say best case; all the numbers change; worst case, all the numbers change and you can’t compare them side by side. If you want to be able to compare them side by side, you do a macro or you need to copy paste values or something like that. It’s quite tricky to do them side by side. Of course, using a data table will allow you to see them side by side.
The advantage of a data table is that can look at multiple outcomes simultaneously; it’s good for sensitivities, if you wanted to see how sensitive the output of a model is to one single variable. You say how much would my value change based on growth rates or something like that. You wanted to see how much that output changed as a result of the input changing. That’s when a data table is really, really useful the disadvantage is that they are quite tricky to build. They are kind of an array formula so there are certain rules; you need to highlight the whole lot, if you’re a beginner it’s quite hard to get your head around it. The inputs and outputs do need to be on the same page. You can only have one or two variables and one output. If you want to look at multiple outputs you need to create multiple data tables. If you use them a lot, it can slow down your calculations so that’s another disadvantage of using data tables.
I will go in now to talk about Goal Seek, that’s something I haven’t covered with you yet. I am just going to go through a case study with you. Here we’ve got a break even analysis. So that is, I guess, a break-even it’s not really a scenario but I just wanted to very quickly show you how to do a break even using a Goal Seek. I will give a copy of this file as well. Goal Seek is really quite handy. Again, just going in to your What-If analysis. We’ve talked about the data table, we’ve talked about scenario manager, so just going into Goal Seek. If I say I want my profit to be zero, I need to know how many units do I need to produce so that my profit will be zero. If I set my profit to zero by changing the amount produced and okay. That will work backwards. It is a really, really powerful tool and you can have really complicated financial model but as long as you have a hardcoded number and a formula that links directly to that hardcoded number, it will all work well.
That’s another type of sensitivity analysis that you can do using a Goal Seek as well; What-If analysis and I just quickly throw this in. Here’s one I prepared earlier.
Nitin wants to know about using Goal Seek for IRR. Yes, you certainly can use Goal Seek with an IRR, that would work in a very similar way. You would just calculate your IRR and then have an input and then work backwards such that your IRR is a certain amount. Yes, it is quite a handy tool for that.
I’ve created a macro here ane we will say break even and there we go. That’s just using a macro. If you go into developer and I will give you guys this file and you can take it apart if you like. It’s just really simple. All I did was basically record myself running a Goal Seek, because Goal Seek can be a little bit tricky trying to remember which one is the input, which one is the output. You can sometimes create a macro to do that, so I thought I would throw that one in for you. I will give you a copy of that also.
The problem with Scenario Analysis, basically, is that it provides a “snap shot” view. We know that it’s always going to be wrong but does that make it meaningless? Not really. We know that the weather forecast is always going to be wrong but it does give us, it is helpful. We know it’s not going to be exactly right but it does give us a bit of an indication in order to prepare. Is the base case really the most likely? It really depends, quite often we have very aggressive modellers that put really optimistic scenarios in. It just really depends and the scenarios and sensitivities are a bottom up, so you really need to know the drivers and get into the financial model in order to create scenarios and sensitivities.
Cole wants to know; do I mean optimistic modellers or do I mean optimistic managers? Well sometimes they are the same thing but as a career financial modeller, I am always very careful to make sure that the assumptions and the inputs for my financial model are created and checked by someone else. I often work with entrepreneurs or people who are very, very optimistic about how their financial model or how the output is going to go and so they want to put in very, very bullish or very aggressive growth amounts. And, as a financial modeller, your responsibility is to make sure that the calculations are right and that the assumptions are logical. It does come down a little bit to the personality of either the modeller or the manager depending on the dynamics of the situation.
It is helpful to use a worst case scenario. We often use worst case scenario really just to see if absolutely everything that could go wrong, does go wrong; is it going to push us into default. Is it going to cause problems? It can help us to prepare for the worst without being overly pessimistic or negative. It is good to know what is the worst that can happen. We sometimes tend to focus on the upside but we do need to be prepared for the downside as well.
That’s about it for the content of the scenario tools that I wanted to go over with you. I just wanted to share with you a free resource. If you’ve got a copy of my book, you probably already know about this. If you go to www.plumsolutions.com.au/book what you will find in there is a whole pile of downloadable resources. A lot of the stuff in the book is available on the blog. If you go to www.plumsolutions.com.au there’s is some articles in there and lots and lots of stuff in there; lots of files. You can download all of the models that we used to create the book. Susan’s just providing you the link there on the chat, so you can click straight into it if you want to. You can download all of that and have a look through. I will make available that property development model that I gave you. I will give you a copy of that. But, all of that is in there as well and the step by step instructions.
What other topics would you like to see in a future webinar? If you enjoyed the webinar, or have any comments then please add your feedback in the comments.
