Skip to content

5. Use Functions to Calculate Total Costs and Contingency

Playback Speed:
Transcript

In this video, you will add a section to your spreadsheet that summarizes your budget’s cost totals in order to get a clear picture of your project’s financial status.

You will see whether you’re on track or if expenses are putting project goals in jeopardy.

You also can clearly show project expenditures to a supervisor for approval.

To start, insert eight new rows at the top of your spreadsheet to create the new summary section.

Right click on the first cell of the sheet, then select “insert row.”

Repeat this multiple times.

In column A, label four of these rows: Total Estimated Cost, Total Actual Cost, Remaining Money, and Contingency.

Bold these headings.

Find the total estimated cost for each of these items using the SUM function.

A function is a pre-made formula that you can insert into your spreadsheet.

The SUM function adds a range of numbers.

You’ll use this function to total your project’s estimated costs.

To start, click the first cell in the “total estimated cost” row.

Then, type an equals sign.

To insert the SUM function, type “S” “U”.

A menu of functions will appear underneath the cell.

Select SUM.

Next, select the entire column labeled Estimated Cost.

The cells you highlight automatically show up between the parentheses in the function.

Edit the formula to begin at the row on which the list of items begins.

In this example, the first list item begins in column C, row 10, so the cell reference is C10.

Hit enter.

The cell now contains the total estimated costs for your project, from the first row of items until the end of the column.

If you add more line items later, the formula will work for those items too.

Use the SUM function again to total the numbers in the Actual Cost column next to Total Actual Cost.

Then, repeat those steps for the total Remaining Money, using the Net column data.

The Net is the difference between the Estimated and Actual budget totals.

The Remaining Money is how much money is left in your budget after finding the net difference of all your listed items.

Finally, add a contingency to your budget sheet.

It is a good idea to leave room in a budget, rather than trying to account for every dollar right away.

This is called “contingency planning.”

The contingency is a percentage of the total budget that is held in reserve for unexpected issues.

Type a formula in the “Contingency” cell.

For your contingency calculation, use a multiplication formula.

Fifteen percent is a reasonable contingency for many projects.

To determine fifteen percent of a number, multiply it by 0.15.

Start with an equals sign.

Then, click on the cell containing the total estimated costs.

To multiply, hold shift, and press 8 to create an asterisk.

Finally, type the number you are multiplying by to determine the percentage.

When you hit enter, you have the total amount of contingency you must plan for your project.

Nice work!

Just imagine how long it would take you to calculate all those numbers and percentages without the SUM function tool.

You would have to redo all the calculations.

Using a spreadsheet saves time and effort and reduces the chance of making a mistake.

By adding functions to your spreadsheet, you have created a single place where you can quickly assess the financial health of your project.

In the next video, you will use conditional formatting to make negative numbers stand out in in red.

Now, it’s your turn: Add a section in your sheet for Total Estimated Costs, Total Actual Costs, Remaining Money, and Contingency.

Use the SUM function to determine the totals for each row.

Create a contingency budget.

Next
Instructions
  1. Add Total Estimated Costs, Total Actual Costs, Net, and Contingency.
  2. Use the SUM function to determine totals.
  3. Create a contingency budget.