modelling capex and s-curves

7/27/2019 Modelling Capex and S-Curves

1/4

1

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.

Cumulative Capex

-

50

100

150

200

250

300

350

400

0 3 6 9 12 15 18 21 24

Period

Sculpted Straight Line S-Curve

Period Capex

-

10

20

30

40

50

60

1 4 7 10 13 16 19 22

Period

$m

Sculpted Straight Line S-Curve

Financial Modelling Expertise

Digit: Free Tutorial

Modelling Capex and S-Curves


2/4

2

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.

S-Curve

-

0.1

0.2

0.3

0.4

0.5

0.6

0.7

0.8

0.9

1.0

(6.0) (4.0) (2.0) - 2.0 4.0 6.0

t

P





3/4

3

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.





4/4

4

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-Curvet 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 payments relative 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 spend in the first place I don't think this is

worth coding.



Digit is an expertise provider of financial modelling consultancy services.

We have in depth experience with a variety of project types, across a range of industry

sectors within investment banking, consulting and industry environments. Digit is well

positioned to work with you to achieve your goals by designing, developing and

operating the financial models and analysis you require.

Visitwww.digitadvisory.comto find out more.

Contact John Stroud for more information +61 2 8006 1728 [email protected]

Contact usExperienceKnowledgeServicesAbout usHome
http://www.digitadvisory.com/http://www.digitadvisory.com/http://www.digitadvisory.com/mailto:[email protected]:[email protected]:[email protected]://www.digitadvisory.com/contact-us.htmlhttp://www.digitadvisory.com/experience.htmlhttp://www.digitadvisory.com/knowledgehttp://www.digitadvisory.com/services.htmlhttp://www.digitadvisory.com/about-us.htmlhttp://www.digitadvisory.com/http://www.digitadvisory.com/http://www.digitadvisory.com/about-us.htmlhttp://www.digitadvisory.com/services.htmlhttp://www.digitadvisory.com/knowledgehttp://www.digitadvisory.com/experience.htmlhttp://www.digitadvisory.com/contact-us.htmlmailto:[email protected]://www.digitadvisory.com/

modelling capex and s-curves

Documents