Calculating the Cost of Delay for a project is an important management tool, particularly when multiple projects use the same finite resources. The projects cannot all start simultaneously if they all need the same resources, so which one is chosen to go first?
Using an Excel spreadsheet to calculate the Cost of Delay will show the impact on the organisation’s profit if a project is delayed, which helps managers evaluate the distribution of resources to mitigate the financial impact.
Most projects also overrun and have an element of slippage. Often, due to outside circumstances, or something unforeseen, milestones and completion dates have to be re-set, which can significantly impact when the project is completed.
In this post, we cover the basics of the cost of delay and why is it so important to quantify the Cost of Delay and link to s spreadsheet to help your business calculate and manage the cost of delay for your projects.
Cost of Delay Example
Let’s use a cost of delay example to demonstrate how helpful this calculation is.
In the example, the product development process would occur in the first three months, with some marketing taking place in months 2 and 3 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.
- New product development – months 1-3
- Marketing starts – months 2-3
- New product launch – month 4
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 (a standard Excel formula).
Everything looks good with a significant 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 the following occurs:
- In month 3, a component supplier goes into administration and causes a supply chain problem
- An important tant resource on this project is required on another urgent project that is of greater importance
These are events that are unforeseen and out of the 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 additional 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). 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 three which will damage the sales.
The cost of this one-month delay is very serious and harms the overall profit for the 12 months.
In month 4, there was a massive 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 in month 4.
The overall result at month 12 is that profit for the new product is 116K below what was expected.
Using Excel For Cost Of Delay Calculations
On seeing the effects of the one-month delay in month 4, management will want to get things back on track so that the original profit figure is achieved in month 12. Excel is an ideal tool to keep track of the profit being made over the timescale.
Excel allows managers to juggle cost and revenue factors and do ‘What If’ scenarios (which is a built-in Excel function) to get the best scenario and profit for the project, considering various assumptions and knowledge.
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 to mitigate the adverse effects of the month four delay.
Managers can produce different scenarios in Excel, save each scenario off as a different file to be easily referred to again, and use ‘What If’ functions to ensure that the goal is achieved at month 12.
Excel has excellent chart facilities, and data such as this is easier to view as a chart than just as a string of numbers:
Conclusion & Cost Of Delay Template Download
Excel is a perfect tool for evaluating the 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 managers 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. It is an easy task for anyone to create a spreadsheet to calculate the Cost of Delay for projects.
Download our Cost Of Delay Template and start modelling your projects today!
