Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Project Budget in Excel (with Easy Steps)

When you are creating a project budget, Excel can be a handy tool for you. Today I am going to show you three easy and suitable steps to create a project budget in Excel effectively with appropriate illustrations. For preparing examples and workbooks I’m using Excel 2019. You can choose your version.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Project Budget

A project budget includes all of the financial information necessary to run a successful project, from resources to materials, as well as estimates of all project expenditures.

Use this Excel budget to keep track of project spending. Even color coding is included to show you when you’re in the red. Your project’s budget is its lifeblood, so you must carefully monitor it to prevent cost overruns. This is where the project budgeting feature in Excel is useful.

Tasks that result in the completion of a project or service make up a project. That procedure costs money. Resources are required, including the hiring of a project team, potential vendor contracts, potential parts, and a wide range of other costs that must be paid and kept track of.


Step-by-Step Procedures to Create a Project Budget in Excel

Let’s say, we have a dataset that contains information about the Project Budget of the XYZ group. From our dataset, we will create a project budget in Excel. We can easily create a project budget in Excel by using the SUM function and Mathematical formula. Here’s an overview of the dataset for today’s task.

how to create a project budget in excel


Step 1: Create Project Budget Header

In the first step, you will have to insert headers to create a project budget and then format them in Excel. Let’s follow the instructions below to learn!

  • First of all, we will create a dataset for a project budget. We will give Creation of Dataset as a header. We also give another header for summary and project details. Hence, we will format those headers as we want from the Home tab.

  • Further, we will create a header for cost calculation. For instance, serial number, particulars, material cost, labor cost, fixed cost, miscellaneous cost, budgeted amount, actual amount, and variance. After that, we will format those headers as we want from the Home tab.

Read More: How to Create an Operating Budget in Excel (with Detailed Steps)


Step 2: Calculate Project Budget

In this section, we will calculate the project budget. To do that, we will divide our project into three functions, named Function 1, Function 2, and Function 3. Firstly, we will calculate the total cost of Function 1. Hence, we calculate the cost of the rest of the functions. Let’s follow the instructions below to learn!

  • First of all, select cell J11 and write down the below mathematical formula in that cell.
=(D11*E11)+(F11*G11)+H11+I11
  • Where,
  • D11 = units of materials,
  • E11 = per unit cost of materials,
  • F11 = units of hours,
  • G11 = per unit cost of hours,
  • H11 = the fixed cost of the project, and
  • I11 = the miscellaneous cost of the project.

  • Hence, simply press Enter on your keyboard. As a result, you will get the Budgeted amount which is the return of the mathematical formula. The return is $15,480.00.

Calculate Project Budget

  • Hence, AutoFill the formula to the rest of the cells under Function 1 in column J.

  • Now, we will calculate the variance. To do that, write down the below subtraction formula in cell After that, press Enter on your keyboard.
=J11-K11
  • Where J11 is the Budgeted amount and K11 is the actual amount of the project.

Calculate Project Budget

  • Further, AutoFill the formula to the rest of the cells under Function 1 in column L.

  • After that, we will calculate the total Fixed Cost of Function 1. To do that, we will apply the SUM function. The SUM function is,
=SUM(H11:H13)
  • Hence, simply press Enter on your keyboard. As a result, you will get the total Fixed Cost of Function 1 which is the return of the SUM function. The return is $11,300.00.

Calculate Project Budget

  • Further, AutoFill the SUM function to the rest of the cells under Function 1 in row 14 to calculate the total miscellaneous cost, total budgeted cost, total actual amount, and total variance.

Calculate Project Budget

  • Similarly, do the above process for Function 2 and Function 3.

  • Now, we will calculate the total cost of the project. To do that, write down the below Addition
=H14+H18+H22
  • Where,
  • H14 = the total fixed cost of Function 1,
  • H18 = the total fixed cost of Function 2, and
  • H22 = the total fixed cost of Function 3.

  • Hence, simply press Enter on your keyboard. As a result, you will get the total Fixed Cost of the project. The total Fixed Cost of the project is $29,800.00.

Calculate Project Budget

  • Further, AutoFill the SUM function to the rest of the cells in row 23 to calculate the total miscellaneous cost, total budgeted cost, total actual amount, and total variance.

Calculate Project Budget

Read More: How to Calculate Remaining Budget Using Formula in Excel


Similar Readings


Step 3: Make a Summary of the Project Budget

In this section, we will calculate the summary of the project budget. This is an easy and time-saving task as well. Let’s follow the instructions below to learn!

  • First of all, select cell E5 and write down the below formula in that cell.
=J23
  • Where J23 is the total budgeted cost of the project budget.

  • After that, press Enter on your keyboard. As a result, you will get the total budgeted cost of the project budget which is the return of the formula. The return is $107,787.50.

Make a Summary of Project Budget

  • Hence, select cell E6 and write down the below formula in that cell.
=K23
  • Where K23 is the total actual cost of the project budget.

  • After that, press Enter on your keyboard. As a result, you will get the total actual cost of the project budget which is the return of the formula. The return is $98,500.00.

  • Further, select cell E7 and write down the below formula in that cell.
=L23
  • Where L23 is the total variance cost of the project budget.

  • After that, press Enter on your keyboard. As a result, you will get the total variance of the project budget which is the return of the formula. The return is $9,287.50.

Make a Summary of Project Budget

  • Finally, we will get the summary of the project budget.

Read More: How to Make a Budget in Excel (2 Easy Methods)


Project Cost Template in Excel

You can use today’s workbook as a template to calculate the project budget. There is a sheet named Template.

Explore that sheet. You will find fields for units of materials, per unit cost of materials, units of hours, per unit cost of hours, fixed cost, miscellaneous cost, budgeted amount, actual amount, and variance of the project. Insert your values. It will calculate the total budgeted cost, total actual cost, and variance of the project budget given in the below screenshot.

Project Cost Template in Excel

Read More: How to Create Budget and Expense Tracker in Excel


Bottom Line

👉 #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.


Conclusion

I hope all of the suitable methods mentioned above to create a project budget will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo