次の方法で共有

PowerPoint 2010 and Excel 2010: Perfect Partners for Tracking Projects

  • [アーティクル]

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.

The Project Plan

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).  Days
ahead are displayed in green.

Risk Management

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.

Resource Management

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.

Milestones

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.

      

Holidays

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.

Communications

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.

Lessons Learned

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.

Conclusion

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.)

Author's Bio

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.

 

MVP Mondays

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.

Comments

  • Anonymous
    January 11, 2012
    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. Thanks

  • Anonymous
    January 22, 2012
    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!

  • Anonymous
    June 20, 2012
    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 jmjpgopal@yahoo.com Thanks in Advance!!

  • Anonymous
    December 16, 2012
    I can not change the dates on the x axis. They stay at 40816 regardless of what I try. What am I missing?

  • Anonymous
    April 27, 2013
    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

  • Anonymous
    May 27, 2013
    Hi, the link to download the spreadsheet template doesn't work anymore. Can you share the template with me via email at pinster82@yahoo.com? Appreciate your work and sharing this great tool.

  • Anonymous
    June 18, 2013
    Glenna, It seems the link to the download is broken.  Can I get it another way?

  • Anonymous
    March 08, 2014
    Wow, this is simply FANTASTIC! Thank you, you're the best...

  • Anonymous
    March 24, 2015
    Wow Thanks!

  • Anonymous
    May 13, 2015
    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?

  • Anonymous
    September 21, 2015
    Excellent work! Keep up the good work. Thanks!

  • Anonymous
    November 15, 2015
    IS this still available - i can not seem to download it.

  • Anonymous
    November 19, 2015
    unable to d/l the template from one drive... it said it's no long exist =(

  • Anonymous
    November 26, 2015
    All, you can download all my templates from glennashaw.com

  • Anonymous
    November 27, 2015
    The autor told me the file is available at glennashaw.com/templates but I got "the page will not load" message, Can someone try this link and see if it's OK to download there?

    • Anonymous
      December 10, 2015
      It does not work for me as well.

  • Anonymous
    December 14, 2015
    Unable to get to file download. Any chance to get access to the file. Thank you and really appreciate the help.

    • Anonymous
      December 14, 2015
      This is the message being received:QUOTEThis item might not exist or is no longer availableThis item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information.UNQUOTE

  • Anonymous
    December 22, 2015
    My apologies, all. It appears Onedrive is broken.You can download all my templates and tutorials from here:https://docs.com/glenna-shaw

  • Anonymous
    January 17, 2016
    I tried to download the template but it says it is no longer available. Is that the case. This looks like it would be perfect for the project I am working on. Thanks for your help.

  • Anonymous
    January 22, 2016
    the file is not available anylonger :(

    • Anonymous
      February 12, 2016
      The comment has been removed

  • Anonymous
    February 25, 2016
    The spreadsheet is no longer available for download, any chance it can be emailed to me?

    • Anonymous
      February 25, 2016
      Nevermind, I found it in the latest comments

  • Anonymous
    March 02, 2016
    Hello GlennaWhen I click your link on blog to download the Excel Project Plan template, it shows the One drive doesn't have the relevant template. Can you please send me the Project Template in excel to me.Thanks for assistance.Chetan BhatnagarChetan.bhatnagar@sage.com

    • Anonymous
      March 02, 2016
      Got it never mind.

  • Anonymous
    April 29, 2016
    Hi - I know this post is a few years old but would have loved to been able to grab the template. Looks like it's no longer available.

  • Anonymous
    July 02, 2016
    when downloading this template I get the following message please advise,This item might have been deleted, expired or you might not have permission to view it. Contact the owner of this item for more information.

  • Anonymous
    October 31, 2016
    I clicked on the MS-Excel template link, however, a messages states "item no longer available>Can someone send it to me please?