## What Is S Curve In Excel?

The

S Curve in Excelhelps to visually describe and predict an entity’s or a business’s progress over a period based on cumulative data. It is a logistic plot representing a variable’s progress relative to another variable.Users can use the

ExcelS Curveto assess a business’ growth rate, potential roadblocks in a project, schedule analysis, and for cash flow forecasting.

For example, the table below shows the annual profit data of a firm from 2012-22. We will analyze the firm’s profit trend over time visually.

We will **create an S Curve in Excel **using the **Line** or **Scatter** chart to perform the required performance review, as shown below.

The above **S Curve in Excel template** shows the profits made by the firm in the initial and final stages are gradual and slow. On the other hand, the firm has an accelerated increase in its profit in the middle phase.

##### Table of contents

###### Key Takeaways

- The
**S Curve in Excel**helps analyze an entity’s performance progressively over a period. It shows the entity’s cumulative data, such as man hours, progress, and cost. Users can use the**S Curve**in project management and domains such as finance. - The four stages in an
**S Curve**are initial gradual growth, accelerated growth, later-stage gradual growth, and constant demand. - We can use the
**Line**or**Scatter**chart to create an**S Curve**. - The Target, Cost vs. Time, Man hours vs. Time, and Baseline are a few of the
**S Curve**s we can create using Excel.

### Explanation And Usage Of S Curve In Excel

#### Explanation of Excel S Curve

Typically, the curve has an S shape with four stages.

- The first stage shows gradual growth,
- The second stage displays accelerated progress.
- The third stage again shows slow progress with a drop in momentum, and
- The last stage shows the curve has a zero slope, with a constant or a drop in the entity’s growth.

And the causes of the inflection points in an **S Curve** can be factors such as evolving technology, funding changes, regulatory changes, and sluggish innovation rate.

#### Uses of the Excel S Curve

- It is useful for project management, such as tracking progress and sales using cumulative data and mapping out expenses vs. cash flows.
- It helps in financial data modeling and cash flow forecasting.
- It helps compare planned versus actual budgets.
- It helps analyze facets, such as performance progress, cash flow predictions, task schedules, planned versus actual figures, man hours vs. time, costs vs. time, and baseline.

### How To Make S Curve In Excel?

We can **create an S Curve in Excel **using the **Line** or **Scatter** chart in the **Insert** tab. And the steps are as follows:

- Ensure the data to plot the
**S Curve**is valid and accurate. **Method 1:**Select the data range,- Click the
**Insert**tab → select the**Recommended Charts**option to open the**Insert Chart**window. [Alternatively, select the data range and follow the path**Insert**tab →**Line or Area**chart →**Line with Markers**chart.] - Click the
**All Charts**tab in the**Insert Chart**window → click the**Line**chart → choose the required**Line with Markers**plot.

- Click the
**Method 2:**Select the data range,- Click the
**Insert**tab → select the**Recommended Charts**option to open the**Insert Chart**window. [Alternatively, select the data range and follow the path**Insert**tab →**Scatter (X, Y) or Bubble**chart →**Scatter with Smooth Lines and Markers**chart.] - Click the
**All Charts**tab in the**Insert Chart**window → click the**X Y (Scatter)**chart → choose the required**Scatter with Smooth Lines and Markers**plot.

- Click the
- Using the
**Chart Elements**option, update the chart title and axis titles to complete the**S Curve**plot.

Let us see the above steps with an **S Curve in Excel template **example.

The following mathematical expression helps calculate the Sigmoid function, **S(x)**.

*S(x) = 1 / (1 + e ^{-x})*

where,

: Euler’s number*e*: Given values for which we must find the Sigmoid function.*x*

Further, the Sigmoid function represents the **formula for an S Curve in Excel**.

The table below contains a list of **x** values. We will determine the Sigmoid function (**S(x)**) value for each **x **value and plot **S(x) **vs.** x**.

The steps to create an **S Curve** in excel are as follows –

**Select cell B2, and enter the formula representing the Sigmoid function mathematical expression.***=1/(1+EXP(-*A*2))***Press Enter to view the output in cell B2.****Using the fill handle, update the formula in cells B3:B16.****Click on a cell in the data range A1:B16, and follow the path Insert → Recommended Charts.**

The**Insert Chart**window opens, where we must select the**Line**chart →**Line with Markers**chart type → The required plot option under**Line with Markers**chart type.

Click**OK**to get the below**S Curve**.

Otherwise, we can click a cell in the cell range**A1:B16**, and follow the path**Insert**→**Line or Area Chart**à**Line with Markers**chart to insert the required**S Curve**.

[Alternatively, select the cell range**A1:B16**, and select**Insert**→**Recommended Charts**to open the**Insert Chart**window. And then, select**X Y (Scatter)**→**Scatter with Smooth Lines and Markers**chart type → The required plot under**Scatter with Smooth Lines and Markers**chart type.

Click**OK**to get the following**S Curve**.]

Otherwise, we can click a cell in the cell range**A1:B16**. And then, select the**Scatter with Smooth Lines and Markers**chart from**Insert**→**Scatter or Bubble Chart**to insert the required**S Curve**.

The Y-axis position differs in the two charts. So, to get the identical plot, right-click on the X-axis and select**Format Axis**from the context menu.

The**Format Axis**window opens, where we must set the**Axis value**under the**Vertical axis crosses**setting as**-8**to move the Y-axis from the center to the left.

Closing the**Format Axis**window will give the below**S Curve**.**Click the chart area to enable the Chart Elements option (‘+’ icon), and check the Axis Titles box.**

And double-click on the chart and axis titles elements, one at a time, to update each element.

Thus, the above example suggests that the Sigmoid function gives the**formula for an S Curve in Excel**.

### Examples

The following illustrations will help us use the **S Curve** concept in Excel effectively.

#### Example #1

The table below shows the man-hours utilized per week. We will achieve the man-hours versus time **S Curve** for the given data.

The steps to get the **S Curve** are as follows:

**Step 1:**Click on a cell in the given data range, and select**Insert**→**Recommended Charts**to open the**Insert Chart**window.

**Step 2:**In the**Insert Chart**window, select**Line**chart →**Lines with Markers**chart type, and click**OK**.

**Step 3:**Right-click on the Y-axis in the chart, and select**Format Axis**from the context menu.

The **Format Axis** window will open, where we shall set the **Maximum** field under **Axis Options** as **100** to display the Y-axis units up to 100.

**Step 4:**Click the chart area to enable the**Chart Elements**option (‘**+**’ icon), and check the**Axis Titles**option.

Finally, updating the chart and axis titles will result in the following graph.

The above plot is nearly an **S Curve**, with the increase in man hours gradual till Week 10 and from weeks 17 to 19. And in the intermediate phase, from weeks 11 to 16, the man hours utilized per week expedites.

#### Example #2

We shall see an example of an **S Curve in Excel planned vs actual**.

The table below shows a firm’s baseline planned percentages, the targeted budget, and the actual and forecasted figures from Jan-22 to Dec-23.

The steps to plot an **S Curve in Excel planned vs actual**, to perform the required analysis are,

**Step 1:**Select the given data range, and click**Insert**→**Recommended Charts**.

The **Insert Chart** window will open, where we must select **Line** chart → **Line with Markers** → Select the appropriate chart option under the **Line with Markers** chart.

Clicking **OK** will give the below **S Curve**.

And update the chart title as shown below.

**Step 2:**Click**Insert**→**Shapes**→**Double arrow**to insert the chosen shape in the chart.

Click the inserted double arrow line to enable the **Format** tab, and use the **Shape Outline** option to change its color.

Here is how we can analyze the above planned versus actual **S Curve**.

In Dec-22, the deviation of the actual figures from the targeted budget is nearly 30% (highlighted by the red double arrow line). And we can determine the reasons for the deviation, such as labor strikes and material shortages, by mapping the factors with the timeline.

And then, based on the graph and the findings, we can estimate the required minimum monthly growth rate to overcome the shortfall.

#### Example #3

The first table shows the projects, tasks, and their start and end dates and durations.

The steps to visually analyze the specified projects’ tasks’ duration allocations across a range of dates using an **S Curve** are as follows:

**Step 1:**We shall list the dates in cells**H1:Y1**of the**Date**row, ranging from the first start date to the last end data in the given data. And then, we shall use the cell ranges**H22:Y22**and**H23:Y23**to display the total and cumulative days, as shown in the above image.

**Step 2:**Select the cell range**A1:E21,**and press**Ctrl**+**T**to convert it into an Excel table.

The **Create Table** dialog box will open, where we must click **OK** if the specified data range is correct.

Clicking **OK** will result in the Excel table, as shown below.

**Step 3:**Select cell**H2**, enter the below formula, and press**Enter**.

*=IF(MEDIAN(Table1[@[Start Date]],Table1[@[End Date]],H$1)=H$1,1,””)*

**Table1** in the above formula is the Excel table name. And the terms **Table1[@[Start Date]] **and **Table1[@[End Date]]** reference the cells C2 and D2 values.

Also, as we require the row number to remain constant in all the cells in each row, we use the absolute reference in excel for the row number in the cell address H1.

- First, the
**MEDIAN excel formula**finds the median of cells C2, D2, and H1 values, which is the value that appears in maximum cells among the three specified cells. Thus, the**MEDIAN()**returns the date**3/1/2022**, as it appears in two cells out of the three. - Next, the
**IF()**checks if the**MEDIAN()**output equals the cell H1 value. And as the**IF()**condition holds, it returns**1**as the output.

**Step 4:**Select cell**H2**, press**Ctrl**+**C**to copy the formula, and select the cell range**I2:Y2**.

And press **Ctrl **+ **V** to paste the formula in the chosen cells.

And using the fill handle in excel, enter the formulas in the cell range **H3:Y21**. As the formula is Excel table-based, the formulas in each row use the corresponding row values in the Excel table.

Thus, the durations get allocated for each task during each project period.

Next, we need the date-wise cumulative allocated days data to plot the **S Curve** required for the analysis.

**Step 5:**Select cell**H22**, enter the**SUM excel**formula*=SUM(H2:H21)***Enter**.

Next, select cell **H23**, enter the **SUM() **formula ** =SUM($H$22:H22)**, and press

**Enter**.

And then, select cells **H22:H23**, and using the fill handle, implement the formulas in cells **I22:Y23**.

**Step 6:**Select the rows**Date**and**Cumulative Days**ranges, and go to**Insert**à**Recommended Charts**to open the**Insert Chart**window.

Select **Line** chart → **Line with Markers** chart type.

Clicking **OK** will result in the below chart.

**Step 7:**To introduce the**Total Days S Curve**to understand why the**S Curve**appears lumpy along the path, select the**Total Days**row range, and press**Ctrl**+**C**to copy the data.

Click in the chart area and press **Ctrl **+ **V** to show the trend for the copied data.

**Step 8:**Right-click on the**Total Days**data series, and choose the option to modify the series chart type from the context menu.

The **Change Chart Type** window opens.

Change the **Total Days** data series chart type to a **2-D ****Clustered Column** chart.

And enable the **Secondary Axis** option for the **Total Days **data series. Click **OK** to complete the action.

**Step 9:**Click the chart area to enable the**Chart Elements**option, and select the**Chart Title**and**Axis Titles**options.

And updating the chart and axis titles will result in the below chart.

The above plot shows momentum across the dates 3/7/2022 and 3/8/2022, leading to the lumpiness in the **S Curve** (Highlighted in the above image).

### Important Things To Note

- The Sigmoid function represents the
**S Curve in Excel**. - The inflection points in an
**S Curve**can be due to factors including labor strikes, material shortages, market saturation, and funding changes. - When using the
**S Curve**to review aspects such as project progress, ensure to plot the cumulative progress over time.

### Frequently Asked Questions (FAQs)

**1. How to create S Curve in Excel for construction?**

We can create **S Curve in Excel** for construction using the following steps, shown with an example.

Consider the below-mentioned construction work scope and the amounts allocated for each task.

The steps to review the construction work progress using an **S Curve** visually are as follows:**• Step 1:** Select cell **B8**, enter the **SUM()** formula ** =SUM(B4:B7) **to determine the total amount allocated for all the tasks, and press

**Enter**.

**• Step 2:**We need to calculate each task’s allocated amount as a percent weightage.

So, select cell C4, enter the formula

**, and press**

*=B4/$B$8***Enter**.

Using the fill handle, enter the formula in cells

**C5:C7**. The values in cell range

**C4:C7**are percentages.

Select cell

**C8**, enter the

**SUM()**, press

**Enter**, &

**SUM()**will return

**100%**, i.e., total percent weightage.

**• Step 3:**Update the task start and end week and duration data for each task in the cell range

**D4:F7**.

We shall now equally distribute the percent weightage across the specified duration (weeks) for each task. And the data format of the cell ranges

**G4:O7**and

**G10:O11**is

**Percentage**in the

**Home**tab à

**Number Format**.

**• Step 4:**Highlight the cell range from the start to the end weeks specified in row 4, as shown below.

Select cell G4, enter the formula

**, and press**

*=$C$4/$F$4***Enter**.

We use the absolute cell references as we require the same value in all the highlighted cells in the specific row. Next, using the fill handle, update the values in cells

**H4:K4**.

And repeat the process for each task in rows 5 to 7.

**• Step 5:**We shall calculate the total weekly percentage of work completed in row 10. And for that, select cell

**G10**, enter the

**SUM()**formula

**, and press**

*=SUM(G4:G7)***Enter**.

And we require cumulative work progress each week. So, select cell

**G11**, enter the

**SUM()**formula

**, and press**

*=SUM($G$10:G10)***Enter**.

Next, select cells

**G10:G11**, and using the fill handle, enter the formulas for all the weeks.

**• Step 6:**We must plot the

**S Curve**for the weekly cumulative progress in the construction work. So, for that, select cell ranges

**G3:O3**and

**G11:O11,**and follow the path

**Insert**→

**Recommended Charts**.

The

**Insert Chart**window will open, where we can select

**Line**chart →

**Line with Markers**chart type.

Clicking

**OK**will give the below chart.

**• Step 7:**Click the chart area, and select

**Chart Elements**→

**Axis Titles**.

Thus, the final

**S Curve**for construction after updating the chart and axis titles, and resizing the chart will be as shown below:

**2. What is the difference between S Curve and histogram in Excel?**

The difference between **S Curve** and histogram in Excel is that the **S Curve** displays the cumulative data. And the graph increases at each point along the curve, indicating the accumulation of assets and costs.

On the other hand, the histogram represents periodic data for the specified period.

**3. What are the inputs for S Curve in Excel?**

The inputs for **S Curve in Excel** are a monitoring entity and a time interval.

The entity can be cost, progress, man-hours, or task duration. And typically, we will monitor, review, analyze, and forecast the entities over time using the **S Curve**.

### Download Template

This article must help understand the **S Curve in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to S Curve In Excel. Here we create S Curve for a cumulative periodic data along with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply