Using the Budget with Details – Details Pivot Table to compare utility bills/staff development costs Month to Month
In this example, we will use the Budget with Details spreadsheet to compare utility costs on a month to month basis.
When working with specific data, use your options on the settings tab to narrow down your information first. In this case we will use the “List of Object Codes” option.
We will select object codes that will allow us to work with just the codes we need.
|
|
Click on the Details Pivot Table Tab
|
|
Move the Fund and Program fields to the top
|
|
Next let’s create a calculated field called Expenses that will add together both the Encumbrances and the Transactions.
|
|
Click in the pivot table and click on the Options Tab within the PivotTable Tools Tab. Select Fields, Items and Sets and then Calculated Field
|
|
Name the field Expenses.
Enter the formula by selecting the fields from the list at the bottom.
Click OK
|
|
Remove the Budget, Encumbrances and Transactions fields from the pivot table.
|
|
This will leave just the Object, Name and Expenses on the pivot table
|
|
Now we want to compare months – click on the Month field at the top and drag ON TOP OF the EXPENSES heading.
|
|
Filter in the Object column for objects 321, 322, 323 and 324 to view the Power and Utility comparisons.
|
|
This provides a Month to Month comparison of the Utilities.
|
|
THINK…
What if we want to view a comparison of locations for the utilities?
What if we want to sort the locations with the ones having the highest utility expense at the top?
Remove all but the Location and Expenses from the pivot table
|
????
|
To sort the location BASED on the Expense column…
Click in the Location Column
|
|
Click on the Sort icon on the toolbar. Select Descending – but we want it descending based on the SUM OF EXPENSES (rather than sorting the location)
|
|
VOILA!
|
|
When you have narrowed your information down to just a few columns, you can create a chart! Click on Insert, Column Chart and then select a 3-D Column Chart
|
|
|
|