Please find below a transcription of the audio portion of Fletcher Hearn’s session, Project Performance Measurement – Part 3: Using MS Project to Track and Report on Performance, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the live recording of this webinar at your convenience.
Kyle: Hello, and welcome to the third and final lesson of MPUG’s project performance measurement course. This one’s titled using MS Project to track and report on performance. My name is Kyle, and I’ll be the moderator today. And today’s session is eligible for one PDU in a technical category, the activity code for claiming that with PMI is on the screen now. Like all MPUG webinars, a recording of this session will be posted to mpug.com shortly after the live presentation ends, and all MPUG members can watch the recordings at any time and still be eligible to earn the PDU credit.
Kyle: All the sessions you watch on demand can be submitted to your history and the live sessions you attend are automatically submitted. And within your MPUG history, you can print or download your transcripts and certificates of completion, including the one for today. You can access that by logging into mpug.com, click my account and then click on the transcript link. If you have any questions during today’s presentation, please send those over at any time using the chat question box on the GoToWebinar control panel. Fletcher has given me permission to interrupt and ask your questions during the session today while we’re doing demos and the presentation. And we’ll try to take any extra questions at the end of the presentation as time permits. All right, we’ll go ahead and get started. So we’re very happy to welcome back Fletcher Hearns today for part three of this course. And at this time, I’ll go ahead and hand it over to you, Fletcher, to get us started.
Fletcher Hearns: Great. Thank you, Kyle. Hopefully everybody can hear me. And hopefully in just a second, you can see my screen if technology will behave today. All right. Perfect. Thank you, Kyle. Again, my name is Fletcher Hearns. I’m the director of technology solutions for Edward Performance Solutions. What I want to do today is I want to show you, in the last two episodes, we talked about what to report, the types of reporting, the types of videos you put together. And that’s all great, but I want today take you to the more practical end, which is let’s talk about how you do it in Microsoft Project.
Fletcher Hearns: So today’s is going to be a combination of a couple of slides. And if technology holds out, we’ll do a demo which actually goes through the steps to create some dashboards both within Microsoft Project and taking it out of Microsoft Project to something else like Excel. And then I’ll give you some demo of at least a glimpse at Project Server using those same types of tools to create dashboard reports for Project Server projects, either the entire portfolio or for an individual project, and also show you how to do that through Excel or through Power BI.
Fletcher Hearns: So I want to thank you for joining. As Kyle said, if you have a question along the way, please do not hesitate to send it over to Kyle, I have given Kyle permission to interrupt me at any time. I want to make this as valuable to the attendees as possible. So please, if have a question, please feel free to ask.
Fletcher Hearns: I’m going to go through only one slide that’s the same through all three presentations, and it’s getting started with performance measurement. To get started with performance measurement, what is the one key thing you must do? And of course that is to set the baseline. So is to set the baseline so we have something to measure against. You cannot measure performance of a race if you don’t know where the start and finish are. You can’t lose 20 pounds of the COVID-19 that we’ve all put on without knowing the starting weight.
Fletcher Hearns: So again, one of the critical factors you must do to accurately track performance of a project is to set a baseline with whatever information you have to set the baseline with. So really, that’s the start of that. I want to talk now about how to use Microsoft Project’s custom field features for reporting project status and performance status. So what I’m going to do today is I’m actually going to go through a talk about custom fields and then I’m going to get into a demo of how to actually use the custom fields to show two metrics on project performance. One of course is scheduled performance and one is cost performance.
Fletcher Hearns: Well, I’ll use custom fields for building graphical indicators. Now Microsoft Project inherently has a set of custom fields, there are 10 cost fields, 10 date fields, 10 duration fields, 20 flag fields, yes or no fields of 20 number, 10 outline, 10 start and 30 text field. All of these can be used by you to hold information that is custom that you want to track. And Microsoft guarantees a couple of things here. A, they will never overwrite, destroy, remove any of those custom fields through upgrades, through the product. So if you put a custom field in a 2003 project file, and you open it in 2019, the custom field will be there with the data. And if you upgrade from 16 to 19, any custom fields you had, their values will remain.
Fletcher Hearns: And you can use local custom fields, custom fields in your project to hold additional information that if you store them in Microsoft Project Server, Project Server cannot access them, can’t munge them, can’t change them, can’t delete them, can’t modify them at all, they are truly customed to this file. And in Project Server that has counteracting thing is because Microsoft Project Server can’t see that, and it can’t report on them at a Project Server level. So you have to create Project Server level custom fields that are used if you want to track information across projects. These custom fields are solely for this one project.
Fletcher Hearns: And then you can associate graphical indicators with the data in any custom fields. And therefore, you can actually have a dashboard as part of your schedule. And so what I’m going to do now is I’m going to attempt to get out of this and actually start using Microsoft Project. So hopefully, you can all now see a Microsoft Project schedule I have. It’s a very simple schedule that is used just for demo purposes. And you can see if you just look at the Gantt chart, it’s very hard to tell from this Gantt chart at a quick glance how we’re performing on the project. Why? I got the start date, the duration, the predecessors resource, they may have all the information about it, but there’s no way to tell which projects are on time, behind time, over costs.
Fletcher Hearns: Yes, you could use the tracking Gantt, which will give you a little more information because now it shows the baseline versus the current plan. So you can see it that way. But are you a
little behind, a lot behind? How are you doing? So what we’re going to do is we’re going to actually create a custom field to hold a piece of information that we can then use to create a graphical indicator.
Fletcher Hearns: So as I said, in project, there are any number of custom fields, there are text fields, cost fields, duration fields, there are at the task level, there also at the resource level. We’re going to work with a task field today, and we’re basically going to create a text field that holds a formula that lets me know, is the task ahead of schedule, behind schedule, slipping a little bit, or there was no baseline actually for that task, and therefore I can’t measure. So that’s what we’re going to take on and it’s fairly straightforward.
Fletcher Hearns: The biggest thing I want to say about using custom field is always, always make sure you rename the field. Why? If you don’t rename the field, and you share your schedule with me, and I don’t know you’re using text field one for something, and I need to use a custom field, Murphy’s Law kicks in, and I will use text field. Number one, because why not.
Fletcher Hearns: So you always rename it. And we’re going to call this Sched.Stat. So it’s going to be our scheduled status field. And you’ll see now that it says schedule stat and in parentheses, text one. Inside a project is now known by both names. So you can find it using text one, or you can find it using the name schedule stat.
Fletcher Hearns: Now, in Microsoft Project, you can have none, it’s just a blank text field that you can type anything in, you can have the lookup field where you can give it the list of values that can be used. So it’s like a pull-down menu. So the user has to select from a given element from the table, or you can say it’s a formula.
Fletcher Hearns: And the formula you might use can be very simple, you can actually just reference another field. When you want to use graphical indicators, you have to use custom fields, you can’t use a graphical indicator against a standard Microsoft field. I can overlay graphical indicator on the duration field saying if it’s greater than 10 days, it’s yellow. If it’s greater than 30 days, it’s purple. If it’s greater than 50 days, it’s red. You can’t use that because that is a standard Microsoft Project field. You can’t put graphical indicators on those, you must put them on a custom field.
Fletcher Hearns: But I could come in here and say the schedule stat is the same as the CPI field. And then it would just copy the CPI value into this and then I could use that to figure out the graphical indicator. In my case, I’m going to put in a formula that basically looks at and compares the baseline finish date with the finish date. So it looks at the baseline finish date and the finish date, if the value is in right brackets, if it’s in square brackets, it means it’s a known field to Microsoft. So it’s how you select the baseline finish field. As I type in bracket baseline space finish bracket, Microsoft knows, oh, use that field.
Fletcher Hearns: You can use very complex field. Basically, think of it as Excel, or a database, and you just put in the values you want there. And this one is going to basically look and say, if it’s NA for the project date value, that means there’s no baseline. If the difference between the finish and the baseline finish is greater than one, it’s ahead of schedule, meaning it’s actually starting early. And if it’s between minus one and plus one, it’s on schedule. So that’s the formula we’re going to use, it’s going to warn me saying, hey, that’s fine, you’re wiping out the data in the text one field, the schedule stat field. And I say, yes, that’s fine.
Fletcher Hearns: And so I put the formula in, and I’ll show you the formula in a minute. You also can see that for summary rows, I can say use the same formula. I can say for the summary rows, use the same format I’m using for the lowest rows, excuse me. And in this case, I’m going to say I want to show the data. So I’ve now created the text field. Now we need to add it, so I’m just going to say insert a row here. And as I’m going to type in schedule, you see it find schedule status. And you’ll now see that I now have the task are falling behind schedule, and we have a couple of on schedule, and it’s done for every task.
Fletcher Hearns: So that’s great. Now I can see it, but now I still have to read it. So what we want to do is we want to start to transpose these into graphical indicators. So now, if you look at this schedule, which is basically the same schedule we just had, except I’ve added the status custom field, which shows me on schedule, falling behind schedule, I then have a cost status, which is the same way except in this case, it’s using a percentage of the cost. So it looked at the baseline cost versus the total cost of that task right now, and says if they’re within 10% of each other, it’ll tell you, it’s within 10% of budget. It’s more than 15% over budget, whatever the value is going to be. And it also calls out milestones, because there is no cost, there should be no cost really associate with milestones.
Fletcher Hearns: So this is great. Now I have all the text. This still doesn’t help me in being able to quickly figure out which tasks are on schedule, behind schedule, over cost, under cost, whatever that is. So what we want to do is we want to transpose these into graphical indicators. So what I do is I come back to the custom field settings, oops, that’s not what I wanted. I come back to custom field, it brings up that custom field, because that’s highlighting, I’m using the formula. Oh, but now I want to say here, these graphical indicators.
Fletcher Hearns: So I’m going to basically say, in my case, I’m going to say if this field contains a 01, which you’re going to have to trust me, it means that we are ahead of schedule, I’m going to pick the blue indicator, I’m going to say contains 02. Oops, it’s on schedule. So I’m going to select green, and then contains 03. It’s yellow, meaning it’s missing its state, but it’s not horrible yet, and then contains 04, that’s red means it’s really behind schedule. And then if it contains 05, it means it doesn’t have the baseline.
Fletcher Hearns: Now, how do I know that? Well, I wrote the formula, so I know what the formula means. And in that case, I’m going to put in here, let’s pick up an X, which says if we have a task with no baseline, it’ll put a red X in that box saying, hey, I can’t evaluate this particular task because there is no baseline. I also want to tell it to do the same graphical indicators for all the summary rows, and is going say, you have to remove any criteria you currently have. I say yes. And I want to do it for the project summary row. I want to use that same criteria.
Fletcher Hearns: And I click, yes, I want to do that. And then I click okay. And what should happen if the world is a good place for me today and technology works, when I click on this, all of those values should change to be a little indicator. And now you can see what I have is I have the status indicator has now been transposed from a text field to a graphics field. And you can see now that if I hover over it, it will say that task is falling behind schedule, these are on schedule.
Fletcher Hearns: And I would now want to do that for both of them. Actually, there is a nice feature in Microsoft Project, I’m going to do that for this one. I’m going to say it’s a custom field, I’m going to say I want a graphical indicator. And you’ll see a… Well, it’s already there, right? I had already put it in there, what you can do is you can actually say import this. And I can say from a formula field, I can say, from a different field, I can say from the, I can use the same graphical indicators that were in another one by
importing the criteria. And then again, if I click, oh, let’s make sure we do this. So we do it for the local summary rows, and we do it for the project summary rows.
Fletcher Hearns: And you want to always do it for both of them so they roll up, if in fact, it’s all based on the same formula. And then again, I now have an indicator that says, okay, here is the cost indicator. And you’ll see green says, if you hover over it, it’s within 10% of budget. So if somebody doesn’t know what a red means, what does the red actually mean? It means more than 15% over the baseline budget for this task. And between 10 and 15, if it’s yellow, and within 10% of the budget for green. So I’m either within minus 10 and plus 10%.
Fletcher Hearns: Because in most cases, no one is expecting you to, what I call, do penny dollar accounting within your project, you’re not going to come in and do exactly the same. So this says if you’re within 10%, you are then good to go. So now with the graphical indicators, in a lot of cases, you put those up front, and now I can see which task I should be concerned about. And it looks like I should be concerned about everything because everything is falling behind schedule.
Fletcher Hearns: Now, as I said, in our case, this is using a pure number. It’s saying if you were more than one day earlier, the task is early. If you’re between one day early, and one day late, you’re on time. If you’re between one day late and four days late, by these late, you’re yellow. And if you’re over five days late, you are then red. So in a very short schedule like this one where the whole schedule is only about two months, that doesn’t make sense, because then a task could be, it might need to be tighter or looser, which is why for cost, we use a percentage, saying you’re this far behind, because that makes more sense.
Fletcher Hearns: So that’s how you can put graphical indicators. And as I said, you can put graphical indicators for a number of things. I’ve had other clients where I’ve done it for the CPI value, the SPI value, I’ve done it for other measures that they’re tracking, that they want to track, we’ll have a custom formula for that, where it’s a very complex formula. If you use custom fields and you do graphical indicators, the only thing I want to stress is do not have too many on a single view.
Fletcher Hearns: So this is, we have two on here, which is great. But if I had five or six of these yellow, red, green, blue indicators, you then need a decoder ring to figure out if I see two greens followed by a blue followed by two reds, is that better or worse than two reds followed by two greens followed by a one blue and a yellow? The combination, permutations get to be too many to know if it’s valid or not valid.
Fletcher Hearns: So a lot of times we’ll set one up and this is just a pure cost and a pure status schedule one, we may then have another view that would have, it would be like an earned value of you, which would have indicators for SPI and CPI, and maybe some other things, but it wouldn’t show all four or six on the same view because it would then be too hard to understand what that actually means.
Fletcher Hearns: As I said, this is a great way to show it for a individual project. The one thing I want to say is I talked about it. And when you do the custom fields, you always want to make sure that on the graphic indicators you are staying to roll it up to summary rows into the project summary row that makes sense. And that’s done so that if I come to format and I say show the project summary, you can now see for this building, the widget project, the schedule status is a little falling behind. But we’re still in cost, we’re still within the entire project. I’m still within my 10% bounds of budget even though design
part A, and the project management tasks are more than 10% over cost for the individual task, at the project level, we’re still within our budget.
Fletcher Hearns: It’s also important that if you’re running a program file or integrated master schedule, where you have multiple projects within a project file, if you use roll up to the project summary task, then that will show up as the indicators in your IMS. So then you can see across a program for each project, how are they running on cost and schedule? Well, if I put it in the project summary level, I get to see that in an integrated master schedule. So are there any questions before I move on, on how to create this? I know it’s quick.
Kyle: No questions in the queue yet, but just a reminder to anyone that does have questions, feel free to chat those over at anytime and we’ll answer those live for you during the session.
Fletcher Hearns: As I said, I’ve added these, also these are in the slide decks, I’m going to go through them so you can see that in the slide we go through and we do that. I now want to talk about one of the new features and it’s new, it’s relatively new to Microsoft Project is using Microsoft Project and the project report features to create reports and dashboards on how your project is progressing.
Fletcher Hearns: Up until 2016, the reporting was a little lackluster, it looked like something… For those people that are old enough, they’ll remember this, it would come out on green and white bar paper on a high speed printer someplace. That’s what the reports looked like, they looked like really text reports. Microsoft now has added, ’16 and ’19 have built in reporting, which allows you to create dashboards and reports that are much more user friendly in their look and their appearance. And I want to take some time and go through that.
Fletcher Hearns: So again, it’s our same schedule, we just working on, it has our graphical indicators, which gives us some idea of how we’re performing. But I want to talk about the reports that are within Microsoft. So Microsoft has a whole bunch of reports that you can look at, here is their project overview report. Here is, Oops, not new report, dashboard report, here is a cost overview report. Here is a resource report, over allocation resources, which we have none. So let’s create one so you can see how this report looks.
Fletcher Hearns: So again, these are all live reports. So if I go back to the Gantt chart, and simply add a new task, again the baseline is 12 days and resource, we’re going to put Betty Smith on this task, simply because I know it should then generate an over allocation. So now if I go to the report, resource report and look at an over allocation resource, you’ll now see that it picked up the Betty Smith that is now over allocated.
Fletcher Hearns: So there’s lots of good information, it’s pulled live from the project as you open up the report. You can see progress in reports here, the critical tasks. You can do a whole bunch looking at these reports. But as Microsoft understand is that probably won’t work for everyone. You need to create a new report, you need to create a custom report on how your organization needs to see information and what they need to report on.
Fletcher Hearns: So there is of course, a way to create a new report. We’re going to create a blank report today, and I’m going to call this MPUG report. And now you see you get a blank report. And then you say to yourself, great, now I got to create all those elements that I want. What you should
remember is no, you can’t cut and paste from existing reports. So for instance, I could say let’s quickly jump back over to the project overview report. And I like that element, the start, the end date, the percent complete. I like the milestones.
Fletcher Hearns: So I want to take this information from this report and put it on my report. So I basically say fine, that’s great. Now let’s, oops, let’s select those again. And I can say copy those. And then I go to report and say, hey, let’s go back to my custom report, which is now called my MPUG report. And let’s just now paste those here.
Fletcher Hearns: And now I have those on here. But now I can move them around and say, listen, I really, that’s a great thing. And we’re just going to take that element, and we’re going to stick it up here. Oops, we’re going to stick it right there. And we want to move this milestone information, we want to move it to here. And that’s great. Now, if I click on, let’s go and look at something else. Let’s go to another report. And let’s pull a piece of information from my dashboard report. Let’s look at the upcoming tasks.
Fletcher Hearns: Now, that doesn’t work. Let’s go look at report, dashboard, a cost overview report. Let’s grab this here, this cost status. Why? Because I like it. So I want to bring the cost status over and say, I want to save this element too on my report. That shows me the information I’m interested in seeing about the cost. And we’ll paste that in here. And we’ll put that over here. And we don’t really want to learn about this. So we’re going to go like that. Oops, oh, no, that sent me to the wrong place.
Fletcher Hearns: Let’s go back to here, I just wanted to pick that up, so I can delete that… Now if you click into a chart, for instance, like this one, which are cost status, you’ll notice on the right hand side, up comes the list of the fields that you use to generate that element, whether it’s a table, which is, this is what it’s using for the field list, for the table. And it’s saying I want the finish date. And I want the name of the task. And I am saying I only want to use it for upcoming milestones. Or for this one, which it’s saying, okay, I want to pick up the baseline cost field. I want to pick up the actual cost field. And I want to pick up the remaining cost field. So those are the three elements that go on the chart.
Fletcher Hearns: And then you can add things to the chart if you want, like I can say, what’s the chart title? What are the data labels? So it’s much like doing graphing, and elements of how you would set up a graph inside of Excel. It’s not full and as complete, but it allows you to do that type of information.
Fletcher Hearns: You can also say, here are the filters and here are the groups that you want to do that. So now let’s go and look at how we would add one. So I want to add a table, I really would like to figure out [inaudible 00:28:15] do to this. Nope, it did it again to me. Let’s just go back. And we’re just going to leave that right word is on the screen that I can’t seem to get it to move, I’m going to add a new table here, because I want to track and I want to look at all the tasks based on that task status, that task schedule that we were looking at.
Fletcher Hearns: So the ones I added to this task, let’s go back here, because you’ll see there’s a task planning two weeks, that’s a custom field, that all it shows is in the next two weeks, which tasks are going to start? Which tasks are going to end? Or which tasks are going to start and end? So END means they’ve already started but they haven’t finished, START&END means in the next two weeks this task will start and we’ll finish in the two week period. And END means the task has already started and will end in these two weeks. And a START means that it will start next week.
Fletcher Hearns: So you can see for this end task, it’s actually started, and it will end in the next two weeks. This task here manufacturer, part a, it’s a fairly short task. It’s like a three or six day task, which means it’ll END, START&END in the next 14 days. So I want to show that on my report, because that’s a great thing that my project program manager wants to know, my boss wants to, “Hey, what are the task for the next two weeks we got to be concerned about?”
Fletcher Hearns: So if we go back to the reports, go to my custom report, I can say hey, I want to insert this table. But I really want this table to have a different set. I don’t really care about the percent complete, because I’m not really concerned about the percent complete. What I really want to know is I want to know, for a custom field, which is our task planning. So I want to look at that and say, okay, for task planning, I’m interested in what the task planning status is of that. And you’ll see I get absolutely nothing in my table. Why? Well, because it’s looking at all tasks, but it’s looking at the outline level called project summary.
Fletcher Hearns: So I can say, now I want to see it for all tasks. And now I will see it for all tasks in the schedule, which is great, but I really don’t want to see it for all the tasks, I really only want to see it for tasks that are due, which have some value in the field. So it’s like anything else in project, I can use a filter. And I have the filter set up, which says, basically go to the task planning two week field and says, if it’s not blank, then I want to know about it. That’s all it does. It filters out the blinds and says, nope, I don’t care about it if it’s blank.
Fletcher Hearns: So now when I do that, it only shows me tasks that are due in the next two weeks, we’ll have some event. Either they’ll end, they’ll start, they’ll start and end. You and I can say, okay, and I’ve said it for all subtasks. So now I can do that, I could also do a grouping. And if you don’t have a group, you can say, nope, I can now create a new group and say I want to group by, and I can pick anything I want to group by just like you would on a Gantt chart, where you can say, nope, I want to do a new grouping, create a view, create a group.
Fletcher Hearns: So that allows me to do all that information fairly quickly. And now I’m just going to select all the data because I really don’t want it in 14 point size, let’s make it 12 point. And then we’ll stick it right here. Because we’re making the pretty one, let’s go to reports. And go design and I say well put a text box in here. And a text box was tasks. Status next two weeks. And I can say here that is, I’ll put that up there.
Fletcher Hearns: And I’m going to just quickly say, hey, that’s great. Let’s make that 14 point, let’s make the color white. And then let’s select the object. And let’s make this still the same color as that. And now I’ve added that to that. And again, I’ve now added that, and I can resize, I can do whatever I want. And I can go ahead and now create the project overview for what my organization wants, what my boss wants, what my customer wants, what my client wants.
Fletcher Hearns: And this is all good. And we’ve now copied, we’ve created our own, we can continue this till we get it. Now there are some limitations. And some limitations are why I’m going to talk about other things as soon as you get done this, is you’ll notice up in here, when I’m on reports, there’s no print. If I go to print, I can’t print the report. I mean, I can print just the page, but I can’t print the data that goes with it. So I can’t save it as, I’ve limited in what I can do. But it’s still good. So if I’m sending somebody a project file, they can go in and look at these.
Fletcher Hearns: But we may want to do some other things. Now what I want to do now is let’s look at something else, I want to show you a more complete one because I have limitations on how much I can show in an hour. So here’s the one and now you’ll notice that I have a couple custom reports and I have my dashboard report. So here’s my dashboard report that I generated. And it’s showing here are the milestones that are coming up. I have no late tasks, so that one is empty. I have the critical path. So it’s showing me the remaining critical path tasks from now to the end of my project.
Fletcher Hearns: I have the major tasks. So this is basically saying at outline level one, what’s the percent complete? I don’t want to know for part A, all the details, but I want to know at the summary level one, how are we proceeding? So we’re 53% done on part A, 40% done on part B, haven’t started part C or final assembly and our management is 100% complete.
Fletcher Hearns: So that allows us to look at all that. And then over on the right hand side, I have my cost that’s actual cost, baseline costs, remaining cost and our cost variance. I then have information about cash flow across all September and October. And then at the bottom, I have information about remaining cost, the actual cost and the baseline cost. So you can see that management, we’re over cost on management, and we’re over cost on part A, because the baseline cost which is the line is less than where the entire histogram shows.
Fletcher Hearns: And you’ll notice on this one, I am showing the page breaks, which are the green lines here. So I know this will fit on one page. So I go to print it out, this will print out on one page. The other thing you can do is you can do a copy report. What that does is that copies the information on this report to your clipboard. The nice thing is, let me see if I can get this to work in real time. Always dangerous when you’re doing a live demo is now I’m in PowerPoint. And I can basically say paste, and it pastes those elements in to here.
Fletcher Hearns: And what you’ll notice is it doesn’t paste them as an image, it paste them as individual graphic elements, so then I can move them around on here as I see fit to make it fit the presentation or whatever I need, or I need to split, put these on two ones. But now it’s not in a picture, I could paste that as an image, but I don’t. And here I can actually then say, hey, I want to group all those things together. And then I could just pull it down here and say, here we go. There’s our project overview as we might see it.
Fletcher Hearns: So I think we talked about all this. I think you’ll see the copy dashboard. And now here is one, the next slide is probably one where I spent some time putting that project dashboard that was in project. It’s now in PowerPoint. And I’ve sized it to meet the requirements here. So if I’m going into my monthly program or project review meeting, I have the dashboard right here, if I can’t show it through the project. Or if I’m in a program review meeting where I’ve got four or five, and I just want to have a slide deck that shows it. So that allows you to do all of that by using the power of Microsoft Project to pull that up.
Fletcher Hearns: And again, you can set page breaks, and you can set margins, and you could set the size. So this is set to legal, I could set it to ledger. And then I know I have this space out here now that I can play with as well as this space down here. So again, one of the things I talked about last week was making sure your dashboards are printable, because there are still a lot of people say yes, break list from dashboard. Can you print it out for me and drop it on my desk?
Fletcher Hearns: And so that’s how we can do all of that with inside of Microsoft Project to communicate our project performance, I’m 31% complete, I have no late tasks, here are the critical task to the end, here’s my task burndown. And by week, here’s the cash flow. Here’s all the information that this fictitious client wanted me to provide to them or, my boss wants to provide to them when he says, “Hey, what’s the cost variance?” Cost variance at the end of the project right now is $942.20. So that’s the capability the project gives you within project alone. Are there any questions before I move on to using Excel?
Kyle: Fletcher, we did have a question that came in from Philip. And he was just curious about the formulas you were speaking to. And if there’s a good resource to get those formulas?
Fletcher Hearns: Well, Philip, great question. I will tell you what, at the end of this presentation, my name and address… No, there’s not a really good one, I can send you the ones that we… We have like three or four that we tend to use with all of our clients and customers. I’ll be glad to send those to you. They’re on the screen right now. It’s probably a little hard to read. There was a good book published years ago, and I’m trying remember what the name it’s called, it was called VBA Programming for Microsoft Project. And it had some information about how to create formulas.
Fletcher Hearns: If not, I’ll be glad to send anybody that wants these. This is the one I’m using, which does simple schedule by days. Then we have a schedule by percentage of baseline, of cost without looking at the milestones by percentage, and then this is the one I use for the two week look ahead. Those are the formulas I’m showing you today, I have no problem sharing this with anybody.
Fletcher Hearns: As I said, learning how to do that is Google formulas in Microsoft Project, and there’ll be numerous ones out there. As I said, I’ll be glad to share these with you. And always, as I’ll say at the end, I’ll say it in the middle, if you have a question, my contact information will be shown at the end of this, please feel free to reach out at any time, and I can help you.
Fletcher Hearns: So this is where we talked about dashboard, selecting the fields. As I said, this is all in the PowerPoint presentation. I want to now talk a little bit about Excel. Excel is almost universally known by anybody in business as a way to get information to and from people. One of the things that you can do in Excel is you can actually extract, export data from Project to excel. And there’s a number of ways of doing it. You can use Save As, you can do visual reports.
Fletcher Hearns: And then what I’m really going to touch on here today is the copy and paste, because Project and Excel are now playing better together than they have in the past. So if we go back to the project, and we’ll go to our widget status, but we’ll go back to the Gantt chart, you always had the option to go up and do a file, Save As and browse out, and we’ll put it in the presentation folder under MPEG files.
Fletcher Hearns: What I want to save as an Excel file. And when I say save, it’ll come back, and it will ask me to produce an export map with the fields I want to export. And it will give me a nice flat Excel file with whatever fields I want. That’s traditionally how you’ve gotten stuff from Project into Excel. I’m not going to do that today. That’s not what I want to show you.
Fletcher Hearns: The other way has always been under reports is you could create a visual report. There it is. A
reports. If you turn one off, it only shows the Excel reports. And you can create your own, you’ll see here that I have a couple that have started with star two, those are for customers that I’ve created some custom visual reports for, for things they need for etcs. But let’s do a baseline cost report. And I’m just going to say, to view that, what happens is Microsoft Project creates an OLAP cube, sends it over to Excel and says to Excel, would you please bring this up in Excel and show it to us and then we have, and this is the cost baseline one for this project.
Fletcher Hearns: And it shows you that the baseline cost, the cost and the actual cost are three shades of purple. Don’t know how that’s coming across. And then you can do things because what this really is, if you’ve looked at the other tab down here at the bottom is the assignment usage. Which is really, these are Power Pivot tables. With all of the goodness that come from Power Pivot tables that I can say, yeah, look at the weak account, not only look at the whole project, I want to look at just the Q4 numbers. And all of a sudden changes shows me just Q4 numbers.
Fletcher Hearns: Or I could say, now I don’t want to do Q3 numbers, I want to do Q4 numbers, but I only really care about the first two weeks, which are the last two weeks of my project. And now it changes. And of course, then the chart changes too behind it. So that’s a great way to get the data out. Sometimes though, that’s a little clunky. And if you don’t know what you’re doing, it’s hard to do that, when really what I want to do is I want to quickly get the information from Project into Excel, so I can handle it from Excel.
Fletcher Hearns: Well, what you can do is basically create the view here that you want, you may not care about how the Gantt chart looks, because I’m not actually going to export the Gantt chart. I’m only going to export this data here that’s in the table format. So what I can do is I basically open up Excel and I have a blank Excel file. And I say yes, what I want to do is I basically want to copy all of this information over to that Excel worksheet. And so I just highlight it, and be careful, don’t highlight like this, it won’t always work. Highlight the entire columns and say these are the columns I want. I say Ctrl+C, oops, cancel. Don’t want to do that. No, I don’t want to do that. What did I get into? It thought I want to delete something. Again, do that, Ctrl+C, I then come over to a blank Excel file, and I say Ctrl+V, and I paste it in here. And lo and behold, all that information comes across.
Fletcher Hearns: Now the first thing you want to do, if you really want to use this for recording, the first thing you do after you get it here, is go up here and basically, it’s already highlighted, they format as a table, and then format as a table, and we’re going to pick that one. Make sure you always say, my table has headers, it will then create a table for you.
Fletcher Hearns: And then you can do whatever you want to make it look better in terms of getting the data over. You can go through and redo all of that, you can come in here and basically say, listen, this is great, but it’s awfully big for today. And so I’m going to come in here and let’s make that 10 point font instead. Now what you’ll notice is, it does not take over the actual graphic indicator, it takes the text behind the graphical indicators.
Fletcher Hearns: And we’re like, well, that’s okay, I can live with that. But now that I have it Fletcher, what could I possibly do with this? Well, now that you have this information in Excel, and it’s in a table, and I’m just trying to get to everything because I’ve… Great, it’s in a table. Now what can I do with this? Well, it’s in a table. So now you can go up and say, okay, let’s go to insert, and let’s insert a pivot chart. I’m going to say insert a pivot chart with a pivot table.
Fletcher Hearns: And I say, well, what table? I’ve already selected the table, and I say wanted in a new worksheet. And it says, okay. Now I can come over here. And now I’m using Power Pivot. And I can say, Okay, what I really want is I want to pass planning, and I want to do the values of those. And you’ll see now, very quickly, it says, Oh, here are the next two weeks, here are the information. And then I can do things like, okay, that’s great. But I really don’t want that, I want to change the chart type to be a pie chart.
Fletcher Hearns: I now have the pie chart. I could then say, let’s do that again. Again, let’s select the table. Let’s go to insert a pivot chart and pivot table, let’s do that again. I’m going to put it in an existing worksheet, click in there, and I go, let’s go back to sheet two and say, we want to put it right here, please. Well, now I really want to do, again, I want one that shows the schedule status.
Fletcher Hearns: And again, I’m just a fan of saying no, let’s do a chart. Again, let’s do a pie chart. And let’s do a three dimensional pie chart. And then we have that in there. And again, all I’m doing is I’m taking the data that’s on sheet one, which we can rename raw data. And sheet two is charts. I can then start to put together a very compelling set of charts that match up with the project data that I pulled out of here.
Fletcher Hearns: But wait, there’s more, really, well, that’s great. But I have to do that every week, you actually do. What I want to do is let’s go to a different Excel file. You’ve got the same date in it, trust me, isn’t it? So here is, I formatted this to be a little nicer, but that’s the raw data. I then have the dashboard which has a bunch of different things. It’s got elements that I copied from a report inside of Microsoft Project. It’s got these two charts, the cost status data and this schedule status data, and it’s got CPI and SPI indicators on it.
Fletcher Hearns: So I’m taking combinations of the best of both worlds to make a dashboard. And people are like, “Well, that’s great.” But what happens if my project changes and I add two more tasks to my project? So I have new task one and new task two. And let’s give them 12 days and eight days of duration. And they’ve been added to my project schedule. So now how do I, now I’ve got to go through, create this whole thing again? No, simply again, because, again, Ctrl+C, copy those elements. Because this is already a table containing data.
Fletcher Hearns: If I come up here and select task name and say what I want to do is I want to paste everything in my clipboard here, starting at A1, Microsoft Excel goes, Oh, I’m in a table. Great. Oops, I didn’t have everything that was supposed to be on there. So go back to project. And it’s like I need… What do I need to make this work? I need processor resource menu.
Fletcher Hearns: We’ll just leave it this way, because it should still work, Ctrl+C, what I’m going to do is come back here. And when I paste this information in again, even though it’s changed, and there’s not values for certain ones, it still maintains that it’s a table. So you see, there’s new task one and new task two, what I want you to do is to now watch 22 tasks before I simply come up here and tell it to refresh the data, it goes back to table one on my raw data and updates those, task counts are now 24, I have two tasks in here that are gray with no baseline.
Fletcher Hearns: I have 24 tasks over here, I have two again with no baseline. And if I go back to my dashboard, they’ve updated also. So simply by refreshing the information that’s in that table, all of your
pivot charts, your pivot tables hold. And so you can just keep doing this every week, you cut and paste, create the view, cut and paste it into your table in Excel, and my dashboard has now been updated.
Fletcher Hearns: They say, oh yeah, now there’s a number of tasks without, and it says the value is two, or 8% of tasks in the project, on schedule is two and behind schedule is 83% or 20. So again, that’s how you take data from Project and get it into Excel and be able to manipulate it. And then you can do graphic charts in here with SPI and CPI. But again, it’s creating them here. And all I did was take these two, I said copy those and paste those on the dashboard here, and then I changed the colors around to make the codes be what we needed them to be.
Fletcher Hearns: So again, we’re trying to figure out how to get the information as quickly as we can to our clients, our customers in a format that they understand. And again, I can now send the Excel file out and people know how to open Excel files, I can come up here and do Save As and save it as a PDF file, if I need to. I can print these reports. So I can do all that from Excel. And again, it was taking the information from one to the other.
Fletcher Hearns: Now, let’s talk just for a moment. We went through copy and paste. We went through all of this and how to do this, I have the slides in case my demo didn’t work. I want to talk a little bit about Microsoft Project Server and how you can query data from that and use that awesome information. So I have two different ways we’ve done that. The first one is I’m going to actually go to my Project Server, look at all my projects, and say, great, but let’s go down to reports in the Edwards reports.
Fletcher Hearns: So for Project Server, we’ve created a set of reports. And I’m just going to say open this report. And I’m going to say opening the app. So it’ll open up on my desktop, in Excel. And it will. Please will, it’s spinning. I now have a project cost dashboard. Not for a single project. This is looking at my Project Server, all the projects in Project Server. It’s looking at all the information here. Again, this is looking at our cost status using basically the same formula but at the project level.
Fletcher Hearns: Here’s our CPI graph, for each project says what’s the CPI number for that? And you can see we have a couple that are value here for the data mining analysis, is not great, let me just blow this up so you can see it a little better. And then I also have a slicer in here that says, okay, show me for just the Agile project, how the cost looks for Agile projects versus all the projects?
Fletcher Hearns: And it’s saying, oh, we only have three Agile projects. And it looks like one’s under budget, one’s on budget, and one is over budget. Convenient how that worked out for demo, isn’t it? And there’s this three CPIs. And if I click on just the false, it shows me for all the other ones, here they are. So again, this data is all pulled from Microsoft Project Server, I can at any time go up and say, refresh all or we have it set up that every time the Excel file is opened, the data connections are refreshed. So every time I open this file, it goes to Project Server and gets the live data from there. Which is why I said, when you create your custom fields, if you’re going to use them for reporting across multiple projects, make sure you say roll up to the project summary level, so that I could have all the information determine all of this information. And then you can see we have information here in a table format and a risk format.
Fletcher Hearns: So that’s one way of doing it for Project Server Data. We’re not going to save that one. The other way is to use Power BI, which is a business intelligence tool from Microsoft. And I’m
wondering whether it’s, maybe it’s [inaudible 00:56:32] meetings. And is taking its sweet time right now, of course. Are there any questions while I wait for that one [inaudible 00:56:45] show up? Because it’s out.
Kyle: No questions, Fletcher, but if anybody does have one, we have a few minutes left, feel free to chat that over now.
Fletcher Hearns: As I said. So Power BI. It allows you to do portfolio views and all that. And this is the one I just showed you. And I’m just waiting and it’s opening now, it’s going out to the Project Server to collect the data. And it won’t let me move the screen lights doing that. Now this is using a different thing. But this is showing, for instance, this is a testing we’re doing for a potential client of ours, and I’m showing that, okay, there are 39, what they call task bars into projects, it’s 182 million, or 1.54 1500 task at risk, their SPI is 0.82, their CPI is 1.43. And you can see here that I now have this.
Fletcher Hearns: Now, because I’m using Power BI, I can click on things and all of a sudden things will change. So it’ll say for RTO type projects, here are how many projects by project manager they’re managing and here’s the budgets for them. If I go back and change the whole thing, and now you can see these are all dynamic. And so if click on, who am I going to click on? I might click on Brock, if I click on Brock and select him there, the bottom changes to show me, only the projects Brock is project manager, the 19 projects.
Fletcher Hearns: So you have a whole bunch of ways in Microsoft Power BI to pull data from other things about how we’re doing. So this again, you can’t do this against a single project file yet, Microsoft does not have a connector from project into Power BI. But if using a Project Server, then you can do this. You can see this is a Kevin Donnelly, how Kevin Donnelly’s PM, how his projects are working, versus Brock, which we showed you before.
Fletcher Hearns: So again, these are all just showing information about the drill down using Power BI, to again, get us to the way we’re communicating information about the projects. And you can see that the schedule variance here is 20, across the 39 task orders is $25 million. And I said this data is just all being used for demonstration purposes. But it allows us to then formulate those out of Microsoft Project Server.
Fletcher Hearns: So with that being said, and I think I’m pretty much right up against. I do want to remind you that you’ll do all this and somebody will still say thank you very much. And we’re going to make a decision based on other factors not on what the actual data actually shows. This is my contact information. If you have questions about-
Kyle: 1:00 p.m.
Fletcher Hearns: … About how I’ve done stuff, you would like the formulas, you would like the example Excel files, the example project files, if you’ll send me an email at my email address that’s shown on the screen here and just mention that you were in the MPUG presentation on the 19th, I’ll be glad to send you a zip file with the project files, the Excel files, the text file with all the formulas in it so you can start to walk through it and understand what I did to get to there and understand the new power that’s coming out of Microsoft Project and reporting performance. So I’d like to open it for questions, I’ll stick around as long as questions are.
Kyle: Thank you so much Fletcher, we really appreciate [inaudible 01:00:28] great session today, and for presenting the full course, we really appreciate it, that was very helpful and insightful. For those of you that are claiming the PDU credit for today’s session, I’ll get that info back on the screen for you now. And today’s session is eligible for one technical PDU.
Kyle: If you missed any of this session, or would like to review any of the previous two sessions, the recording will be posted to mpug.com on just a couple hours and you’ll receive an email with a link to that, you can do that on demand on mpug.com. We do have smaller sessions on the calendar, including on June 9th, flow return for project accelerators powerful instance of Power Apps, supporting project management with Project for the Web.
Kyle: [Nina Tarkovsky 01:01:21] will present that session for us, eligible for three quarters of a technical PDU. And he’s going to cover in detail what this pre configured instance of Power Apps with Project for the Web will do for you as a Project for the Web user, and all the benefits that are around that. So be sure to sign up for that session. It’s available now and I chatted over the link, which you’ll find in the chat box there so you can register right away.
Kyle: And that does it for today. So once again, thank you, Fletcher. Thank you to everyone out there that joined us live for today’s session and the previous two. We hope you have a great rest of your day and I’ll see you back soon for the next live presentation. Thanks.
View the on-demand webinar recording
