Full-year actual+budget forecast sheet

@joern. Love the sheet! I do a similar analysis of my progress during the year but a slightly different way. I look at total spending year to date and then generate a monthly average. Then I assume I will maintain this monthly avg (especially once I get further into the year) so I extrapolate the rest of the year using the monthly avg - rather than using the budget for the remaining months. I also use a similar adjustment column to fix any oddities. Depending on the category one method may be more applicable than the other. Ex: Gas is really run rate based…how much I drive stays pretty constant so that is the best method to calculate. But for something like entertainment, I would rather assume I have the budget left for the remaining part of the year. I may consolidate the ideas and put a flag next to the category to choose the method. Really appreciate you sharing!

@mjsef Interesting idea! I track my expenses across 52 categories. ~70% are pretty even steven, with fairly low variability (every month is pretty similar to the next), 30% are highly seasonal or variable (that once a year life insurance premium, or vacation spending concentrated in a few months per year, or periodic tuition bills etc).

Let me think on how I could build an optional / additional extrapolation mechanism for those even steven categories.

sounds great! Ironically I am at 51 cats…similar breakout as well. It might be interesting to tag the categories as you describe (consistent, seasonal, variable). then actually group those categories together so you can also see a total of each. not sure how hard that would be to do but I think it would be helpful to breakout how each grouping was doing in total. Looking forward to seeing your next rev!

@mjsef

So I looked at my data in more detail. Here is my actual spending over past 9 months (I dont have data in Tiller from before 1/1/20), analyzed for variability.

Coefficient of Variation =standard deviation of actual spend for each category for each of last 9 months / average spend for each category for each of last 9 months.

A CoV of 0 means that the spending is exactly the same each month. A CoV of 50% means that 1 Standard Deviation is 50% of the average spend (e.g., if average monthly spend in a category is $1000, and standard deviation is $500 - so CoV of 50% - 68% of the monthly spending data falls within $500 to $1500 range).

In other words, anything with a CoV of north of say 20% sees very material swings. Which make the forecasting of trend line moves the way you suggest very hard to implement. So unlike my initial assumption, I think at least for my data set I only have a couple of categories that are predictable / trend-extensible and they only represent a tiny fraction of my annual spend.

Is your data very different?

Categories Coefficient of Variation % of Total TTM Spend
1 0% 1.5%
2 12% 0.4%
3 12% 0.7%
4 27% 0.8%
5 29% 12.4%
6 36% 12.1%
7 38% 2.4%
8 43% 0.8%
9 52% 0.7%
10 56% 0.9%
11 62% 0.9%
12 67% 4.4%
13 68% 0.5%
14 68% 2.6%
15 71% 4.5%
16 72% 2.1%
17 80% 1.7%
18 81% 0.2%
19 85% 0.5%
20 94% 0.7%
21 101% 7.6%
22 101% 0.3%
23 105% 2.3%
24 106% 0.6%
25 124% 1.3%
26 125% 0.2%
27 125% 0.1%
28 139% 2.3%
29 142% 1.2%
30 150% 0.0%
31 155% 0.2%
32 163% 0.3%
33 169% 1.0%
34 174% 0.4%
35 176% 0.2%
36 192% 0.3%
37 204% 10.9%
38 207% 1.1%
39 213% 0.0%
40 214% 3.1%
41 238% 4.1%
42 240% 0.9%
43 249% 5.5%
44 267% 2.1%
45 298% 0.5%
46 300% 0.9%
47 300% 0.2%
48 300% 0.9%
49 300% 0.5%
50 300% 0.4%