Percentage of completion is a method of measuring the progress of a project or a task based on the amount of work done compared to the total amount of work required. It is often used in accounting, project management, and other fields where the performance of a long-term contract or a complex task needs to be evaluated.
In this article, we will explain the basic theory of percentage of completion, how to calculate it in Excel using different methods, and how to use an Excel table to display the results. We will also provide a scenario to illustrate the application of percentage of completion with real data.
The basic formula for percentage of completion is:
The work done and the total work required can be measured in different ways, depending on the nature of the project or the task. Some common ways are:
- Using the cost incurred and the estimated total cost
- Using the hours worked and the estimated total hours
- Using the units produced and the estimated total units
- Using the milestones achieved and the total number of milestones
The percentage of completion can be used to:
- Estimate the revenue and the expenses of a project or a task based on the progress
- Monitor the performance and the efficiency of a project or a task
- Identify the risks and the issues of a project or a task
- Report the status and the results of a project or a task to the stakeholders
How to Calculate Percentage of Completion in Excel
In Excel, we can use different functions and formulas to calculate percentage of completion based on different methods of measurement. Here are some examples:
Using the Cost Incurred and the Estimated Total Cost
Suppose we have a table that shows the cost incurred and the estimated total cost of a construction project for each month:
| Month | Cost Incurred | Estimated Total Cost |
|---|---|---|
| Jan | 10,000 | 100,000 |
| Feb | 15,000 | 100,000 |
| Mar | 25,000 | 100,000 |
| Apr | 35,000 | 100,000 |
| May | 50,000 | 100,000 |
| Jun | 70,000 | 100,000 |
To calculate the percentage of completion for each month, we can use the following formula in cell D2 and drag it down:
=B2/C2
To format the result as a percentage, we can select the cells in column D and click the Percentage button on the Home tab.
The result will look like this:
| Month | Cost Incurred | Estimated Total Cost | Percentage of Completion |
|---|---|---|---|
| Jan | 10,000 | 100,000 | 10% |
| Feb | 15,000 | 100,000 | 15% |
| Mar | 25,000 | 100,000 | 25% |
| Apr | 35,000 | 100,000 | 35% |
| May | 50,000 | 100,000 | 50% |
| Jun | 70,000 | 100,000 | 70% |
Using the Hours Worked and the Estimated Total Hours
Suppose we have a table that shows the hours worked and the estimated total hours of a software development project for each week:
| Week | Hours Worked | Estimated Total Hours |
|---|---|---|
| 1 | 40 | 400 |
| 2 | 60 | 400 |
| 3 | 80 | 400 |
| 4 | 100 | 400 |
| 5 | 120 | 400 |
| 6 | 160 | 400 |
To calculate the percentage of completion for each week, we can use the same formula as before in cell D2 and drag it down:
=B2/C2
To format the result as a percentage, we can select the cells in column D and click the Percentage button on the Home tab.
The result will look like this:
| Week | Hours Worked | Estimated Total Hours | Percentage of Completion |
|---|---|---|---|
| 1 | 40 | 400 | 10% |
| 2 | 60 | 400 | 15% |
| 3 | 80 | 400 | 20% |
| 4 | 100 | 400 | 25% |
| 5 | 120 | 400 | 30% |
| 6 | 160 | 400 | 40% |
Using the Units Produced and the Estimated Total Units
Suppose we have a table that shows the units produced and the estimated total units of a manufacturing process for each day:
| Day | Units Produced | Estimated Total Units |
|---|---|---|
| 1 | 100 | 1000 |
| 2 | 150 | 1000 |
| 3 | 200 | 1000 |
| 4 | 250 | 1000 |
| 5 | 300 | 1000 |
| 6 | 400 | 1000 |
To calculate the percentage of completion for each day, we can use the same formula as before in cell D2 and drag it down:
=B2/C2
To format the result as a percentage, we can select the cells in column D and click the Percentage button on the Home tab.
The result will look like this:
| Day | Units Produced | Estimated Total Units | Percentage of Completion |
|---|---|---|---|
| 1 | 100 | 1000 | 10% |
| 2 | 150 | 1000 | 15% |
| 3 | 200 | 1000 | 20% |
| 4 | 250 | 1000 | 25% |
| 5 | 300 | 1000 | 30% |
| 6 | 400 | 1000 | 40% |
Using the Milestones Achieved and the Total Number of Milestones
Suppose we have a table that shows the milestones achieved and the total number of milestones of a marketing campaign for each quarter:
| Quarter | Milestones Achieved | Total Number of Milestones |
|---|---|---|
| Q1 | 1 | 4 |
| Q2 | 2 | 4 |
| Q3 | 3 | 4 |
| Q4 | 4 | 4 |
To calculate the percentage of completion for each quarter, we can use the same formula as before in cell D2 and drag it down:
=B2/C2
To format the result as a percentage, we can select the cells in column D and click the Percentage button on the Home tab.
The result will look like this:
| Quarter | Milestones Achieved | Total Number of Milestones | Percentage of Completion |
|---|---|---|---|
| Q1 | 1 | 4 | 25% |
| Q2 | 2 | 4 | 50% |
| Q3 | 3 | 4 | 75% |
| Q4 | 4 | 4 | 100% |
How to Use an Excel Table to Display Percentage of Completion
An Excel table is a useful tool to organize and analyze data in a structured way. We can use an Excel table to display percentage of completion in a clear and concise manner. Here are some benefits of using an Excel table:
- It automatically applies formatting and styles to the data
- It allows us to filter and sort the data easily
- It creates dynamic formulas that adjust to the changes in the data
- It provides total rows and columns to summarize the data
- It enables us to create charts and pivot tables based on the data
To create an Excel table, we can follow these steps:
- Select the data range that we want to convert into a table
- Click the Insert tab and choose Table
- Check the option “My table has headers” if the data has headers
- Click OK
The data range will be converted into a table with a default style and name. We can change the style and the name of the table from the Design tab that appears when we select the table.
We can also add a total row to the table to show the overall percentage of completion. To do that, we can follow these steps:
- Select the table and click the Design tab
- Check the option “Total Row” in the Table Style Options group
- Click the cell in the total row under the percentage of completion column
- Choose Sum from the drop-down list
The total row will show the sum of the percentage of completion values. We can divide this sum by the number of rows in the table to get the average percentage of completion. To do that, we can follow these steps:
- Click the cell in the total row under the percentage of completion column
- Edit the formula from
=SUBTOTAL(109,[Percentage of Completion])to=SUBTOTAL(109,[Percentage of Completion])/ROWS(Table1) - Press Enter
The total row will show the average percentage of completion for the entire data set.
We can also create a chart to visualize the percentage of completion over time. To do that, we can follow these steps:
- Select the table and click the Insert tab
- Choose the chart type that we want from the Charts group, such as a line chart or a column chart
- Adjust the chart title, axis labels, legend, and other options from the Chart Tools tabs that appear when we select the chart
The chart will show the trend of the percentage of completion for each period.
