Using the Scenario Manager to Create Scenarios in Excel

I've created a very quick spreadsheet here it basically shows monthly income it's got my rent travel food and so on as my expenses going out what those total expenses are and the amount left over what I want to do is basically create different scenarios so I can change my income just in case I've got a promotion change jobs what might happen to my rent Travel food etc and see which one of those is going to give me the best outcome now I could make a copy of my worksheet and then just change the figures in each one of those however there's a handy tool for this called scenarios and what that allows me to do is put in figures and instantly recall them just using that one sheet so the first thing I need to do I'm just going to select all the cells that might change so I'm going to select that one hold down the control key and select these here because any one of these could change these two here a formula so they're not going to change so what I do next is going to tools two scenarios and my scenario manager appears here and it says that there are no scenario is defined now there's something I want to show you on this which is going to mean that I'm going to need to go back to the spreadsheet and make a change I'm going to click on add to add a scenario and the first one I always do is an original that always keeps the original figures I have I'm just going to click on add I'm going to call it original you can see here these are the cells that are going to be changing b1 and b3 through to be 11 if I hadn't selected them first I could go and select them now just by clicking and dragging and selecting on the spreadsheet I can put in a comment here as to whatever I'd like it to say about this one so I could get rid of what it says there so I'm jumping in here these are the the original figures so I can come thanks to them you don't need to put anything in there at all you can choose protection prevent changes and hide as well which aren't actually relevant for this because we're not going to be applying protection I'm going to click on OK and up pops the scenarios values dialog box and you'll see here it says is put in b1 with the dollar signs 2000 and we happen to know that that is the monthly income b3 is the rent and so on but if I was really looking at this I might not know so easily will each of these aren't I'd prefer it to have something a bit more descriptive I'm going to click on add because I'm going to click on OK because we know that these figures are going to be ok for this and I'm not too worried about what I'm changing but we're going to fix that I'm going to click on OK I'm now just going to close this because what we need to do is go through naming these cells here and when I do that it will actually make it a lot easier to use there is a tutorial on naming cells and ranges but very quickly there's my monthly income I click here in the name box I type in a name you can't use spaces or any unusual characters but underscores are fine a press enter and it's put it in there you'll see that if I click back on it it's there if I want to give this one here call it rent food travel basically what it says here just highlight all of those click on inserts go to name and I'm going to choose create and I'm going to get it to take the names from the left-hand column here that I've highlighted and apply it to each of these so I'm just going to click on OK and you'll see now looking at the top corner here as I click on each one they've all been named just so that you know naming cells and ranges does have another use so you should go and have a look at the tutorial of naming cells and ranges in that if you click on this list here if I was to choose say shopping it jumps that sell directly to there I could be on a completely different sheets and again if I choose monthly income it tastes so great navigational tool go and check out that other tutorial I'm going to go back now to my scenarios I'm just going to click on tools again scenarios there it says there's my original one I'm going to add another one now and in fact you can see here it says changing cells because that one on its own is selected now so it's monthly income I'm going to click on add this scenario name we're going to call it promotion there's the cells again I'm not too worried about this and the promotion is operation to manager so we're going to click on OK and now you'll see rather than having the cell references here it actually has some names so being promoted to manager I'm going to get two thousand five hundred and the only problem is is that I will be going out a bit more so I'm going to change that to 450 now I could click on OK to go back to my scenarios manager window or I can add another scenario right now I'm going to click on air I'm going to click on add and this one is I can put in new company so there's a new company that have offered me a job and let's just put that in there click on ok they're offering me 3,000 a month but I'm going to have to travel a bit more and I'll probably have a bit more in the way of going out and I could keep clicking on add and adding in more and more but I want to show you what it does now so let's just click on OK here are my scenarios I can go back into any of them click Edit make any changes ok make any of those changes to it and so on I can delete any of them at any time and when I save this workbook it's actually going to save all of these scenarios in there so I can come back to them at any time so you can see down here it actually has my original figures so I want to see what happens if I choose my promotion if I click on show what it does you can see it actually change the figures here and I can see the amount left over is 740 when we go back to original I can click on it and hit show you'll see here it's got my comment down here so if I quick show you you can see it's changed it back so the promotion is actually a lot better off which you would expect now don't have to click on show each time I can actually just double click so if I go to original I can double click promotion and new company and very obviously I can see which one is going to be the best option for me it's going to be the new company even with all the additional expenses but if I'd like to see a summary of this in one go what I can do is click on this summary button and it will create a brand new spreadsheet that shows me all of those so I'm going to click on summary the scenario summary is what it's going to create not a pivot table I'm not interested in the pivot table for this and it's telling me that it's going to show me what the amount left over is going to be and I could actually also if I wanted to say that I wouldn't see the total expenses as part of that summary the most important factor after having the right job and being happy with it is how much money you're going to have left over at the end of each month so if I click on it ok it creates a new sheet for me and you can see here it's got this summary there you go it tells me all of my expenses it tells me here all of the cells it could change now I didn't name these two but that one is my total expenses that's my total outgoings and you can see here which one is which and you can see on this it's actually showing you the current values the original the promotion and the new company so that basically gives you a good run down and in you could see here that that's probably the best option so I just chose out the wrong way around that one there is the total out Gaines that one there is the total outgoings and that one there is the amount left over I can actually change these spreadsheets are not linked together it's just created them that on there is total lift over this one here is total expenses what you will see on the side here is these outlines and you'll see it's got these little minus signs here what you can do is if this will allow these rows to be collapsed so effectively hidden I may not need to see this all I'm interested in is this I click on that minus sign there you'll see it collapse it down just to show me that so the way of sort of summarizing what you've got if I click on the plus sign it expands it out so once again if I hit the minus sign hit that one although that doesn't show anything and you can see a little summary of them so that is scenarios in Excel 2003

Channel: jargonfreehelp

Views: 308,106

Rating: 4.6983352 out of 5

Keywords: scenarios, scenario manager, what-if, excel, Microsoft excel, data analysis, analytics, excel trick, excel tip, excel data, learn excel, excel tutorial, how to excel

Id: c0tdVlPvFZ4

Length: 9min 47sec (587 seconds)

Published: Sun Jul 01 2012