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 we are going to show you how to create a project budget in Excel effectively with appropriate illustrations. For preparing examples and workbooks we’re using Microsoft 365 version. You can choose your version.

Here’s an overview of the dataset for today’s task.

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. You must carefully monitor the project’s budget to prevent cost overruns.

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.

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.

Step 1: Creating a 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 give a header for the 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.

Step 2: Calculating 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, select cell J12 and write down the below mathematical formula in that cell.

=(D12*E12)+(F12*G12)+H12+I12

Where, D12 = units of materials,
E12 = per unit cost of materials,
F12 = units of hours,
G12 = per unit cost of hours,
H12 = the fixed cost of the project, and
I12 = 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.

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 the cell. After that, press ENTER on your keyboard.

=J12-K12

Where J12 is the Budgeted amount and K12 is the actual amount of the project.

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(H12:H14)

Hence, simply press ENTER on your keyboard. As a result, you will get the total Fixed Cost of function 1. Which is $11,300.00.

Further, AutoFill the SUM function to the rest of the cells under function 1 in row 15 to calculate the total miscellaneous cost, total budgeted cost, total actual amount, and total variance.
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.

=H15+H19+H23

Where H15 = the total fixed cost of function 1,
H19 = the total fixed cost of function 2, and
H23 = 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.

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

Step 3: Making 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.

=J24

Where J24 is the total budgeted cost of the project budget.

After that, press ENTER on your keyboard. So, you will get the total budgeted cost of the project budget. Which is $107,787.50.

Hence, select cell E6 and write down the below formula in that cell.

=K24

Here, K24 is the total actual cost of the project budget.

After that, press ENTER on your keyboard. So, the total actual cost of the project budget is $98,500.00.
Further, select cell E7 and write down the below formula in that cell.

=L24

Where L24 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.
Finally, we will get the summary of the project budget.

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.

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.

Download Practice Workbook

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

Project Budgeting.xlsx

Conclusion

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

<< Go Back To How to Create a Budget in Excel | Excel For Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!