We are continuing our column on the different tools that will help you improve your project management practices with Excel.
Today, we are going to focus on capacity planning graphs and how this prediction tool can help you better manage your projects.
How to create a Capacity planning graph
Now that you understand the purpose of capacity planning graphs, here's how to actually create one.
Important: The planned capacity of a person corresponds to the ratio between the numbers of hours that are planned for him/her compared to his/her total hours of availability. Here is how you calculate planned capacity:
Note: Microsoft Excel version 2011 was used for this article. If you are using a different version of Excel, or Google Sheets, the process remains the same.
Once you have calculated your planned capacity, highlight your data table then click on "Charts" in your toolbar, then click on "Line".
A graph will appear, right click on the graph and then "Select data". Go to "Series" and remove all departments except for the R&D department and Total Capacity (those are the ones we used in our example). In this example, we want to determine whether the R&D department can support another project. Then click on OK. Now you have your capacity planning graph.
Highlight your data table and click on "Charts" then simply select "Lines" and click on "Marked Lines".
If you are facing any other issues with Excel, please let us know or leave a comment below!
For more articles on Excel, please visit How to Create a Timesheet in Excel to improve your time management.