Business analysts frequently have to choose which goods or projects to prioritize. There is a trade-off between projects, particularly about which project one should concentrate on first to start the project. The Cost of Delay Calculator is a really useful tool to use when making decisions of this nature because it shows you the project’s Cost of Delay. You can undoubtedly use **Excel**, once again to develop a **Cost of Delay Calculator**. In this article, I’m going to provide a thorough, step-by-step demonstration of how to make a **Cost of Delay Calculator** and explain the procedures so that you may utilize them whenever necessary.

Before going to the next phase, let’s have a look at the final output of what you are going to get.

**Table of Contents**hide

## Download Practice Workbook

You may download the Practice Workbook and practice yourself.

## What Is Cost of Delay Calculator?

If a project or product launches after the planned time frame, the **Cost of Delay** is the amount of money lost per unit of time. The Cost of Delay for the product “**P**” would be $10,000 per month, for instance, if you were preparing to launch a new product “**P**” in the market, and you ultimately launched the product one month later than you first anticipated, incurring a loss of $10,000.

## Types of Cost of Delay

There are different types of **Cost of Delay(CoD)**. I’m mentioning them briefly here.

**Standard Curve**: In standard**CoD**, the relationship between delay time and**CoD**is quite linear. This means, if the delay time increases, the**CoD**increases in a linear fashion. This is usually preferred to ease calculation.**Fixed Date Curve**: This type of**CoD**works on a fixed deadline. And the rate of change of**CoD**is variable. Hence, the calculation of**CoD**is complicated as well. In short, there is a time when**CoD**will increase a lot and the value of**CoD**is not that much before or after that specific time period.**Intangible Curve**: This applies for projects with low priority. The**CoD**is minimal here and the change is rare.**Urgent Curve**: This is related to the projects with top priority. The**CoD**is huge in this case. That means, even a small delay in the project will result in a great loss.

## Steps to Create Cost of Delay Calculator in Excel

In the following parts of this article, I’ll show you how to create a **Cost of Delay Calculator **in Excel. I’ll break down the full process into four steps so that you can visualize it clearly. I’ve used the **Microsoft Excel 365 **version for demonstration purposes. You may use your preferred version if needed.

### Step 1: Creating Basic Particulars of Cost Analysis Data Set

In this section, let’s create a **Data Set **on which we’ll calculate the **Cost of Delay. **As we’ve stated earlier, we’ll show the full process in 4 simple steps and in the first step, we’ll create a table containing all the cost information. To prepare the **Basic Particulars of Cost Analysis**, we’ll create two tables following the steps described below.

- First of all, for a table titled
**Per Unit Cost Analysis**creation, create columns titled**Time**,**Material Cost per Unit**,**Production Cost per Unit**,**Packaging Cost per Unit**and**Total Cost**in the**B5:F5**We will create this table in the worksheet named**Basic Particulars of Cost**. Also, create another table named**Fixed Cost Analysis**with the headers named**Time**,**Engineering Cost**,**Promotional Cost (Total)**and**System Development Cost**.

- Now input the data and formulas in the corresponding cells to calculate the
**Total Cost**and**System Development** - Note that the
**Total Cost**is the summation of**Material Cost**,**Production Cost**and**Packaging Cost**per unit. So, the formula in cell**F7**is**=C7+D7+E7**. Type this formula in cell**F7**and press**ENTER**. - Similarly I’ve used
**=C8+D8+E8**formula in cell**F8**and**=C9+D9+E9**formula in cell**F9**. - Also, the
**System Development Cost**is the summation of**Engineering Cost**and**Promotional Cost(Total)**. So I’ve used**=C13+D13**formula in cell**E3**. Press**ENTER**after writing this formula. - Similarly, I’ve used
**=C14+D14**in cell**E14**,**=C15+D15**in cell**E15**and**=C16+D16**in cell**E16**to calculate the respective**System Development Cost.**

Hence the data set for cost analysis is complete now.

### Step 2: Calculating Profits According to Assumed Data Set

In this step, we’ll create a table for calculating Profit. Then, we’ll insert the corresponding cell values and use formulas to calculate the final profit. We’ll use this data for the final Cost of Delay calculation.

- First of all, create columns titled
**Time**,**No of Assumed Unit**,**Selling Price per Unit**,**Cost per Unit**,**Revenue Per Unit**,**Total Revenue**,**Percent of Revenue Deducted**,**Revenue Received**,**Net Profit**and**Cumulative Profit**in the**B5:K8**We will create this table in the worksheet named**Profit Calculation**.

- Now, input the following data and formulas in the corresponding cells for profit calculation.
- I’ve used the formula
**=’Basic Particulars of Cost’!F7**in cell**E6**to populate the**Cost per Unit**Type this formula in cell**E6**and press**ENTER**. Similarly,**t**he formula in cell**E7**is**=’Basic Particulars of Cost’!F8**and the formula in cell**E8**is**=’Basic Particulars of Cost’!F9**. - The formula for calculating
**Revenue per Unit**in cell**F6**is**=D6-E6**. Press**ENTER**after typing this formula. Similarly, the formulas in cell**F7**is**=D7-E7**and in cell**F8**is**=D8-E8**. - I’ve calculated
**Total Revenue**in cell**G6**using the formula**=C6*F6**. Press**ENTER**after typing the formula. Similarly, the formula in cell**G7**is =**C7*F7**and in cell**G8**is**=C8*F8**. - To calculate the
**Revenue Received**, I’ve used the formula in cell**I6**is**=G6*(1-H6)**. Press**ENTER**after typing the formula. Similarly, the formula in cell**I7**is**=G7*(1-H7)**and in cell**I8**is**=G8*(1-H8)**. - I’ve typed the formula
**=I6-‘Basic Particulars of Cost’!E14**in cell**J6**and pressed**ENTER**to get the**Net Profit**. Similarly, the formula in cell**J7**is**=I7-‘Basic Particulars of Cost’!E15**and in cell**J8**is**=I8-‘Basic Particulars of Cost’!E16**. - Now we’ll calculate
**Cumulative Profit**. The formula in cell**K6**is**=J6**. Press**ENTER**after typing the formula. The formula in cell**K7**is**=J6+J7**and in cell**K8**is**=J6+J7+J8**.

Hence the **Profit Calculation for Unit Assumption** is complete now.

### Step 3: Calculating Profits If Delay Happens

At this step, we are assuming that the product launched is delayed by one month. Therefore, it won’t be possible to produce **12%** of the assumed unit.So the overall unit produced will be (**1-0.12**) times the unit assumed. Hence, Our **Net Profit **will be affected. As a result, **Cost of Delay **will appear.

- Create a table titled
**Profit Calculation if Delayed**just like the table above titled**Profit Calculation for Unit Assumption**, except the second header(**C11**) will be titled as**No of Unit if Delayed**.

- Now, insert the corresponding cell values and formulas as shown in the image to calculate the profit.
- To calculate the
**No of Unit if Delayed**, I’ve used the formula**=(1-0.12)*’Basic Particulars of Cost’!C6**in cell**C12**. Type this formula in cell**C12**and press**ENTER**. - Similarly, the formula in cell
**C13**is**=(1-0.12)*’Basic Particulars of Cost’!C7**and the formula in cell**C14**is**=(1-0.12)*’Basic Particulars of Cost’!C8**. - The formula to populate
**Cost per Unit**in cell**E12**is**=’Basic Particulars of Cost’!F7**.Press**ENTER**after typing the formula. - Similarly, the formula in cell
**E13**is**=’Basic Particulars of Cost’!F8**and in cell**E14**is**=’Basic Particulars of Cost’!F9**. - The formula to calculate
**Total Revenue**in cell**G12**is**=C12*F12**. Press**ENTER**after typing the formula. Similarly, the formula in cell**G13**is =**C13*F13**and in cell**G14**is**=C14*F14**. - Use the formula in cell
**I12**is**=G12*(1-H12)**to get the value of**Revenue Received**. Press**ENTER**after typing the formula. Similarly, the formula in cell**I13**is**=G13*(1-H13)**and in cell**I14**is**=G14*(1-H14)**. - To compute the
**Net Profit**, I’ve typed the formula**=I12-‘Basic Particulars of Cost’!E14**in cell**J12**and pressed**ENTER**. Similarly, the formula in cell**J13**is**=I13-‘Basic Particulars of Cost’!E15**and in cell**J14**is**=I14-‘Basic Particulars of Cost’!E16**. - Now we’ll calculate
**Cumulative Profit**. The formula in cell**K12**is**=J12**. Press**ENTER**after typing the formula. The formula in cell**K13**is**=J12+J13**and in cell**K14**is**=J12+J13+J14**.

Hence, we’ll get all the data necessary to calculate the **Cost of Delay**.

### Step 4: Evaluating Cost of Delay

Now, we’re only one step behind to create the **Cost of Delay Calculator **in Excel. We need to create a table including the data to proceed our calculation. We’ll create this table in a new Worksheet named **Cost of Delay**.

- Create a table titled
**Evaluating Cost of Delay**in cells ranging from**B4**to**E7**. The table contains headers like**Time**,**Assumed Profit**,**Profit if Delayed**and**Cost of Delay**.

- Insert the formulas in the corresponding cells to calculate
**Cost of Delay**. I’ve used the formula**=’Profit Calculation’!K6**in cell**C5**,**=’Profit Calculation’!K12**in cell**D5**to populate the respective values in the corresponding cells. - To calculate the
**CoD**for the**First Year Production**, I’ve typed the**=$C$5-$D$5**in cell**E5**and pressed**ENTER**.I’ll get the**CoD**value now. - The rest of the
**CoD**are calculated following this pattern.We can also use the**Fill Handle**to compute the rest of the**CoD**.

So, cells **E5**, **E6 **and **E7 **contain the **Cost of Delay **values for the first, second and third year production. This is visible that, The value increases significantly over the time.

## How to Calculate CD3 Score to Prioritize Projects in Excel

CD3 score helps you prioritize projects. We can calculate the CD3 score dividing **CoD **by duration. For example, if we have a total of three projects named **Project A**, **Project B **and **Project C**. The time to develop these projects are 6, 8 and 12 months respectively. Also, the projects will generate a profit of 10,000 USD, 12000 USD and 22000 USD.

The dataset looks like this.

Now we need to calculate the CD3 values. The formula to do so is:

CD3=(Profit per Month/Time to develop Project/1000)

- Type
**=D6/C6/1000**in cell**F6**and press**ENTER**. This will give us the**CD3**value for**Project A**. - Similarly use the formula
**=D7/C7/1000**in cell**F7**and**=D8/C8/1000**in cell**F8**. We’ll get all the**CD3**values in this way.

We can see from the image that the value of CD3 is greatest for **Project C**. So, according to the concept of CD3, **Project C **should get top priority in terms of monetary value.

## Takeaways from This Article

After thoroughly following this article, you should know:

- Have a clear concept of what is the
**Cost of Delay Calculator**and how to build it in Excel. - Be able to create and use the
**Cost of Delay Calculator**of your own or reuse our**Cost of Delay Calculator**. - Get a functional idea of how to calculate
**CD3**value in Excel and how to use it.

## Things to Remember

When you will work to build a **Cost of Delay Calculator **of your own using the method I’ve demonstrated in Excel, please keep in mind that:

- Include all the no of years you are calculating
**CoD**for in your dataset. - We’ve used worksheet references in our formulas. Please do accordingly when you’re using formulas and make necessary adjustments if needed.
- Take care when you refer to any cell in the formulas.

## Frequently Asked Questions

**What are the benefits of using the Cost of delay Calculator?**

The benefits of Cost of Delay Calculator are

- The Cost of Delay Calculator helps to prioritize projects that are most important based on financial impact.
- Reduce the mismanagement of allocating limited resources in projects. Thus, it brings out the most efficient way to save time and money.

## Conclusion

If you’re at this segment, I thank you for your interest in this content. I’ve demonstrated how to create a **Cost of Delay Calculator **in Excel. I’ve demonstrated a practical example of how you can use it in real life scenarios. I hope you get the necessary solution. Being said that, if you face any problem regarding this article or have any queries, please feel free to leave a comment in the comment box, **Exceldemy** team will try to solve that for you. Have a good day!