Budgeting a Construction Project with Spreadsheet.com

Many construction projects with complex budgets use a system of cost codes to break the overall budget down into a series of line items. Cost codes make it easier for project managers to track KPIs and understand where the project budget is – or isn’t – on track, and gives other project staff a simple way to assign project costs to categories and line items.

In this guide, we’ll take a look at Spreadsheet.com’s Construction Budget template and explore how features like row hierarchies, hierarchy formulas, and conditional formatting can help you build a project budget spreadsheet that reflects your project’s cost code structure. Take a look at our example workbook and follow along, or create a copy for yourself to experiment with.

Use Row Hierarchies to Break Costs into Cost Codes

Cost codes have a hierarchical structure – broad categories of expenses are broken down into subcategories, which in turn are broken down into line items. Spreadsheet.com’s row hierarchies make it easy to reflect this hierarchical structure in your budget workbook. Row hierarchies let you create parent-child relationships between rows in your workbook. Take a look at Rows 5 through 12 below:

The Budget worksheet with cost codes represented with row hierarchies

Row 5 is a parent row (first-order), the highest level of the hierarchy. Row 6 and Row 9 are child rows (second-order), and the rest are grandchild rows (third-order; children of child rows). The hierarchy reflects that of the cost code; codes “1110” and “1120” are line items from code “1100”, which is a subcategory of code “1000”.

Creating row hierarchies in your budget worksheet enables hierarchy formulas to quickly generate budget rollups.

Use Hierarchy Functions to Quickly Calculate Totals and Subtotals

When you create row hierarchies in your workbook, you can use hierarchy functions to call data from within a hierarchy. In our budget workbook, we’re using hierarchy functions to create budget rollup totals in parent rows and subtotals in child rows.

Budget totals and subtotals are calculated with hierarchy functions

Cell E6 uses the formula SUM(DESCENDANTCELLS()) to calculate the sum of all of the cells beneath it in the row hierarchy (Cells E7 and E8) to generate a subtotal of the cost code’s fixed costs. Above it, Cell E5 uses the formula SUM(CHILDCELLS()) to calculate the sum of all of the cell’s direct descendants (E6 and E9) and generate a total of the cost code’s fixed costs.

By using the CHILDCELLS function instead of the DESCENDANTCELLS function, we can avoid double counting the individual line items. These same functions are used for all of the first- and second-order hierarchy rows in Columns C through F.

Spreadsheet.com also supports the ANCESTORCELLS and PARENTCELL functions for calling data from the opposite direction in the row hierarchy.

Use Column Formulas to Generate Summary Statistics

In addition to budget rollups, budgeting a construction project also requires you to calculate additional summary information, like the delta between actual costs and fixed costs. The Column formula data type – one of more than two dozen rich data types unique to Spreadsheet.com – can calculate these values for your entire budget in just a few clicks.

In our budget workbook, Column G - Actual Cost, Column H - Under/Over, and Column I - Delta all use the Column formula data type to calculate these summary values.

With the Column formula data type, you can apply a formula to an entire column of data and format the output as needed

Take a look at the Column formula for Column H - Under/Over. It tests if Column G - Actual Cost is blank and, if not, calculates the difference between the actual cost and the budgeted cost (Column F - Budget). With formatting options in the “Update column” dialog, we’ve specified that negative values appear in red so we can quickly see which line items or categories are over budget.

Using the ISBLANK function in our formula lets us avoid having zero values or error messages when budget values have not yet been entered, like in Rows 39 through 43.

Using the ISBLANK function in your formula can help avoid blank cells or error messages as formula outputs

Whenever a new row is added to your budget, the Column formula column will automatically calculate the same formula as previously for your new row. And like all other functions in Spreadsheet.com, the calculation will automatically update when any changes are made to the underlying data.

Use Conditional Formatting to Analyze Budget Overruns

If you have dozens – or even hundreds – of cost codes, it can be difficult to quickly scan through a budget spreadsheet and identify areas where your project is at risk or already over budget. Using conditional formatting rules can make this task easier by applying a color scale to your budget delta columns, like in Columns H and I of our budget workbook.

Applying conditional formatting rules to your budget worksheet makes it easy to get a visual overview of your current costs

In Column H - Under/Over, we’ve created a conditional formatting rule that applies a white-to-yellow color scale to the column with the midpoint set at 0. With this rule, only negative budget values (cost overruns) will be highlighted, and the tint of the highlighting will reflect how over budget an item is.

Unfortunately for our hypothetical construction project, three out of the four main cost codes have budget overruns. With our conditional formatting rules, it’s easy to dive into each one and quickly see which individual budget items are the most problematic.

By comparing these values to the adjacent cells in Column I - Delta, which has the same conditional formatting rule applied, we can also compare the impact that the dollar-value-overruns have on the budget as a whole. In Row 29, for example, even though our Structural Steel Erection is $2,250 over budget, it only accounts for a 1.15% budget deviation.

With Spreadsheet.com’s sharing and collaboration features, it’s easy to distribute a budget workbook like this to your entire team to keep the entire project staff on track and on budget.

Get Started with Spreadsheet.com

Ready to get started with Spreadsheet.com to budget your next project? Browse through our Template Gallery for more construction templates, as well as templates for budgeting other types of projects. Or, start from a scratch with a blank workbook to begin creating your project budget.

Learn more about managing construction projects with Spreadsheet.com in our guide “Managing a Construction Project with Spreadsheet.com

Sign up — it's free