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%
1 Like

@joern. thanks for the thoughtful analysis! sorry for my delay in responding…busy week. I did the same analysis and got similar results - even more variable than yours even! Ironically there are only a few “run rate” type categories. So I totally agree that analysis doesn’t make much sense. But maybe the other thought is that I have categories where I WILL spend the budget - its just a timing thing. Ex: Christmas gifts, auto registration taxes, property taxes… but there are other categories that are lumpy and not dependent on how many months are behind or in front. Something like Clothes for example is dependent on what seasons are left. Spending for our family tends to revolve around spring and winter. So if our spend was much less in the spring then it won’t be more in the winter. Really makes me think I may look harder at these categories and just try to budget them better based on previous year. Anyway, interesting dialogue and I appreciate you sharing!

@mjsef thanks for sharing!