Editor's Note: The Following MVP Monday post is by PowerPoint MVP Glenna Shaw.
The challenge for every project manager is to keep their project on track, on time and on budget and the best way to accomplish this is to have up to date, reliable information and real time communications. While Microsoft Project is a great tool for managing large projects, for those of us working on smaller projects of 30 tasks or less, a well-designed Excel spreadsheet combined with PowerPoint can be just as effective. To that end, I’ve created an Excel Project Plan template that allows you to efficiently estimate time and costs, create the project schedule, monitor the schedule and budget and manage the resources and risks as well as document lessons learned. The template also includes the graphs to create PowerPoint dashboards for your project team as well as your project client.
Begin by downloading the Excel Project Plan template and opening it in Excel. Once the spreadsheet is open, enter the Date of the Report in cell B4. If you’d like the Date of the Report to always be the day you open the file, enter the formula =Today(). To create your project plan, click on the filter drop down for tasks. Check the box for Select All and click OK to show all the available rows for entering your tasks including blank rows.
Starting with cell A9, enter the tasks for your project and enter the assignees for each task. To estimate your time and costs, enter (in hours) the Optimistic Estimate (OE), Most Likely Estimate (MLE), and Pessimistic Estimate (PE) and enter the hourly wage for each task. Enter the Start Date for each task and the number of Workdays you want to allow for completion of each task. Once you’ve entered all task elements, click the filter drop down for tasks again and uncheck the box for (Blanks) and click OK. This will hide the blank rows in your project plan. Then click on the drop down for Start Date and select Sort Oldest to Newest and click OK.
Finally, you need to adjust the horizontal axis on the Project Schedule Performance chart to accurately display your project schedule. Right click on the dates at the top of the chart and left click on Format Axis. In the Axis Options, enter the first day of your project for the Minimum and the last day of your project for the Maximum and click close.
Your project plan is now complete. By entering the Actual % Complete and the Actual Hours for each task as the project progresses the spreadsheet will automatically calculate the performance of your project on any given day. It is important that you do not make changes to the other cells on the spreadsheet as these cells contain the industry standard formulas for calculating your project’s schedule and performance. Only make changes to cells with the light gray background. It is also important to note that while a task may be on schedule it can also be over budget if it’s taking more hours than estimated and vice versa. Negative values are displayed in (red). Daysahead are displayed in green.
With any project it also important to plan your resources and risks effectively. To plan for your project risks, click on the Risk worksheet tab at the bottom of the spreadsheet. Enter your risks, the percentage of probability of the risk happening, the numbers of hours of impact if the risk does occur and your actions to mitigate the risk if it does occur. The spreadsheet will automatically calculate the risk score showing you the number of hours your project may be impacted. Once you have all your risks identified, click the drop filter for Risks and uncheck the box for (Blanks) to hide the blank rows. You’ll note there’s a reminder on this worksheet to remember Reputation Risk. While we all want to please our clients, there’s always the risk an unhappy client may totally trash you in a variety of venues or in the course of pleasing the client your product is not of a quality that you want associated with your name. Since this can adversely impact future business it’s important that you acknowledge and plan for this risk.
Overloading yourself or a team member is one sure way of placing your project at risk of going over schedule or over budget or both. The Excel template automatically calculates the percentage of time you’ve assigned to each assignee and provides an easy to use chart to see your resource commitments for the project at a glance. Click on the HR worksheet tab at the bottom of the spreadsheet. Do not make any entries in this worksheet. All values are automatically pulled from your project plan. The only thing you need to do on this worksheet is adjust your horizontal axis options to the same minimum and maximum settings as you did for the Project Schedule Performance chart in the instructions for the Project Plan. To see the commitment for a resource, click the drop down filter for Assigned to and uncheck all the boxes except for the resource you want to see. The chart will automatically show the commitment for that person for the duration of the project.
The spreadsheet includes a worksheet for you to create a milestones chart especially for your client. Click on the Milestones worksheet tab at the bottom of the spreadsheet. Enter your project milestones, start date and number of workdays for each milestone. Filter out blank rows and sort your milestone table by start date just as you did for the project plan table. Adjust your horizontal axis options to the same minimum and maximum settings as you did for the Project Schedule Performance chart and the Resource Management chart. You now have a chart that clearly displays milestones along a timeline.
The project plan spreadsheet automatically accounts for U.S. Holidays through 12/31/2028. If you do not want a holiday excluded from your project schedule, click on the Holidays worksheet tab at the bottom of the spreadsheet. Delete the rows for any holidays you want to include as a workday, add rows for excluding additional holidays, etc.
As any good project manager will tell you, effective communications is the most important factor for the success of any project. At any time you must be able to effectively communicate with all stakeholders of the project including your client and team members. PowerPoint is the obvious choice for your communications allowing you to easily share relevant information with all parties. With PowerPoint you can easily use the same presentation to present in person, email a slideshow or post your slides online. By combining key elements of the project plan spreadsheet with PowerPoint you have an effective communication tool that can be kept up to date with a few clicks. Open both your Project Plan spreadsheet and a new PowerPoint presentation. Click New Slide to create a Content Slide in PowerPoint. Go to Excel, click to select the Project Schedule Performance chart and click Copy on the Home tab. Go to your PowerPoint slide, click to select the Content Placeholder, click Paste on the Home tab. This will automatically paste the chart in your PowerPoint. Click the Paste Options pop-up in the lower right corner of your chart and select Keep Source Formatting and Link Data. Now your chart is linked to your spreadsheet. For more about copying and pasting from Excel to PowerPoint, see this article on Office.com.
Use this method to copy and link the data from the project plan spreadsheet for all the charts you want to include in your presentation. You can also copy and link the data from any of the tables in your project plan spreadsheet.
To automatically update your PowerPoint slides, select any chart on any slide and click File, Info and on the right side of the window at the bottom, click Edit Links to Files. Change all the links to update Automatic instead of Manual. Now your PowerPoint will automatically update from any changes made to your project plan spreadsheet.
A key component of any project is documenting lessons learned. The project plan spreadsheet includes a method for you to easily record your lessons learned and reference them for your next project. Click on the Lessons Learned worksheet tab at the bottom of the spreadsheet. Enter your Lessons Learned, the type (Positive or Negative) and your future plans for each lesson learned. You can easily see at a glance which lessons learned are positive/negative for future reference.
The content of this article is derived from a session at The Presentation Summit titled Every Presentation is a Project. Project Management is a complex subject to cover in one hour, but by combining a preconfigured Excel template and PowerPoint anyone can manage a project more effectively. Experiment with the template as you see fit and find all the features that work best for your needs. Don’t be afraid to make mistakes, you can always download a new copy of the file. If you’re one of those folks who prefer to manage your projects “on the fly” the project plan template also makes an effective post-mortem tool. By entering the values after you’ve completed your project you can easily determine how effective your “winging it” strategy works (or not.)
Glenna Shaw is a Most Valued Professional (MVP) for PowerPoint and the owner of the PPT Magic Web site and the Visualology blog. She is a Project Management Professional (PMP) and holds certificates in Accessible Information Technology, Graphic Design, Cloud Computing and Professional Technical Writing.
The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager for Dynamics, Excel, Office 365, Platforms and SharePoint in the United States. She has been working with MVPs since her early days as Microsoft Exchange Support Engineer when MVPs would answer all the questions in the old newsgroups before she could get to them.
This is going to save me so much time, excellent work!
Can you help me change the horizontal axis on the WBS tab above the chart.
I've entered new date values in Column I, Axis options only sees the entries as numbers 40816.0 cannot force date display using Format Axis dialog in Excel 2007.
Worked with the values as they are in format axis dialog and it all works fine. This spreadsheet is very helpful for organizing. Thanks again for such a useful tool!
When I tried to downaload, it's asking for some id and password, please could you help me with this. I can be reached over on my email email@example.com
Thanks in Advance!!
I can not change the dates on the x axis. They stay at 40816 regardless of what I try.
What am I missing?
I've created a freely downloadable excel project plan that you can use to excelerate and improve your project planning... Here's a link to the blog article where you can access this free excel project template - www.mlynn.org/.../excel-project-planning-spreadsheet-updated-version-3
Hi, the link to download the spreadsheet template doesn't work anymore. Can you share the template with me via email at firstname.lastname@example.org? Appreciate your work and sharing this great tool.
It seems the link to the download is broken. Can I get it another way?
Wow, this is simply FANTASTIC! Thank you, you're the best...
A great piece of work. I am puzzled as to why you check the 'type' on the 'Lessons Learned' sheet ( =IF([@Type]="P",2,IF([@Type]="N",0,1) ) - why not just the straight 'If' statement?
Keep up the good work.