Modelling Capex and S-Curves
Download Excel file
Capital Expenditure (Capex) is a common component of all project finance and many other types of financial model. An asset (power station, refinery, toll road etc) has to be constructed before it can generate any income. In the early stages a projects assessment there may not be a lot of information regarding this capex. Usually there is a total spend estimation and duration. Then as the project progresses and the construction contracts are finalised a more complete capex schedule can be included in the latter versions of the model.
So how should capex be modelled prior to this data being available? This tutorial runs through three methods for modelling capex:
· Sculpted – the user defines the profile
· Straight line – equal payments across the construction period
· Sigmoid or S Curve
Below are two charts illustrating the cumulative and period amounts for each of the above options.
The first two profiles are fairly self explanatory and will not be discussed (they can been seen in the Excel file accompanying this tutorial). This tutorial focuses on the S-Curve method as this is probably the most widely used when it comes to modelling capex.
An S-curve is defined by the formula:
where the P denotes ‘population’ and t ‘time’. Whilst t can range from −∞ to +∞, in practice, to obtain a useable S-curve a smaller range is used.
With a bit of manipulation this S-curve can then be ‘stretched’, ‘compressed’ and ‘offset’ across the construction periods in a financial model to give the required profile.
The steps below will assist you in producing a flexible S-curve that can be used for a variable capex amount and construction period.
Step 1 – Create the Construction Inputs & Flags
Define the inputs as suggested above. Include the Construction period flag in Row 15.
Step 2 – Define the S-Curve and Calculate Cumulative & Period Capex
Create the calculations as indicated in the screen shot above.
| Row Label | Formula / Input | Description |
| t-range | Input | The range of the S-Curve that is ‘overlaid’ onto the model timeline. A lower range creates a flatter curve ie more even payments. |
| t offset | Input | Offsets the curve either to left or right of the middle of the construction period. Zero indicates the curve is symmetrical. |
| t start | =-$F$29/2+F30 | The start point of the S-Curve |
| t end | =$F$29/2+F30 | The end point of the S-Curve |
| t increments | =F29/(F11-1) | The amount t (row 33) increases for each period in the model. |
| t | =IF(SUM($H$15:J$15)=1,$F$31,I34+$F$33)*J$15 | Calculates the t for each period |
| P | =1/(1+EXP(-J34))*J15 | Using the S-Curve formula above P is calculated. |
| Cumulative Capex | =J35/MAX($H$35:$AU$35)*$F$8 | The ratio of the periods P to the maximum P is multiplied by the Capex Amount. This calculates the Cumulative Capex up to this point in the timeline. |
| Period Capex | =(J36-I36)*J$15 | The Capex in the period |
Note. The S-curve is approximated over the timeline and any spend prior to the first time period is assumed to be spent in the first period. In some instances, this may create a spend in the first period greater than that of the second period. As the t range increase this difference reduces and the size of this first payment relative to the latter payments is insignificant. With a couple of extra lines of code this difference can be added to the final periods construction spend or spread across all the payments as an alternative. As this is an approximation to a construction profile in the first place I don’t think this is worth coding.
