S CURVE in Excel (Table of Content)
What is S CURVE in Excel?
A type of curve that shows a graphical report of cumulative progress of a project with reference to time & the growth of a variable in terms of another variable, often expressed as units of time. It is helpful for real-estate builders & developers to track & implement different phases of the project in their ongoing building project, where it will help out to finish the ongoing project within scheduled time & with the allocated budget. It is also useful to track the project timeline and its costs in the IT & pharma company (With daily progress report).
You can easily keep an eye on Project progress on a daily or monthly basis with the S CURVE implementation (it’s a measure of productivity)
Cumulative Value of Work Progress with Reference to Time
In S curve of any building project, at the beginning, where work starts in a relatively slower pace, then it picks up slowly, in the middle phase you can observe the rapid pickup, and in the final phase, it gradually slows down, final work tasks go in a slower phase.
Similarly, in the case of the S curve for a new product launch & its progress, in the initial phase of the curve, the rapid initial growth of company sales for a new product can be seen, i.e. exponential increase in sales for a specific period time, later part of the curve, you can see a leveling off or taper off. This phase occurs when the population of new customers declines. At this point, you can observe negligible or slower growth rate and is sustained for a longer duration with an existing customer who continues to purchase or buy the product.
How to Create S CURVE in Excel?
Let’s look at a few examples on how to work on S CURVE in Excel.
Example #1 – S CURVE Creation for Building Project
In the below-mentioned table, I have project task list and amount allocated for each task in the building project in the column “C” & “D”, here I need to calculate the total amount of each task in that range (D3 to D6), in the cell “D7” by applying sum formula. i.e. =SUM(D3:D6)
Once it is done, we need to convert the amount of each task allocated to percentage allocation for computing & for S curve preparation. Now, enter the formula to get a percentage allocation for each task i.e. by dividing each task with the total amount allocated e.g. in the cell E3 type =D3/$D$7
4.8 (2,393 ratings)
View Course
Here D7 reference is made absolute or locked so that the formula is applied to other cell references.
The output values are converted to percentage format with format cells option.
Now, enter the details of the allocated task timeline for each task (in weeks) & its duration.
Now, we need to distribute percentage allocation of work on a weekly basis, prorated work percentage distribution for each week. Let’s apply for the first task activity in the cell “I3”, i.e. Allocation percentage for that task divided by total duration or timeline for that task (In weeks) =$E$3/$H$3
Here we need to lock both the cell reference in the formula or make it an absolute reference and drag it other colored cells (I3 to M3) to apply the formula. The output values are converted to percentage format with format cells option. Similarly, it is followed & applied to other 3 tasks allocation also, to get the correct distribution of percentage allocation of work on a weekly basis in a respective cell
After calculation of percentage allocation of work on a weekly basis, we need to gets the total for a weekly percentage of work done on a weekly basis (from week 1 to week 8) with help of sum function E.G. For week 1, let’s apply sum function for the total percentage of work done.
i.e. =SUM(I3:I6) for a week 1 total work progress
Similarly, step is followed until a week 8 work progress to get the distribution of percentage allocation of work on a weekly basis. Apply the sum function or formula to other cell references also i.e. (from I9 TO P9 cell reference), by dragging it till week 8.
Once we obtain weekly progress data, we need to calculate Cumulative progress for each week. Now, let’s calculate Cumulative progress, from the cell “I10”. In the cell “I10” add cell reference “I9”, and in the cell “J10” add the cell value of “I10” and “J9” to get the cumulative progress. Now you can drag or apply this formula till “P10” cell to get the cumulative progress for each week with reference to weekly progress
Now, I can use this cumulative work progress for each week to create an S curve chart. So to Create an S Curve chart, Select the cumulative work progress from week 1 to week 8 & simultaneously by pressing CTRL key to select the cells from week 1 to week 8.
Once both the cell ranges are selected, go to insert option, under that select line with markers option chart.
Now, you can carry out the formatting of the chart.
You can edit and change the title text of the chart to S CURVE. In the Vertical axis, we can change the vertical limit from 120% to 100% with format axis options, in the format axis change the maximum value under the bond option from 1.2 to 1.0
Simultaneously, we can change the data series name to cumulative progress, by right click on the chart area, select the edit option under legend entries. Now edit series window appears, in the series name box you can enter a cell reference of cumulative progress i.e. “C10”
Now the S curve is ready.
You can simultaneously plot another data series i.e. standard one (planned work progress) to compare with actual work progress to track how the project is moving on
Things to Remember About S CURVE in Excel
- It helps in the adoption of a new product launch & its progress rate.
- From S curve, you can also plat a graph for Actual costs against the planned budget cost for any project work.
- To draw the S curve, you can either use a Scatter Chart or Line Chart.
Recommended Articles
This is a guide to S CURVE in Excel. Here we have discussed How to Create S CURVE Graph in Excel along with Steps and downloadable excel template. You may also look at the following articles to learn more –
