Adding Progress Bars to the Savings Budget

Google Sheets Show & Tell
,
1

This post from @catpatriciamorgan got me thinking about adding progress bars to the Savings Budget.
So, I gave it a try in my personal spreadsheet…

If you’re interested in implementing this…

  1. Insert a new column between ACTUAL and AVAILABLE. I called it PROGRESS in the header.
  2. Insert this formula in F8 and drag/expand it down to the bottom.
=IFERROR(
  IF(or(N8="Expense",M8="Expense",and(M8="Type",B8="EXPENSE")),
    IF(E8+1<=D8+C8,  
      SPARKLINE({if(isblank(E8),0,E8)},{"charttype","bar";"max",D8+C8;"color1","#2e86de"}), 
      SPARKLINE({D8+C8,E8-D8-C8},{"charttype","bar";"max",E8;"color1","#69c569";"color2","#de4c60"})),
    IF(or(N8="Income",M8="Income",and(M8="Type",B8="INCOME")),
      IF(E8<=D8-C8, 
        SPARKLINE({if(isblank(E8),0,E8),D8-C8-E8},{"charttype","bar";"max",D8-C8;"color1","#2e86de";"color2","#de4c60"}),
        SPARKLINE(E8,{"charttype","bar";"max",E8;"color1","#69c569"})),
    IFERROR(1/0))))

SavingsProgress
SavingsProgress1500×971 194 KB

Let me know if this works for you or if you have any improvements.

2

@randy

Thank you! This is so great! One thing though is that the formula needs there to be values for each category, but (in the case of the screenshot) there are no values for “Cash” in Budget and Actual because I did not budget any cash for this month. Any way to get the progress bar to be blank in months that I do not budget for cash?

Screen Shot 2021-01-27 at 6.08.45 PM
Screen Shot 2021-01-27 at 6.08.45 PM2168×514 76.7 KB

3

Wrap the whole formula is an iferror( formulas)

1 Like
4

@richl
Thank you!! That worked!

1 Like
5

Good fix, @richl. I made that change in the original solution post.

If people like the progress bars, we can add them to the master. Let me know.

2 Likes
6

The month kicked over to February and I noticed a bug in the Income section when rollovers are present. I have updated the formula in the original post. I recommend you update your sheets.

Please let me know if further changes are needed.

7

Hey everyone! I just wrote the progress bars into the Savings Budget master so now this feature is readily available to everyone.

I also added some new top-level sparkline headers similar to the ones in the Monthly Budget.

These changes are noted in a new Tiller Money Labs Roundup.

Best,
Randy

1 Like
8

Loving the updates, @randy! One thing I am noticing, however, is that the sparklines don’t appear to respect hidden categories, which is limiting their usefulness to me. I’ll take a look into it if I get a chance, but I wanted to throw that out there in case anyone else was running into the same issue.

9

Good catch, @cculber2. (I don’t use many hidden categories so I didn’t notice this.)
I think it is a relatively easy fix. Just need to touchup a few formulas in J11:K28.
I’ll add it to my list.

10

It’s kind of a fussy problem, @cculber2, because that J11:K28 data range feeds both the sparklines and also the budget health calculations in J1:K3. (Previously, the section only fed budget health.)

Is it possible that hidden categories should be included in budget health but not the sparklines? (I’m glad, as someone who helped develop the budget health concept, I can ask you this…)

11

Is it possible that hidden categories should be included in budget health but not the sparklines?

My first thought is “no”, possibly on both counts, as I don’t actually put in budget amounts for those categories. I’m tracking them because they are real transactions, such as my employer’s Roth 403B contribution and personal contribution paycheck deduction, but they have no bearing on my actual budget. Similarly, my property taxes deduction from escrow shows on my mortgage account ledger and is a very real transaction, but it has no bearing on my budget because my escrow is funded by my monthly mortgage payment. There is value in knowing those numbers, but they don’t interact with my budget. Of course, investments and mortgages are probably some of the most complicated situations to consider, so take my thoughts with a grain of salt.

12

I just updated the Savings Budget master (to version 0.73), @cculber2.

  1. I changed six formulas in the J11:K28 range to respect the Hide setting for the header metrics values.
  2. I noticed that the main Savings column has had the same issue. I updated the formula in C12 to check the Hide setting when summing Group and Type savings in column C.

Let me know if you notice any other issues.

1 Like
13

Hey @randy, thanks for the update! I applied the new version and we’re getting close, but it looks like there are still a few tweaks necessary. I noticed that the Actuals, Expense formula in K22 and Actuals, Income formula in K23 weren’t using the Hide filtering logic, so I copied that into those SUMPRODUCT() formulas.

After I made that change, a more subtle issue was revealed. It seems that because of the way the above formulas are calculated, categories marked as Debt do not get summed properly as their Period Actuals in AI11:AI will always net out to $0.00. This is creating yet another discrepancy between the sparkline and category rollup values for Expense Actual.

Sorry that my contributions seem to always end up pointing out errors. :frowning: I really do appreciate everything you’re putting into these optimizations.

14

I did some more investigation, and it seems the solution was far simpler than I was imagining. I was able to update the Actuals, Expense formula in K22 and the Actuals, Income formula in K23 to sum the Modified Actuals in AO11:AO rather than the Period Actuals in AI11:AI. Making this change yielded the expected expense and income values in the sparkline.

Actuals, Expense, K22

=sumproduct(($AE$11:$AE="Expense"),($AG$11:$AG<>"Hide"),$AO$11:$AO)

Actuals, Income, K23

=sumproduct(($AE$11:$AE="Income"),($AG$11:$AG<>"Hide"),$AO$11:$AO)

On a side note, I do not recommend adjusting the formulas for Actuals Prior to Period in P10 and Actuals in Period in S10, as it throws off the ACTUAL calculation in E12, and possibly elsewhere.

I also noticed a polarity issue for Net Budgets in K13 for all periods. The formula is set to Budget, Expense - Budget, Income whereas the current period calculates it in K21 as Budget, Income - Budget, Expense. I made a simple correction of swapping the terms.

Net Budgets, K13

=K12-K11

15

Thanks for digging into this, @cculber2.
The changes make sense.

I implemented them in just-published version 0.74.
Let me know if you find anything else requiring modifications to the master.

Always grateful for your help.
Randy

1 Like
16

2 posts were split to a new topic: Adding Progress Bars to the Monthly Budget

17

Well, I’ve been using the Savings Budget happily for quite a while, but am embarrassed to admit I could never quite figure out why there are blue bars. My guess is that they apply when your budget plus your savings is equal or better than the expense for that month, and then the blue bar shows the percentage of that total which you spent. But my question is - why isn’t that just a green bar of the same size?