Year End Summary

Google Sheets Show & Tell
1

image
image1574×1192 152 KB

Overview

It was such a pleasure to meet so many Tiller customers at our Atlanta Meetup last night.

I particularly enjoyed talking to longtime-customer Kevin Q who asked if there was a graphical end-of-year template that showed how a year closed out. Kevin knew the Yearly Budget had these numbers but was hoping for something more graphical and also that allowed comparisons to prior years. (Kevin has one Tiller spreadsheet with several years of history.)

In a some downtime between sessions today, I worked up the concept below. I hope it helps Kevin… and others wanting to see a summary of 2024 as we near the end.

Installation

Installation is quick and easy:

  • Open the Year End Summary master
  • In the template master, copy the sheet named Year End Summary into your personal Tiller spreadsheet

The template relies on data from the Categories and Transactions sheets. There are no additional dependencies.

Usage

The sheet is pretty straightforward. There are a few user-controls colored green. Goof around with them and watch the charts change.

Permissions

Please use and build on this template as you see fit.

2

Hi @randy ,

Maybe I’m not fully understanding the second chart in the worksheet for the comparison year. The calculations don’t seem to add up. Is the second chart intended to show a delta amount to the first year selection or is it intended to show the values for that year in order to compare the charts?

I changed the formula for BG3 to:

=LET(UNFILTERED,sort(if($B$3=$G$2,unique(filter(AR3:AR,AQ3:AQ="Expense")),if($B$3=$G$3,unique(filter(AR3:AR,AQ3:AQ="Income")),"Cashflow")),1,FALSE),if(or(isblank($E$3),iserror(match($E$3,$G$7:$G))),UNFILTERED,$E$3))

I also changed the formula for BH3 to:

=map($AB3:$AB,
  Lambda(
    CAT,
    if(isblank(CAT),
      iferror(1/0),
      iferror(
        if($B$3=$G$4,
          SUMIFS(xlookup(text(BH$1,"0"),$AT$2:$BE$2,$AT$3:$BE),$AQ$3:$AQ,
            "Expense",
            $AP$3:$AP,"<>Hide")+
          sumifS(xlookup(text(BH$1,"0"),$AT$2:$BE$2,$AT$3:$BE),
            $AQ$3:$AQ,
            "Income",
            $AP$3:$AP,"<>Hide"),
        if($B$3=$G$2,-1,1)*SUMIFS(xlookup(text(BH$1,"0"),$AT$2:$BE$2,$AT$3:$BE),if($B$3=$G$4,$AQ$3:$AQ,$AR$3:$AR),
          CAT,
          $K$3:$K,"<>Hide"))))))

I then dragged BH3 to BS3.

Perhaps I’m missing the original intent.

Cheers,

AHB

3

Thanks for the quick and detailed feedback, @ahb. The intent of the second chart was a like comparison from a different annual period. The values are not intended to be deltas just actuals from a different year.

I knocked this out pretty quick. Can peek again in the AM.

Randy

UPDATE Oct 25, 2024 10:30AM EST - I checked on this just now @ahb and confirmed the numbers are totaling as I intended. It sounds like you had hoped it rendered more of a difference calculation? If you think there are still outstanding functional problems with the template, can you let me know?

1 Like
4

It works great Randy. Thank you for developing the YE Summary. Being a visual learner, I grasp budget numbers much faster visually than looking at the raw numbers. So, the more graphs, charts and dashboards the better!

5

That’s good to hear, @Clint.C. Thanks for the feedback.