1 00:00:00,05 --> 00:00:02,09 - [Instructor] So what is a key performance indicator, 2 00:00:02,09 --> 00:00:05,00 also called a KPI? 3 00:00:05,00 --> 00:00:08,02 A KPI is anything that helps us measure 4 00:00:08,02 --> 00:00:09,06 how well we reach our goals. 5 00:00:09,06 --> 00:00:11,04 And you can see for project management, 6 00:00:11,04 --> 00:00:13,03 this is really important. 7 00:00:13,03 --> 00:00:16,03 Now the thing is a KPI could be anything, 8 00:00:16,03 --> 00:00:19,02 but thankfully, there are a few industry standard KPIs 9 00:00:19,02 --> 00:00:21,01 that we can use for project management, 10 00:00:21,01 --> 00:00:22,05 which we'll be using here. 11 00:00:22,05 --> 00:00:23,07 So let's talk about the ones 12 00:00:23,07 --> 00:00:26,03 that I would like to use going forward. 13 00:00:26,03 --> 00:00:28,08 So the first two are going to require a little bit of math. 14 00:00:28,08 --> 00:00:30,05 Don't worry, I'm going to walk you through it. 15 00:00:30,05 --> 00:00:32,01 Those are cost performance index 16 00:00:32,01 --> 00:00:33,09 and schedule performance index. 17 00:00:33,09 --> 00:00:36,01 We're going to show you how to do that in a second. 18 00:00:36,01 --> 00:00:38,05 But the last three - safety incidences, 19 00:00:38,05 --> 00:00:39,07 quality score, 20 00:00:39,07 --> 00:00:41,05 and stakeholder satisfaction, 21 00:00:41,05 --> 00:00:42,04 these are just numbers 22 00:00:42,04 --> 00:00:44,01 and they would come out in the real world 23 00:00:44,01 --> 00:00:46,03 from surveys and research. 24 00:00:46,03 --> 00:00:48,07 So let's talk about two important calculations 25 00:00:48,07 --> 00:00:50,07 before we move into the bigger calculations 26 00:00:50,07 --> 00:00:52,03 because we're going to use these both, 27 00:00:52,03 --> 00:00:54,03 and they're both very important to project management. 28 00:00:54,03 --> 00:00:56,00 If you ever sit for the PMP, 29 00:00:56,00 --> 00:00:57,07 I believe that they're on that test. 30 00:00:57,07 --> 00:00:58,08 So earned value, 31 00:00:58,08 --> 00:01:01,06 that's the percentage complete that we have so far 32 00:01:01,06 --> 00:01:03,05 on a project multiplied by the budget. 33 00:01:03,05 --> 00:01:04,08 So that basically tells us 34 00:01:04,08 --> 00:01:06,09 how much of the budget have we already earned. 35 00:01:06,09 --> 00:01:08,04 Then planned value, 36 00:01:08,04 --> 00:01:11,04 well, that is how much we plan to complete, 37 00:01:11,04 --> 00:01:13,02 which may be different, right, 38 00:01:13,02 --> 00:01:14,07 than the amount we've earned, 39 00:01:14,07 --> 00:01:16,01 than is actually complete. 40 00:01:16,01 --> 00:01:17,09 And both of those are going to help us 41 00:01:17,09 --> 00:01:20,01 with the indices we're about to calculate. 42 00:01:20,01 --> 00:01:21,08 Now if you're looking at this 43 00:01:21,08 --> 00:01:24,04 and you're thinking this is about to be a lot of math, 44 00:01:24,04 --> 00:01:26,08 and wouldn't it be better if you were just sitting in Excel 45 00:01:26,08 --> 00:01:27,06 and typing it out? 46 00:01:27,06 --> 00:01:30,02 I'm with you. Sometimes it's hard for me to fall along too, 47 00:01:30,02 --> 00:01:31,05 but I think that this part is important. 48 00:01:31,05 --> 00:01:34,05 So just fall along and it'll all come together. 49 00:01:34,05 --> 00:01:37,00 All right, so the cost performance index 50 00:01:37,00 --> 00:01:38,01 is that earned value, 51 00:01:38,01 --> 00:01:41,00 that's the percentage complete multiplied by the budget 52 00:01:41,00 --> 00:01:42,09 divided by the actual cost. 53 00:01:42,09 --> 00:01:43,09 I wrote expenses here 54 00:01:43,09 --> 00:01:46,06 because that's the name of our actual cost column. 55 00:01:46,06 --> 00:01:47,08 But just so you know, 56 00:01:47,08 --> 00:01:50,02 it would be divided by the actual cost. 57 00:01:50,02 --> 00:01:52,05 Now, values that are greater than 1 58 00:01:52,05 --> 00:01:54,07 means that the project is under budget. 59 00:01:54,07 --> 00:01:56,03 Ding, ding, ding, we like that. 60 00:01:56,03 --> 00:01:58,05 Over 1 means that they're over budget. 61 00:01:58,05 --> 00:02:00,06 So a very quick and easy way to understand 62 00:02:00,06 --> 00:02:02,07 where you are with regard to the budget. 63 00:02:02,07 --> 00:02:04,09 Now, schedule performance index, very similar. 64 00:02:04,09 --> 00:02:08,07 Okay, so this is calculated by taking the earned value, 65 00:02:08,07 --> 00:02:09,06 we just talked about that 66 00:02:09,06 --> 00:02:11,06 and dividing it by the planned value. 67 00:02:11,06 --> 00:02:13,04 Are you earning 68 00:02:13,04 --> 00:02:15,04 what you thought you'd be earning by this point? 69 00:02:15,04 --> 00:02:16,08 Or are you behind schedule? 70 00:02:16,08 --> 00:02:18,09 Or are you ahead of schedule? 71 00:02:18,09 --> 00:02:21,08 So if you're greater than 1, you're ahead of schedule. 72 00:02:21,08 --> 00:02:24,02 If you're less than 1, you are on a delay. 73 00:02:24,02 --> 00:02:26,00 All right, so safety incidents. 74 00:02:26,00 --> 00:02:28,08 These are just account of the safety incidents 75 00:02:28,08 --> 00:02:30,09 that have occurred on-site. 76 00:02:30,09 --> 00:02:32,03 Ideally, you would just be collecting 77 00:02:32,03 --> 00:02:33,06 that information anyway. 78 00:02:33,06 --> 00:02:35,04 The quality score, again, hypothetical score 79 00:02:35,04 --> 00:02:39,02 based off of a review of the workmanship of your product. 80 00:02:39,02 --> 00:02:41,09 And then finally, stakeholder satisfaction. 81 00:02:41,09 --> 00:02:44,02 Are your clients and stakeholders 82 00:02:44,02 --> 00:02:46,04 happy with the project progress? 83 00:02:46,04 --> 00:02:48,01 Now as I switch to Excel, 84 00:02:48,01 --> 00:02:50,01 what I'm going to say here is 85 00:02:50,01 --> 00:02:53,02 you can notice here that CPI and SPI are already here, 86 00:02:53,02 --> 00:02:55,01 but we're going to actually have to add two more metrics, 87 00:02:55,01 --> 00:02:56,05 and you're going to see that in a second. 88 00:02:56,05 --> 00:02:58,02 So first thing I'm going to do is put my cursor 89 00:02:58,02 --> 00:03:00,03 anywhere in this continuous region. 90 00:03:00,03 --> 00:03:02,02 My table has headers. 91 00:03:02,02 --> 00:03:03,06 I'm going to hit OK on that. 92 00:03:03,06 --> 00:03:06,06 I'm going to remove the banded rows for personal reasons, 93 00:03:06,06 --> 00:03:08,06 and then I'm going to right-click on column J. 94 00:03:08,06 --> 00:03:10,06 I'll hit Insert to put in a new column. 95 00:03:10,06 --> 00:03:13,05 Here we're going to type in EV for earned value. 96 00:03:13,05 --> 00:03:15,06 Right-click on K, hit Insert, 97 00:03:15,06 --> 00:03:18,02 we're going to do PV for planned value. 98 00:03:18,02 --> 00:03:20,03 This is not present value as it would be in finance, 99 00:03:20,03 --> 00:03:21,02 but rather planned value. 100 00:03:21,02 --> 00:03:22,08 So keep that in mind. 101 00:03:22,08 --> 00:03:24,04 Now, the earned value calculation 102 00:03:24,04 --> 00:03:26,04 is basically just going to be 103 00:03:26,04 --> 00:03:30,09 the progress multiplied by the budget. 104 00:03:30,09 --> 00:03:33,02 So here's what we've allotted for the year, 105 00:03:33,02 --> 00:03:34,06 here's how much we've gotten done. 106 00:03:34,06 --> 00:03:36,05 That is our earned value. 107 00:03:36,05 --> 00:03:38,00 And if you feel so inclined, 108 00:03:38,00 --> 00:03:39,07 highlight that whole thing there 109 00:03:39,07 --> 00:03:43,04 and put a nice currency on it. 110 00:03:43,04 --> 00:03:46,00 I'm actually going to just reduce that down 111 00:03:46,00 --> 00:03:48,00 so that we don't have decimal places. 112 00:03:48,00 --> 00:03:50,03 Okay, so let's talk about planned value. 113 00:03:50,03 --> 00:03:53,00 This is going to be equal to how much we plan 114 00:03:53,00 --> 00:03:54,02 to have spent of that budget. 115 00:03:54,02 --> 00:03:56,02 So let's think about how to calculate this. 116 00:03:56,02 --> 00:03:57,01 There's a few different ways, 117 00:03:57,01 --> 00:03:59,06 but I'm going to give you the one that I'm going to use. 118 00:03:59,06 --> 00:04:01,05 So think about this, right? 119 00:04:01,05 --> 00:04:03,06 We have an end date and a start date, 120 00:04:03,06 --> 00:04:05,09 that's going to give us our elapsed time in total. 121 00:04:05,09 --> 00:04:09,07 So I'm just going to build this formula from the inside out. 122 00:04:09,07 --> 00:04:14,00 So start date less end date. 123 00:04:14,00 --> 00:04:16,08 Or actually, let me do this differently. 124 00:04:16,08 --> 00:04:22,06 Let's do end date less start date. 125 00:04:22,06 --> 00:04:25,02 So that's the total duration of the project. 126 00:04:25,02 --> 00:04:27,00 And then we want to figure out, 127 00:04:27,00 --> 00:04:29,03 well, based off of the time 128 00:04:29,03 --> 00:04:31,00 we expect this project to be complete, 129 00:04:31,00 --> 00:04:32,02 where are we? 130 00:04:32,02 --> 00:04:34,05 So I'm going to take a lapse days here 131 00:04:34,05 --> 00:04:36,06 and I will divide it by this quantity. 132 00:04:36,06 --> 00:04:39,05 So that gets us our percentage complete in terms of time. 133 00:04:39,05 --> 00:04:42,00 And then we could take this whole thing here 134 00:04:42,00 --> 00:04:46,06 and we multiply it by the budget. 135 00:04:46,06 --> 00:04:49,05 And I'm just going to use the format painter here 136 00:04:49,05 --> 00:04:51,01 and format all of this. 137 00:04:51,01 --> 00:04:54,03 Okay, so this is our planned value. 138 00:04:54,03 --> 00:04:57,00 So to find our cost performance index, 139 00:04:57,00 --> 00:04:58,01 we're going to do equals, 140 00:04:58,01 --> 00:05:00,06 and in my case, I'm just going to do an IFERROR here, 141 00:05:00,06 --> 00:05:02,06 because in case it's a divide by zero, 142 00:05:02,06 --> 00:05:03,09 I just want to handle it. 143 00:05:03,09 --> 00:05:05,09 So I'm going to do IFERROR, 144 00:05:05,09 --> 00:05:09,08 and we're going to do our earned value 145 00:05:09,08 --> 00:05:12,01 divided by our actual cost, 146 00:05:12,01 --> 00:05:18,09 which is going to be the expenses right there. 147 00:05:18,09 --> 00:05:22,05 And don't forget to put a zero at the end of this. 148 00:05:22,05 --> 00:05:27,06 And then let's do our schedule performance index. 149 00:05:27,06 --> 00:05:29,09 Okay, so we're going to do this very similarly. 150 00:05:29,09 --> 00:05:31,05 I'll do an IFERROR here, 151 00:05:31,05 --> 00:05:35,04 and then I'm going to take the earned value 152 00:05:35,04 --> 00:05:40,06 and divide it by the planned value. 153 00:05:40,06 --> 00:05:43,07 And don't forget to add a zero here. 154 00:05:43,07 --> 00:05:46,06 Okay, so we have our indices created here, 155 00:05:46,06 --> 00:05:48,05 and then if we just look over to the right, 156 00:05:48,05 --> 00:05:51,05 these KPIs are already built in. 157 00:05:51,05 --> 00:05:52,08 So now what we're going to do is we're going to actually 158 00:05:52,08 --> 00:05:55,02 build a dashboard that's similar to this 159 00:05:55,02 --> 00:05:58,08 so that we can see how well we're doing. 160 00:05:58,08 --> 00:06:00,07 Isn't it great when we can do that? 161 00:06:00,07 --> 00:06:02,07 So I'm just going to create a new sheet 162 00:06:02,07 --> 00:06:06,09 and we can call this Dashboard like that. 163 00:06:06,09 --> 00:06:09,08 And in this case, I'm just going to label 164 00:06:09,08 --> 00:06:15,04 some of these cost performance index, 165 00:06:15,04 --> 00:06:21,07 call this schedule performance index over here. 166 00:06:21,07 --> 00:06:28,03 And you can lay this out however you want. 167 00:06:28,03 --> 00:06:30,08 We're going to do safety incidents right there. 168 00:06:30,08 --> 00:06:33,06 And let's see, what were my other two? 169 00:06:33,06 --> 00:06:36,01 Quality score 170 00:06:36,01 --> 00:06:39,05 and stakeholder satisfaction. 171 00:06:39,05 --> 00:06:40,09 I'm going to highlight both of these. 172 00:06:40,09 --> 00:06:43,03 Let's see if this works. 173 00:06:43,03 --> 00:06:44,05 So I highlighted both. 174 00:06:44,05 --> 00:06:45,05 Hit Ctrl + C. 175 00:06:45,05 --> 00:06:47,03 I'm just going to do a paste values here. 176 00:06:47,03 --> 00:06:49,00 So I'll drop that there. 177 00:06:49,00 --> 00:06:51,02 And then I'm going to drop this one over here. 178 00:06:51,02 --> 00:06:52,09 Now, there's a few ways to do this. 179 00:06:52,09 --> 00:06:54,09 Some people really disagree with me 180 00:06:54,09 --> 00:06:58,05 on the use of merge and center. 181 00:06:58,05 --> 00:06:59,06 I don't mind. 182 00:06:59,06 --> 00:07:00,05 If it bothers you 183 00:07:00,05 --> 00:07:02,07 obviously you're welcome to write in the comments. 184 00:07:02,07 --> 00:07:04,03 I don't hate merge and center, 185 00:07:04,03 --> 00:07:07,04 so I'm going to highlight these cells right here, 186 00:07:07,04 --> 00:07:09,09 B3 through B34. 187 00:07:09,09 --> 00:07:12,00 I'm going to hit merge and center on that. 188 00:07:12,00 --> 00:07:17,05 Then I'm just going to do that little paintbrush copy 189 00:07:17,05 --> 00:07:23,04 and go through the rest of these. 190 00:07:23,04 --> 00:07:24,07 I'm going to highlight all of these. 191 00:07:24,07 --> 00:07:25,09 I'm holding Ctrl right now, 192 00:07:25,09 --> 00:07:28,09 and I'm just going to center them so that it doesn't bother me. 193 00:07:28,09 --> 00:07:29,08 And let's see if we can just 194 00:07:29,08 --> 00:07:38,05 kind of make 'em all just a little bit bigger. 195 00:07:38,05 --> 00:07:40,07 Okay, so to get to this reporting, 196 00:07:40,07 --> 00:07:42,01 we're going to go back here. 197 00:07:42,01 --> 00:07:44,04 We're going to go to the bottom of our table here. 198 00:07:44,04 --> 00:07:46,04 So just click anywhere in the bottom of the table, 199 00:07:46,04 --> 00:07:47,05 go to table design. 200 00:07:47,05 --> 00:07:49,05 Let's add a total row. 201 00:07:49,05 --> 00:07:51,04 So this is going to allow us to actually 202 00:07:51,04 --> 00:07:54,03 do some reporting off of the information in this table. 203 00:07:54,03 --> 00:07:57,04 So I'm going to go to CPI right here. 204 00:07:57,04 --> 00:07:59,05 We're going to make this all of these just averages 205 00:07:59,05 --> 00:08:03,04 so we can see how we're doing overall. 206 00:08:03,04 --> 00:08:05,05 So I'll just click down there 207 00:08:05,05 --> 00:08:08,07 and make sure that these are all average. 208 00:08:08,07 --> 00:08:10,06 And then I'm going to go back to my dashboard. 209 00:08:10,06 --> 00:08:12,03 So we'll just do a simple link. 210 00:08:12,03 --> 00:08:14,04 So I'll do equals here, 211 00:08:14,04 --> 00:08:16,08 put that one right there. 212 00:08:16,08 --> 00:08:18,09 Equals here, 213 00:08:18,09 --> 00:08:22,07 put that one right there. 214 00:08:22,07 --> 00:08:26,08 And I'm just going to keep going. 215 00:08:26,08 --> 00:08:28,03 So we'll do equals here, 216 00:08:28,03 --> 00:08:30,07 put this right here, 217 00:08:30,07 --> 00:08:33,09 equals like that, 218 00:08:33,09 --> 00:08:35,04 put that one right there. 219 00:08:35,04 --> 00:08:39,08 Okay, so now let's do some interactive driving of this. 220 00:08:39,08 --> 00:08:42,04 So I'm going to go back to my table here 221 00:08:42,04 --> 00:08:43,07 and I'll click Insert 222 00:08:43,07 --> 00:08:45,05 and I'm going to insert a slicer. 223 00:08:45,05 --> 00:08:47,06 Let's insert a status slicer. 224 00:08:47,06 --> 00:08:48,06 Of course, if you're playing around, 225 00:08:48,06 --> 00:08:50,05 you could do whatever you want. 226 00:08:50,05 --> 00:08:52,05 So I've inserted this here. 227 00:08:52,05 --> 00:08:53,09 Now that this is on this page, 228 00:08:53,09 --> 00:08:56,04 I'm going to hit Ctrl + X on my keyboard, 229 00:08:56,04 --> 00:08:58,01 and then I'm going to go to the dashboard 230 00:08:58,01 --> 00:08:59,06 and drop it in over here. 231 00:08:59,06 --> 00:09:04,02 So this is going to actually drive changes on the dashboard. 232 00:09:04,02 --> 00:09:06,07 At this point, all we have to do is some formatting. 233 00:09:06,07 --> 00:09:08,04 So I'm going to go to View, 234 00:09:08,04 --> 00:09:11,06 I'm going to turn off the grid lines first, 235 00:09:11,06 --> 00:09:14,07 and then I'm going to highlight all of these, 236 00:09:14,07 --> 00:09:17,07 make them bold, 237 00:09:17,07 --> 00:09:19,09 maybe make them a little smaller, 238 00:09:19,09 --> 00:09:22,05 and I'll give it kind of a peach top. 239 00:09:22,05 --> 00:09:24,06 That's one of my favorite things to do there. 240 00:09:24,06 --> 00:09:25,09 And then here, 241 00:09:25,09 --> 00:09:28,04 I'm just going to apply some borders like that. 242 00:09:28,04 --> 00:09:30,05 Again, I'll just do that format painter. 243 00:09:30,05 --> 00:09:32,01 But let's actually fix this up. 244 00:09:32,01 --> 00:09:33,02 Just one last way here, 245 00:09:33,02 --> 00:09:34,05 I'll click in the middle of here, 246 00:09:34,05 --> 00:09:39,04 and then we're going to make this a regular number like that. 247 00:09:39,04 --> 00:09:42,06 So I'm going to highlight this whole thing here 248 00:09:42,06 --> 00:09:49,09 and just keep doing this. 249 00:09:49,09 --> 00:09:51,04 But still, you may look at this and say, 250 00:09:51,04 --> 00:09:52,09 well, I want these to be bigger. 251 00:09:52,09 --> 00:09:55,00 And that, by you, I mean me. 252 00:09:55,00 --> 00:09:59,01 So we can just shorten everything up. 253 00:09:59,01 --> 00:10:00,09 And of course, if we want, 254 00:10:00,09 --> 00:10:07,04 we can always wrap to the next line. 255 00:10:07,04 --> 00:10:09,08 Okay, so that's how to create a quick dashboard 256 00:10:09,08 --> 00:10:11,01 using these KPIs. 257 00:10:11,01 --> 00:10:14,00 These are KPIs that people use out in the real world. 258 00:10:14,00 --> 00:10:16,01 But remember that you aren't beholden 259 00:10:16,01 --> 00:10:17,04 to what the industry standard is. 260 00:10:17,04 --> 00:10:18,03 Of course, you need to do that 261 00:10:18,03 --> 00:10:19,08 to communicate your project status, 262 00:10:19,08 --> 00:10:21,00 but also be creative 263 00:10:21,00 --> 00:10:24,08 because there's many ways to measure a project. 264 00:10:24,08 --> 00:10:27,02 So let's think about everything that we've done so far. 265 00:10:27,02 --> 00:10:28,07 We've used data visualization 266 00:10:28,07 --> 00:10:30,03 to get record level information. 267 00:10:30,03 --> 00:10:32,01 We created our own KPIs, 268 00:10:32,01 --> 00:10:34,05 and then we even created a dashboard 269 00:10:34,05 --> 00:10:38,04 to help us visualize the results of project data. 270 00:10:38,04 --> 00:10:40,07 So now we're actually ready to visualize 271 00:10:40,07 --> 00:10:41,07 the schedule of projects, 272 00:10:41,07 --> 00:10:43,06 and we're going to talk about more how to do that 273 00:10:43,06 --> 00:10:46,00 next up in the Gantt chart video.