1 00:00:00,05 --> 00:00:02,00 - [Instructor] Okay, so now that we're in Excel, 2 00:00:02,00 --> 00:00:04,08 let's create our EVM calculator. 3 00:00:04,08 --> 00:00:09,03 First step is we want to identify our important columns. 4 00:00:09,03 --> 00:00:12,03 So the Expenses column, that is our actual cost, 5 00:00:12,03 --> 00:00:14,04 so I'm going to put in parentheses AC here, 6 00:00:14,04 --> 00:00:15,06 just so we remember that. 7 00:00:15,06 --> 00:00:16,04 And then over here, 8 00:00:16,04 --> 00:00:18,06 I'm going to type an EV for the earned value 9 00:00:18,06 --> 00:00:20,07 and then PV for the planned value. 10 00:00:20,07 --> 00:00:25,06 So because every formula that we want to use for our KPIs 11 00:00:25,06 --> 00:00:28,08 is a function of these three parameters, 12 00:00:28,08 --> 00:00:30,02 we just want to easily identify them 13 00:00:30,02 --> 00:00:31,06 so that when we use the formulas, 14 00:00:31,06 --> 00:00:33,04 it all comes together really easily. 15 00:00:33,04 --> 00:00:36,02 Okay, so let's start with earned value. 16 00:00:36,02 --> 00:00:38,05 So earned value is just going to be the progress 17 00:00:38,05 --> 00:00:39,06 multiplied by the budget. 18 00:00:39,06 --> 00:00:40,09 If you think about it, that makes sense. 19 00:00:40,09 --> 00:00:42,05 It's, how much have you earned of that budget 20 00:00:42,05 --> 00:00:43,08 that you've allocated? 21 00:00:43,08 --> 00:00:46,00 Now, planned value is based off of the time 22 00:00:46,00 --> 00:00:46,08 that it has elapsed. 23 00:00:46,08 --> 00:00:47,07 So what we're going to do 24 00:00:47,07 --> 00:00:49,09 is we're going to take the amount of time that's elapsed, 25 00:00:49,09 --> 00:00:51,04 so that's 180 days, 26 00:00:51,04 --> 00:00:53,02 and we are going to divide it 27 00:00:53,02 --> 00:00:55,05 by the end date less the start date, 28 00:00:55,05 --> 00:00:57,00 that gives us our entire range. 29 00:00:57,00 --> 00:00:58,04 And then this whole thing 30 00:00:58,04 --> 00:01:01,08 is the proportion of how much time has passed, 31 00:01:01,08 --> 00:01:03,06 and we multiply that by the budget. 32 00:01:03,06 --> 00:01:05,06 That gives us our planned value. 33 00:01:05,06 --> 00:01:07,08 And that makes sense, that's how much we plan to spend 34 00:01:07,08 --> 00:01:10,03 based off of the amount of time that has elapsed. 35 00:01:10,03 --> 00:01:12,06 Now that we have these three different parameters set, 36 00:01:12,06 --> 00:01:15,03 let's just do some quick calculations. 37 00:01:15,03 --> 00:01:17,04 So let's talk about the Schedule Variance. 38 00:01:17,04 --> 00:01:19,04 I'm going to actually word wrap this here 39 00:01:19,04 --> 00:01:21,02 just so I can see it bigger. 40 00:01:21,02 --> 00:01:25,03 And then I'm going to put, for my own reference, 41 00:01:25,03 --> 00:01:26,06 the calculations in there. 42 00:01:26,06 --> 00:01:30,00 So schedule variance is EV less PV. 43 00:01:30,00 --> 00:01:31,02 Okay, so this tells us: 44 00:01:31,02 --> 00:01:33,04 Are we ahead of schedule? Are we behind schedule? 45 00:01:33,04 --> 00:01:37,00 If we have a positive number, we are ahead. 46 00:01:37,00 --> 00:01:40,04 If it's negative, we are behind in terms of spending. 47 00:01:40,04 --> 00:01:44,08 Now, let's talk about cost variance. 48 00:01:44,08 --> 00:01:49,09 Cost variance is going to be the earned value 49 00:01:49,09 --> 00:01:51,05 less the actual cost. 50 00:01:51,05 --> 00:01:53,07 Again, we already have this set up, 51 00:01:53,07 --> 00:02:01,09 EV less actual costs, AC. 52 00:02:01,09 --> 00:02:02,09 Boom. 53 00:02:02,09 --> 00:02:03,07 Okay. 54 00:02:03,07 --> 00:02:07,04 Next, we want to get the schedule performance index. 55 00:02:07,04 --> 00:02:08,09 That's going to be the earned value 56 00:02:08,09 --> 00:02:11,02 divided by the planned value. 57 00:02:11,02 --> 00:02:13,05 And if you get dollar signs here, 58 00:02:13,05 --> 00:02:16,02 what we're going to do is we're going to turn this into a number, 59 00:02:16,02 --> 00:02:20,01 and then we're going to do the cost performance index. 60 00:02:20,01 --> 00:02:22,07 And just for our reference, before I move on, 61 00:02:22,07 --> 00:02:26,00 I'm going to type in EV divided by PV over here. 62 00:02:26,00 --> 00:02:32,07 And then for this one we're going to do EV divided by AC. 63 00:02:32,07 --> 00:02:44,03 So in cell O2, we're going to say equals EV divided by AC. 64 00:02:44,03 --> 00:02:47,04 And I'm going to make that a number type. 65 00:02:47,04 --> 00:02:50,06 Now, if you get this divided by zero error, which you might, 66 00:02:50,06 --> 00:02:52,09 it is a good idea to go in here 67 00:02:52,09 --> 00:02:56,04 and just update your formula to put an IFERROR around it. 68 00:02:56,04 --> 00:03:00,06 If you get an error, just turn it into a zero. 69 00:03:00,06 --> 00:03:01,05 You have to think: 70 00:03:01,05 --> 00:03:03,05 Is there ever going to be a time 71 00:03:03,05 --> 00:03:06,00 when your SPI or CPI is going to be a zero? 72 00:03:06,00 --> 00:03:09,00 Well, only when it's ever going to be an error, right? 73 00:03:09,00 --> 00:03:12,03 Or unless you are dividing, it has no earned value. 74 00:03:12,03 --> 00:03:15,02 So it's zero divided by something. 75 00:03:15,02 --> 00:03:16,06 Okay, so with that, 76 00:03:16,06 --> 00:03:18,06 let's just do a little bit of conditional formatting 77 00:03:18,06 --> 00:03:21,02 so we know which projects are going to have issues. 78 00:03:21,02 --> 00:03:23,01 So if it's greater than one, that's good. 79 00:03:23,01 --> 00:03:25,02 If it's less than one, that is bad. 80 00:03:25,02 --> 00:03:27,03 So that is in the case for the index, 81 00:03:27,03 --> 00:03:29,01 for the schedule variance and the cost variance, 82 00:03:29,01 --> 00:03:30,02 the rule is zero, right? 83 00:03:30,02 --> 00:03:31,08 So if it's positive, it's good. 84 00:03:31,08 --> 00:03:32,06 Negative, it's bad. 85 00:03:32,06 --> 00:03:35,03 So that's one of the great things about the EVM metrics 86 00:03:35,03 --> 00:03:38,01 is that they're very easy and intuitive to understand. 87 00:03:38,01 --> 00:03:41,02 So I went on the Home tab, I hit Conditional Formatting, 88 00:03:41,02 --> 00:03:42,03 I made a new rule. 89 00:03:42,03 --> 00:03:44,03 We're going to format only cells that contain 90 00:03:44,03 --> 00:03:52,07 the cell value is less than zero. 91 00:03:52,07 --> 00:03:56,01 And we're going to make that format a peach color here, 92 00:03:56,01 --> 00:03:57,09 just kind of reddish so people notice. 93 00:03:57,09 --> 00:04:00,00 And then for these ones over here, 94 00:04:00,00 --> 00:04:02,03 the rule is greater than or less than one. 95 00:04:02,03 --> 00:04:04,08 So we're going to add a new rule here. 96 00:04:04,08 --> 00:04:06,07 Same deal, only cells that contain, 97 00:04:06,07 --> 00:04:09,07 cell value is less than one. 98 00:04:09,07 --> 00:04:12,09 Format, put on that peach. 99 00:04:12,09 --> 00:04:15,06 And we should see is that they are parallel, right, 100 00:04:15,06 --> 00:04:17,04 that the colors are very similar for both of them 101 00:04:17,04 --> 00:04:20,03 'cause they're measuring the same thing in different ways. 102 00:04:20,03 --> 00:04:22,05 Okay, so now that you know how to create an EVM calculator 103 00:04:22,05 --> 00:04:23,05 for your projects, 104 00:04:23,05 --> 00:04:25,04 in the final part of this course, 105 00:04:25,04 --> 00:04:27,05 we're ready to take everything that we've learned, 106 00:04:27,05 --> 00:04:31,00 put it all together into a way that you can understand it 107 00:04:31,00 --> 00:04:33,00 and your manager can understand it. 108 00:04:33,00 --> 00:04:35,00 I'll see you in the next video.