S Curve & Progress Tracking through Excel

What is S-Curve?

S-Curves are very important project management tool; they allow the progress of a project to be tracked visually over time and form a historical record of what has happened to date. S-Curve in construction project management industry is a display of cumulative costs, man-hours or other quantities plotted against time. The name derives from the S-like shape of the curve, flatter at the beginning and at end and steeper in the middle, which is typical of most projects. The beginning represents a slow, cautious but accelerating start, while the end represents a deceleration as the work runs out.

 

How to make an S-Curve?

Prepare a baseline schedule (schedule with planned start and finish dates, used as reference to compare with actual schedule) of your project with few activities, duration and planned start & finish dates. Assign weightage to each activity and it could be man-hours, cost or other quantities which represent the grade of that activity among others. The weightage allotment based on one of these criterions alone may not be so logical because the activity with larger effort (man-hours) cost cheap or costly activity could complete with lesser effort. Still this is the fundamental way and is followed every time.

Those who think differently could correlate the three project management triangle attributes (cost, schedule and scope) together in some logical way and allocate a better weightage value for each activity. I have used a tentative formula to correlate and calculate the weightage for the mentioned activities. Further these values were converted to fractions of 1000 (total value) to make next calculations comprehensible, as shown in the below table.

4762
untitled.jpg

Time scale selected along S Curve X-axis could be in days, week, two weeks or in months as per client or management demand. So the next stage in S curve preparation is to distribute the activity weightage through the time period chosen. X-axis start date could be conveniently selected, say the week day when you get the data/updates from the sites. Project start could be before or after the X-axis start date. In the illustration, considers that weightage is linearly distributed and no holidays in between. Formulas shall be used to calculate the (say with IF, AND/OR functions) weightage distribution for the selected time scale periods.

Once you tabularise the weightage for each activity, planned progress for these activities against these dates to be calculated by dividing the sums of each work period weightage with sum of whole activity weightage, say project weightage. Then percentile these obtained values to get the planned progress percentages. Adding up these values along the time scale provide you the cumulative planned progress percentages.  

4763
untitled1.jpg

**If you prepare the table & cell values in the similar way, the same formula shall be copied in your workbook and make the corresponding cell coordinate changes to get the required results. For example, the selected excel cell contains the below given formula;

=IF(AND(P$13>$C16,P$13<$D16),((P$13-O$13))*$G16,IF(AND(P$13=$C16,P$13<$D16),((P$13-$C16+1))*$G16,IF(OR(P$13>=$C16,P$13>=$D16),((($D16-$C16+1)*$G16)-SUM($H16:O16)),"0")))

In this, excel row#13 covers the timescale (dates) and the row#16 covers the cells for periodic weightages. So after copying the formula, only the row number for time scale needs to be changed. Say, the time scale row for your program table falls on the excel row#35; then replace the number13 in the whole formula by 35.

Upon starting the project, actual progress (physical progress in %) collected from site to be updated as per the time scale frequency and the multiplication of overall activity weightage with % progress will give you the consumed/actual weightage achieved as of date. Summing up the same for whole activities and dividing with project total weightage will provide you the project’s current actual percentage progress. Adding up the actual progress % values will provide you the cumulative planned progress percentages.  

4764
untitled2.jpg

Use the Insert line chart option in Excel to create the S-Curve plot and input the necessary data (right click & select data) for planned and actual progress in Y axes and dates in X axis. Screen shot shows the data selection process in the chart preparation stage. You may use Primary and Secondary Y axes to show both incremental and cumulative progresses. Initially both incremental and cumulative progress values will reflect as line chart and the incremental progress values could be made in columns/bars by right clicking on these values in chart area and selecting the chart type as columns.

4765
untitled3.jpg

If the project is for longer duration, day wise plot might look crammed up and generally week wise plot will give you better look. The timescale need to be changed to data date+7 and the set formula will feed you the weightage distribution automatically. But actual progress values need to be re-entered week wise. Subsequent table and S-Curve plot will look like as given below.

4766
untitled4.jpg

4767
untitled5.jpg

The chart area, line, bars and legends shall be customised to your requirement by using format options.

During the project advancement, the Actual S-curve will terminate at the Cut-off Date. This is the date the Schedule/Progress was last updated.

Analyses of S-curves allow us to quickly identify project growth, slippage, and potential problems that could adversely impact the project if no remedial action is taken.

Market Place

For 25+ years, APMX has been providing competency based project management training to Fortune 500 companies around the world, applying the principles of project based learning, designed to produce measurable results, generating a favorable “return on training investment”.
The aim of this training is to provide delegates with a fundamental understanding of the Guild of Project Controls Planning and Scheduling skills level at the GPC Preparatory level.
See how the TIME - LOCATION - VIEW brings clearness to your work programmes
Primavera P6 and Microsoft Project books, on-line video training courses and training material available from an internationally recognised publisher and PMI accredited REP. Teach yourself using on-line or book based learning or run your own in-house or public PMI accredited courses.