- [Instructor] Now once we have our Gantt chart,
one of the amazing things we can do with it
is we can actually do scenario analysis.
So I'm going to show you a basic way
to do scenario analysis
right now on this dropdown on the complete tab.
I have it under original,
but I can actually select scenario here.
And this is going to change my data
based off of the different dates I put in here.
So you can imagine, if you will,
if you had to start a project later or remove it,
you could use this to actually change
what it would look like and that would visualize down here
the different concentrations of where those projects are.
Now, if you want on your own,
you could even take it a step further
and adjust your CPI and SPI metrics
based off of the changing scenario.
But to even get there,
you have to set up the dynamic to do it.
And I'm going to show you how in this video.
So go ahead and click on that start tab.
And I've built out pieces of it
just to make our life easier.
First thing we want to do is add in that dropdown.
So I'm going to click on data.
Having clicked on B4 over here,
I will go to our data validation dropdown here.
I'm going to click data validation.
Under allow, I'm going to select list.
And here I'm just going to make this easy.
I'll type an original and scenario.
It's possible when you do this on your own
that you would rather set it up to
be something on the spreadsheet.
So maybe you have the list of those things
and you use that as the source.
And what I will do here is
I'm actually going to remove that equals there.
That was the problem.
And so now we have original and scenario here.
So we have those dropdowns.
Note that this is going to identify between these two here,
original and scenario.
So the next thing I want to do is
I'm going to highlight the original here.
I'll hit Control C,
and I'm just going to paste this directly on there.
But actually I'll do a paste values.
So when this is selected to scenario,
we're going to want the formula in here
to refer to the scenario.
And when it refers to the original,
we'll have it refer to this one over here.
So how can we build that formula?
Well, there's several ways to do it.
I'm going to show you how I built it.
Feel free to be creative on your own.
So I'm going to say ifs here that I'll do Alt Enter
one, two, three, four.
And what we're going to do is we're going to test if B4,
make sure we lock this in, is equal to original.
We'll do a comma here, Alt Enter,
one, two, three, four.
Now we could put another ifs in here.
I'm just going to use a basic if again.
So we'll do one, two, three, four again just
so we have good indentation.
So we'll do if, and
we're going to make that D7, make sure to lock in the seven,
but keep that D free is greater than,
we're going to use these values right here.
And I showed you how to do this in the last video.
So we'll do comma here.
Again, do another D7, one, two,
make sure that seven is locked
is less than or equal to
AP8.
Make sure the A P is locked
and the eight is free like that.
And if you're wondering how I do that,
I'm hitting the F4 key on my keypad.
And then if it is between, we'll make it a one.
If not, we'll make it a zero comma.
Now we want to do something very similar for scenario,
except that we wanted to pull from this set of data.
So watch this, I'm going to copy all this
and then I'll hit Control C,
I'll do another Alt Enter, Control V.
And I'm going to change this to
scenario right here.
Make sure you spell it right.
And then this here, this A08, let's see if this works.
Okay, it moves the top one, so that's not going to work.
So I'm going to replace this one here
with this one,
and just make sure you cycle through
so that the dollar signs line up correctly.
I'm going to remove this one over here,
select this one,
and then from here I'll just do another Alt Enter
and close off that parentheses.
And let's see what issue does it have?
Okay, so I did have a comma down below here.
We'll just remove that comma, and we are good, okay.
So I'm going to take this across.
It might ruin our formatting, but that's okay.
We're going to just reinstate the forming at the end,
formatting at the end of this.
All right, so our conditional format was already applied.
Again, I don't really like the borders
as it tried to fix it, so
I'm just going to do a thick outside border here.
I think before I also had borders here,
you'll note I just kind of do it differently every time.
There's no specific way that I love
when it comes to these things.
I just like to do it differently
and just see what comes up as I work with it.
Okay, so last part here is we do need to reinstate
this visualization here.
So as you might recall,
this is just a basic sum equals sum like this.
I'm going to highlight this here,
close it off,
and then I'll drag this across.
Okay, so this looks kind of correct,
but let's test it out.
So we have it on scenario here.
So let's go figure out if we can change this.
I'm going to change this to nine over here.
Okay, so it did fix it, that's correct.
And then I'll switch this two scenarios so it switches back.
Perfect.
And we can see here, let's take a look and modify something
that would be running at this point.
So we have concrete conviviality
and we're going to change it from the scenario.
So in this case, I'm going to make this end
in 10 22 like that.
So that removed it.
Let's see here.
Let's try to do this on another one.
Now, I don't know if you notice, but it is changing these.
This one actually, I made it end before it started.
Surreal, it's kind of an existential problem.
Okay, so this is going to be based off the highest values,
but if we did mess around,
you would see this update accordingly.
I did see it update just a little bit.
Now this is showing scenario versus original.
How would I even know if I changed my scenario?
Wouldn't it be great to actually know
if what I'm doing is changing from the original?
Okay, so what I'm going to do is I'm going to select AR8
all the way through AS28.
I'm going to go to conditional formatting here.
We'll do a new rule.
So in this case, we want to know if I've changed a value.
So I'm going to click on this last one.
Use a formula to determine which cells to format.
I'm going to say equals and I will select A08 here.
But in this case, because we want it to test all of them,
we're going to hit F4 to remove those dollar signs.
So we're going to say this is
not equal
to this.
And again, we want to remove our dollar signs here.
Go to format.
I'm just going to make it a light blue
just to let us know that it happened.
So I'll hit okay on that.
So we can see these are the things we changed.
So for the most part,
you probably don't even want to see the original.
And what you can do in that case is highlight AO through AP.
You can go to data
and you'll hit the group button right here.
Make it smaller.
So we don't even care what it is,
we just know that it's changed.
Let's try changing another one just to make sure it works.
Perfect, all right.
Now you say, all right, I want to go back to the original.
So you can just highlight all of this here.
Do Control C and copy it.
Of course, what you could also do is
you could take this scenario here,
do a Control C,
paste it, you know, somewhere over here,
and then when you're ready to put it back in,
you can always put it over there,
or you can modify the formulas
to pull from multiple different scenarios.
It's really up to you.
Excel is your oyster in terms of where you can go from here.
Okay, so so far in this course,
we've brought a lot of things together,
Gantt charts, KPIs, things like that.
In the next chapter,
we're going to go over earned value measures,
and then from there we're going to bring it all together
and create a final dashboard.
Can't wait to see you in the next video of this course.