Table of Contents
The first target is project tracking. There are many diverse tools at our disposal to support this main goal. In Excel we have modeled a software development task and complemented it with some decision supporting tools.
The most important ones are: monitoring risk, shaping a work breakdown structure, issue tracking and creation of a Gantt chart. We already have the framework. Below you can find some ready-to-use project templates!
Phases of Project Management
Now let us see the key project phases and the project life cycle. The main goal is to improve control and quality. Here are a few words about what part tasks we should list in the specific phases!
- In the initiation phase we perform the following tasks: resources management, cost management, definition of goals, risk analysis and finally the approval phase.
- In the second stage (development phase) the main tasks are: creating WBS (work breakdown structure), setting up the budget and cash-flow planning. At the end of this phase we have to decide based on the collected data, if we should launch the project or not.
- If we get the green light, we can proceed with the most important phase. This is the execution phase that is about effective doing the work. The following key tasks must be done: progress monitoring, testing, resolving issues, creating templates, reports and forecasts.
- The fourth phase of is the ending (deliverance) of the project, all testing is done, and we analyze the tasks been done so far.
Project Status Template
Today’s featured project status template explains how to build a useful excel project management solution.
We have combined the flexibility of KPI traffic light indicators with the power of Gantt Chart. We can use also traffic lights to visualize the popularity of products further to measure the project status.
This is an easy task at first look but building of such template needs some attention. There can be some obstacles but if you read the tutorial carefully you can avoid them.
If you look at this figure you can quickly review how a project works. Actually, all projects work the same way.
Analyzing Project Tasks – Closer Look
Take a closer look at the Excel template. The List column contains the name of the task list.
In our example these are only marked as numbered Tasks.
Obvious that in real operation here must follow the activities that are divide the life cycle of the project into four main stages: concept phase, development phase, execution phase and transfer phase.
By specifying a completion rate between 0% (for a task that has not launched) and 100% (for a process that is completed), we can review projected progress to real progress.
The name of the next column is Start; it can be used to set the sub project’s start date. Duration of a project’s key element is the number of days it takes from the beginning of the sub projects starts to the point it is done.
The percentage complete (Done) fields provide the actual status of a task.
This Excel project status template help communicate the goals of projects. By specifying a completion rate between 0% (for a task that has not launched) and 100% (for a process that is completed), we can review planned progress to real progress.
Effective Issue Tracking Using Gantt Chart
Here is a zoomed-in photo of issue tracker. Now you can see a series of status colors on the main task. When we complete a task, we mark it as finished using blue marker. The grey will be used if the task is already running but not finished yet.
Now setting up the actual status using traffic lights!
We can use colors in status project status template to make the important information about project health. On the calculation sheet you can set up the ranges for red, yellow and green.
- If the key performance indicator is green the project is under control and impact of problems is minimal.
- If the indicator is yellow our plan may contain a few risks, but we have a strategy and experience to get back the traffic light to green.
- Red light is critical problem with time frame of project, cost calculations or the project is behind schedule.
On this picture we can see the structure of the control panel.
You can determine here for the ‘traffic light’ what color to use for what range. In this example from range 33% to 75% completion rate the light is yellow, and above 75% is green the status of the project.
This is how we would summarize the operation of a project.
Modelling a Software Development Project
Project management templates provide an overview procedure in the field of IT and software development. There will be very interesting topics discussed because software development isn’t that we design a product and within two weeks we develop it without any arising issues. There is no such thing!
We effectively support the work of the developers with the application of an Excel template. Also, can be its further advantage that it is a continuous feedback for the management. We should start with an interesting question.
Ticket status tracking
A ticket is such a warning sign (not always necessary means a mistake!) that indicates we have to reconsider a particular activity or a procedure. Imagine a software development studio where everyday activities are conducted.
After a module is done it is carefully checked and tested. Nobody expects things to be perfect for the very first time.
A ticket can have three statuses.
- Thew assigned means that we have recognized the mistake and forwarded to the development team for correction.
- In the corrected phase we have corrected the mistake, but it still needs testing.
- The closed phase is the best for all developers. The fixing and testing was successful so the only thing to do is to close the support ticket.
We’ve created the template from the ground up, so that all important information can be reached immediately.
Forms of risks and their effects in PM
Our goal is to create a project management template in Excel to inform IT managers about the actual status of the software development. Let’s say a few words now about the risks!
Software bugs can be categorized into the following groups:
- Critical bugs can lead to a breakdown of the application or data loss. A workaround the problem is impossible.
- High level bugs result in raise damaged functionality. A workaround won’t exist or is impossible.
- Mid-level bugs result in non-critical problems of the system. A sufficient workaround exists.
- As low identified bugs are of low or very low impairment. An acceptable workaround exists.
In this section we provide a possible solution for the display of mistake-signs current and past status with the help of data-visualization.
Add interactive controls for Project Management Templates
And now we got to the part we love the most, the design! We have tried to plan the four charts, so we get an insistent high quality and easily readable result. Let’s see the upper right corner!
This is a risk-status matrix. You can see the actual status of risk factors / mistake signs and the corresponding risks. We portray the possible effects using the previously mentioned four categories.
As you can see it on the picture we have portrayed the results of the 31st week. For simplicity’s sake we have applied a one-year period in the analysis.
With the help of a Scroll Bar (VBA form control) we can jump from week to week in the table.
It is very important to note that this part of the template shows WEEKLY information, the other three charts show accumulated values!
Analyzing and tracking the actual status of project
Let’s study closely the bottom right part! This also is a very interesting analysis. We can track the changes of support tickets’ status.
These are summarized values on the chart we show the changes between the 14th and 31st weeks. Look at the results of around 20th and 22nd weeks!
We can see the assigned and corrected type of support tickets was dominating.
But by the end of the 31st week the closed support tickets were in domination.
Risk Analysis – Create Breakdown Chart
Let’s see the percentage breakdown also! We have disclosed the four risk categories. But we haven’t talked about the most important thing yet.
We also have to analyze the status changes of the support tickets! The usual way is that if we find a mistake in the development then we assign that to the developers / testers, and after successful testing we close it.
With the help of the Excel project management template these little but essential information can be also tracked and followed.
The fourth chart is almost completely just like the previous one. There is only one important difference, here the quantity of the support tickets is indicated.
This also changes dynamically if we move the Scroll Bar.
Project Activity Template
The project activity template will show one of the most important fields of the project management, the planning and scheduling of the resources from a completely new view.
Previously we have acquired the concept of issue tracking and Gantt chart and now this is the time to take another step to make more complex solutions.
What does the phrase project activity template mean?
We know that when we perform business tasks we use up resources, materials or tools needed for manufacturing and finally, human resources and project risks.
We will pay attention to the synchronization of tasks / activities and human resources.
Scheduling is an important task and sometimes we don’t spend enough time with it, and this will ‘present’ us with 40%-50% additional charges at the end. On the picture below, we have listed all important information.
On the data worksheet you’ll find the well-known base table: name, activity, responsible person, start and end date.
We’ll be able to handle the data of 15 business procedures and 15 participating persons all together.
Only one question remains, how will we jam this large amount of data on only one page with the help of the template? Fortunately, Excel provides us with multiple options for this!
Tracking Activities – Projects vs. Human Resources
We can keep changing between the ‘Project’ and Human resources with the help of a switch, and now we’ll explain to you the main difference between them. On the picture below the project view is active.
To each project more tasks can be assigned to.
To summarize these, we can see how many tasks are extruded in a given period, thus we get a view of the peaks that can affect the whole business process.
So, we have to note that this view summarizes the tasks in a given time period.
We have to know about the HR-view that arises from its name, it focuses on human resources.
As you can see we have switched view, so now in the list on the left-hand side appears the names of the persons who participate in the execution of the tasks.
Why can this be interesting and at the same time also important?
We can establish the encumbrance of resources at once, so we can completely reorganize certain tasks.
If 1-1 resources work too many sub tasks in the same time and the project manager judge that as a risk than we can revise or modify that.
Visualize project activity using Excel Form Controls
We have not yet talked about those Form Controls that really makes the presentation interactive.
The first one is the Period Picker which task is to enable the switching between the daily, weekly and monthly views. If we would like to analyze the details then chose the daily break down, if we need a review / cumulative screen then we should use the monthly view.
A slider enables us to be able to handle even several months or even years of activity in a simple manner.
By clicking on the arrow on the slider’s right side we can display future information, and by clicking on the arrow on its left side there will be past information displayed.
Heat map is a data visualization tool that displays business connections by the help of colors. In our current example we displayed the lower rate of charging by light colors. We used darker colors in the periods where the rate of charging is large.
The later can occur because of two things: either too many tasks are running in the same time or one participant is working on too many different tasks at the same time. This way is understandable why the project activity map is so important, is it?
We have used the IF(), INDIRECT() and OFFSET() functions to create the template.
Everyone knows the first one which is a comparably simple logic formula. The other two requires deeper Excel knowledge. We have it in our plans to introduce their use in a separate article.
We hope you have taken liking in study the connections of the project activity map elaborately.
There’s nothing else to do than share with you the excel resource planner template related to today’s presentation.
Project Risk Template
Our project risk template will show you how to track and analyze risks. We don’t have to explain the risks linked to the project regarding business decisions. Using the now introduced free business spreadsheet we will utterly simplify tasks.
Scope of analysis
Let’s take a closer look at the operation of a company and relating risks. In the first column (Issue Description) we can see the factor that if it shows generates risk.
The second column (Department) contains the departments currently operating at the company.
The third column (Risk Impact) marks the risk factor on a scale from 1 to 10, namely the negative effect that will come about in case the risk would occur.
The fourth column is an assistant column, its function is that this has to be identical with the third column, it will help the creation of the template.
In the first analysis with a help of a very spectacular chart we will show how the sum of certain factors (Issue) influence the risk.
We will examine specifically the factors that cause the risk, for the time being we will not examine which department is the cause of it.
Let’s start the above presented macro application and highlight all the data board along with the headings. As soon as we are ready with the risk heat map, then we would briefly show how to interpret the received results.
It is visible that the greatest risk concerns the Sales department, but Marketing also has considerable significance. Let’s don’t forget that we are talking about Issue Level Analysis here so one department can play a role several times.
The function of the chart is that we can identify the most crucial factors regardless of department.
All in all, it can be said that the greatest risk factor / issue is the “Sales Issue 1”, it is enough to look at the chart and we see its risk impact is 7, the highest in the list.
According to the heat map the least risky operations are located on the right bottom corner of the square and their color is green.
Department Level Analysis
In the second step we summarized the risk factors of the operations of the departments. On this picture you can see the initial setup made for the heat-map.
Similarly, to the first step the assignment here is to start the macro and highlight all the three columns (along with the heading).
When we are done the heat-map after this will show a totally different picture, even though we are talking about the same data sheet, with unchanged values!
The risks added up on department levels and we can establish as a result of this the most endangered department is the Marketing, after this is Operations. Fortunately, there is a field where everything is working well and this is Finance.
The question is legitimate, why the first and second heat maps are showing such a different picture?
Let’s take the Sales department for example. Is should appear by the first heat-map that Sales is the troubled department. If we take a closer look we can see that very much average risk factors related to the “Marketing” department.
The second tree-map chart sums up these average values and according to these it is obvious that in the operation of the given company the biggest challenge is the “Marketing” field.
Naturally the here shown analysis is only a possible example. If you are a business analyst or a risk analyst, you have to look at the processes inside of the company and make such study.
Are you interested how the risk management template was made? The Risk Map Creator is a part of our excel data visualization add-in.
How to build the project risk template using conditional formatting?
In this section we’ll show you how to build excel risk template. We used an exclusive ID to categorize the issue in the tracker. The Description of Issue column should be populated with a description of the issue.
The priority column should be populated with the priority of the issue. Select from categories as defined by the risk management plan.
Effective choices include the following: High, Medium and Low.
Instructions for Changing the Contents of Drop-Down Menus:
Highlight the cell of which you wish to change the content of the drop-down menu. From the file menu click Data then Validation and you can change the content of the source field.
This is calculated once you select the priority and actual status. The matrix uses conditional formatting to display color.
The project risk template use risk ranking to determine matrix score. This is a priority (with weighted category) list which is determined by the relative ranking of the risks.
So, if you change the actual status of priority the risk score calculated automatically.
Issue Tracker Template
Issue Tracker Template is a visually oriented Excel project management tool which shows any project plan’s various tasks progress.
The template is separated into three sections: the Gantt chart area, the gauge area and the resources heat map. Gauges and speedometers are useful tools to measuring the actual project completion ratio.
Let’s get acquainted with some terms before we start the progress in detail.
It’s useful to know about them because they are essential in the field of project management and also throughout the project tracking process.
What does issue tracking means?
The most important thing that we must know about it is they conduct a continuous tracking and analysis of project related activities.
Activities have that characteristic that they executable parallel to each other. We’re going to talk about its setbacks at the creating of the resource heat map.
What is the Gantt chart?
Why is it essential to create it in the planning phase?
The Gantt chart has a double function: for one we record the steps of one key process so that the realization of it can be whole.
Secondly the project manager will be able to track the project plan.
We must not forget that it plays a major role in tracking of the realization plan – fact indicators.
Take a closer look to spot this project management report!
Issue Tracker Template – Overview
We could write down the creation process as follows: first we list all the activities, then we determine the relations between them (if there are any). The next step is to assign each activity the related timeline and resources. The last task is to draw the Gantt chart.
We can analyze and report task status. We can easily follow project events. It automatically detects the inserted values into the column and it shows them in percentage value, just place them according to our plan.
The task timeline is the key component of a project status report. Our issue tracker template shows the expected completion and actual completion.
We use color-code (green and red) in this section, which shows in different colors the tasks that are finished or on hold.
Visualize the Plan vs Actual variance using Gauge chart
On the summery screen placed on the left-hand side we can find very important information about the actual status of the project. Here’s the project’s start-, and end-date which we calculated based on all activity.
And we used a slider to be able to scroll in one step intervals the part of the screen containing the Gantt chart.
It is useful, so we can handle the whole project in space saving mode, doesn’t matter if its span is 30 days or even one-year long. The day by day portrayal provides detailed enough display.
With the help of a gauge chart we portrayed the completion rate you can find in the Overall Progress field, its values can be between 0% and 100%. Finally, the Days Required field shows the planned length of the project in days.
With help of the gauge chart we will visualize the completion of overall procedure. Project status report shows you a short visual description of the important elements of the project.
Resource Planning – Human resource heat map
The third part of the Issue Tracker is the resource heat map.
With knowledge of resources assigned to activities and based on the Gantt chart this section can be easily made.
There are many methods making the heat map, we will show you one made by us and thought to be simple.
We have enlarged here one part of the template for better understanding the logic of making the resource heat map. So, we want to know what kind of problems arouse if we have to do several sub-tasks each day.
On the bottom of the picture the color chart can be seen made by the classical conditional formatting.
Number 7 indicates all the sub-tasks we have to perform on that day according to plan.
For the other days we do an average of 6tasks.
By the help of the color chart we can see it in an instant on what days we may encounter some trouble because of lack of resources.
Just practice with our issue tracker template a little and create your own project management tool using Excel!
Project Timeline Template – Milestones
With the help of the project timeline template we will give you a tool which will make the project tasks administration very easy. In today’s story we will review this subject in detail.
In developing the Excel template, we used such visualization representation that adrift from custom. We would like to give you something useful, it is our hope that we can be successful again.
The milestone is an event that marks the overall end of a main process or processes related to each other.
The project milestone by its definition not related to duration and doesn’t require effort, doesn’t connect to work.
Actually, in the work plan it acts as a control flag that shows that some other job has been done.
Milestone is used as a control point of a project, used to approve advancement of the project and confirmation of the remaining work again.
For the management they use it as a snapshot for the approval of project improvement.
It is important to note that a lot of times reaching a milestone requires decision making.
Create Timeline using dynamic chart
In this section we’ll show you how to create a timeline in excel. We placed two variables on the dynamic timeline template.
At the left top side, you can set the number of timeline element you want to display.
If this parameter is set for 1, then we can trace the project form the very beginning, if the value is 5 than we see the project from the fifth step and so on.
On the right top corner, we can set how many tasks we can see at one time on the dashboard. On this picture we have maximized for 20 the number of tasks can be seen on the project timeline template.
You decide what is most appropriate for the given task, project or process.
In the middle we presented a period, its starting point is the beginning day of the first appearing task. Its closing point is the execution day of the last appearing task.
Project Tracking – How to Create the Milestone Plan?
It’s worth to pay attention to the following rules when creating a milestone plan:
- We should compose a maximum of 12-25 milestones; of course, the most important factor is the size of the project.
- The chosen milestones should cover the whole life cycle of the project.
- The planned period between the realizations of the given milestones should be about the same.
- While planning we should pay attention that it should be explicit, plain and simple.
- Lastly but not least, results / achievements of the milestones should characterize the nature of the project as far as possible.
Conclusion and Free Downloads
Project Management is a complex topic we had anticipated today’s articles as a teaser. We hope some of these novelties grabbed your attention and you will start creating a self-planned excel template!
There is only one advice for you worth considering. A lot of times it is enough to think our dashboard planning through before we would get to implementation right away.
Walk slowly, live longer – this is true also to Excel. We hope you agree that these project management templates were elegant solutions for various project related tasks.