How to Create a Project Budget in Excel

Here’s an overview of the dataset:

Step 1 -Creating a Project Budget Header

Choose a header for the summary and project details. Format the headers in the Home tab.

Create Project Budget Header in Excel

Create a header for cost calculation: serial number, particulars, material cost, labor cost, fixed cost, miscellaneous cost, budgeted amount, actual amount, and variance.
Format the headers in the Home tab.

creating a header for cost calculation

Step 2 – Calculating the Project Budget

Divide the project into three functions: Function 1, Function 2, and Function 3.

Calculate the total cost of function 1.

Select J12 and enter the formula below.

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

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.

Press ENTER. You will get the budgeted amount: $15,480.00.

Calculation of Project Budget

AutoFill the formula to the rest of the cells in column J.

Calculate the variance.

Enter the subtraction formula.
Press ENTER.

=J12-K12

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

calculating variance

AutoFill the formula to the rest of the cells in column L.
Calculate the total Fixed Cost of function 1, using the SUM function:

=SUM(H12:H14)

Press ENTER. You will get the total Fixed Cost: $11,300.00.

Calculating total Fixed Cost of Function 1

AutoFill the SUM function to the rest of the cells in row 15 to calculate the total miscellaneous cost, total budgeted cost, total actual amount, and total variance.
Apply the same procedure to Function 2 and Function 3.

Make more functions

Calculate the total cost of the project.

Enter the formula below.

=H15+H19+H23

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.

Press ENTER . You will get the total fixed cost of the project: $29,800.00.

calculating total cost of the entire project

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 – Creating a Summary of the Project Budget

Make a Summary of the Project Budget in Excel

Select E5 and enter the formula:

=J24

J24 is the total budgeted cost of the project budget.

Press ENTER . You will get the total budgeted cost: $107,787.50.

Select E6 and enter the formula:

=K24

K24 is the total actual cost of the project budget.

Press ENTER . The total actual cost is $98,500.00.
Select E7 and enter the formula.

=L24

L24 is the total variance cost.

Press ENTER. You will get the total variance: $9,287.50.

Project Cost Template in Excel

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. Enter your values. It will calculate the total budgeted cost, total actual cost, and variance of the project budget.

Project Cost Template in Excel