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.
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.
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.
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.
- 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.
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.
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.
- As usual, press ENTER.
Alike, you can calculate them for Phase B and C as well.
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.
- Afterward, press ENTER.
- 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.
- Then, tap ENTER.
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.
Immediately, we can see a blank chart on the sheet.
- Just change the chart title and give a suitable one.
- Right now, right-click anywhere inside the chart area to open the context menu.
- From the menu, click on the Select Data… option.
- In the Select Data Source dialog box, tap on the Edit button under the Horizontal (Category) Axis Labels section.
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.
- Again, bring the Select Data Source box.
- We renamed the series as Cost. Now, select it and click on the Edit button.
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.
- Lastly, click on the OK button.
Now, we can see a blank chart like the following.
Similarly, we inserted another Column Chart to plot the phase-wise cost of the project.
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.
The charts are in the following states now.
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.
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.
You may download the following Excel workbook for better understanding and practice.
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.