Project Management in Excel-The DHSS Expenditure Forecasting Model (S-CURVE)
Views > Excel charts > Project management in Excel - The DHSS expenditure forecasting model (S-CURVE)
The construction industry is increasingly characterized by successful financial management strategies to manage cashflows. Construction projects should be delivered on time, below budget and according to specifications. Not managing cashflows properly is recipe for failed projects.
There are various models developed by the research community to help forecast construction cashflows. These models predict the timing and size of cashflows during construction. The output of these models can be represented graphically by a dual axis chart where the cumulative outlays are represented by an S-curve and the periodic outlays are represented by the vertical bars as per the chart below.
One of the models is the DHSS Expenditure Forecasting Model developed by Hudson and Maunich in 1974 and represented by the following equation:
y = x + Cx^2 - Cx - (6x^3 - 9x^2 + 3x) / K
where:
y is the cumulative cost in %,
x is the cumulative time in %,
and C and K are two parameters defined by the following table:
So, let's say I'm in 2025 and forecasting the cash outlays of a project with a capital expenditure of 29,289,000£. To define the two parameters C and K I need to evaluate the equivalent of 29,289,000£ in 1978 pounds. For that I looked up the CPI in the UK from 1978 till 2024 and found that 1£ in 1978 is worth 8.413£ in 2024 according to the below table:
Adjusting for inflation, my CAPEX of 29,289,000£ in 2025 is equivalent to 29,289,000 / 8.413 = 3,481,560 in 1978 pounds. By linear interpolation between 3.0 million and 4.0 million pounds I can estimate C = 0.134 and K = 3.884.
Having calculated the two parameters of the model, let's build the chart in Excel.
The first thing to do is to input the assumptions of the model.
1. Enter "Construction duration", "Development cost", "C" and "K" in cells B2, B3, B4 and B5 respectively.
2. Enter "months" and "£" in cells D2 and D3 respectively.
3. Enter the number 26 in cell C2. This will define the number of months that it will take to complete the construction. Enter the number 29,289,000 in cell C3, the number 0.134 in cell C4 and the number 3.884 in cell C5.
The worksheet should look like this:
Next, I'll define the setup for the forecast.
4. Enter the headers "Period", "Cumulative time (%)", "Cumulative cost (%)", "Periodic cost (%)", "Periodic cost (£)", and "Cumulative cost (£)" in the range C7:H7.
5. Enter the following formulas in the respective cells:
C8 --> = SEQUENCE(C2,1,1,1)
D8 --> = C8# / C2
E8 --> = (D8# + C4 * D8#^2 - C4 * D8# - (6 * D8#^3 - 9 * D8#^2 + 3 * D8#) / C5)
F8 --> = IF(C8# = 1,E8#,INDEX(E8#,C8#) - INDEX(E8#,C8# - 1))
G8 --> = F8# * C3
H8 --> = SCAN(0,G8#,LAMBDA(a,b,a + b))
The worksheet should look like this:
