How to Create S Curve in Google Sheets and Its Purpose in Scheduling

0
397
How to Create S Curve in Google Sheets

I have already written several tutorials on Info Inspired related to Google Sheets Charts and in that I’ve included S Curve too. But there is no dedicated tutorial for S CURVE as well as example to S Curve so far on Info Inspired. I hope this tutorial will cover that void spot. Here I will explain not only the steps on how to create S Curve in Google Sheets but also the basic scheduling part of it. At the end of this post I also explained how to create S-Curve in Excel. Either you should have a scheduled quantity with you or you should at least know what is scheduling in order to create S-curve in Google Doc Spreadsheet. Don’t worry. I have the sample data for you  and I will let you know how prepare data for S Curve too. Creating S curve is so easy if you understand the scheduling part of it.

S Curves are very useful in Construction jobs. My earlier experience with a well established MNC related to construction industries helped me a lot in writing this tutorial. Before going to tell you how to create an S-curve in Google Sheets, I believe, I should explain the below things connected with S Curve.

  1. What is S Curve in Project Management?
  2. The relation of S-Curve with Line Graph or Line Chart in Spreadsheets.
  3. How to Schedule for S Curve?

There is no free template available for creating S Curve. But you can feel free to make a copy of our finished S Curve for your project use. Just message me on the comments at the end of this tutorial for that. Saying that we can move to our tutorial section.

S Curve in Project Management and in Spreadsheet Solutions

S-curve is related to job scheduling and tracking progress of the scheduled job. S Curve or S Graph is an S shaped curve typically appears in project management. Basically it’s none other than S shaped Line chart.

The Relation of S Curve with Line Graph or Line Chart in Spreadsheets

The purpose of a line chart is comparison over time. If you have checked our chart selection tool, you may know about it. It’s applicable to S Curve too. But in S Curve, we use cumulative quantity or value to compare over time. The line chart gets an S shape in scheduling due to the cumulative quantity distribution over time.

Job Scheduling Part for S Curve

Now I should go little bit off topic. If you ask me what is the best tool for Job Scheduling, without any doubt, I can recommend you Microsoft Project and Primavera Project Management Tool. Both are different software from two different companies. The first one is of course from Microsoft and the latter is from Oracle.

What is the relation of these project management tools with S-Curve? Normally people tend to use any of the above tool to schedule their job systematically. But you can use spreadsheets too to schedule your job but not that much perfectly.

We can make a completion schedule of an assigned job with project management tools. I mean a month wise, fortnight wise or week wise schedule of job to complete it in an allotted or specified time frame.

Sample Data Preparation to Create S Curve in Google Sheets

Suppose I have a job in hand for completing a sub station work. The job may involve lots of activities related to it like mobilisation, supply of material, cabling, panel erection, testing and finally commissioning. See the sample data under Column A on the below image.

S curve Sample Data 1

To schedule this job, normally a planning person may first assign 100% weightage to total job and then distribute the weightage among all the job activities involved. The weightage distribution depends many things like the total number of manpower, time, priority, difficulty level, etc. required for the job. Then they assign time frame to complete the job.

Here in the above, I just put the time schedule against each job. That means the particular job against the date should complete in that month itself. But in real case each job can further summarise. For example you can add related jobs like excavation, structural work, cable laying and back filling under one main heading and add their weightage together. I didn’t do this here because I only took few items for example purpose and I can’t further shorten it. Also my main purpose is to give you an idea about the data which we are going to use for our S Curve. From the above we can get the data for our S Curve.

With a start date and end date we can easily create a GANTT chart from the above using Google Sheets itself. But it can easily be achieved using Primavera or MS Project in a dynamic way.

Now we have the data for S Curve. We should just make the monthly target weightage in cumulative as below to create S curve. We did it in column D.

See the above image. We require the Column B and Column D data to create S Curve in Google Spreadsheet. So hide rest of the columns. See the below screenshot.

Final Steps – S Curve Creation

Now select the entire B1:D16 data and click insert menu Chart. It will insert a chart. Don’t worry how the chart looks. It may or may not be perfect. In most cases the inserted chart will be S Curve. If not, you should change the below marked settings on the chart editor on your right hand side of the screen.

S curve customization in Google Sheets

Make sure that the Chart Type is Line Chart. Then click on X-AXIS and edit the range to B2-B16. It will create our perfect S Curve.

finished raw s curve

Now you can do the customization part yourself on the chart editor. If you want to show the entire date on X-AXIS select Treat Label as text on customization panel as above.

Thus we have finished our S Curve. This S-Curve is showing the scheduled quantity. Now you can track monthly progress against the above schedule. I have another detailed tutorial on it. Click HERE.

S-Curve in Excel

You can use the same data above to create an S-curve in Excel. There also select the entire data and go to insert menu -> Line Chart. It will automatically create the S Curve as the important thing is we have already have a proper data to plot.

S Curve in Excel

Conclusion

In this tutorial my main focus was to explain you how to create S Curve in Google Sheets. I’ve just touched the scheduling part to make you understand it. Please remember that I didn’t touch even 10% of the scheduling part above.

LEAVE A REPLY

Please enter your comment!
Please enter your name here