Creating an S-Curve Chart in MS Excel

[Music] our demonstration on how to create an s-curve in ms excel so before we demonstrate how to create an s-curve maybe it would be best to describe what an s-curve is for those who are not familiar what an s-curve is for a place so an s-curve is a very common project management tool wherein it's used to check or to depict the utilization or the usage of certain quantities like work hours or man hours or costs over a period of time as you could see here in our example if i am going to analyze this s-curve what it is me is that this project will last for nine weeks and the cost will start of course being utilized on week one with a very low utilization only at point seventy one percent but as we go along throughout the duration of the project the cost is being utilized like by week two one point forty 1.43 of the budget or the cost is now being used week 3 it's already going up around 15.71 and so on and you will see that the project will eventually start using a big amount of its cost or budget around week four and then by week seven we've already utilized around 90 percent of our budget by week 8 and week 9 of course 100 of your budget should have been utilized so it somehow tells me how fast or how gradual costs or in other s-curves maybe man-hours are being used by the project if we create a very steep s-curve what it means is that the cost or the man hours are being used immediately or very fast that's why the s-curve is very steep this one is a very gradual very well-distributed s-curve and it usually tells the project manager how the budget is being allocated over a period of time now programs like ms project or other software used for project project management can already create s-curves but sometimes we don't have those programs so we are limited to probably a worksheet tool that can create graphs and that is what we are going to discuss how to make an s curve in ms excel a very widely used program for creating charts step of your creation of s-curve is of course create the different stages or different phases of your project so here i already have a created table wherein it's divided into five stages so we have the planning stage the acquisition stage and assembly manufacturing delivery and this is my projection of how the cost will be utilized on each of the stages so this is up to the project manager on how he or she will plot the total costs of the project on each stage and then the next thing that you need is a start and an end column wherein it tells you the duration of the stages and then this optional duration column so not really needed but it will help in plotting your s-curve later on now the first step in our s-curve is to get the total cost and that is by adding all those costs per stage so it's done by using a simple function and this tells me that the total cost for the project is 7 million now the next thing is i want to check the percentage that each stage will consume from the budget or the total cost so i will say that this is the percentage weight probably and i will calculate it such that i will get the cost for that stage divided by the total cost of the project and then i will make the denominator or the 707 million absolute by using f4 or by manually putting dollar signs leaving the numerator as a relative cell no need to lock i will format it as percentage and maybe increase the decimal and then i will drag this one or click it in order to see how much percentage of the budget is going to be used by each stage of the project the next part of our s-curve is to plot the weight according to the week so i'll start by putting here week one and then drag your header until week nine because that's the last week of our project and then next is i'm going to plot the percentage that the weight is going to be distributed among those weeks so for example this stage is plotted for week one until week two so what i will do is i will start at week one get the weight for this stage and divide it by the duration okay and then i will lock the columns so that if i plot it it will not move so i will be manually locking column c and column f enter and that means that by week point seventy one percent of the budget should have been utilized and that should be the same with the second week and as you could see here what happened is that the weight 1.43 has been distributed evenly between week 1 and week 2 because that is the weeks that this stage is expected to happen now i have to do the same for the other stages so for example here we have week three to week five so i will plot starting from here week three and that would mean or that would be the weight divided by the duration which is three weeks again i will lock the column letters so that when i drag it later on it will not move to another column so i will drag it until week five and that means that evenly it will be 14.29 percent of the budget will be used by week 3 week 4 and week 5 for the acquisition stage now i will do the same for the rest so let me do this so weight divided by the duration this one is for week four to week six right this one is for week five to week eight so this divided by four weeks so i will again put dollar signs this one is from week five to week eight next is just one week week nine so that means i don't have to do anything but just simply get that amount that percentage for week nine i want to check the percentage completed or the percentage utilized per week so as you could see some of the stages of the project will overlap so we want to get the sum or get the total for each of the week so that is done by using a sum of each of the week and then i will drag it until the right side your clue that you got this correct is that when you sum all those percentages you should get a hundred percent because remember that this are simply a distributed percentage of the overall costs for each of the stages so when you add them back together they should result into a hundred percent now finally we will get the cumulative percentage for the first one we are going to just get the first amount and then for the next week we will get the previous percentage add the percentage that will be used for that week and then drag it until the last week and you know that you are correct if by the last cell you will also get a hundred percent because again this is simply a distributed percentage of a 100 of a whole so you will get 100 at the end and now we are actually ready to plot our s-curve so the s-curve is actually this line over here so i could drag or i could highlight the cumulative percentage insert line chart and our is now created and we have to do some modifications like excel will somehow exceed the hundred percent so you just have to right click on it on the axis format axis make the maximum as one one is a hundred percent if you want to change the interval or the graduation then maybe instead of point one change it to point 0.2 or something it's up to you and then we also want to get the labels for the access below so i could simply right click on it select data and then here we have to edit the horizontal axis labels click edit and it's asking you where the labels are it's going to be this week one to week nine as you could see it's already reflecting correctly at the background of the dialog box click ok click ok and now we're done with our cost distribution or whatever you want to name your s curve and there you have it your s-curve is now completed

Channel: Insights Training

Views: 8,935

Rating: 4.9304347 out of 5

Keywords: MS Excel, S-Curve, Project Management, Data Analysis

Id: c_bNNwFuvlA

Length: 12min 56sec (776 seconds)

Published: Fri Aug 14 2020