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.
Read More: How to Create an Operating Budget in Excel (with Detailed Steps)
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.
Read More: How to Calculate Remaining Budget Using Formula in Excel
Similar Readings
- How to Prepare a Sales Budget with Example in Excel
- Prepare a Vacation Budget in Excel (with Easy Steps)
- How to Make Food and Beverage Budget in Excel
- Create Actual Vs Budget Variance Reports in Excel
- How to Make a Wedding Budget in Excel (2 Suitable Methods)
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.
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.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Related Articles
- How to Create a Zero Based Budget in Excel (With Easy Steps)
- How to Create a Budget vs Actual Chart in Excel
- Create Retirement Budget Worksheet in Excel (with Easy Steps)
- How to Create Renovation Budget in Excel (2 Simple Methods)
- Create a Department Budget in Excel (with Detailed Steps)
- How to Create a 50-30-20 Budget Spreadsheet in Excel