Comparing Reports and Dashboards

Joseph Phillips
A free video tutorial from Joseph Phillips
4.5 instructor rating • 32 courses • 407,170 students

Lecture description

What’s the difference between a report and a dashboard? Is there a difference? Of course – but why should you care. In this lecture we’ll dive into the basics and build a good foundation of the primary goal of this course: to create reports and dashboards.

Reports are packaged and formatted information you share with stakeholders: management, the project sponsors, the project team, customers, and other stakeholders that need the information on a regular basis. Reports can be automated and that’s one of our goals in this course.

Dashboards are consoles of information. As you enter data into Microsoft Excel you can create a custom, single-worksheet that displays the results of your project performance. We’ll create a dashboard later in this course.

Learn more from the full course

Microsoft Excel for Project Management - Earn 5 PDUs

Mastering Microsoft Excel for Project Management: Controlling, Tracking, and Reporting

04:34:22 of on-demand video • Updated March 2016

  • Design reports for your stakeholders
  • Create a dashboard with some common project management requirements
  • Improve project communications with charts
  • Format, change, and edit charts
  • Apply conditional formatting to save time and automate reporting
  • Implement the Microsoft Excel Camera Tool
  • Create a speedometer chart for reporting
  • Claim five (5) Professional Development Units with PMI
English [Auto] Let's talk about reports in dashboards one of your primary focuses is a project manager using Excel will be to create a report or a dashboard to communicate information about your project a report intent is to report information to communicate project information. It also provides some data for viewing and analysis. Now a report doesn't lead a reader to a conclusion you don't want to do a sales pitch in your report. We just want to give facts. We just want to give work information on how the project is performing and then the reader makes a judgment on the information provided. Now I know in your organization that may be a little bit different that you want to tell the reader how the project is performing and that's you know you know with sometimes a spin on that we're doing great but we want to be honest in our reporting. So that's why we say that we don't want to leave the reader to a conclusion especially a false conclusion. Some examples of reports that you probably will do as a project manager a status report. What's the status of the project. What activities have been done today. What activities are left to do. What did you do between the last report and this report what risk issues have happened or passed or been resolved or what new risks or issues have entered the project. What's the cumulative cost. What's the remaining budget. Things like that. All of that will go into a status report a variance report is when you have a difference between what was planned and what you actually experienced. So a variance is just typically in an exception sometimes are called exception reports because you're showing that we have an exception to the baseline. So we have a time or schedule baseline a cost baseline and we also have a scope baseline a scope baseline is our project scope statement. The work breakdown structure in the WTS dictionary and so then that one you probably are going to have big variances because you're going to go through change control typically variance reports we're talking about time and cost. You might be creating a work remaining report. So this is what we've accomplished but these are the activities or the major deliverables yet to do. So it's like a burn down report of how much we have left to do in proportion to how much time and money we have left another type of report is a risk and issue report. Now a risk is an uncertain event or condition that can have a positive or a negative effect on our project. So risk are really we're talking about opportunities and threats. Now at issue is a risk that has already happened typically a negative risk that's happened. We have an issue in the project something broke the software didn't act the way that you thought it was going to or resource quits. All of those are risk before they actually happen once they happen you have an issue. So you might have a risk and issue a report to explain what's happening in your project. So how often do you do a report. Status reports typically Weekly a variance report. Maybe when you have a variance or maybe once a month and then work remaining risk in issue reports. These are you know there's really no set rule work remaining Maybe you do that as part of your status report or you do it once a month. Risk an issue you may do it once a month or every other week. Although some organizations may require you to do that in every report you turn in so what's a dashboard. A dashboard provides quick views into your eyes a KPI is a key performance indicator. Typically you have three KPI time cost in quality or you might have time cost scope. Either way it's a visual representation of your project so you can see in this little dummy dashboard here the amount of time in relation to the work or the different time periods of the project. The different countries where you've spent money and then you can even go into you know some variables that you added over here some top stories that may be relevant. So maybe you have links doesn't have to be a news story but links to like your project files with the Excel documents or the charter or what have you. You might have some maps in here especially if you're working in you know multiple locations you could add a little HDMI map and you know some other charts and whatnot. Now a dashboard is just two things. One it's dynamic where report is static. You do a report you do a Save As he hit print and you're done. A dashboard is dynamic so that as you change the data behind the dashboard this dashboard is updated and it does lead us to conclusions pre-defined conclusions for the reader. So show me exactly what the revenue is. Show me the sales. Show me how much time we have left. So dashboards that are dynamic and we're going to be making a dashboard in this course now in Excel you can build reports and dashboards. But before we get into building it you really need to do some needs analysis what do your stakeholders expect from you as far as a dashboard or reporting. So up front you want to do some planning just like all areas of project management. It's best to plan before you hop in and do so you need to define what type of data will you need and then how will you get that data. So what are your data sources. So do some planning and then we can get in and build an actual dashboard. So preparing to build that dashboard What's the goal. So define the message what we want to do this establish the audience who's going to be looking at the dashboard or the report. What are your KPI is your performance measurement time cost scope quality but you might have some other variables on that like amount of monies in relation to how much time or how many activities are left or an estimates based on current work utilization. So all of that should be defined as you go in to building a dashboard. And then also where will you get the data from is it all best guess or do you have hard data from your project team and vendors. So the data source and the accuracy of that data is needed. You can do a dashboard mock up I think this is a really good exercise. I did this in PowerPoint but you could go and just sketch it out on paper or use Vizio or what have you but just kind of map out what you see the dashboard being. So for example I put in here the project status and just some quick info on how complete is the project. How much time is left in the project. How much budget cumulative cost. Things like that. My editor Riskin issue a cost base line and a schedule baseline and then you might have an overall project helf like I have up here based on CPI and SBI and earned value management. So you just kind of mock it. You dont have to do what I've drawn out here but just mark it out and that will help you create the dashboard later and also help you to define your data sources. How are you going to collect that data. So how will you enter the data into Excel and then how often will you refresh that data fairly refreshing data once a month it's going to get really stale. So you want to have a good balance between too much and too little so maybe weekly or every few days of when you go in or update your dashboard data. Also how reliable is the data again. Is this actual factual or is this best guesses. So you need to have some idea of the reliability of the data and then communicate that to your stakeholders because if you have stakeholders have a wrong idea of where this data is coming from or the validity and accuracy of the data. It's really going to cause some problems in our project. We're going to have a communication issue because obviously we're going to have two different ideas about what the data is and where it comes from. So we need to be clear on that. And then we need to reflect that accuracy reliability the validity of data in the dashboard then we need accuracy. So that's me giving my editorial here accurate data is better than estimated data some principles in designing your dashboard. You want to keep it simple. If you've taken any of my other courses you know that I'm a big advocate that simple is best. It's easier to manage. It's easier to view data. You don't want to cram a bunch of stuff in Excel. Like most things in life just because you can doesn't mean you should want to have one dashboard per screen or would be too busy so we don't put too much information for the reader to look at and then only have dashboard information. And what I mean by this is you put your data on other worksheets. You put the results of that data on the dashboard. And that's what we're working towards. And this course we'll be doing a lot of exercises coming up where we're going to have data on one sheet and then we'll begin to create a dashboard of the results of that data on another. And you know no fancy formatting it did I don't know you just don't need it management. They just want the facts don't need all the pretty colors and clip bar. And all of that kind of nonsense so just get rid of it. So avoid the colors. Remember people are printing out it's probably grayscale if they're pretty in color. Good for them. But you want to use a minimal amount of color if necessary. I'm not a big fan though we're going to talk about it about borders and backgrounds in our charts things like that. Overall no. And then forget all those little doodads and sparkles and whatnot in Excel. So I'm talking about all the 3-D effects and the shadows and the glow that stuff just takes away from the message it looks amateur hour. Don't do it. And guess what I think about clip art in project management dashboards. No no no we do not put clip art in a report or on our dashboard. This is serious business. It just looks like amateur time. It takes away from the value of the report. So no. No clip art throughout this course will be making a lot of chart. So if you're new to charting this will be great if you've been experienced and charting. I hope you pick up some some new things. I'm sure you will I've got some secrets coming up here about charting that are pretty cool. So how to make good charts where you focus on the data. Get rid of the grid. Get rid of the border. Trend lines are actually I think really distracting. Too many labels even need a legend like in this example the early need that legend maybe maybe not. Probably don't need the legend and the labels but it put them both on there for example. And then we want to have an access for value only can add multiple axes in Excel. But if you start adding more and more it just gets confusing as to what are you charting. So I like a couple of times we use more than one access but as a general rule one chart one axis and we'll talk more about that coming up when we begin to create some charts on your dashboard we also want to think about how the reader looks at the information. If you ever pick up the newspaper and you open the newspaper you're going to read the newspaper or magazine. Most of us that are you know speak English. You start in the upper left hand corner. And as you are I moved to the lower right hand corner. It's less and less important information. Go to the next page you started the upper left and you skimmed the lower right. So this one two three that you see here one means that's what you see first. That's really valuable information too is like you know you're more prominent or slightly less prompt I should say than what you see first and then anywhere's the three is butyl least important. So on our dashboards we want to keep the semi what's most important down to what is least important. It's important also to format the text properly so it's not distracting from the message. So in numbers you need a comma four thousand five hundred sixty seven needs a comma decimal places especially if you're in a really large project. They just kind of get in the way. So round up or round down and then money you need a dollar sign or a euro or a yen or whatever appropriate for all your projects taking place. But refl. money with the appropriate symbol and then often large numbers it's best just to format them as with a K for thousands or an M for millions. So it's just quick and easy to read because you're just communicating information. If our stakeholder needs more detail then we can dig behind the scene and pull that information out. Dashboard titles and labels just enough is enough give quick info we don't want to belabor the point and then it's always important especially on reports to give a date and a time stamp because if you're giving the same report every week they're going to start to look the same. So you want to put a date on there or a time stamp on there so week to week it's easy to see the collection of these reports. Finally I want to document the dashboards and reports so add a little tab with the any info or notes I like to use comments as you'll see as you can right click and add a comment about what that cell or group of cells will do in Excel and your worksheet and then you can also provide a model map to summarize key ranges. So a model map is just a way of saying this data used in these places throughout the worksheet. All right that's good. Great job knocking out this first lecture about building a dashboard or report.