How to Create a Project Budget in Excel

Here’s an overview of the dataset:

Introduction to Project Budget

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

The dataset below contains information about the Project Budget of the XYZ group. Create a project budget using the SUM function and a mathematical formula.

Step 1 -Creating a Project Budget Header

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

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.

Step 2 – Calculating the Project Budget

To calculate 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.

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.

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.

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.

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.

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

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

You can use this workbook as a template to calculate a 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. Enter your values. It will calculate the total budgeted cost, total actual cost, and variance of the project budget.