Using Excel to Control Project Cost of Delay

Evaluating the Cost of Delay for a project is an important management tool, particularly when there are multiple projects which all use the same finite resources. The projects cannot all start at the same time if they all need the same resources, so which one is chosen to go first?

Evaluating Cost of Delay will show the impact on the profit of the organisation if a project is delayed, and this helps managers to evaluate distribution of resources in order to mitigate the financial impact.

Also, most projects within organisations have an element of slippage.  Often, due to outside circumstances, or something unforeseen, milestones and completion dates often have to be re-set, which can have a big impact on when the project is completed.

In terms of the profit of the organisation, this will have a negative impact, which could have serious repercussions elsewhere e.g. a profits warning if the organisation is a public limited company which could lead to a drop in the share price.

This could apply to the development of a new product or service within the organisation, or a rationalisation of departments within the organisation that would lead to cost savings.

When the decision is taken to undertake a project, a budget is calculated over a period of time to monitor the costs and to ensure that a profit is being made.  After all there is no point in a project which does not have a positive effect on the profit of the organisation.

Excel is an ideal tool to keep track of the profit being made over the timescale.  The timescale could be in weeks, months, or years.

The initial budget that is calculated is effectively the baseline for the project. It is usually the best calculation of the numbers, based on all known factors and constraints at the time. 

Cost of Delay Example in Excel

As an example, an organisation is developing a new product. It is expected that it will take 3 months to develop, and a further 9 months of the financial year to sell.

Calculations can be made to set percentages for cost types, which can easily be adjusted. The projection could also include Discounted Cash Flow calculations for the net return (this is a standard Excel formula).

Excel allows managers to juggle cost and revenue factors, and to do ‘What If’ scenarios (which is a built in Excel function) in order to get the best scenario and profit for the project, taking into account various assumptions and knowledge.

In the example, development would take place in the first 3 months, with some marketing taking place in months 2 and 3 in order to increase public awareness.

In month 4, the product is ready for the market, and there is now growing sales revenue and costs budgeted for subsequent months.

Everything looks good with a large cumulative net profit at the end of month 12.  The project gets approval and off it all goes.

However, the best laid plans do not always work out as expected.  Suppose that in month 3, a component supplier goes into administration and causes a supply chain problem, or the government bans the import of a vital component from abroad. Also, an important resource on this project could be required on another urgent project that is of greater importance. Solutions need to be found to these problems, and this takes time.

These are events that are totally unforeseen and out of control of the project manager.  The effect is to push the product launch back by a month, and to have a further month of development costs and some further marketing costs at month 4.

Sales revenue is likely to be lower in the first month because of the delay (public perception of the product may have been damaged), and the revenue will always be playing ‘catch up’ against the original budget over the subsequent months.

Also, a competitor may have been working on a similar product, and they may have managed to launch their product in month 3 which will damage the sales.

The cost of this one-month delay is actually very serious, and has a negative effect on the overall profit for the 12-month period.

On month 4 there is a huge change in the projected profit from 61K to a loss of 11K.  Working along to month 12, the amended cumulative profit is running well below the budgeted profit which is the cost of the delay that happened back in month 4.

The overall result at month 12 is that profit for the new product is 116K below what was expected.

Management on seeing the effects of the one-month delay in month 4 will want to get things back on track, so that the original profit figure is achieved in month 12.

They could do this by increasing sales volume, but this will need increased marketing costs. Because of the flexibility of Excel, it is very easy to play around with the numbers for subsequent months in order to mitigate the adverse effects of the month 4 delay.

Managers can produce different scenarios in Excel, save each scenario off as a different file so that it can be easily referred to again, and use ‘What If’ functions to ensure that the goal is achieved at month 12.

Excel has very good chart facilities, and data such as this is easier to view as a chart, than just as a string of numbers:

Conclusion

Excel is an extremely good tool for evaluating Cost of Delay on a project.  This can be used to decide between two or even multiple projects, and the order in which they are executed.

If there is an unexpected delay on a project, it can also allow mangers to generate different scenarios to mitigate the financial impact of the delay.

Excel has enormous flexibility in terms of displaying and calculating data, and presenting it in chart form, and it is an easy task for anyone to create a spreadsheet to calculate the Cost of Delay for projects.