How to Display Baseline & Actual % Completed
Against the Time with a S-Curve Chart

 

 

Ms Project is a powerful project management tool to plan, monitor, control, analyse and forecast project’s status, progress and performance.


Ms Project has Earned Value Analysis that allows us to plot S-Curve chart displaying cumulative cost, labour hours or other quantities plotted against project’s time span. But I came across a question asked by my student, i.e. how to display S-Curve chart with percentage complete against the time, side by side comparing the progress between your baseline and actual completion.


In this tutorial we will show walk you through how to create a chart to display the percentage completion of Baseline Cumulative Work and Cumulative Actual Work against the time.


Here is the step: 


1. In Ms Project, select Report > Visual Reports > Baseline Work Report.


2. Click Edit Template button.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

3. At the Field Picker dialog box, select field Baseline Cumulative Work, click Add


4. At the Field Picker dialog box, select field Cumulative Actual Work, click Add.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

5. Then click Edit Template.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

6. The Visual Report is generated and opened in Excel Program. Select Assignment Usage worksheet. 

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

7. In Excel Program, change the PivotTable fields to as image below.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

8. Expand the Year, Quarter and Week fields. You may filter and modify the data as you want.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

9. For our case, we will:

  • Hide the Quarter column: right click anywhere at Year/Quarter/Week column, choose Show/Hide Fields > untick Quarter
  • Hide the value for Year 2019
Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

10. Your PivotTable will display as shown below.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

Now, we want to duplicate the table and change the values to percentage. Then do the following:


11. Copy the table and Paste As Value to the empty column on the right.

  • Delete the date for each week.
  • Change the Cumulative Actual Works Grand total to 5220
Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

12. Select cell in the first row of each column and enter the following formula.

For Cell I5:=C5/$I$23
For Cell J5:=D5/$J$23
For Cell K5:=E5/$K$23

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

13. Copy down the formula, and format the value to percentage style.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

14. Highlight the data and create a Line chart.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

15. Let’s modify the axis to change the maximum value to 100%.


Right click at the vertical axis, choose Format Axis.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

16. In Axis Options, change maximum value to 1.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

17. The chart has now showing Percentage work completed for Baseline, Actual, Planning against the time.

Display Baseline and Actual Percent Completed with S-Curve Chart in Microsoft Project tutorial

 Here are the sample files to download: Ms Project Sample, Excel Report Sample

 

By Chee SK   | LinkedIn

Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
64-2, Jalan Puteri 2/2, Bandar Puteri Puchong, 47100 Puchong Selangor, Malaysia. Tel: 603-80684461, Fax: 603-80684240
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka. Tel: 606-2835955, Fax: 606-2845955
Instant SSL