@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!
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|