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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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”