Project Cost Estimation Example in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Cost estimation is the basis of project management. In our day-to-day lives, we have to design several types of estimation lists or tools. Using Microsoft Excel, we can do such work so easily. In this article, we will demonstrate 5 easy and quick steps to create a project cost estimation example in Excel. So, let’s go through the entire article to understand the process properly.


Project Cost Estimation: Basics

Before starting a project, it must be planned out. Calculating a rough estimate of the project’s cost can avoid any unwanted problems. The purpose of cost estimation is to finish the project on schedule and on budget. Your investor can become impatient with the budget shortage. However, an incorrect project cost estimation results in a failed project. Your chances of sticking to your spending plan and completing a project on schedule increase with the accuracy of your estimations. So, from the above discussion, we can easily see that cost estimation is very important for any kind of project.


5 Steps to Create Project Cost Estimation Example in Excel

Creating a project cost estimation example in Excel could seem challenging at first, especially if you don’t use the application frequently. No matter how simple or complex the estimation is, this step-by-step article will show you how to create a project cost estimator in an Excel spreadsheet that can be easily customized to meet your needs. Excel includes a huge selection of budgeting templates. But in this article, we will create this estimation spreadsheet in Excel from scratch. So, let’s follow the steps to make a project cost estimation example in Excel.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


Step 01: Create Basic Outline

At the very beginning, we have to create a basic outline of the cost estimation spreadsheet. This should be divided into two different parts. One is the short information about the project and the other is the cost calculation part. In the info area, we included the Project Name, Project Number, Project Manager, Client Name, Start and End Date of the Project in the B6:J8 range.

project cost estimation example excel

In the cost calculation part, we created a table to contain different cost components and their amount. We’ll calculate these amounts step-by-step in the following part of the article.

Create Basic Outline of cost estimation sheet

Read More: How to Perform Building Estimation and Costing in Excel Sheet


Step 02: Estimate Phase-wise Total Cost

Basically, we divide a large project into multiple phases. Then, we complete the entire project by working out the tasks phase-wise. Let’s see the process in detail.

  • First, go to cell J13 and enter the following formula into the cell.
=(D13*E13)+(F13*G13)+H13+I13

Here, D13 and E13 represent the Units and Per Unit cost of Material. Also, F13 and G13 serve as the Hours and Per Hour cost of Labour. On the other hand, H13 and I13 substitute the Fixed Cost and Miscellaneous Cost.

  • Then, press ENTER.

Estimate Phase-wise Total Cost

  • Secondly, apply the same type of formula for Job 2 and Job 3 in cells J14 and J15 respectively. Just, make sure to change the cell references. Like, write D14*E14 instead of D13*E13.

  • After that, calculate the total material cost for these 3 jobs. To do this, go to cell D16 and insert the formula below.
=SUMPRODUCT(D13:D15,E13:E15)

Here, we used the SUMPRODUCT function to take two arrays (D13:D15 and E13:E15) as arguments, multiply the corresponding values of all the arrays, and then return the sum of the products.

  • As always, press ENTER.

Calculating total material cost

In a similar way, we can calculate the Total Labour Cost. And, just use the SUM function to calculate the Total Fixed Cost and the Total Miscellaneous Cost of this phase.

Now, we’ll compute the Total Estimated Cost of Phase A.

  • At this moment, select cell J16 and write down the formula below.
=SUM(J13:J15)
  • As usual, press ENTER.

total cost estimation of phase A

Alike, you can calculate them for Phase B and C as well.

Estimation of Phase-wise Total Cost

Read More: How to Calculate Residential Construction Cost Estimator in Excel


Step 03: Calculate Total Estimated Project Cost

In this section, we’ll determine the totals of each different cost component and also the out-and-out total estimated cost of the entire project.

To calculate the Total Material Cost of the project,

  • Firstly, select cell D25 and paste the following formula.
=SUM(D16,D20,D24)
  • Afterward, press ENTER.

Calculate Total Estimated Project Cost

  • Correspondingly, do the same for Labor, Fixed, and Miscellaneous costs also.

Finally, we’ll figure out the total cost of this project by adding all the estimated costs of every project.

  • So, select cell J25 and put down the formula below.
=J16+J20+J24
  • Then, tap ENTER.

Calculating Total Estimation of Project Cost in Excel

Read More: How to Make an Effort Estimation Sheet in Excel 


Step 04: Insert Chart to Aid in Visualization

Chart is a great tool for visualization. We can easily understand a large amount of data or info easily with the help of charts. So, we’ll insert some charts to achieve different aspects of this estimation. Let us go through the following steps.

  • Initially, navigate to the Insert tab.
  • Then, click on the Insert Pie or Doughnut Chart drop-down icon.
  • Following this, select the Pie chart from the 2-D Pie section.

Insert Chart to Aid in Visualization

Immediately, we can see a blank chart on the sheet.

  • Just change the chart title and give a suitable one.

blank chart in Excel

  • Right now, right-click anywhere inside the chart area to open the context menu.
  • From the menu, click on the Select Data… option.

Context menu in Excel

  • In the Select Data Source dialog box, tap on the Edit button under the Horizontal (Category) Axis Labels section.

select data source dialog box in Excel

As axis labels, we want to show the different cost components like material cost, labor cost, etc.

  • So, on the Axis Labels dialog box, select those cells (D10, F10, H10, and I10) to get the value in the Axis label range box.
  • Also, click OK.

The labels are now present here in the box.

  • Just click OK.

New axis labels for chart in Excel

  • Again, bring the Select Data Source box.
  • We renamed the series as Cost. Now, select it and click on the Edit button.

Editing series

Instantly, the Edit Series box will pop up.

  • Here, give the cell references of the total amount of different cost components. For example, D25 has the total material cost, and F25 has the total labor cost.
  • Then, click OK.

Edit series dialog box in Excel

  • Lastly, click on the OK button.

Now, we can see a blank chart like the following.

pie chart in project example in Excel

Similarly, we inserted another Column Chart to plot the phase-wise cost of the project.

Inserting Chart in project cost estimation to Aid in Visualization in Excel


Step 05: Verify with Sample Data

We have completed creating the whole template. Now, we have to put the numbers down.

  • Here, we inserted sample data in the blank cells and the results are before our eyes.

Verify with Sample Data

The charts are in the following states now.

Verifying project cost estimation example with Sample Data in Excel

Column chart in project cost estimation in Excel

Read More: How to Do Interior Estimation in Excel


Free Template of Project Cost Estimation [Ready to Use]

In the above section, we tried to show how we created this template. You can use it instantly by just downloading the Excel file. You just have to write down your values in the light-blue-colored cells.

Free template of project cost estimation in Excel

It’s completely free. Just download and use it.


Things to Remember

📌 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

📌 #DIV/0! error happens when a value is divided by zero (0) or the cell reference is blank.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice.


Conclusion

This article explains how to create a project cost estimation example in Excel in a simple and concise manner. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo