Create Calculated Fields

If you want to know what your average Total Uses are over the past four years, you start by going to the Financial dashboard -> Trend -> Trend Fund (Figure 1).  Since you are focusing on Uses, you will need to exclude Carryforward, Sources, and Ending Balance because these are not part of the four year Total Uses calculation. 

One at a time, hover over the column heading that you want to exclude, and Right click on “Exclude Column.” For this analysis, begin by excluding the Carry Forward column, then the Sources column, and finally the Ending Balance column (Figure 2).

Excluding a column removes it from every year shown (Figure 3).

Now you can begin to calculate the average.  Since 2025-2026 is the current fiscal year and therefore does not represent a full year of Uses, we will want to exclude that from the four year average calculation.

Begin by highlighting the years that you want to include.  In this example we will highlight 2021-2022, 2022-2023, 2023-2024, and 2024-2025 by right clicking on 2021-2022, (hold the click down) and move the mouse to highlight the other years. Alternatively, you can right click on 2021-2022 and click on the SHIFT key on your keyboard, and then right clicking on the other years so all four years are highlighted (Figure 4).

Next right click on one of the highlighted columns, and choose “Create Calculated Item” (Figure 5).

Now you’ll need to name the new column.  We will name our new column “4-YEAR AVE” (Figure 6).

Once you have named the column, select the FUNCTION you want to perform.  In this example we want to calculate an average, so we select “Average” from the drop-down menu and then click OK (Figure 7).

The new column appears with the calculated 4-YEAR Ave Uses (Figure 8).

When planning your budget you may want to review how much on average you spend by category or sub-category over the past four years.  For this example, go to the Financial dashboard -> Trend -> Trend – Actuals (Figure 9).

Since “% of Total” is not relevant to the calculated average, hover over that column and right click “Exclude Column” (figure 10).

You will now only see the Actual/Uses for the fiscal years shown (Figure 11).

To begin adding the calculated average, hover over the first fiscal year (2021-2022 in this example), hold the click down and move the mouse to highlight the other years. Alternatively, you can right click on 2021-2022 and click on the SHIFT key on your keyboard then hit SHIFT on your keyboard and right click on the other fiscal years that you want to include in this calculation. Since 2025-2026 is the current fiscal year and therefore does not include a full year of expenditures, we will not include that year in the four year average calculation (Figure 12).

Then right click and select “Create Calculated Item” (Figure 13).

A dialogue box will appear and you will need to name the new column being added.  In this example we are naming it “4-YR Ave by Category.”  Then, select the Function you want performed for this calculated item.  In this example we are selecting “Average” (Figure 14).

Click on OK and you will have the 4-YR average spend by category (Figure 15).

For budget planning purposes you may want to compare your average spend to the year that you spent the most.  You are able to do this by adding a column with the highest amount spent.

Using our previously created page that includes 4-YR Ave by Category, right click on 2021-2022, hit the SHIFT button on your keyboard and right click on 2022-2023, 2023-2034, and 2024-2025.  Then right click and select “Create Calculated Item” (Figure 16).

A dialogue box will appear and you will need to name the new column being added.  In this example we are naming it “Highest Spend Am’t.”  Then, select the Function you want performed for this calculated item.  In this example we are selecting “Max” for the highest amount spent (Figure 17).

Click on OK and you will have the Highest Spend Amount by category. Note that it will not designate which year the highest amount was spent in, rather it shows what the highest actual amount spent was (Figure 18).

You can now compare your average spend with the highest amount spent over the past four years to see if the difference is significant.

You can view the details behind the calculation by right clicking on the column heading and selecting “View Calculated Item Definition” (Figure 19).

This will show what function was selected (“Average” in this case) and what years were included in the calculation (Figure 20).

Helpful hint: Be sure to save your work as a custom view for easy access later!