Hi, this is Wayne again with a topic “Excel WhatIf Analysis with Scenario Manager and Goal Seek”.
In this tutorial, I’m gon na show you two of the what-if analysis features in Microsoft, Excel first, we’ll look at scenario manager and then we’ll look at goal seek and both of these, what they help us to do is to envision what could happen if some things Fall into place and the spreadsheet that we’re gon na use for this is this games. Sales spreadsheet and it’s a spreadsheet for a hypothetical board and card game store, and you can see. There’S some board games and card games here at the left, with their price here and the twelve months of the year. Now you can see over here to the right.
We have the yearly total sales for each of these board games and these are dollars. So if you look at the formula for these, if I double click on that cell, it says that it’s the sum of these numbers multiplied by the cost of the item. Okay and then we have a grand total amount of money earned this year by selling these games. But let’s say I want to dream a little bit and look forward to next year and I want to ask myself what could I do to get this number here of grand total amount of money taken in by this small business? What if I want to get that to a hundred thousand or a million or even more well, I can use the what-if analysis, features that are found here on the data tab. The data ribbon over here on the right where it says, forecast.
So I’m gon na click where it says what-if analysis and it pops up with three options: scenario, manager, goal seek and data table we’re gon na look at the first two in this tutorial, so I’ll click on scenario manager and it pops open with a kind of Wizard here this will help me to make my scenarios and to compare them, and I get to create those scenarios now this can be a little tricky, the first time you do it so hopefully, this video will help with that. What you’ll do is you’ll click here to add a new scenario, and maybe I want to first start with just acknowledging where we stand right now, so I’ll type in something like current year. That’S the name of this scenario and then next it wants me to establish what is it that we’re going to be looking at in these scenarios? What might hypothetically, and so, let’s say with this small business – I want to make a push for Christmastime. I want to try to sell more than I ever have at Christmastime of these games.
Well here, where it says changing sells. I can just click this button here and then click and drag to select the December numbers for each of the games, and then I can tap enter on the keyboard and it puts in that range next. I can put in some comments, so I could say actual current year.
Numbers click, OK, and here it wants me to put in some hypothetical numbers that we might be able to achieve, but remember in this case this is just the actual current numbers. This is what I’m gon na be comparing against, and so I don’t really want to change these I’ll. Just click. Ok and now I’ve got the current year actual information stored here. In this first scenario, now I’m gon na add a second scenario, and this is gon na, be the optimistic scenario and here for comment. I’M gon na put best-case scenario for December sales and then I’ll click, OK and now for this one, I’m gon na say all right, best-case scenario: maybe we sell a hundred of each of these items and then I click.
Ok. Now I’ve got the optimistic scenario. Finally, I’m gon na add the pessimistic scenario: it’s the same cells in 3 through n 10, and this is the worst case scenario.
I click. Ok and let’s say the worst case scenario is 3 sales each. I click OK and so now, at this point I have 3 scenarios. Of course I could add more, but now all I have to do is click on one of these scenarios and click show and look the numbers all update. Now I realized I didn’t update. All of the numbers like I should have so I need to go in and fix that so here in pessimistic scenario, I’m gon na edit and I’m going to click, OK and notice. The mistake I made I just typed in the number 3 down to here record number 5, but notice that there is a scrollbar and I need to scroll down to change the other hypothetical numbers. Click.
Ok, give me a minute to do so with the optimistic scenario and then I’ll resume the video okay. Now that I’ve fixed that mistake, let’s look at our three scenarios. We have the current scenario I just clicked on it and then clicked show you can also double click to get the same result so the current year $ 51,000, taken in by the small business in the optimistic scenario of a strong December. If I double click there, it updates the numbers and that takes us to $ 64,000.
What about the pessimistic scenario? I double click there and we could drop down to 46,000 dollars now. Hopefully, you can see the reason why I like to have a scenario that reflects the actual data: the real data. It’S because that way, I can easily compare and contrast. I can go back and forth between the different options.
Now I’m gon na click close and notice that I’m purposefully leaving the setting to be optimistic scenario. I click close and notice that now my spreadsheet shows that optimistic scenario. So it is important that when you’re done with the scenario manager that you go back to the real data, click close and then you’ll be back where you need to be next. Let’S look at the what-if analysis goal seek, and this is similar, it’s kind of related. Let’S say I want to set a goal of not 51,000 in sales, but rather 200,000.
So I’m gon na click here on sell, Oh 11, that’s the place for the goal and then what is the goal? Let’S say 200,000, so I type in 200,000, and how am I gon na get to that goal? What number is going to change? That’S gon na get us to this point now. This is gon na be a little bit outlandish, but let’s say I want to get us to 200,000 in sales just by selling the game ticket to ride. So I could click this cell for the December sales of ticket to ride click, ok, and it calculates in order for us to get to the goal of 200,000 in sales just by improving what we sell for ticket to ride in December.
It’S gon na take us selling three thousand three hundred and forty seven or eight ticket to ride games in the month of December. So, like I said, that’s a little bit outlandish of an example, but hopefully you see the purpose here you put in the goal that you want to, and then you say what is it that’s gon na change to get you to that goal. Maybe it’s the number of sales of a particular game, and then it tells you how many you would need to sell in this case to get to that number now.
I can do this again, I’m gon na undo the goal seek that we’ve already done just by using the undo button here or ctrl Z on the keyboard and then this time when I go to what-if analysis goal seek. Yes, I want it to be this cell. Yes, I want the number to be 200,000, but this time, what if we just changed the price of a particular game like skip-bo skip-bo, is a pretty inexpensive card game. But what if we raise the price a little bit and the sales stayed the same kind of doubtful, but let’s try it. If I click OK, it gets us to 200,000 how by us charging 677 dollars and 13 cents for each pack of skip-bo cards.
It’S kind of fun to run some of these scenarios and these goals. So I find these what-if analysis scenario, manager and goal seek tools to be really helpful, really fun to kind of imagine what could be and what would happen if we reached some goals and some benchmarks. I hope that you found this tutorial to be helpful. If you did, please click the like button below and consider connecting with me on my social media websites like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students, and when you do that, please click the Bell next to the subscribe button, if you do you’ll be notified whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my youtube channel, please become a patron of mine through my patreon account and you’ll find Links to that in the description below .