In this article, you will learn about three quick steps on how to create a construction budget in Excel. We will be using the Microsoft 365 version; however, you can use any version of Microsoft Excel and follow this tutorial.
Construction Budget
A budget means planning the income and expenses for a particular time period. When the budget is related to the property development, then we know this as the construction budget. Without a good construction budget, the project may not be completed within the stated time. This budget includes lots of expenses, for example, labor costs, financing costs, utilities costs, etc. If the owner or developer sells the property, then it will generate income. Moreover, the property can be rented too.
Now, in our case, we will be considering a residential construction budget. The owner will leave the building after completion, and he or she will not rent the property for a long time. Therefore, only expenses will be considered in this construction budget.
There are three easy steps to create a construction budget in Excel. Firstly, we will set up the dataset. Secondly, we will enter the estimated and actual values. Lastly, we will create a clustered bar chart to visualize the difference between the forecasted and actual values. Additionally, the estimated values are not based on any statistical models, and you are free to create a basis for the estimation and modify the budget.
Step 1: Setting Up Dataset to Create a Construction Budget in Excel
We will enter the required columns and fields in this first step. There will be four columns consisting of “Activity”, “Estimated”, “Actual”, and “Difference”. Moreover, we will calculate the total values of the estimated budget and actual budget in row 24, using the SUM function. So, we have created a field “Total” in the dataset.
- Firstly, type the following fields on the Excel file.
Activity Explanation
We have considered a broad category for creating the construction budget. These activities include the following sub-categories:
- Plan review fees, various permits, surveys, impact fees, administrative costs, financing costs, legal fees, and engineering fees are examples of service fees.
- Site preparation consists of: demolition fee, dust control, site surface protection, garbage removal, portable toilet, temporary energy, and equipment rental etc.
- Soil tests, septic system, pump system are included in the on-premise water & sewer task.
- Gas and electrical permits, oil, telecom, water, and sewer fees are in the utilities task.
- Excavation activity consists of blasting, fill, dirt or stone removal, foundation excavation, drainage system, backfill etc.
- Then, foundation activity includes pires, steel reinforcing, vents construction, bulkheads, etc.
- Next, the elements of paving are patios, chimneys, driveways, walkways, etc.
- After that, seals, floor framing, sheathing, nails and screws, labor cost, etc. are included in the rough framing task.
- Then, exterior sheathing, brick and stone veneer, window and door trim, labor cost etc. are in the exterior activity.
- Windows & doors task comprises door slabs, windows, garage door, etc.
- Next, plumbing consists of drain, gas and water supply piping, faucets, toilets, sinks, tubs, showers, etc.
- Electrical tasks consist of wiring, lighting fixtures, doorbell system, smoke alarms, security systems, etc.
- After that, the heat pump, central air conditioner, air filter, boiler, radiators, HVAC controls, etc. are included in the HVAC task.
- All the remaining types of insulations are in the insulation task.
- Next, walls, ceilings, decorative plaster, labor, etc. are in the drywall & plaster activity.
- Door knobs, wood flooring, carpeting, stairs, painting, vinyl flooring, etc. are included in the interior task.
- Then, kitchen, medicine, and bath cabinets, ceramic tiles, mirrors, towel hangers, etc. are in the kitchen and bath activity.
- Different types of porches and decks are in the porches task.
- Lastly, the appliances task consists of the refrigerator, microwave oven, dishwasher, washing machine, etc.
Read More: How to Create a Project Budget in Excel
Step 2: Entering the Values
We will be typing the values for the estimated column first. Then, after the completion of the construction project, we will type the actual values. For the sake of this article, we are assuming the project complete, and it took 6 years. Now, we will compare the difference by subtracting both values.
- To begin with, type the estimated and actual values in column C and D respectively.
- Then, select the cell range E5:E23, type the following formula and press CTRL+ENTER. We are subtracting the actual values from the estimated values. You can see the process in the following animated image.
=C5-D5
- Next, type this formula in cell C24 and using the Fill Handle, auto fill the formulas to the next two cells. Using this formula, we are calculating the total values.
=SUM(C5:C23)
- Lastly, the final output will look similar to this.
Step 3: Creating Charts
In this last step, we will show how you can visualize the difference between the estimated and actual values. We will insert a clustered bar chart to demonstrate that. Most of the time, Excel can suggest the best chart for your data type, and we will follow the Recommended Charts feature to create the chart. Without further ado, let us show you the steps.
- Firstly, select the cell range B4:D23.
- Secondly, from the Insert tab, select Recommended Charts.
- So, the Insert Chart dialog box will pop up.
- Then, select Clustered Bar and press OK.
- By doing so, we will get a basic chart.
- Lastly, we will insert a chart title, change the color of the bars, and enlarge the graph. From this graph, we can see the biggest discrepancy is on the plumbing task.
Read More: How to Create an Operating Budget in Excel
Download Practice Workbook
You can download the Excel file from the link below.
Conclusion
We have shown you three quick steps on how to create a construction budget in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible.
