In this video, you will use formulas to calculate whether your project is under, on, or over budget.
This is commonly called the Net, or the difference between the estimated and actual costs budgeted.
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.
A formula is a mathematical relationship expressed in numbers, such as a subtraction problem.
A formula prevents you from manually subtracting, adding, or otherwise calculating a list of numbers, saving you time and limiting errors.
The result of the formula appears in the “Net” column.
Formulas in Google Sheets always start with an equals sign.
After typing in 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.
Now, it’s your turn: Enter the Actual Costs for the items in your project, using real or imagined numbers.
Calculate the difference between the estimated and actual costs using a subtraction formula.
Copy the formula to the entire Net column.
1. Keeping Track of Project Finances
2. Set Up a Budget and Estimate Costs
3. Create a Category Menu
4. Compare Expenses to Cost Estimates
5. Use Functions to Calculate Total Costs and Contingency
6. Turn Negative Numbers Red with Conditional Formatting
7. Use Functions to Determine Total Budget by Category
8. Analyze Data to Adjust Project Goals and Purpose
9. Budgeting for Work and Personal Projects
10. Activity Reflection (Budget)