Excel's Business Tools - What-if Analysis

I'm Martin green and I'm going to show you some of the tools that Excel has for what-if analysis what-if analysis is a technique that's used widely in business to try and predict what might happen if certain circumstances prevailed for example what would happen if we increase our selling price what if our sales increased or decreased what would happen if the interest rate on our loan increased or decreased and to demonstrate these particular tools I'm going to use a really simple example but most people can relate to and that is of a loan from a lender such as a bank at a particular interest rate over a particular time to do that I'm going to use the PMT function provided by Excel and I've already set out on my spreadsheet here an outline for the information that we need first of all how much do we want to borrow what's the prevailing interest rate how long do we want to borrow it for and here we're going to calculate how much we will have to repay each month now the PMT function is quite a widely used one and you'll find it on the formulas tab in the usual place go to the insert function tool and it's under my most recently used this but you can find it by looking through the alphabetical list I'm going to select it here and here we are a description of the function it calculates the payment for a loan based on constant payments at a constant interest rate so I'm just going to click OK now here are the arguments that the function requires first of all the rate and I'm using an annual interest rate and to start with out chosen 6.8% which is what is current at the moment so I'm clicking on that cell and notice that I'm putting the cell reference into the box here rather than the actual rate itself that means that if we change the rate in the cell our calculation will automatically update itself now in addition to putting that cell reference in I'm going to divide by 12 and that's because it's an annual interest rate but we're calculating re payments on a monthly basis the next argument is the number of payments again because we're doing it monthly I've started off with a value of 60 so that's 60 months so I'm putting that cell reference in there and the next one is the present value present value is essentially the amount of money that you want to borrow I like to put that in as a negative amount the reason for that is then the repayments then show as positive amounts so I'm typing a minus sign and then putting in the cell reference for the cell that's got my loan amount in it now you'll notice that the three arguments that i'm i've already supplied here are marked here in bold and that's because they're obligatory you'll have to supply them the next two are optional and I'm not going to supply those because they're not relevant to what I'm doing at the moment it will tell you what they are FB is future value now that is relevant if you are for example reducing a debt from one amount to another maybe you owe 10 thousand pounds and you want to reduce it to five thousand pounds although you're repaying just the difference between those two numbers you are actually paying interest on the full amount so that's why that's relevant the final one is type which is whether or not the repayment is made at the beginning of the or at the end of the month but normally we repay at the end of month which is the default so I don't need to put anything in there you can see over here that Excel has already worked out the result and it will insert that onto the spreadsheet when I click OK so there we are if I brother borrow 20,000 pounds at 6.8% of a 60-month is going to cost me three hundred and ninety-four pounds 14 a month ok what if I decide that maybe I can't afford that but I can afford three hundred and fifty pounds a month where my options well it's unlikely that I can negotiate a different interest rate although I could look at different lenders who might be offering different interest rates it's more likely that I can decide to grow a small amount of money or repay that same amount of money over a longer period so how do I work that out well Excel has got a very useful tool called the goal seek tool which allows me to do that and you find that on the data tab we go to what-if analysis then choose goal seek and the goal seek tool allows you to set a particular cell to a certain value by changing the value in another cell so we want this cell the one with our repayment amount in to be set to 350 which is the maximum that I can afford that cell has already been inserted here because I haven't done it selected when I open the goal seek tool I'm going to tell it they wanted to set it to that amount 350 by changing one of the other cells that has an input into this formula I'm going to choose to ask it to change the loan amount so I'll click in this cell here and on loan amount and when I click OK it'll work the PMT function backwards if you like to achieve that value of 350 there we are it's arrived at a value and it's told me that I can borrow seventeen thousand seven hundred and sixty at that same interest rate and over that same term if I want the repayments to be three hundred and fifty pounds if I'm happy with that I just click OK here but what if I want to try something else I might say no I need that twenty thousand maybe I can borrow it over a longer period of time so I'll cancel that dialog box and now I ask for it again this time I'll set the same cell to 350 but this time I'll ask it to change the term and it's arrived at an answer which is just over 69 months so you can see that this is a very useful tool but it's only ask answering one question at a time what if I'd like to ask that a whole number of different times different circumstances and what if I want to see the combinations of different terms and different blown amounts and we have another tool that will do that for us and that's called the data table it's how it works the first thing I have to do is to set a reference to the original formula and I'm going to do that by clicking on a cell where I want my data table to start just typing an equal sign and clicking on the cell where I wrote my original PMT function now press ENTER I get the same value of course I look at different amounts of money maybe I'll start at 15,000 and I'll go up in increments of 1000 pounds and I'm going to use the autofill tool to do this and take it up to 30,000 pounds I'll just format those values so that we can see what we're talking about and in order to create our data table I need to draw a rectangle around the cells that I've just put information in now note how I laid out the data there my column of variables here so this variable amount refers to the loan amount up here in the original formula and that is one row down and one column for the left of my reference value here now having laid that out and selected this rectangle I'll go to the data tab and to what-if analysis and ask for a data table data tables can be of two kinds you can have a single variable data table and a two variable data table well in this example I have a single variable which is the loan amount so I only need to supply one of these pieces of information and with a single variable data table you arrange your variable in a column so the information it requires is called the column input cell and what you must tell it is the cell in the original calculation is equivalent to your variable here and that of course is the loan amount cell when I click okay it creates the data table and you can see it's done those calculations for all the different loan amounts and again I'll just format those so that you can read them easily what if I wanted to look at a combination of two different variables and here I can look at combinations of lung amount and term for example I do that in a similar way I start off by setting a reference to the original calculation I need my column of variables so rather than type them out again I'm just going to copy and paste but this time my column of variables needs to go directly underneath the original reference I'll set out the second variable in a row immediately to the right of the reference I'll start off with 36 months and go up in increments of 12 because there will be years and I'll use the autofill tool to help me what I get up to 10 years and again I select a rectangle that encloses all that data on the data tab I'll go to what-if analysis and data table and this time I need to supply both bits of information now this time I have a row of variables so my row input cell is the cell in the original formula that's equivalent to the variable here which is the term variable which is this cell and the column input cell is as before the loan amount and okay and it's filled in that entire block of cells and I'll just format those again so you can see I've saved myself an awful lot of experimentation with the goal seek tool and I've worked out all those values in just a few clicks of the mouse using a data table and one more thing to show you here is a way of formatting that data so that it becomes easier to understand and I'm going to use a kind of formatting called conditional formatting where the formatting of the cells will actually change depending upon what the value in the cell is I want to format these cells here now if you look at the values in these cells they run from 172 right up to 923 and I said earlier on that I was interested in something that would cost me about three hundred and fifty pounds so supposing I say that I'm interested in repayments that fall within the range of 300 to 350 and I'd like to see those quite clearly here in this block of data and it's quite difficult to see them at the moment but you can make it really easy using conditional formatting let's take a look I've selected the cells that I want to format I'll go to the conditional formatting tool which is on the Home tab I'm going to choose highlight cells and to start with greater than now up if the sales are greater than 350 I'm not really interested in those because I can't afford them so you tell it what your limit is and here there are a number of choices of how those cells are going to be formatted I'm going to choose the default here which is light red fill with dark red text but you can see they've also given us yellow and green and a couple of other examples and in fact you can create your own custom format which can be anything you like I'm going to select that one and click OK and you'll see that it's already formatted all those cells which currently have a value exceeding 350 and it's formatted them in red now with the same cells selected I'm going to add another condition this time less then now I'm said I'm interested in values between 300 and 350 so if it's less than 300 I'm not really interested although I can afford it so I'm going to format those yellow and okay and finally the ones that are in the range I'm going to go to between 300 and 350 and I'm going to format those ones green because those are the ones that I'm interested in okay it's still selected so it looks a bit dull but if I click somewhere else you'll see that now it's very clear which values are the ones that I'm interested in anything between 16 thousand over 60 months and 30 thousand over 120 ones now the great thing about conditional formatting is that when the value shown in the cells changes then the formatting changes accordingly so we've already looked at a variable term and a variable loan amount the only other variable here is the interest rate so I'm going to change that I'm actually going to change it by quite a large amount so you can see the change here on the screen I'm going to say well what those repayments be if maybe we had a 10% interest rate I know that's a big change but I want you to be able to see it quite clearly on the screen so as soon as I make that change watch the cells that are colored green now notice that the area that was colored green moved into the upper right corner of the table meaning that I can either borrow a smaller amount or I have to borrow over a longer time in order to repay the same amount let's change that interest rate again let's take it to five percent and watch where the green area moves to this time it moved in the opposite direction meaning that I could either borrow over a shorter time or I could borrow a larger amount and so there's an infinite amount of variation there available to me and it's all very clear to see thanks to the conditional formatting finally I'm just going to show you a tool which is much underused in Excel and it's really useful and that's the format painter now I spent a little while formatting these cells here and now I'd like to format these cells in exactly the same way but I don't want to have to go through all those steps it's very easy to do with a format painter first of all I select any cell that has got the format that I want I'll go and click the format painter tool and what that does is call copy the formatting of that cell and then I paint that formatting over the cells that I want to paste it into and there we are job done the cells that are less than 300 are yellow greater than 350 are red and in between they're green just like over here because it has exactly the same formatting applied ok we've taken a look at the PMT function the goal seek tool to different kinds of data tables conditional formatting and the format painter if you'd like a free PDF handout of this particular tutorial go to my office tips website at WWE and you can download it from there

Channel: Martin Green

Views: 331,645

Rating: 4.8863826 out of 5

Keywords:

Id: jTthq0yjNQA

Length: 21min 4sec (1264 seconds)

Published: Tue Nov 13 2012