WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Project Management Template in Excel

Project Management Template in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel Project Management Template

Project management is one of the important roles in any organization. You cannot simply track all the activities in the project just by hand; you need some software to help you in managing the project better.

MS Excel is one of that software that can help you in tracking the status of the project with easy templates in place. In this article, we exclusively show you to use excel to prepare project management templates in excel.

Project Management Template in Excel

How to Use Excel to Prepare Project Management Templates in Excel?

Below are the examples to prepare project management templates in excel.

Example #1 – Excel Project Management Template by Using Gantt Chart

Gantt Chart is one of those charts in excel, which is very helpful in reviewing the tasks of the project, current status against set standards.

When the project is up and running after a week as a project manager want to know the status of the project, whether it is in line or not. From the start of the project to the end of the project at each stage, you can track the project stages.

Assume you are an event management company and you had taken a very important project which is very lucrative for you. But the only thing is you need to execute the tasks based on the agenda given by the organizers. Consider below is the agenda given by the organizers.

Project management Example 1

Now let us create an Excel Gantt Chart to track all the activities.

Step 1: Select Only Start time and insert a column bar chart. Go to the INSERT tab and insert the Stacked Bar Chart.

Project management Example 1-1

Step 2: Now, your chart must be looking like this.

Project management Example 1-2

Step 3: Now, right-click on the chart and choose Select Data.

Project management Example 1-3

Step 4: Now, in the below window, choose Add.

Project management Example 1-4

Step 5: In the below window, in the Series name, choose Minutes, and for series value, select data from C2: C14. Click on OK.

Project management Example 1-5

Step 6: In the below window, select the EDIT option on the right-hand side for our Horizontal Axis Labels.

Project management Example 1-6

Step 7: Now select horizontal values as our tasks. Click on OK.

Project management Example 1-7

Step 8: Your chart should be looking like this now.

Project management Example 1-8

Step 9: We need to make some formatting to make it look better. Select the horizon series values, i.e., time value. Right-click and choose to format Axis.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Project management Example 1-9

In the Axis, formatting applies below values.

Project management Example 1-10

Step 10: Now, the time format will look like this in the chart.

Project management Example 1-11

Step 11: Select blue colored bars, right-click and select, format data series.

Project management Example 1-12

Step 12: Go to FILL and select NO FILL.

Project management Example 1-13

Step 13: Now coffee-colored bars and click on format data series and go to FILL and choose the different colors as per your wish.

And your chart is ready to take over the task, and you can manage the project better now.

Project management Example 1-14

Example #2 – Sales Cycle Tracker Example

Now we will see how to create a sales cycle tracker with excel. Consider the below data for this example. Data includes Sales Starting Date, Ending Date, Target, No., of Days Completed, Current Achievement and Variance, and many other things.

Step 1: First, mention Target & Current Sales. Arrive variance value, i.e., Target vs Current.

Project management Example 2

Step 2: Now arrive Project Completion %.

Project management Example 2-1

Step 3: Now mention Sales Starting Date & Ending Date.

Project management Example 2-2

Step 4: After this arrives, net workdays between a start date and end date.

Project management Example 2-3

Step 5: Now arrive Target Per Day by dividing the Target with No., of Days Available.

Project management Example 2-4

Step 6: Now, arrive at the current date by applying the TODAY formula in Excel.

Project management Example 2-5

Step 7: Now calculate no., of day’s completed from start date to the current date.

Project management Example 2-6

Step 8: Arrive as per daily target what should be the current revenue.

Project management Example 2-7

Step 9: Now calculate Avg Per Day Achieved so far.

Project management Example 2-8

Step 10: Calculate if the average trend sales continue for the rest of the project, what would be the end revenue.

Example 2-9

Step 11: Now, arrive achieved percentage if the same trend continues.

Project management Example 2-10

Now we have completed our calculation part. Using this, let’s create some visual excel charts which can help us track the project by just looking at the chart.

Step 1: Let’s create a simple column chart first. Select the cell B6 and create a column chart.

Project management Example 2-11

Step 2: Right Click on the chart and choose Select Data.

Example 2-12

Step 3: Now click on ADD.

Example 2-13

Step 4: Now, in the below window, don’t select anything, just press the OK.

Example 2-14

Step 5: We have a chart like this now.

Example 2-15

Step 6: Now select the bar and click Format Data Series. On the right-hand side, you will see the Format Data Series dialogue box.

Example 2-16

Step 7: Make the series overlap to 100%.

Example 2-17

Step 8: Now, both the columns are overlapped, and the chart looks like this.

Example 2-18

Step 9: Now change the color of the red bar to NO FILL.

Example 2-19

Step 10: It is looking like this.

Example 2-20

Step 11: Draw a round shape just below the chart and adjust it.

Example 2-21

Step 12: Add chart title.

Example 2-22

We have one chart ready. Similarly, create the second chart as well for Achieved % at the same trend.

We have two charts that look like this now.

Project management Example 2-23

You just need to update your daily sales numbers in cell B3. Everything will change automatically for you.

If you observe the template now as on 14th Feb 2019 total of 33 days are completed, and the current should have been 1.51 lakh USD, but the actual revenue now is 1.1 lakh, i.e., 22% of the overall revenue.

Target per day, in the beginning, was 4587 USD (B11 cell) per day, but as of today, the achievement is 2894.74 USD per day (B16 cell).

If the same trend of 2894.74 Per day continues for the rest of the period of the project, it will yield you 2.53 lakh USD, which is 50.67% of the targeted revenue at the start.

Using this Project management template, you can track your daily sales Target vs. Actual revenue.

Things to Remember About Project Management Template in Excel

  • Charts will automatically charge if you change the manual entry columns.
  • You can create a Gantt Chart for activities where tasks are followed by one after the other.
  • In the case of different project management templates, you need to identify the tasks and line of activities involved in the project.

You can download this Project Management Excel template here – Project Management Excel Template.

Recommended Articles

This has been a guide to Project Management Templates in Excel. Here we discuss how to create a Project Management Excel Template along with practical examples and a downloadable template. You may learn more about excel from the following articles  –

  • Excel Grouped Bar Chart
  • Excel Stacked Bar Chart
  • Inventory Template Excel
  • Project Timeline in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More