What-If Analysis

Quick reference

• 00:04 In this video we're gonna look at some ways that we can actually do a little
• 00:09 bit of what if analysis around a scenario here, so
• 00:12 what you can see here is my little table has some values set up for
• 00:16 a financial contribution for a number of years.
• 00:20 So $1500 here, I'm going to try and put this away for ten years.
• 00:23 And I'm hoping to earn 6.5 percent interest.
• 00:26 If I do, I should earn about $20,000 by the time I
• 00:28 actually go to cash that in after the end of year ten.
• 00:32 Now, I want to play around with this a little bit and see what
• 00:35 would happen if I had some goals that I wanted to reach about future earnings, or
• 00:39 if I had some goal with contributions or target interest rates.
• 00:42 So the first thing that I'm gonna do before I start messing around
• 00:45 with the numbers here and trying to guess by putting values in,
• 00:48 I'm actually gonna use a little tool here to lock these values in so
• 00:52 I can always roll back to them very easily.
• 00:54 So to do that I'm gonna go to the data tab and
• 00:57 I'm gonna go to what if analysis and I'm gonna choose the scenario manager.
• 01:02 You'll notice there's no scenarios to find right now.
• 01:05 So what I'm gonna do is I'm going to click the plus button to add one, and
• 01:09 I'm going to call this scenario, Base.
• 01:12 So this is my base case scenario.
• 01:14 And it says what cells would you like to change?
• 01:16 I'm gonna say, what?
• 01:17 I'm gonna click this little raffette box here,
• 01:19 and I'd like to change all three of these cells.
• 01:22 And we click that again, and we say Okay.
• 01:26 It comes back, and it says all right, so
• 01:28 we're gonna enter values by changing each of these cells, $1500.
• 01:32 That's Okay. Let's see what happens here.
• 01:35 We'll say Close.
• 01:37 And at this point, we now have a scenario set up.
• 01:40 I could go and change this to $2,000.
• 01:43 I could then look at the amount of money it's gonna bring in.
• 01:47 If I decide I don't like that I can go back to the scenario manager, select
• 01:51 my scenario and say show and it will actually change the value back for me.
• 01:56 Looks like close.
• 01:57 So that should actually work out nicely.
• 01:59 Now, why did I want to do that?
• 02:01 Well, because I have some goals here.
• 02:03 What I'd actually like is to figure out how
• 02:06 I can actually earn $27,500 at the end of ten years instead of this 20,000.
• 02:10 Now, I could go and start punching in a bunch of different numbers here trying to
• 02:15 get a little bit closer by honing in on that eventual value, or
• 02:18 I could shortcut that process by actually using what we call Goal Seek.
• 02:21 And what Goal Seek does, it's in the same place as What If Analysis.
• 02:25 But when I go into Goal Seek,
• 02:27 it actually allows me to change a cell to a specific value by changing another cell.
• 02:33 So in this case, what I'm gonna say is I'd like to select B7,
• 02:35 and I'd like to set that value to $27,500.
• 02:41 And this says which cell would you like to change in order to make that happen.
• 02:44 I say well, let's change the contribution.
• 02:46 Let's try it and see what happens here.
• 02:49 So if all things were equal in the number of years and
• 02:51 the interest rate, you can see that Excel works it through and says hey,
• 02:54 in order to come up with this target value that you asked for,
• 02:59 I would actually have to invest $2,037 every year instead of 1,500.
• 03:03 So that's kinda interesting information to know.
• 03:06 Say well all right that's fine, but what if I wanted to keep it $1,500?
• 03:11 Maybe I would wanna go back actually
• 03:13 see if I can get a more aggressive interest rate.
• 03:15 So this is where the scenario matter comes in really useful cuz now I can just go and
• 03:19 say, well let's roll the scenario manage back, we'll show it back at 1500.
• 03:24 And now I'll try and do a goal seek again, on my estimated future value here.
• 03:30 I'm gonna set it once again, to 27,500.
• 03:34 And I'll change cell B5 in order to do that.
• 03:39 Say Okay, there we go.
• 03:41 I need to get 13% pretty much,
• 03:44 which might be a little bit aggressive in today's market.
• 03:47 So what if I said, well, Okay, what if I change the value here to 12.
• 03:52 And said OK, let's re goal seek this again,
• 03:59 once again we'll come back to my target of 27,500 by changing the interest rate.
• 04:03 And you can see that that interest rate might be a little bit more achievable.
• 04:10 So if I extend the amount of years that I invest,
• 04:13 that might make it work a little bit better.
• 04:15 But again, if I decide I don't like any of these things,
• 04:17 I can always roll back to my original scenario by going to the Scenario Manager.
• 04:23 And clicking show and it'll bring it back for me to work with.
• 04:26 So the scenario manager is a great tool to be able to set up a base case that you can
• 04:30 always roll back to if you're messing around with your inputs.
• 04:33 Goal seek allows you to quickly drill in to actually find what precedent
• 04:37 numbers need to be changed in order to come up with exactly what you need.

Lesson notes are only available for subscribers.