Project Dashboard and MS Excel – Developing Project Dashboard in Excel Sheets
June 24, 2010
The term “Project Dashboard” refers to a project tracking tool that provides a project manager with a graph view of the project progress, so that the project manager can see at a glance whether project characteristics are on track. Basically, sample project dashboard is a set of project charts and diagrams that help you review the current status of your project in real time. In the software market there are special project dashboard software products that allow you to build project charts and diagrams as well as project dashboard templates either automatically or manually. An example of project dashboard software that lets manually build project charts and diagrams is MS Excel. Today many people involved in project monitoring and control use project dashboard and MS Excel software to have the best chance of success.
In this article, we tried to depict several steps to create a project dashboard view in Microsoft Excel software. You can follow the steps listed below to build project dashboard charts and diagrams in Excel sheets.
To create your own project dashboard in MS Excel, please take the following steps:
- Use a Pen and a Sheet of Paper
Use a pen and a paper sheet to list the things that define the success of your project. Typically, there are three key success criteria (or constraints) that prove project completion: Time, Budget and Scope. When your project is delivered on time, your customer gets the product within the agreed deadlines. When your project is delivered under the allocated budget, you get no debts and financial difficulties at the closure phase. And when your project is accomplished within the planned scope, human and technical resources are used optimally and you get no overheads.
Thus, you need to write down a list of tasks that you want to track and monitor by using MS Excel project dashboard.
Time, budget and scope will be used as success-indicating units on the Excel dashboard. You will use the list of tasks to enter values and numbers in cells and rows to build a project dashboard XLS sheet.
- Collect, Sort and File Your Data
At this step, you need to collate all of the data required to estimate the success criteria you have determined at the previous step. By means of Excel project dashboard software you can collect the data and use cells and rows to organize the data into a project dashboard template. The software application gives you a wide range of formatting tools that let you edit and customize the view of your project dashboard template. Alternatively, you can use MS Word or any other text processor to develop similar templates.
The data will include timesheets, expense forms, calculations, risk / issue / change logs. Note that the quality of your project dashboard template example depends on the quality of the input data, so you should gather and investigate it carefully. In the MS Excel template, you can use sorting and filtering capabilities of the software to organize your data by filtering and sorting it into groups. When you are done, save the project dashboard template in an XLS file.
- Develop Your Project Dashboard Example
Now you are ready to use your MS Excel project dashboard spreadsheet to develop dashboard charts and diagrams. The software combines a number of different charts and diagrams that you can use for tracking your project. Creating project dashboard samples is a simple process that consists of the following steps:
- Time Chart. By means of project dashboard time chart, you can track time-relating attributed of the project and check whether the project is performed on schedule. In your MS Excel template, you need to add project tasks and identify the planned versus actual percent complete for each of the tasks. Use Charts function of MS Excel to summarize this data and find out whether your project is ahead or behind schedule.
- Cost Chart. By using project dashboard cost chart, you can find out if your project is under or over budget. Use your template and enter all of your project costs and then compare them with your basic budget. To do this, calculate the total cost amount of your resources, equipment, materials and any expense on administrative needs.
- Tasks. With help of project dashboard tasks chart, you can find out how much of the project has been done at this point in time. For this purpose, you will need to estimate the percentage of tasks completed to date. In your MS project dashboard template, list all of your tasks and use Charts capability to identify whether the tasks are “Not Started”, “In Progress” or “Complete”.
- Resources. Project dashboard resources chart will let you find out whether your resources are over-worked. In your Microsoft project dashboard template, list all of your resources and estimate how many calendar days they have been scheduled for your project. Then the amount of scheduled calendar days to the amount of actual days spent on doing tasks. If the comparison shows that the resources have been assigned to tasks for more days than they have available in their work schedule, then you have allocated too many tasks to the resources and you need to reduce their workload.
By following these tree steps, you can develop your sample project dashboard to track projects and view tasks. A fully populated dashboard for your project will tell you whether you’re on track. It’s recommended using MS sample project dashboards in daily practice to have the best chance of success. Note that you can download free project dashboard samples and templates on the Web.