4. Compare Expenses to Cost Estimates

Playback speed:
Transcript

In this video, you will use formulas to calculate whether your project is under, on, or over budget.

This enables you to quickly view your project’s financial status as it progresses.

To start, fill in the actual cost column with numbers.

If this was a real project, you would adjust the numbers in this column according to the project expenses.

For the purpose of this exercise, enter any numbers in the actual cost column.

Some of the numbers should be higher than the estimated cost for that line item, some should be lower than the estimate, and some should be exactly the same.

Format the numbers in the actual cost column as currency.

To determine the difference between the estimated and actual costs for the first line item on your sheet, use a formula.

The result of the formula appears in the Net column.

Formulas in Google Sheets always start with an equals sign.

After typing an equals sign, enter your formula.

In this case, you want to find out if your actual costs are above or below your estimated costs.

So, subtract the actual cost number from the estimated cost number.

The formula is cell C2 minus cell D2.

When you hit enter, the formula subtracts one number from the other.

The number in the Net column represents the difference between the estimated cost of the project line item and what was actually spent.

To copy this formula to the entire Net column, click on the cell containing the formula.

Then, grab the handle in the lower right-hand corner of the cell, hold it, and drag it down the column until you reach the bottom of your line item list.

When you release the handle, a list of totals appears in the Net column, some positive, some negative.

Click into one of the cells to make sure the formula copied as expected.

Although spreadsheets are powerful tools, you should always double-check your work.

The formula copied itself down the column, automatically updating cell references each time to indicate the correct cell.

Finally, make sure the numbers in the Net column are formatted as currency..

Formatting your spreadsheet by adding specific numerical categories, dates, or fonts allows you to customize its appearance and quality.

Formatting also draws attention to specific sections so they convey information more clearly.

Instructions

  1. Record the actual costs.
  2. Calculate the difference between the estimated and actual costs using a subtraction formula.
  3. Copy the formula to the entire Net column.