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 8 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 4 of these rows: ...total estimated cost...
...total actual cost...
...and contingency Bold these headings.
Find the total estimated cost for each of these items using the SUM function.
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.
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.
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.
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.
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.
Just imagine how long it would take you to calculate all those numbers and percentages without the SUM function tool.
Plus, every time a number changed, 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 red.
- Add Total Estimated Costs, Total Actual Costs, Net, and Contingency.
- Use the SUM function to determine totals.
- Create a contingency budget.