1 00:00:00,05 --> 00:00:02,04 - [Instructor] Now once we have our Gantt chart, 2 00:00:02,04 --> 00:00:04,00 one of the amazing things we can do with it 3 00:00:04,00 --> 00:00:05,09 is we can actually do scenario analysis. 4 00:00:05,09 --> 00:00:07,04 So I'm going to show you a basic way 5 00:00:07,04 --> 00:00:09,00 to do scenario analysis 6 00:00:09,00 --> 00:00:12,03 right now on this dropdown on the complete tab. 7 00:00:12,03 --> 00:00:13,03 I have it under original, 8 00:00:13,03 --> 00:00:15,00 but I can actually select scenario here. 9 00:00:15,00 --> 00:00:17,07 And this is going to change my data 10 00:00:17,07 --> 00:00:20,08 based off of the different dates I put in here. 11 00:00:20,08 --> 00:00:23,02 So you can imagine, if you will, 12 00:00:23,02 --> 00:00:25,07 if you had to start a project later or remove it, 13 00:00:25,07 --> 00:00:27,05 you could use this to actually change 14 00:00:27,05 --> 00:00:30,06 what it would look like and that would visualize down here 15 00:00:30,06 --> 00:00:33,01 the different concentrations of where those projects are. 16 00:00:33,01 --> 00:00:34,08 Now, if you want on your own, 17 00:00:34,08 --> 00:00:36,06 you could even take it a step further 18 00:00:36,06 --> 00:00:38,09 and adjust your CPI and SPI metrics 19 00:00:38,09 --> 00:00:40,08 based off of the changing scenario. 20 00:00:40,08 --> 00:00:41,07 But to even get there, 21 00:00:41,07 --> 00:00:43,07 you have to set up the dynamic to do it. 22 00:00:43,07 --> 00:00:45,09 And I'm going to show you how in this video. 23 00:00:45,09 --> 00:00:48,03 So go ahead and click on that start tab. 24 00:00:48,03 --> 00:00:50,00 And I've built out pieces of it 25 00:00:50,00 --> 00:00:52,03 just to make our life easier. 26 00:00:52,03 --> 00:00:54,09 First thing we want to do is add in that dropdown. 27 00:00:54,09 --> 00:00:56,03 So I'm going to click on data. 28 00:00:56,03 --> 00:00:58,04 Having clicked on B4 over here, 29 00:00:58,04 --> 00:01:00,07 I will go to our data validation dropdown here. 30 00:01:00,07 --> 00:01:02,01 I'm going to click data validation. 31 00:01:02,01 --> 00:01:05,07 Under allow, I'm going to select list. 32 00:01:05,07 --> 00:01:07,09 And here I'm just going to make this easy. 33 00:01:07,09 --> 00:01:10,09 I'll type an original and scenario. 34 00:01:10,09 --> 00:01:13,07 It's possible when you do this on your own 35 00:01:13,07 --> 00:01:16,04 that you would rather set it up to 36 00:01:16,04 --> 00:01:17,09 be something on the spreadsheet. 37 00:01:17,09 --> 00:01:19,03 So maybe you have the list of those things 38 00:01:19,03 --> 00:01:22,08 and you use that as the source. 39 00:01:22,08 --> 00:01:24,07 And what I will do here is 40 00:01:24,07 --> 00:01:26,08 I'm actually going to remove that equals there. 41 00:01:26,08 --> 00:01:28,00 That was the problem. 42 00:01:28,00 --> 00:01:30,09 And so now we have original and scenario here. 43 00:01:30,09 --> 00:01:32,04 So we have those dropdowns. 44 00:01:32,04 --> 00:01:35,01 Note that this is going to identify between these two here, 45 00:01:35,01 --> 00:01:37,00 original and scenario. 46 00:01:37,00 --> 00:01:39,01 So the next thing I want to do is 47 00:01:39,01 --> 00:01:40,08 I'm going to highlight the original here. 48 00:01:40,08 --> 00:01:42,04 I'll hit Control C, 49 00:01:42,04 --> 00:01:44,08 and I'm just going to paste this directly on there. 50 00:01:44,08 --> 00:01:50,01 But actually I'll do a paste values. 51 00:01:50,01 --> 00:01:52,07 So when this is selected to scenario, 52 00:01:52,07 --> 00:01:55,02 we're going to want the formula in here 53 00:01:55,02 --> 00:01:56,07 to refer to the scenario. 54 00:01:56,07 --> 00:01:58,03 And when it refers to the original, 55 00:01:58,03 --> 00:02:01,05 we'll have it refer to this one over here. 56 00:02:01,05 --> 00:02:03,08 So how can we build that formula? 57 00:02:03,08 --> 00:02:05,02 Well, there's several ways to do it. 58 00:02:05,02 --> 00:02:06,07 I'm going to show you how I built it. 59 00:02:06,07 --> 00:02:08,08 Feel free to be creative on your own. 60 00:02:08,08 --> 00:02:13,08 So I'm going to say ifs here that I'll do Alt Enter 61 00:02:13,08 --> 00:02:15,08 one, two, three, four. 62 00:02:15,08 --> 00:02:18,05 And what we're going to do is we're going to test if B4, 63 00:02:18,05 --> 00:02:22,08 make sure we lock this in, is equal to original. 64 00:02:22,08 --> 00:02:25,05 We'll do a comma here, Alt Enter, 65 00:02:25,05 --> 00:02:27,03 one, two, three, four. 66 00:02:27,03 --> 00:02:29,05 Now we could put another ifs in here. 67 00:02:29,05 --> 00:02:31,09 I'm just going to use a basic if again. 68 00:02:31,09 --> 00:02:33,08 So we'll do one, two, three, four again just 69 00:02:33,08 --> 00:02:35,04 so we have good indentation. 70 00:02:35,04 --> 00:02:43,00 So we'll do if, and 71 00:02:43,00 --> 00:02:46,03 we're going to make that D7, make sure to lock in the seven, 72 00:02:46,03 --> 00:02:49,04 but keep that D free is greater than, 73 00:02:49,04 --> 00:02:55,05 we're going to use these values right here. 74 00:02:55,05 --> 00:02:57,03 And I showed you how to do this in the last video. 75 00:02:57,03 --> 00:02:58,09 So we'll do comma here. 76 00:02:58,09 --> 00:03:02,06 Again, do another D7, one, two, 77 00:03:02,06 --> 00:03:04,06 make sure that seven is locked 78 00:03:04,06 --> 00:03:08,04 is less than or equal to 79 00:03:08,04 --> 00:03:09,08 AP8. 80 00:03:09,08 --> 00:03:12,03 Make sure the A P is locked 81 00:03:12,03 --> 00:03:14,02 and the eight is free like that. 82 00:03:14,02 --> 00:03:15,03 And if you're wondering how I do that, 83 00:03:15,03 --> 00:03:17,04 I'm hitting the F4 key on my keypad. 84 00:03:17,04 --> 00:03:19,07 And then if it is between, we'll make it a one. 85 00:03:19,07 --> 00:03:22,02 If not, we'll make it a zero comma. 86 00:03:22,02 --> 00:03:25,09 Now we want to do something very similar for scenario, 87 00:03:25,09 --> 00:03:28,02 except that we wanted to pull from this set of data. 88 00:03:28,02 --> 00:03:30,06 So watch this, I'm going to copy all this 89 00:03:30,06 --> 00:03:32,01 and then I'll hit Control C, 90 00:03:32,01 --> 00:03:35,02 I'll do another Alt Enter, Control V. 91 00:03:35,02 --> 00:03:39,00 And I'm going to change this to 92 00:03:39,00 --> 00:03:42,04 scenario right here. 93 00:03:42,04 --> 00:03:44,01 Make sure you spell it right. 94 00:03:44,01 --> 00:03:47,05 And then this here, this A08, let's see if this works. 95 00:03:47,05 --> 00:03:50,00 Okay, it moves the top one, so that's not going to work. 96 00:03:50,00 --> 00:03:53,02 So I'm going to replace this one here 97 00:03:53,02 --> 00:03:54,00 with this one, 98 00:03:54,00 --> 00:03:55,08 and just make sure you cycle through 99 00:03:55,08 --> 00:03:58,09 so that the dollar signs line up correctly. 100 00:03:58,09 --> 00:04:02,00 I'm going to remove this one over here, 101 00:04:02,00 --> 00:04:04,09 select this one, 102 00:04:04,09 --> 00:04:08,08 and then from here I'll just do another Alt Enter 103 00:04:08,08 --> 00:04:10,09 and close off that parentheses. 104 00:04:10,09 --> 00:04:12,06 And let's see what issue does it have? 105 00:04:12,06 --> 00:04:15,02 Okay, so I did have a comma down below here. 106 00:04:15,02 --> 00:04:17,08 We'll just remove that comma, and we are good, okay. 107 00:04:17,08 --> 00:04:18,09 So I'm going to take this across. 108 00:04:18,09 --> 00:04:21,01 It might ruin our formatting, but that's okay. 109 00:04:21,01 --> 00:04:23,02 We're going to just reinstate the forming at the end, 110 00:04:23,02 --> 00:04:24,07 formatting at the end of this. 111 00:04:24,07 --> 00:04:27,00 All right, so our conditional format was already applied. 112 00:04:27,00 --> 00:04:28,06 Again, I don't really like the borders 113 00:04:28,06 --> 00:04:30,05 as it tried to fix it, so 114 00:04:30,05 --> 00:04:32,07 I'm just going to do a thick outside border here. 115 00:04:32,07 --> 00:04:34,07 I think before I also had borders here, 116 00:04:34,07 --> 00:04:36,09 you'll note I just kind of do it differently every time. 117 00:04:36,09 --> 00:04:40,04 There's no specific way that I love 118 00:04:40,04 --> 00:04:42,01 when it comes to these things. 119 00:04:42,01 --> 00:04:43,08 I just like to do it differently 120 00:04:43,08 --> 00:04:46,03 and just see what comes up as I work with it. 121 00:04:46,03 --> 00:04:49,08 Okay, so last part here is we do need to reinstate 122 00:04:49,08 --> 00:04:51,06 this visualization here. 123 00:04:51,06 --> 00:04:53,00 So as you might recall, 124 00:04:53,00 --> 00:04:56,09 this is just a basic sum equals sum like this. 125 00:04:56,09 --> 00:04:59,08 I'm going to highlight this here, 126 00:04:59,08 --> 00:05:01,09 close it off, 127 00:05:01,09 --> 00:05:04,01 and then I'll drag this across. 128 00:05:04,01 --> 00:05:07,07 Okay, so this looks kind of correct, 129 00:05:07,07 --> 00:05:09,00 but let's test it out. 130 00:05:09,00 --> 00:05:10,03 So we have it on scenario here. 131 00:05:10,03 --> 00:05:12,07 So let's go figure out if we can change this. 132 00:05:12,07 --> 00:05:14,08 I'm going to change this to nine over here. 133 00:05:14,08 --> 00:05:18,02 Okay, so it did fix it, that's correct. 134 00:05:18,02 --> 00:05:20,05 And then I'll switch this two scenarios so it switches back. 135 00:05:20,05 --> 00:05:21,05 Perfect. 136 00:05:21,05 --> 00:05:24,09 And we can see here, let's take a look and modify something 137 00:05:24,09 --> 00:05:27,08 that would be running at this point. 138 00:05:27,08 --> 00:05:30,06 So we have concrete conviviality 139 00:05:30,06 --> 00:05:32,07 and we're going to change it from the scenario. 140 00:05:32,07 --> 00:05:35,04 So in this case, I'm going to make this end 141 00:05:35,04 --> 00:05:38,03 in 10 22 like that. 142 00:05:38,03 --> 00:05:39,07 So that removed it. 143 00:05:39,07 --> 00:05:40,05 Let's see here. 144 00:05:40,05 --> 00:05:44,01 Let's try to do this on another one. 145 00:05:44,01 --> 00:05:46,09 Now, I don't know if you notice, but it is changing these. 146 00:05:46,09 --> 00:05:49,08 This one actually, I made it end before it started. 147 00:05:49,08 --> 00:05:53,08 Surreal, it's kind of an existential problem. 148 00:05:53,08 --> 00:05:56,05 Okay, so this is going to be based off the highest values, 149 00:05:56,05 --> 00:05:58,00 but if we did mess around, 150 00:05:58,00 --> 00:05:59,04 you would see this update accordingly. 151 00:05:59,04 --> 00:06:01,02 I did see it update just a little bit. 152 00:06:01,02 --> 00:06:04,00 Now this is showing scenario versus original. 153 00:06:04,00 --> 00:06:06,06 How would I even know if I changed my scenario? 154 00:06:06,06 --> 00:06:08,08 Wouldn't it be great to actually know 155 00:06:08,08 --> 00:06:12,03 if what I'm doing is changing from the original? 156 00:06:12,03 --> 00:06:15,02 Okay, so what I'm going to do is I'm going to select AR8 157 00:06:15,02 --> 00:06:17,07 all the way through AS28. 158 00:06:17,07 --> 00:06:19,02 I'm going to go to conditional formatting here. 159 00:06:19,02 --> 00:06:21,02 We'll do a new rule. 160 00:06:21,02 --> 00:06:24,08 So in this case, we want to know if I've changed a value. 161 00:06:24,08 --> 00:06:26,02 So I'm going to click on this last one. 162 00:06:26,02 --> 00:06:28,06 Use a formula to determine which cells to format. 163 00:06:28,06 --> 00:06:32,07 I'm going to say equals and I will select A08 here. 164 00:06:32,07 --> 00:06:35,08 But in this case, because we want it to test all of them, 165 00:06:35,08 --> 00:06:38,07 we're going to hit F4 to remove those dollar signs. 166 00:06:38,07 --> 00:06:41,09 So we're going to say this is 167 00:06:41,09 --> 00:06:43,08 not equal 168 00:06:43,08 --> 00:06:45,01 to this. 169 00:06:45,01 --> 00:06:50,02 And again, we want to remove our dollar signs here. 170 00:06:50,02 --> 00:06:51,03 Go to format. 171 00:06:51,03 --> 00:06:52,05 I'm just going to make it a light blue 172 00:06:52,05 --> 00:06:54,04 just to let us know that it happened. 173 00:06:54,04 --> 00:06:55,05 So I'll hit okay on that. 174 00:06:55,05 --> 00:06:57,09 So we can see these are the things we changed. 175 00:06:57,09 --> 00:06:58,08 So for the most part, 176 00:06:58,08 --> 00:07:01,01 you probably don't even want to see the original. 177 00:07:01,01 --> 00:07:04,05 And what you can do in that case is highlight AO through AP. 178 00:07:04,05 --> 00:07:05,08 You can go to data 179 00:07:05,08 --> 00:07:08,07 and you'll hit the group button right here. 180 00:07:08,07 --> 00:07:09,05 Make it smaller. 181 00:07:09,05 --> 00:07:10,09 So we don't even care what it is, 182 00:07:10,09 --> 00:07:12,05 we just know that it's changed. 183 00:07:12,05 --> 00:07:15,04 Let's try changing another one just to make sure it works. 184 00:07:15,04 --> 00:07:16,05 Perfect, all right. 185 00:07:16,05 --> 00:07:18,05 Now you say, all right, I want to go back to the original. 186 00:07:18,05 --> 00:07:20,02 So you can just highlight all of this here. 187 00:07:20,02 --> 00:07:22,09 Do Control C and copy it. 188 00:07:22,09 --> 00:07:24,02 Of course, what you could also do is 189 00:07:24,02 --> 00:07:26,02 you could take this scenario here, 190 00:07:26,02 --> 00:07:27,05 do a Control C, 191 00:07:27,05 --> 00:07:29,07 paste it, you know, somewhere over here, 192 00:07:29,07 --> 00:07:31,04 and then when you're ready to put it back in, 193 00:07:31,04 --> 00:07:32,06 you can always put it over there, 194 00:07:32,06 --> 00:07:34,00 or you can modify the formulas 195 00:07:34,00 --> 00:07:36,05 to pull from multiple different scenarios. 196 00:07:36,05 --> 00:07:37,08 It's really up to you. 197 00:07:37,08 --> 00:07:41,01 Excel is your oyster in terms of where you can go from here. 198 00:07:41,01 --> 00:07:42,08 Okay, so so far in this course, 199 00:07:42,08 --> 00:07:44,08 we've brought a lot of things together, 200 00:07:44,08 --> 00:07:47,07 Gantt charts, KPIs, things like that. 201 00:07:47,07 --> 00:07:48,05 In the next chapter, 202 00:07:48,05 --> 00:07:51,02 we're going to go over earned value measures, 203 00:07:51,02 --> 00:07:53,05 and then from there we're going to bring it all together 204 00:07:53,05 --> 00:07:55,09 and create a final dashboard. 205 00:07:55,09 --> 00:08:00,00 Can't wait to see you in the next video of this course.